这里面学习一下python中操作mysql的第三方库pymysql的使用。很多我们以为一辈子都不会忘掉的事情，就在我们念念不忘的日子里．被我们遗忘了．
pymysql的简单使用
我们创建一张表来进行下述案例的测试。创建表的语句：
CREATE TABLE `springlearn`.`user` ( `user_id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(45) NULL, `password` VARCHAR(45) NULL, PRIMARY KEY (`user_id`));
一、pymysql的简单使用
pymysql的安装：pip install PyMySQL。下面我们对官方文档的例子做一些修改，代码如下
import pymysql.cursors # Connect to the database connection = pymysql.connect(host='localhost', user='root', password='chenhui', db='springlearn', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) try: with connection.cursor() as cursor: # Create a new record sql = "INSERT INTO `user` (`username`, `password`) VALUES (%s, %s)" effect_count = cursor.execute(sql, ('huhx', '123456')) print('effect counts = %d.' % effect_count) # connection is not autocommit by default. So you must commit to save your changes. connection.commit() with connection.cursor() as cursor: # Read a single record sql = "SELECT `user_id`, `password` FROM `user` WHERE `username`=%s" cursor.execute(sql, ('huhx',)) result = cursor.fetchone() print(result) finally: connection.close() # effect counts = 1. # {'user_id': 1, 'password': '123456'}
首先我们创建数据库的连接：pymysql.connect()。connect的参数可以在http://pymysql.readthedocs.io/en/latest/modules/connections.html里面看到详细的介绍。connect方法里面指定的cursorclass，python中有以下的几种Cursor提供我们使用。详细文档可以参考：http://pymysql.readthedocs.io/en/latest/modules/cursors.html。以下是关于Cursor的一些简单介绍。
Cursor：class pymysql.cursors.Cursor(connection)
- 调用存储过程：callproc
callproc(procname, args=())
Execute stored procedure procname with args
procname – string, name of procedure to execute on server
args – Sequence of parameters to use with procedure
Returns the original args.
- 游标的关闭： close()
Closing a cursor just exhausts all remaining data.
- 普通执行：execute()
execute(query, args=None) Execute a query Parameters: query (str) – Query to execute. args (tuple, list or dict) – parameters used with query. (optional) Returns: Number of affected rows Return type: int If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.
- 批量执行：executemany()
executemany(query, args) Run several data against one query Parameters: query – query to execute on server args – Sequence of sequences or mappings. It is used as parameter. Returns: Number of rows affected, if any. This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().
- 获取记录数据的方法：
fetchall()：获取所有的记录
Fetch all the rows fetchmany(size=None)：获取指定数目的记录
Fetch several rows fetchone()：获取一条记录
Fetch the next row
class pymysql.cursors.SSCursor(connection)
Unbuffered Cursor, mainly useful for queries that return a lot of data, or for connections to remote servers over a slow network.
Instead of copying every row of data into a buffer, this will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network or if the result set is very big.
There are limitations, though. The MySQL protocol doesn’t support returning the total number of rows, so the only way to tell how many rows there are is to iterate over every row returned. Also, it currently isn’t possible to scroll backwards, as only the current row is held in memory.
class pymysql.cursors.DictCursor(connection)
A cursor which returns results as a dictionary
class pymysql.cursors.SSDictCursor(connection)
An unbuffered cursor, which returns results as a dictionary
我们在下面开始我们对上述方法的测试。
pymysql的详细使用
我们的数据库数据现在有三条记录，如下：
| 1 | huhx | 123456 |
| 2 | linux | 234567 |
| 3 | tomhu | 345678 |
一、fetchall，fetchone和fetchmany方法
查询的sql是：sql = "select * from USER"。以下的方法不是同时执行的结果，因为cursor会向后移动的。
for item in cursor.fetchmany(2): print(item) # {'user_id': 1, 'username': 'huhx', 'password': '123456'} # {'user_id': 2, 'username': 'linux', 'password': '234567'} print(cursor.fetchone()) print(cursor.fetchone()) # {'user_id': 1, 'username': 'huhx', 'password': '123456'} # {'user_id': 2, 'username': 'linux', 'password': '234567'} for item in cursor.fetchall(): print(item) # {'user_id': 1, 'username': 'huhx', 'password': '123456'} # {'user_id': 2, 'username': 'linux', 'password': '234567'} # {'user_id': 3, 'username': 'tomhu', 'password': '345678'}
二、我们重点说一下executemany方法
executemany对于批量的插入是有效的，对于其它的调用这个方法。是循环的执行而不是批量的插入。
- 批量的数据可以是一个dict：
sql = "insert into user(username, password) VALUES (%(username)s, %(password)s)" dict1 = {'username': 'liu', 'password': 'ling'} dict2 = {'username': 'chen', 'password': 'hui'} data_list = [dict1, dict2] cursor.executemany(sql, data_list) connection.commit()
- 批量的数据可以是一个元祖：
sql = "insert into user(username, password) VALUES (%s, %s)" data1 = ('chen', 'hui') data2 = ('liu', 'ling') data_list = [data1, data2] cursor.executemany(sql, data_list) connection.commit()
下面我们对executemany方法的源码做一些分析：
RE_INSERT_VALUES = re.compile(
r"s*((?:INSERT|REPLACE)s.+sVALUES?s+)" +
r"((s*(?:%s|%(.+)s)s*(?:,s*(?:%s|%(.+)s)s*)*))" +
r"(s*(?:ON DUPLICATE.*)?);?s*",
re.IGNORECASE | re.DOTALL)
def executemany(self, query, args): # type: (str, list) -> int """Run several data against one query :param query: query to execute on server :param args: Sequence of sequences or mappings. It is used as parameter. :return: Number of rows affected, if any. This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute(). """ if not args: return m = RE_INSERT_VALUES.match(query) if m: q_prefix = m.group(1) % () q_values = m.group(2).rstrip() q_postfix = m.group(3) or '' assert q_values[0] == '(' and q_values[-1] == ')' return self._do_execute_many(q_prefix, q_values, q_postfix, args, self.max_stmt_length, self._get_db().encoding) self.rowcount = sum(self.execute(query, arg) for arg in args) return self.rowcount
首先对我们的sql做正则的判断，如果不是insert或者replace，那么就是循环的执行execute方法。而不是批量的执行，下面我们重点看一下批量的执行流程：_do_execute_many方法
def _do_execute_many(self, prefix, values, postfix, args, max_stmt_length, encoding): conn = self._get_db() escape = self._escape_args if isinstance(prefix, text_type): prefix = prefix.encode(encoding) if PY2 and isinstance(values, text_type): values = values.encode(encoding) if isinstance(postfix, text_type): postfix = postfix.encode(encoding) sql = bytearray(prefix) args = iter(args) v = values % escape(next(args), conn) if isinstance(v, text_type): if PY2: v = v.encode(encoding) else: v = v.encode(encoding, 'surrogateescape') sql += v rows = 0 for arg in args: v = values % escape(arg, conn) if isinstance(v, text_type): if PY2: v = v.encode(encoding) else: v = v.encode(encoding, 'surrogateescape') if len(sql) + len(v) + len(postfix) + 1 > max_stmt_length: rows += self.execute(sql + postfix) sql = bytearray(prefix) else: sql += b',' sql += v rows += self.execute(sql + postfix) self.rowcount = rows return rows
以上的操作其实就是根据cursor.executemany(sql, data_list)的参数sql和data_list，拼接成一条sql语句。例如：
INSERT INTO `springlearn`.`user` (`username`, `password`) VALUES ('liu', 'ling'),('chen', 'hui');
当然这种语法并不是所有的数据库都支持的，oracle就不支持这种的批量插入。executemany方法返回生效的行数。