CODEWITHSUNDEEP
pythonmysqlmysql-python
user1898958
user1898958

Reputation: 93

Executing multiple MySQL inserts at once in Python

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!

Upvotes: 1

Views: 2800

Answers (1)

alko
alko

Reputation: 48317

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)

Upvotes: 2

Related Questions