使用多个SQL语句更新数据库

Question

我正在使用从MySQL网站下载的mysql connector.Python 1.0.9。

这里有一个样本表格。

DROP TABLE IF EXISTS my_table; 
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT UNIQUE,
Shot VARCHAR(4),
sec varchar(5),
lay VARCHAR(15) NOT NULL,
lay_status VARCHAR(15) NOT NULL,
blk VARCHAR(10) NOT NULL,
blk_status VARCHAR(15) NOT NULL,
pri VARCHAR(10) NOT NULL,
pri_status VARCHAR(15) NOT NULL,
ani VARCHAR(10) NOT NULL,
ani_status VARCHAR(15) NOT NULL,
status VARCHAR(5)
);

INSERT INTO my_table VALUES
(1,'SH01','3','1863','yes','1863','yes','P4645','yes','P4557','yes','Over'),
(2,'SH02','2.5','1863','yes','P4645','no','P4557','yes','1863','no','Over'),
(3,'SH03','0.5','P4645','yes','P4557','yes','1863','yes','1863','yes','WIP'),
(4,'SH04','1.25','1863','no','P4645','no','P4557','yes','1863','yes','RTK'),
(5,'SH05','1','1863','yes','1863','yes','P4645','yes','P4557','yes','WIP'),
(6,'SH06','6','P4557','yes','P4645','yes','P4645','yes','P4557','yes','WIP');

我可以像下面这样执行一个单独的SQL语句。

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='test')
cursor = cnx.cursor()

SQL = '''
        update my_table 
        set 
        LAY = 'P6682'
        , BLK = 'P6682'
        , ANI = 'P6682'
        where
        Shot = 'SH01';
      '''

cursor.execute(SQL)

一切都很好，数据库也正确更新了。

现在，当我尝试用多个语句更新数据库时，如下所示：

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='test')
cursor = cnx.cursor()

SQL = '''
    update my_table 
    set 
    LAY = 'P6682'
    , BLK = 'P6682'
    , ANI = 'P6682'
    where
    Shot = 'SH01';

    update my_table 
    set 
    LAY = '1863'
    , BLK = '1863'
    , ANI = '1863'
    where
    Shot = 'SH02'
'''

cursor.execute(SQL)
cnx.commit()
cur.close()
cnx.close()
cnx.disconnect()

我遇到了下面的错误信息。

Traceback (most recent call last):
  File "Test_Module.py", line 24, in 
  File "C:\Python26\Lib\site-packages\mysql\connector\cursor.py", line 396, in execute
    "Use multi=True when executing multiple statements")
InterfaceError: Use multi=True when executing multiple statements

我把我的命令更新成了下面这样：

cursor.execute(SQL,multi = True)

cnx.commit()
cur.close()
cnx.close()
cnx.disconnect()

现在我没有收到任何错误或追踪信息。但数据库没有更新。

有没有人能告诉我我哪里做错了？

Answer 1

你需要有某种形式的提交

cnx.commit()

MySQL 参考文档

Answer 2

在查看MySQL的文档时

如果将multi设置为True，execute()就可以执行操作字符串中指定的多个语句。它会返回一个迭代器，让你可以处理每个语句的结果。不过，在这种情况下使用参数可能不太好，通常最好是单独执行每个语句。

所以，将multi设置为True会返回一个迭代器，如果你只是想逐个循环处理每个语句，其他提供的解决方案效果很好：

for result in cursor.execute(SQL, multi=True):
    pass

Answer 3

经过长时间查阅文档和求助，最后我终于解决了这个问题。

我在cursor.execute中使用了一个for循环，并设置了multi=True，这样就成功了。我也不太明白为什么需要用循环。

for result in cursor.execute(SQL, multi=True):
    pass

如果不使用循环，直接用cursor.execute(SQL, multi=True)是无法对数据库进行任何更改的。

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='test')
cursor = cnx.cursor()

SQL = '''
    update my_table 
    set 
    LAY = 'P6682'
    , BLK = 'P6682'
    , ANI = 'P6682'
    where
    Shot = 'SH01';

    update my_table 
    set 
    LAY = '1863'
    , BLK = '1863'
    , ANI = '1863'
    where
    Shot = 'SH02'
'''

for result in cursor.execute(SQL, multi=True):
    pass

cnx.commit()
cur.close()
cnx.close()
cnx.disconnect()