使用多个SQL语句更新数据库 [英] Update database with multiple SQL Statments
问题描述
我正在使用mysql连接器.从MySQL站点下载了Python 1.0.9.
I am using mysql connector.Python 1.0.9 downloaded from MySQL site.
我有一个示例此处的表
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语句.
i am able to execute a single SQL statment as below.
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)
一切正常,数据库正确更新.
and everything is fine and database gets updated correctly.
现在当我尝试使用以下多个语句更新数据库
now when i am trying to update the database with multiple statements as below
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()
现在,我没有收到任何错误/回溯.但是数据库没有更新.
Now i dont get any error/Traceback. But the database is not getting updated.
谁能告诉我我在哪里做错了.
Can any one tell me where am i doing wrong.
推荐答案
对文档和帮助进行了长期研究之后,终于有了.我可以解决这个问题.
At-last after a long research on docs and help. I could able to solve the issue.
在cursor.execute
与multi=True
一起使用for
循环有效.我不知道为什么我们需要遍历.
Using a for
loop at cursor.execute
with multi=True
worked. I don't know why we need to loop through.
for result in cursor.execute(SQL, multi=True):
pass
没有循环,只是cursor.execute(SQL, multi=True)
在数据库中未做任何更改.
Without loop just cursor.execute(SQL, multi=True)
did not do any changes in the database.
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()
这篇关于使用多个SQL语句更新数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!