一、数据库驱动介绍
任何应用都离不开数据，所以在学习 Python 的时候，当然也要学习一个如何用 Python 操作数据库了。MySQLdb 就是 Python 对 MySQL 数据库操作的模块之一。在 Python 2 时代 MySQLdb 库可以说是标配，但是很遗憾目前 MySQLdb 还不支持 Python 3，官方说即将支持，但是市场已经没有了。
目前 Python 3 操作 MySQL 的驱动常用的有 pymysql 和 mysqlclient，其中 mysqlclient 是基于 MySQLdb 做的升级版本，支持 Python 3，以及修复了一些 Bug，用法跟 MySQLdb 基本相同。mysqlclient 的作者就是 pymysql 的作者，Github 地址：https://github.com/PyMySQL，反正这几个都学一学也没什么关系。
使用这种数据库接口大多是就是执行连接数据库 -> 执行query -> 提取数据 -> 关闭连接这几个步骤。pymysql 提供比较关键的对象，分别是 Connection、Cursor、Result，使用方式上与 MySQLdb 或 mysqlclient 没什么差别。
在 CentOS 系统下使用 pip 安装 pymysql 库即可。
下面简单学习一下这几个关键对象。
二、Connection对象
pymysql.connections.Connection(host=None, user=None, password=”, database=None, port=0, unix_socket=None, charset=”, sql_mode=None, read_default_file=None, conv=None, use_unicode=None, client_flag=0, cursorclass=<class ‘pymysql.cursors.Cursor’>, init_command=None, connect_timeout=10, ssl=None, read_default_group=None, compress=None, named_pipe=None, no_delay=None, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map={}, read_timeout=None, write_timeout=None, bind_address=None, binary_prefix=False)
获得这个类的实例的正确方法是调用 connect() 方法，建立到MySQL数据库的连接，重要的常用参数如下：
- host – 数据库服务器所在的主机。
- user – 登录用户名。
- password – 登录用户密码。
- database – 连接的数据库。
- port – 数据库开放的端口（默认: 3306）。
- bind_address – 当客户端有多个网络接口时，请指定连接到主机的接口，参数可以是主机名或IP地址。
- unix_socket – 使用unix套接字而不是tcp/ip。
- charset – 连接字符集。
- sql_mode – 默认SQL模式。
- read_default_file – 指定my.cnf文件路径，以便从[client]部分读取参数。
- conv – 要使用的转换字典，而不是默认值。
- use_unicode – 是否默认为unicode字符串，对于Py3k，此选项默认为true。
- client_flag – 发送到MySQL的自定义标志。
- cursorclass – 使用自定义的游标类。
- init_command – 建立连接时要运行的初始SQL语句。
- connect_timeout – 建立连接超时时间。（默认: 10，最小: 1，最大: 31536000）
- read_default_group – 从配置文件中读取组。
- compress – 不支持
- named_pipe – 不支持
- autocommit – 设置自动提交模式，不设置意味着使用数据库默认。（默认值: False）
- local_infile – 是否启用“LOAD LOCAL INFILE”命令的使用。(默认值: False)
- max_allowed_packet – 发送到服务器的数据包的最大大小 (以字节为单位，默认值: 16MB)，仅用于限制小于默认值 (16KB) 的 “LOAD LOCAL INFILE” 数据包的大小。
- defer_connect – 不要显式连接建设，等待连接调用。（默认值: False）
- auth_plugin_map – 自定义认证插件，具体看文档。
- db – 连接数据库别名（兼容 MySQLdb 库）
- passwd – 密码输入别名（兼容 MySQLdb 库）
- binary_prefix – 在bytes和bytearray上添加_binary前缀（默认: False）
创建一个 Connection 对象：
|
1 2 3 4 5 6 7 8 9 10 |
import pymysql conn = pymysql.connect( host='10.10.0.109', port=3306, user='mha', password='123456', database='sbtest', charset='utf8' ) |
这个Connection对象通过cursor方法查询返回的数据时tuple类型，如果想以字典类型输出创建Connection对象时需要加入这么一个参数：
|
1 2 3 4 5 |
from pymysql.cursors import DictCursor conn = pymysql.connect( .... cursorclass=DictCursor ) |
Connection对象提供了如下方法：
| 方法 | 描述 |
| begin() | 开启事务 |
| commit() | 提交事务 |
| cursor(cursor=None) | 创建一个游标用来执行语句 |
| ping(reconnect=True) | 检查连接是否存活，会重新发起连接 |
| rollback() | 回滚事务 |
| close() | 关闭连接 |
| select_db(db) | 选择数据库 |
| show_warnings() | 查看warning信息 |
三、Cursor对象
建立完Connection对象后，就可以创建Cursor对象，如下示例：
|
1 2 3 4 5 6 7 |
try: with conn as cursor: sql = "" cursor.execute(sql) affected_row = cursor.fetchall() finally: conn.close() |
pymysql支持with语句，所以一般就直接使用with语句。
Cursor对象支持的方法：
| 方法 | 描述 |
| close() | 关闭游标。 |
| execute(query, args=None) | 执行单条语句，传入需要执行的语句，是string类型；同时可以给查询传入参数，参数可以是tuple、list或dict。执行完成后，会返回执行语句的影响行数，如果有的话。 |
| executemany(query, args) | 执行多条INSERT语句，传入需要执行的语句；同时可以给查询传入参数，参数是一个mappings序列。执行完成后，会返回执行语句的影响行数，如果有的话。 |
| fetchone() | 获取下一行数据。 |
| fetchall() | 获取所有数据。 |
| fetchmany(size=None) | 获取几行数据。 |
| read_next() | 获取下一行数据。 |
| callproc() | 用来调用存储过程。 |
| mogrify() | 参数化查询，防止SQL注入。 |
| scroll(num,mode) | 移动游标位置。 |
创建一张测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE `user` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user select 1,'user1'; insert into user select 2,'user2'; insert into user select 3,'user3'; insert into user select 4,'user4'; insert into user select 5,'user5'; insert into user select 6,'user6'; insert into user select 7,'user7'; insert into user select 8,'user8'; insert into user select 9,'user9'; |
- 查询数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
#!/usr/bin/env python #coding:utf8 import pymysql conn = pymysql.connect( host='10.10.0.109', port=3306, user='mha', password='123456', database='sbtest', charset='utf8' ) cursor = conn.cursor() # 执行SQL; sql = "select * from user" cursor.execute(sql) # 打印影响行数(数据就是返回数据行数); rs = cursor.rowcount print("打印影响行数:",rs) # 打印第一行的数据; rs = cursor.fetchone() print("打印第一行的数据:",rs) # 打印从第二行起的三行数据; rs = cursor.fetchmany(3) print("打印从第二行起的三行数据:",rs) # 打印剩下的所有行; rs = cursor.fetchall() print("打印剩下的所有行:",rs) cursor.close() conn.close() |
运行程序，结果如下：
|
1 2 3 4 5 |
$ python connection.py 打印影响行数: 9 打印第一行的数据: (1, 'user1') 打印从第二行起的三行数据: ((2, 'user2'), (3, 'user3'), (4, 'user4')) 打印剩下的所有行: ((5, 'user5'), (6, 'user6'), (7, 'user7'), (8, 'user8'), (9, 'user9')) |
我们看到，每次使用fetch方法，都是在上一次fetch方法执行的结果的尾部开始。
- 事务操作
对于MySQL来说，如果使用支持事务的存储引擎，那么每次操作后，commit是必须的，否则不会真正写入数据库，对应rollback可以进行相应的回滚，但是commit后是无法再rollback的。commit可以在执行很多sql指令后再一次调用，这样可以适当提升性能。
虽然MySQL默认是开启自动提交的，也就是说执行了DML操作后不需要commit动作。但是pymysql默认是没有开启自动提交事务，所以我们就必须手动提交或回滚事务。
基本操作如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
#!/usr/bin/env python #coding:utf8 import pymysql conn = pymysql.connect( host='10.10.0.109', port=3306, user='mha', password='123456', database='sbtest', charset='utf8' ) cursor = conn.cursor() # 插入sql; sql_insert = "insert into user (userid,username) values (10,'user10')" # 更新sql; sql_update = "update user set username = 'name91' where userid=9" # 删除sql; sql_delete = "delete from user where userid < 3" # 把一个事务放到一个try块里，如果出现异常就回滚； try: # 开启事务; conn.begin() cursor.execute(sql_insert) print(cursor.rowcount) cursor.execute(sql_update) print(cursor.rowcount) cursor.execute(sql_delete) print(cursor.rowcount) # 提交事务; conn.commit() except Exception as e: # 若有异常就回滚; conn.rollback() cursor.close() conn.close() |
执行程序，打印影响行数，结果如下：
|
1 2 3 4 |
$ python connection.py 1 1 2 |
正确使用，基本都是结合上下文管理器一块使用。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
try: with conn.cursor() as cursor: # Create a new record sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.execute(sql, ('webmaster@python.org', 'very-secret')) # connection is not autocommit by default. So you must commit to save # your changes. conn.commit() with conn.cursor() as cursor: # Read a single record sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s" cursor.execute(sql, ('webmaster@python.org',)) result = cursor.fetchone() print(result) finally: conn.close() |
- 批量插入
对于批量插入，pymysql也提供了一个方法就是executemany(query, args)，基本操作如下。
|
1 2 3 |
sql = "insert into user(userid,username) values(%s,%s)" param = ((100, 'user100'), (101, 'user101'), (102, 'user102') ) n = cursor.executemany(sql,param) |
另外如果执行的SQL需要使用传参数，就使用如下格式：
|
1 2 3 4 |
def test(table,field,value): sql="select %s from %s where %s = %s;" % (field,table,field,value) cursor.execute(sql) cursor.fetchall() |
通过上面的简单例子说明了通过Python的pymysql模块去进行MySQL数据库操作，网上有很多例子。
四、封装PyMySQL常见操作
使用上下文管理器来封装连接与操作有一个好处，__enter__有异常时不会调用__exit__，也就不会触发 conn 对象关闭操作，否则 conn 对象未生成时调用 close 又会触发异常。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
#!/usr/bin/python # -*- coding: UTF-8 -*- import pymysql class DatabaseConn: def __init__(self, ip=None, user=None, password=None, db=None, port=3306): self.ip = ip self.user = user self.password = password self.db = db self.port = int(port) self.con = object def __enter__(self): self.con = pymysql.connect( host=self.ip, user=self.user, passwd=self.password, db=self.db, charset='utf8mb4', port=self.port ) return self def __exit__(self, exc_type, exc_val, exc_tb): self.con.close() def dml_execute(self, sql=None): try: with self.con.cursor() as cursor: cursor.execute(sql) result = cursor.rowcount self.con.commit() return result except Exception: self.con.rollback() raise Exception("database commit error") def dql_execute(self, sql=None): with self.con.cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() fields = cursor.description return fields, result def dbname(self): with self.con.cursor() as cursor: cursor.execute('show databases') result = cursor.fetchall() data = [c for i in result for c in i] return data def tablename(self): with self.con.cursor() as cursor: cursor.execute('show tables') result = cursor.fetchall() data = [c for i in result for c in i] return data def tablestructure(self, table_name): with self.con.cursor() as cursor: cursor.execute('show create table %s' % table_name) result = cursor.fetchall()[0][1] return result |
使用方法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
try: with DatabaseConn( ip='10.10.0.109', user='root', password='123456', port=3306, db='sbtest' ) as curosr: fields, result = curosr.dql_execute(sql='select * from sbtest1 limit 10') dbname = curosr.dbname() tablename = curosr.tablename() tablestructure = curosr.tablestructure('sbtest1') columninfo = curosr.columninfo('sbtest1') tableindex = curosr.tableindex('sbtest1') except Exception as e: print(e) |
<参考>
