一、数据库驱动介绍
任何应用都离不开数据，所以在学习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->提取数据->关闭连接这几个步骤。MySQLdb提供比较关键的对象，分别是Connection、Cursor、Result。
在CentOS系统下使用pip安装MySQLdb或mysqlclient库：
|
1 2 3 4 5 |
# Python 2 $ pip install MySQLdb # Python 3 $ pip3 install mysqlclient |
如果有报错就需要安装依赖：
|
1 |
$ yum install python-devel mysql-devel |
如果安装有ipython，可以看到它有非常多的对象，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
In [1]: import MySQLdb In [2]: MySQLdb. MySQLdb.BINARY MySQLdb.NotSupportedError MySQLdb.escape_sequence MySQLdb.Binary MySQLdb.OperationalError MySQLdb.escape_string MySQLdb.Connect MySQLdb.ProgrammingError MySQLdb.get_client_info MySQLdb.Connection MySQLdb.ROWID MySQLdb.paramstyle MySQLdb.DATE MySQLdb.STRING MySQLdb.release MySQLdb.DATETIME MySQLdb.TIME MySQLdb.result MySQLdb.DBAPISet MySQLdb.TIMESTAMP MySQLdb.server_end MySQLdb.DataError MySQLdb.Time MySQLdb.server_init MySQLdb.DatabaseError MySQLdb.TimeFromTicks MySQLdb.string_literal MySQLdb.Date MySQLdb.Timestamp MySQLdb.test_DBAPISet_set_equality MySQLdb.DateFromTicks MySQLdb.TimestampFromTicks MySQLdb.test_DBAPISet_set_equality_membership MySQLdb.Error MySQLdb.Warning MySQLdb.test_DBAPISet_set_inequality MySQLdb.FIELD_TYPE MySQLdb.apilevel MySQLdb.test_DBAPISet_set_inequality_membership MySQLdb.IntegrityError MySQLdb.connect MySQLdb.thread_safe MySQLdb.InterfaceError MySQLdb.connection MySQLdb.threadsafety MySQLdb.InternalError MySQLdb.constants MySQLdb.times MySQLdb.MySQLError MySQLdb.debug MySQLdb.version_info MySQLdb.NULL MySQLdb.escape MySQLdb.NUMBER MySQLdb.escape_dict |
我们这里主要说一下数据库的连接对象，及游标。
二、Connection对象
connect(parameters…)
用于创建到数据库的连接的构造函数，返回一个连接对象。请注意，一些参数必须指定为关键字参数！根据需要，每个参数的默认值为NULL或0。主要常用重要参数如下：
| 参数名 | 类型 | 说明 |
| host | 字符串 | MySQL服务器地址 |
| port | 数值型 | MySQL服务器端口，默认标准端口3306 |
| user | 字符串 | 连接用户名 |
| passwd | 字符串 | 用户密码 |
| db | 字符串 | 数据库名称 |
| charset | 字符串 | 连接字符集 |
| connect_timeout | 数值型 | 连接超时时间，默认不超时 |
| compress | 布尔型 | 是否压缩，默认不压缩 |
我们经常使用的只是这几个参数，但是其实里面还有很多比如字符集、线程安全、ssl等也都是很重要的参数，使用时要身份注意。
连接对象支持的方法：
| 方法名 | 说明 |
| cursor() | 使用该连接创建并返回游标对象 |
| commit() | 提交当前事务 |
| rollback() | 回滚当前事务 |
| begin() | 开始一个事务 |
| close() | 关闭连接 |
同样这里罗列了几个常用的，另外当使用Connection.query()函数进行query后，connection对象可以返回两种result，分别是store_result和use_result，store_result 将结果集存回client端，而use_result则是结果集保存在server端，并且维护了一个连接，会占用server资源。此时，不可以进行任何其他的查询。建议使用store_result，除非返回结果集（result set）过大或是无法使用limit的情形。
实例讲解：编写connection.py文件
|
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 |
#!/usr/bin/env python #coding:utf-8 #name: connection.py import MySQLdb # 创建一个连接对象; conn = MySQLdb.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'test', charset = 'utf8', compress = 1, connect_timeout = 1 ) # 创建一个游标对象; cursor = conn.cursor() print(conn) print(cursor) # 关闭游标,关闭连接; cursor.close() conn.close() |
执行结果：
|
1 2 3 |
$ python connection.py <_mysql.connection open to '127.0.0.1' at 26a6f00> <MySQLdb.cursors.Cursor object at 0x7fac8c0695d0> |
可以看到成功连接了MySQL。
三、Cursor对象
下面介绍一下游标（cursor）对象： 用于对数据库执行增删改查
MySQL本身不支持游标（Cursor），但是MySQLdb对Cursor进行了仿真。重要的执行query方法有execute 和 executemany 。execute方法，执行单条sql语句，调用executemany方法很好用，数据库性能瓶颈很大一部分就在于网络IO和磁盘IO将多个insert放在一起，只执行一次IO，可以有效的提升数据库性能。游标具有fetchone、fetchmany、fetchall三个方法提取数据，每个方法都会导致游标游动，所以必须关注游标的位置。游标的scroll(value, mode)方法可以使得游标进行卷动，mode参数指定相对当前位置(relative)还是以绝对位置(absolute)进行移动。
游标对象支持的方法：
| 参数名 | 说明 |
| execute(op[,args]) | 执行一个数据查询命令，返回影响行数 |
| fetchone() | 取的结果集的下一行 |
| fetchmany(size) | 获取结果集的下几行 |
| fetchall() | 获取结果集中剩下的所有行 |
| rowcount | 最近一次execute返回的行数或影响行数 |
| close() | 关闭游标对象 |
fetch*方法：移动rownumber，返回数据。
- 查询操作
创建一张测试：
|
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'; |
编辑cursor.py文件：
|
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 |
#!/usr/bin/env python #coding:utf8 import MySQLdb conn = MySQLdb.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'test', charset = 'utf8', compress = 1, connect_timeout = 10 ) 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 cursor.py 打印所有数据行数: 9 打印第一行的数据: (1L, u'user1') 打印从第二行起的三行数据: ((2L, u'user2'), (3L, u'user3'), (4L, u'user4')) 打印剩下的所有行: ((5L, u'user5'), (6L, u'user6'), (7L, u'user7'), (8L, u'user8'), (9L, u'user9')) |
我们看到，每次使用fetch方法，都是在上一次fetch方法执行的结果的尾部开始。
如果我们想把表里的数据格式化打印出来，因为从上面的结果我们可以看到返回的是元组的元组（tuple），我们通过for方法把它取出：
|
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 |
#!/usr/bin/env python #coding:utf8 import MySQLdb conn = MySQLdb.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'test', charset = 'utf8', compress = 1, connect_timeout = 10 ) cursor = conn.cursor() sql = "select * from user" cursor.execute(sql) # 获取所有行的数据; rs = cursor.fetchall() for row in rs: print("Userid=%s, Username=%s" % row) cursor.close() conn.close() |
执行程序：
|
1 2 3 4 5 6 7 8 9 10 |
$ python cursor.py Userid=1, Username=user1 Userid=2, Username=user2 Userid=3, Username=user3 Userid=4, Username=user4 Userid=5, Username=user5 Userid=6, Username=user6 Userid=7, Username=user7 Userid=8, Username=user8 Userid=9, Username=user9 |
说明：字符串格式化操作符的右操作数如果是元组，那么在格式化字符串中必须将元组中的各个元素都有对应的转义说明符。否则会报错，如下：
|
1 2 3 4 5 6 7 8 9 |
>>> data = tuple(list("123")) >>> print(data) ('1', '2', '3') >>> print("data is %s %s %s" % data) data is 1 2 3 >>> print("data is %s" % data) Traceback (most recent call last): File "<stdin>", line 1, in <module> TypeError: not all arguments converted during string formatting |
另外MySQLdb还可以以Json格式输出（DictCursor），要做到这点也很简单，那就是建立数据库连接是传递cusorclass参数，或者在获取Cursor对象时传递cusorclass参数即可：
|
1 2 3 |
conn = MySQLdb.connect( cursorclass=MySQLdb.cursors.DictCursor ) |
或者
|
1 |
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) |
展示结果如下：
|
1 2 |
{'userid': 1L, 'username': 'user1'} {'userid': 2L, 'username': 'user2'} |
- 事务操作
对于MySQL来说，如果使用支持事务的存储引擎，那么每次操作后，commit是必须的，否则不会真正写入数据库，对应rollback可以进行相应的回滚，但是commit后是无法再rollback的。commit可以在执行很多sql指令后再一次调用，这样可以适当提升性能。
编辑一个增删改的脚本iud.py
|
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 |
#!/usr/bin/env python #coding:utf8 import MySQLdb conn = MySQLdb.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'test', charset = 'utf8', compress = 1, connect_timeout = 10 ) 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() # 格式化增删改后的数据查出来; select_sql = "select * from user" cursor.execute(select_sql) rs = cursor.fetchall() for row in rs: print("userid=%s, username=%s" % row) except Exception as e: # 若有异常就回滚; conn.rollback() cursor.close() conn.close() |
执行程序，结果如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$ python iud.py 1 1 2 userid=3, username=user3 userid=4, username=user4 userid=5, username=user5 userid=6, username=user6 userid=7, username=user7 userid=8, username=user8 userid=9, username=name91 userid=10, username=user10 |
如果需要批量的插入数据，就这样做 ：
|
1 2 3 |
sql = "insert into user(userid,username) values(%s,%s)" param = ((100, 'user100'), (101, 'user101'), (102, 'user102') ) n = cursor.executemany(sql,param) |
每个值的集合为一个tuple，整个参数集组成一个tuple或者list，然后使用executemany方法来批量的插入数据。
另外如果执行的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的MySQLdb模块去进行MySQL数据库操作，网上有很多例子。
<参考>
http://www.cnblogs.com/coser/archive/2012/01/12/2320741.html
