在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的使用也是如此,基本如下:
- import MySQLdb
-
-
- db = MySQLdb.connect(host="localhost", port=3307, user="joebob", passwd="moonpie", db="thangs")
-
- cursor = db.cursor()
-
- max_price=5
- sql =
-
-
- cursor.execute(sql, (max_price,))
-
-
- cursor.close()
- db.close()
MySQLdb提供了
execute
(query, args)和
executemany
(query, args)来执行SQL语句,后者主要用于多行插入。支持
Parameterized Query
,即将SQL语句与参数分离,在SQL语句中采用占位符来占位(关于Parameterized Query和Prepared Statement的说法有很多种)。注意几点如下:
1) 使用%s来作为占位符,示例如下:
- c.execute(, (max_price,))
- c.executemany(
-
- ,
- [
- ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
- ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
- ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
- ] )
2) 除了上述的
'...WHERE name=%s'的格式,还支持'...WHERE name=%(name)s'的格式,此时需要使用map来作为参数。
2.连接池
在上述的操作中,每次访问数据库时,都需要发起连接请求,比较浪费资源,且访问数量较多时,会对mysql的性能会产生较大的影响。因此,在实际使用中,通常会使用连接池技术,来实现资源复用。
这里主要使用DBUtils来实现连接池,DBUtils是一套用于管理数据库连接池的包,为高并发的数据库访问提供更好的性能,可以自动管理连接对象的创建和释放。常用的两个外部接口是 PersistentDB 和 PooledDB,前者提供了单个线程专用的数据库连接池,后者则是进程内所有线程共享的数据库连接池。下面是一个基于DBUtils和MySQLdb的使用类,有需要者可以参考使用:
-
-
- ''
-
-
-
-
- import MySQLdb
- from MySQLdb.cursors import DictCursor
- from DBUtils.PooledDB import PooledDB
-
- sql_settings = {'mysql': {'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': '123456', 'db': 'test'}}
-
- class MySqlUtil(object):
- __pool = {}
-
- def __init__(self, conf_name='mysql'):
- self._conn = MySqlUtil.__get_conn(conf_name)
- self._cursor = self._conn.cursor()
-
-
- self._cursor.execute('SET NAMES utf8mb4')
- self._cursor.execute("SET CHARACTER SET utf8mb4")
- self._cursor.execute("SET character_set_connection=utf8mb4")
-
- @classmethod
- def __get_conn(cls, conf_name):
- if conf_name not in MySqlUtil.__pool:
- print 'create pool for %s' % conf_name
- MySqlUtilV2.__pool[conf_name] = PooledDB(creator=MySQLdb,
- mincached=1, maxcached=20,
- use_unicode=True, charset='utf8',
- cursorclass=DictCursor,
- **sql_settings[conf_name])
- return MySqlUtil.__pool[conf_name].connection()
-
- def close(self):
- if self._cursor:
- self._cursor.close()
- self._conn.close()
-
-
- def insert_one(self, sql, value):
- return self._cursor.execute(sql, value)
-
- def insert_many(self, sql, values):
- return self._cursor.executemany(sql, values)
-
-
- def update(self, sql, param=None):
- return self._cursor.execute(sql, param)
-
-
- def fetch_all(self, sql, param=None):
- if param is None:
- count = self._cursor.execute(sql)
- else:
- count = self._cursor.execute(sql, param)
- if count > 0:
- result = self._cursor.fetchall()
- else:
- result = False
- return result
-
- def fetch_one(self, sql, param=None):
- if param is None:
- count = self._cursor.execute(sql)
- else:
- count = self._cursor.execute(sql, param)
- if count > 0:
- result = self._cursor.fetchone()
- else:
- result = False
- return result
-
- def fetch_many(self, sql, num, param=None):
- if param is None:
- count = self._cursor.execute(sql)
- else:
- count = self._cursor.execute(sql, param)
- if count > 0:
- result = self._cursor.fetchmany(num)
- else:
- result = False
- 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个字节的插入,官方解释:
![](https://img-blog.csdn.net/20161015210720617)
因此,需要做出两个动作来完成修改:
A. 修改table的编码为utf8mb4,即设置:
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci
B. 在Python的连接数据库代码中,增加对client的设置,即上述代码中的:
2) Warning: Truncated incorrect DOUBLE value
[问题]
- sql = 'SELECT `name` FROM `tb_test` WHERE `id` IN (%s);'
- id_list = [36, 45, 44, 39, 40, 41, 42, 43, 37]
- ids = ','.join(map(lambda x: str(x), id_list))
- 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的方式,可以避开此类问题。针对这里的问题,可以使用下述代码来替换:
- sql = 'SELECT `name` FROM `tb_test` WHERE `id` IN (%s);'
- id_list = [36, 45, 44, 39, 40, 41, 42, 43, 37]
- place_holders = ','.join(map(lambda x: '%s', id_list))
- results = mysql.fetch_all(sql % place_holders, [id_list])