Update database with multiple SQL Statments

Question

I am using mysql connector.Python 1.0.9 downloaded from MySQL site.

I have a sample table here

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');

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()

I get below trackback error

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

I update my command as below

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.

Mel · Accepted Answer · 2017-05-31 08:43:09Z

32

At-last after a long research on docs and help. I could able to solve the issue.

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

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()

edited May 31, 2017 at 8:43

Mel

6,10510 gold badges40 silver badges42 bronze badges

answered Mar 8, 2013 at 9:55

Rao

2,99219 gold badges56 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

joel3000 Over a year ago

Thank you! I spent hours trying to figure out why this wasn't working.

user1717828 Over a year ago

It'd be great if a mysql.connector guru could explain why the for loop is necessary.

10mjg Over a year ago

good to know: For python 3.7+, make sure you have MySQL Connector/Python 8.0.13 or later ( bugs.mysql.com/bug.php?id=87818 )

Nicholas · Accepted Answer · 2016-06-30 14:44:58Z

3

Looking at the MySQL docs

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement. However, using parameters does not work well in this case, and it is usually a good idea to execute each statement on its own.

so setting multi=True returns an iterator and if you just want to loop through each statement, the other solution offered works well:

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

answered Jun 30, 2016 at 14:44

Nicholas

5761 gold badge7 silver badges19 bronze badges

Comments

Bhargav Rao · Accepted Answer · 2020-06-17 11:30:13Z

0

You need to have somekind of commit

cnx.commit()

MySQL reference

edited Jun 17, 2020 at 11:30

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered Mar 8, 2013 at 7:12

elrado

5,3321 gold badge19 silver badges15 bronze badges

1 Comment

Rao Over a year ago

,i have cnx.commit() i did not write in my question. i have updated my question with cnx.commit()

Anatoly · Accepted Answer · 2020-12-08 06:30:39Z

-1

Another option is to use executemany, as follows:

sql=('insert into articulos (descripcion, precio) values(%s,%s)')  
data=[('naranjas',22), ("pomelos", 29), ("frutillas", 130)]

cursor.executemany(sql,data)

edited Dec 8, 2020 at 6:30

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

answered Dec 8, 2020 at 2:00

Raul Presa

1

Collectives™ on Stack Overflow

Update database with multiple SQL Statments

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related