Executing multiple MySQL inserts at once in Python

Question

I have a string that is basically a concatenation of multiple insert statements such as

sql = INSERT INTO test (a,b,c) VALUES ("test","test",1);INSERT INTO test (a,b,c) VALUES ("2nd test","2nd test",6);

When I run this in SQL as a query, it works fine and inserts both statements.

However, when I run it in python using the following:

cursor = db.cursor()
sql = INSERT INTO test (a,b,c) VALUES ("test","test",1);INSERT INTO test (a,b,c) VALUES ("2nd test","2nd test",6);
cursor.execute(sql)
db.commit()

I get this error:

ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Whats the best way to resolve this and execute multiple statements in one go?

Thanks!

alko · Accepted Answer · 2013-11-20 19:03:36Z

2

This error is about the fact that cursor.execute can handle only one sql per run. you either want to loop it:

sql = 'INSERT INTO test (a,b,c) VALUES (%s, %s, %s)'
for values in [("test","test",1), ("2nd test","2nd test",6)]
    cursor.execute(sql, values)

or execute at once:

sql = 'INSERT INTO test (a,b,c) VALUES ("test","test",1),("2nd test","2nd test",6)'
cursor.execute(sql)

answered Nov 20, 2013 at 19:03

alko

48.7k12 gold badges99 silver badges105 bronze badges

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

1 Comment

user1898958 Over a year ago

Perfect! Thank you. For a variety of reasons needed them to go in all at once so the second method worked.

Collectives™ on Stack Overflow

Executing multiple MySQL inserts at once in Python

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related