pymysql模块批量插入数据报错问题处理

1. 问题现象

如下 用pymysql建立的连接 一次执行多个sql时候会报错 说语法错误

In [1]: import pymysql

In [2]: conn = pymysql.connect(host='10.1.2.154',port=3306,user='root',password='',db='one',charset='utf8')

In [3]: cursor = conn.cursor()

In [4]: sql = "insert into qiubai values('a','b');insert into qiubai values('a','b');"

In [5]: cursor.execute(sql)
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-5-c09949f62056> in <module>
----> 1 cursor.execute(sql)

/usr/local/python/lib/python3.9/site-packages/pymysql/cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result

/usr/local/python/lib/python3.9/site-packages/pymysql/cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount

/usr/local/python/lib/python3.9/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    546             sql = sql.encode(self.encoding, "surrogateescape")
    547         self._execute_command(COMMAND.COM_QUERY, sql)
--> 548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows
    550 

/usr/local/python/lib/python3.9/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:

/usr/local/python/lib/python3.9/site-packages/pymysql/connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():

/usr/local/python/lib/python3.9/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    723             if self._result is not None and self._result.unbuffered_active is True:
    724                 self._result.unbuffered_active = False
--> 725             packet.raise_for_error()
    726         return packet
    727 

/usr/local/python/lib/python3.9/site-packages/pymysql/protocol.py in raise_for_error(self)
    219         if DEBUG:
    220             print("errno =", errno)
--> 221         err.raise_mysql_exception(self._data)
    222 
    223     def dump(self):

/usr/local/python/lib/python3.9/site-packages/pymysql/err.py in raise_mysql_exception(data)
    141     if errorclass is None:
    142         errorclass = InternalError if errno < 1000 else OperationalError
--> 143     raise errorclass(errno, errval)

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'insert into qiubai values('a','b')' at line 1")

In [6]: 

2. 问题分析

出现这个问题的时候 在想 是否pymysql不支持批量插入数据 能否有其他办法
参考了文字MySQLdb、Mysqlclient、PyMySQL、mysql.connector用法总结和比较

发现了mysql.connector这个模块有执行批量sql的功能 如下图

马上想到 pymysql是不是也支持这个方法 查阅文档后发现 也是支持的 所以 可以使用executemany进行数据批量插入

3. 执行批量插入

使用的时候 如下 需要传入一个sql 和一个可迭代序列 这个序列可以是列表里套列表 也可以是元组里套元组 也可以是列表里套元组等

注意 一定呀进行commit操作 否则可能看不到数据

In [6]: help(pymysql)


In [7]: sq = 'insert into qiubai values(%s,%s);'

In [8]: lst = [['aa','bb'],['cc','dd']]

In [9]: cursor.executemany(sq,lst)
Out[9]: 2

In [10]: conn.commit()

In [11]: cursor.close()

In [12]: conn.close()