Python下访问MYSQL的方法总结

在Python下做过服务器开发的小伙伴对ORM技术一定都不陌生，ORM（Object-Relational Mapping），将关系数据库的表结构映射到对象上，隐藏了数据库操作背后的细节，简化了对数据操作的写法，使得不懂SQL语法的人也可以快速开发，同时也避免了SQL注入的隐患。目前比较著名的ORM框架有Django中的ORM和SQLAlchemy（Flask中经常使用）。

  本文总结的Python下访问MySQL的方法是通过原生的MySQL Driver来操作，非ORM，目前主要使用在我们的数据分析和ETL脚本中。比较常用的两个Driver是MySQLdb和mysql-connector，前者通过C来实现的，而后者是纯Python实现，我没有具体做过性能测试，但是从查阅的资料来看，MySQLdb要优于mysql-connector（参考：Python MySQLdb vs mysql-connector query performance），所以下文所述的访问方法都是通过MySQLdb来实现的，主要总结基础访问中的注意事项、数据库连接池、使用中的两个Warning问题。

1.基础访问及注意事项

  数据库的访问无碍乎是"建立数据库连接-->>执行操作-->>关闭连接"这样的过程，对于MySQLdb的使用也是如此，基本如下：

[python]  view plain  copy

 

1. import MySQLdb  
2.   
3. # 连接数据库  
4. db = MySQLdb.connect(host="localhost", port=3307, user="joebob", passwd="moonpie", db="thangs")  
5.   
6. cursor = db.cursor()  
7.   
8. max_price=5  
9. sql = """SELECT spam, eggs, sausage FROM breakfast WHERE price < %s"""  
10.   
11. # 执行操作  
12. cursor.execute(sql, (max_price,))  
13.   
14. # 关闭连接  
15. cursor.close()  
16. db.close()  

  MySQLdb提供了 execute (query, args)和 executemany (query, args)来执行SQL语句，后者主要用于多行插入。支持 Parameterized Query ，即将SQL语句与参数分离，在SQL语句中采用占位符来占位（关于Parameterized Query和Prepared Statement的说法有很多种）。注意几点如下：

1) 使用%s来作为占位符，示例如下：

[python]  view plain  copy

 

1. c.execute("""SELECT spam, eggs, sausage FROM breakfast WHERE price < %s""", (max_price,))  

[python]  view plain  copy

 

1. c.executemany(  
2.       """INSERT INTO breakfast (name, spam, eggs, sausage, price) 
3.       VALUES (%s, %s, %s, %s, %s)""",  
4.       [  
5.       ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),  
6.       ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),  
7.       ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )  
8.       ] )  

2) 除了上述的 '...WHERE name=%s'的格式，还支持'...WHERE name=%(name)s'的格式，此时需要使用map来作为参数。

2.连接池

  在上述的操作中，每次访问数据库时，都需要发起连接请求，比较浪费资源，且访问数量较多时，会对mysql的性能会产生较大的影响。因此，在实际使用中，通常会使用连接池技术，来实现资源复用。

  这里主要使用DBUtils来实现连接池，DBUtils是一套用于管理数据库连接池的包，为高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。常用的两个外部接口是 PersistentDB 和 PooledDB，前者提供了单个线程专用的数据库连接池，后者则是进程内所有线程共享的数据库连接池。下面是一个基于DBUtils和MySQLdb的使用类，有需要者可以参考使用：

[python]  view plain  copy

 

1. # -*- coding: utf-8 -*-  
2.   
3. ''''' 
4.     MySQL的处理库 
5.     解决两个问题：1.连接池；2.公共insert/update/query接口 
6. '''  
7.   
8. import MySQLdb  
9. from MySQLdb.cursors import DictCursor  
10. from DBUtils.PooledDB import PooledDB  
11.   
12. sql_settings = {'mysql': {'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': '123456', 'db': 'test'}}  
13.   
14. class MySqlUtil(object):  
15.     __pool = {}  
16.   
17.     def __init__(self, conf_name='mysql'):  
18.         self._conn = MySqlUtil.__get_conn(conf_name)  
19.         self._cursor = self._conn.cursor()  
20.   
21.         # Enforce UTF-8 for the connection.  
22.         self._cursor.execute('SET NAMES utf8mb4')  
23.         self._cursor.execute("SET CHARACTER SET utf8mb4")  
24.         self._cursor.execute("SET character_set_connection=utf8mb4")  
25.  
26.     @classmethod  
27.     def __get_conn(cls, conf_name):  
28.         if conf_name not in MySqlUtil.__pool:  
29.             print 'create pool for %s' % conf_name  
30.             MySqlUtilV2.__pool[conf_name] = PooledDB(creator=MySQLdb,  
31.                                                      mincached=1, maxcached=20,  
32.                                                      use_unicode=True, charset='utf8',  
33.                                                      cursorclass=DictCursor,  
34.                                                      **sql_settings[conf_name])  
35.         return MySqlUtil.__pool[conf_name].connection()  
36.   
37.     def close(self):  
38.         if self._cursor:  
39.             self._cursor.close()  
40.         self._conn.close()  
41.   
42.     # insert  
43.     def insert_one(self, sql, value):  
44.         return self._cursor.execute(sql, value)  
45.   
46.     def insert_many(self, sql, values):  
47.         return self._cursor.executemany(sql, values)  
48.   
49.     # update  
50.     def update(self, sql, param=None):  
51.         return self._cursor.execute(sql, param)  
52.   
53.     # query  
54.     def fetch_all(self, sql, param=None):  
55.         if param is None:  
56.             count = self._cursor.execute(sql)  
57.         else:  
58.             count = self._cursor.execute(sql, param)  
59.         if count > 0:  
60.             result = self._cursor.fetchall()  
61.         else:  
62.             result = False  
63.         return result  
64.   
65.     def fetch_one(self, sql, param=None):  
66.         if param is None:  
67.             count = self._cursor.execute(sql)  
68.         else:  
69.             count = self._cursor.execute(sql, param)  
70.         if count > 0:  
71.             result = self._cursor.fetchone()  
72.         else:  
73.             result = False  
74.         return result  
75.   
76.     def fetch_many(self, sql, num, param=None):  
77.         if param is None:  
78.             count = self._cursor.execute(sql)  
79.         else:  
80.             count = self._cursor.execute(sql, param)  
81.         if count > 0:  
82.             result = self._cursor.fetchmany(num)  
83.         else:  
84.             result = False  
85.         return result  

3. 两个Warning问题

  在访问MySQL的过程中，遇到过很多问题，不仅有CRUD本身的问题，也有性能的问题等，这里主要列举其中两个问题，也跟上面的代码有关系，希望能对遇到同样问题的人起到帮助。

1) Warning: Incorrect string value

[问题]

  插入数据时，出现如下Warning，导致这个字段写入了空值NULL，没有写入预期的字符串值。

/usr/local/lib/python2.7/dist-packages/DBUtils/SteadyDB.py:552: Warning: Incorrect string value: '\xF0\x9F\x91\x8F\xF0\x9F...' for column 'description' at row 2889

[原因]

  MySQL默认只支持3个字节的utf8编码，而这里的字符串中包括了需要四个字节来标志的字符（字符串中有一些笑脸之类的符号）。

[解决]

  mysql5.5之后，支持使用utf8mb4来完成4个字节的插入，官方解释：

因此，需要做出两个动作来完成修改：

A. 修改table的编码为utf8mb4，即设置：

 DEFAULT CHARACTER SET = utf8mb4

 COLLATE = utf8mb4_unicode_ci

B. 在Python的连接数据库代码中，增加对client的设置，即上述代码中的：

2) Warning: Truncated incorrect DOUBLE value

[问题]

[python]  view plain  copy

 

1. sql = 'SELECT `name` FROM `tb_test` WHERE `id` IN (%s);'  
2. id_list = [36, 45, 44, 39, 40, 41, 42, 43, 37]  
3. ids = ','.join(map(lambda x: str(x), id_list))  
4. results = mysql.fetch_all(sql, [ids])  

  在使用类似上述代码来做IN查询数据时，发生如下Warning，导致查询失败：

/usr/local/lib/python2.7/dist-packages/DBUtils/SteadyDB.py:552: Warning: Truncated incorrect DOUBLE value: '36, 45, 44, 39，40， 41， 42， 43, 37'

[原因]

  发生这个错误的根本原因是参数传递错误导致MySQL尝试去比较int和string数据，而这里的比较会统一转换为double来做。具体的意思是：id是INT数据，而在上述的拼接中，最终的SQL语句变成了:

SELECT `name` FROM `tb_test` WHERE `id` IN ('36, 45, 44, 39，40， 41， 42， 43, 37');

注意，多了一个引号。

[解决]

  应该尽量避免自己拼接SQL语句，而采用Parameterized Query的方式，可以避开此类问题。针对这里的问题，可以使用下述代码来替换：

[python]  view plain  copy

 

1. sql = 'SELECT `name` FROM `tb_test` WHERE `id` IN (%s);'  
2. id_list = [36, 45, 44, 39, 40, 41, 42, 43, 37]  
3. place_holders = ','.join(map(lambda x: '%s', id_list))  
4. results = mysql.fetch_all(sql % place_holders, [id_list])