Python操作MySQL数据库实例详解【安装、连接、增删改查等】

本文实例讲述了python操作mysql数据库。分享给大家供大家参考，具体如下：

1、安装

通过python连接mysql数据库有很多库，这里使用官方推荐的mysql connector/python库，其官网为：。

通过pip命令安装：

pip install mysql-connector-python

默认安装的是最新的版本，我安装的是8.0.17，对应mysql的8.0版本。mysql统一了其相关工具的大版本号，必须相同或更高才可以兼容。例如我使用的是mysql8.0，如果使用低于8的mysql-connector就会报错。事实上也是这样，在某些旧的文档中提示安装pip install mysql-connector，就会安装较低的版本，在连接mysql时，会报错如下：

mysql.connector.errors.notsupportederror: authentication plugin 'caching_sha2_password' is not supported

这是由于mysql8.0使用了use strong password encryption for authentication即强密码加密，而低版本的mysql-connector采用旧的mysql_native_password加密方式，导致无法连接，因此注意使用和数据库相兼容的版本。

2、连接

可以通过connector类的connect()方法进行数据库的连接，传入服务器、端口号、用户名、密码、数据库等参数，其中服务器与端口号可省略，默认为localhost:3306。

import mysql.connector
db = mysql.connector.connect(
  host='localhost',
  port='3306',
  user="root",
  password="123456",
  database="test"
)

3、数据库、表操作

对数据库、数据表的操作属于模式定义语言(ddl)，所有ddl语句的执行都是依赖于一个叫cursor的数据结构进行操作的。通过从connect对象中获取cursor对象后就可以进行数据库、表的相关操作了。例如创建一个数据库、数据表

# 获取数据库的cursor
cursor = db.cursor()
# 创建数据库
cursor.execute("create database mydatabase")
# 创建数据表
dbcursor.execute("create table customers (name varchar(255),address varchar(255))")
# 修改表操作
dbcursor.execute('alter table customers add column id int primary key auto_increment')
# 查询并打印数据库中的所有表
cursor.execute("show tables")
for table in cursor:
  print(table)

4、增删改

插入、删除、修改操作依旧是通过cursor对象来实现，通过cursor的execute()方法执行sql操作，第一个参数是要执行的sql语句，第二个参数是语句中要填充的变量。

在执行完所有的sql操作后记得要通过数据库对象的commit()将操作事务提交到数据库，如果需要撤销则通过rollback()方法回滚操作。

sql语句中的变量可以用%s的形式作为占位符，然后再以python中元组的形式在执行时将变量填入，如下所示：

值得注意的是无论是什么类型的数据在传入时都被当做字符串类型，然后在执行sql操作时会将字符串转化为相应的类型，因此此处的占位符都是%s，而没有%d、%f等。

# 要执行的sql语句
sql = "insert into customers (name, address) values (%s, %s)"
# 以元组的形式填入数据
val = ('mike', 'main street 20')
# 执行操作
cursor.execute(sql, val)
# 提交事务
db.commit()

也可以用python中字典的形式填充变量，在sql语句中的占位符需要使用对应的变量名

# 在sql语句中指明变量名
sql = "insert into customers (name, address) values (%(name)s, %(address)s)"
# 以字典的形式填入数据
val = {
  'name': 'alice',
  'address': 'center street 22'
}
cursor.execute(sql, val)

如果需要一次插入多条数据，可以使用executemany()方法，将多条数据以数组的方式传给第二个参数。

通过cursor的rowcount属性可以返回成功操作的数据条数，lastrowid属性是最后一个成功插入的行的id

sql = "insert into customers (name, address) values (%s, %s)"
# 以数组的形式填充数据
val = [
 ('peter', 'lowstreet 4'),
 ('amy', 'apple st 652'),
 ('hannah', 'mountain 21'),
]
cursor.executemany(sql, val)
print("成功插入%d条数据,最后一条的id为:%d" % (cursor.rowcount, cursor.lastrowid))

修改、删除数据的方法与插入类似，只需要把对应的sql语句和变量值传给execute()函数即可。可以看出mysql-connector库的操作是非常贴近原生sql语言的。

# 修改数据
sql = "update customers set address=%s where name=%s"
val = ('center street 21', 'mike')
cursor.execute(sql, val)
# 删除数据
sql = "delete from customers where name=%s"
val = ('hannah',)
cursor.execute(sql, val)

5、查询

执行查询操作和之前类似，都是通过execute()执行对应的sql语句，在执行时将相应的数据填入即可。查询结束后，结果集会保存在cursor当中，可以直接把cursor当作迭代器iterator来进行展开取得结果集中每条数据的对应字段。也可以通过cursor的fetchall()、fetchone()方法取得所有或一条结果集。

# 查询customers表中id介于6到8之间的数据并返回name、address字段
query = "select name,address from customers where id between %s and %s"
cursor.execute(query, (6, 8))
# 循环取出结果集中的每条数据并打印
for (name, address) in cursor:
  print("%s家的地址是%s" % (name, address))
# 输出结果为：
# peter家的地址是lowstreet 4
# amy家的地址是apple st 652
# hannah家的地址是mountain 21

通过原生的sql语句可以进行更为复杂的查询操作，例如通过where设置查询条件、order by进行字段排序、limit设置返回结果条数、offset查询结果集的偏移、join进行表连接操作