用时间序列举例 PYMSSQL cursor.execute() 与 cursor.executemany()在写入数据时的用法和不同--Python + SQL server

cursor.execute()和cursor.executemany()是用Python向SQL server插入数据时最常使用的两种方式，后者的效率比前者高，但是两者对于传入数据的格式要求有一定的区别，这可能导致executemany()的性能损失。比如：有个列表中的数据全是以字符串形式储存的数字、时间、货币、图片，在使用executemany()时就必须把字符串转化成对应的Python数据格式传入，导致性能的减损。

任务假设

在Python中

假设有一个列表time储存了一串时间（字符），字符串，整数值的元组。

time = [('2024-1-1','a',3),('2024-1-2','c',4)]
'
运行
运行

在SQL中

假设用TEST 库创建表TEST并且各列设定如下：

USE TEST
CREATE TABLE TEST(T DATE,S CHAR(1),I INT)

如果要存储行数据('2024-1-1','a',3)应该使用查询：

USE TEST
INSERT INTO TEST VALUES('2024-1-1','a',3)

在Python中利用cursor.execute()插入数据

cur.execute()实际上就是向SQL server输入一个字符串表示的SQL命令。

如果要写入行数据('2024-1-1','a',3)，使用含占位符的语句：

request = 'INSERT INTO TEST.TEST VALUES(%s,%s,%s)'%('2024-1-1','a',3)
cur.execute(request)

或者字符串的format()语法

request = 'INSERT INTO TEST.TEST VALUES({},{},{})'.format('2024-1-1','a',3)
cur.execute(request)

那么恭喜你成功地触发了SQL的语法错误，因为SQL server得到的命令其实是：

INSERT INTO TEST.TEST VALUES(2024-1-1,a,3)

由于Python的算法在SQL中指定字符串的引号消失了，SQL直接判定这条语句语法错误，所以正确的Python代码应该是：

request = 'INSERT INTO TEST.TEST VALUES(\'%s\',\'%s\',%s)'%('2024-1-1','a',3)
cur.execute(request)

 需要用转义引号对字符串进行单独的规定\'，使得request成为符合SQL语法的字符流。

一个用cur.execute()能正确批量插入数据的Python代码如下：

conn = pymssql.connect(...)
cur = conn.cursor()
for t in time:
    request = 'INSERT INTO TEST.TEST VALUES(\'%s\',\'%s\',%s)'%t
    cur.execute(request)
conn.commit()
cur.close()
conn.close()

在Python中利用cursor.executemany()插入数据

使用executemany(para1,para2)需要向para1传入一个SQL语句的字符串，向para2传入一个用列表或者元组封装的一系列元组数据——比如：[(),(),()]或者((),(),())。

向para1传入的SQL语句必须使用占位符%s表示输入数据的位置。

如果使用execute()方法下的request查询语句：

request = 'INSERT INTO TEST.TEST VALUES(\'%s\',\'%s\',%s)'
cur.executemany(request,time)

那么恭喜你，又会触发无法对带有 '' 的数据进行转化的错误，因为executemany()语句会直接把Python中的字符串当成字符串传入给SQL，直接把python中的int、float数据传入SQL而无需考虑数据格式在SQL语句中的表达。

正确的语句必然就是：

request = 'INSERT INTO TEST.TEST VALUES(%s,%s,%s)'
cur.executemany(request,time)

所以该方法下的一个批量插入数据的正确代码就是： 

 
conn = pymssql.connect(...)
cur = conn.cursor()
request = 'INSERT INTO TEST.TEST VALUES(%s,%s,%s)'
cur.executemany(request,time)
conn.commit()
cur.close()
conn.close()

这就引来了一个不方便的地方，如果一组数据是用字符串的形式组成的，但是各个数据的SQL类型不一样，比如在SQL的表中应该是字符、时间、整数、小数、货币等，这时就必须在python中把这些数据转化成对应的python数据格式，然后再用executemany()方法传入。