使用pyodbc执行多条SQL插入语句如何仅提交一次事务保证全部生效

• 你当前每次调用dbQuery_Multiple_Row函数时，都会重新执行db_connection_dev01()获取新的数据库连接，覆盖全局的conn和cursor变量
• 前两次执行INSERT的连接实例在第三次调用函数时就被覆盖，后续执行的conn.commit()只能提交最后一次连接的事务，前两个连接的操作没有提交就被丢弃，所以只有最后一条插入生效

要保证所有SQL都在同一个数据库连接的同一个事务内执行，调整后的代码如下：

import pyodbc

def dbQuery_Multiple_Row(sql, li, cursor):
    try:
        cursor.execute(sql)
    except Exception as e:
        li.append("Error-"+str(e))
        # 出现异常直接回滚全部事务
        conn.rollback()

# 全局只获取一次连接和游标
cursor, conn = db_connection_dev01()
li = []

sql1 = "insert into stars.sampledata values ('Suneel Kummar I', 2, 'Sullurepeta')"
sql2 = "insert into stars.sampledata values ('Suneel Kummar Il', 3, 'Sullurepeta')"
sql3 = "insert into stars.sampledata values ('Suneel Kummar Ill', 4, 'Sullurepeta')"
sql_list = [sql1, sql2, sql3]

# 同一个游标执行所有SQL
for sql in sql_list:
    dbQuery_Multiple_Row(sql, li, cursor)
    # 有错误直接终止执行
    if li:
        break

# 无错误统一提交
if not li:
    conn.commit()
else:
    print("执行出错：", li)
conn.close()

更优的批量插入方案 #

推荐使用参数化查询+executemany实现批量插入，性能更高，还能避免SQL注入风险：

import pyodbc

cursor, conn = db_connection_dev01()
li = []
# 插入值单独存为参数列表
insert_values = [
    ('Suneel Kummar I', 2, 'Sullurepeta'),
    ('Suneel Kummar Il', 3, 'Sullurepeta'),
    ('Suneel Kummar Ill', 4, 'Sullurepeta')
]
insert_sql = "insert into stars.sampledata values (?, ?, ?)"

try:
    cursor.executemany(insert_sql, insert_values)
    conn.commit()
except Exception as e:
    li.append("Error-"+str(e))
    conn.rollback()
finally:
    conn.close()

if li:
    print("执行出错：", li)

• pyodbc默认开启事务，所有execute操作都在当前连接的事务中，必须主动调用commit()才会持久化到数据库，出现异常调用rollback()可以撤销所有未提交的操作
• 同一个事务的所有操作必须复用同一个连接实例，不要每次执行SQL都新建连接

内容的提问来源于stack exchange，提问作者Suneel Kumar