Use multi=True in cursor.execute(query) mysql.connector Python

Question

I have the following queries in python mysql.connector:

query="SELECT `AUTO_INCREMENT`FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cl55-mm-px-db' AND   TABLE_NAME   = 'dj_Venues';"
cursor.execute(query)
autoV=str(cursor.fetchone())
autoV=autoV.replace('(','').replace(')','').replace(',','')
query="REPLACE INTO dj_Venues SET venueName = %s,venueAddress = %s,venueBands = %s,venueResident = %s,city=%s,country=%s;'"
variable=(venue,address,venueID,venueID1,city,country1)
cursor.execute(query,variable )          
query="SELECT `AUTO_INCREMENT`FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cl55-mm-px-db' AND   TABLE_NAME   = 'dj_Events';"
cursor.execute(query)
autoE=str(cursor.fetchone())
autoE=autoE.replace('(','').replace(')','').replace(',','')
query="REPLACE INTO dj_Events SET eventName = %s,bandsintownID = %s,residentID=%s,date = %s,startTime=%s,sourceBands=%s,sourceResident=%s;'"
variable=(eventName,eventID,eventID1,date,start,source,source)
cursor.execute(query,variable )
query="REPLACE INTO dj_Master SET artistID = %s,eventID = %s,venueID = %s;'"
variable=(artistID,autoE,autoV)
cursor.execute(query,variable )

And when I run this script I get the following error:

    "Use multi=True when executing multiple statements")
mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements

I added multi=True in every cursor.execute() but the queries don't execute so my tables remain empty!

Remove your semicolons from the end of your statements instead, I think that is why you get those error messages. As a side note: I'm disappointed this library has chosen to ignore the Python DB API specification and added multi=True to execute, rather than have you use cursor.executemany() — Martijn Pieters
– Martijn Pieters, Commented Apr 3, 2016 at 9:37

Martijn Pieters · Accepted Answer · 2016-04-03 09:58:43Z

1

All your statements have ; semicolons at the end. Remove these, these are not needed when running in cursor.execute() and it is those that create the error message:

query="SELECT `AUTO_INCREMENT`FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cl55-mm-px-db' AND   TABLE_NAME   = 'dj_Venues'"
cursor.execute(query)

Next, don't use str() on a row; if you want the values concatenated use str.join():

autoE = ''.join(cursor.fetchone())

or just index into the tuple if you have just one value:

autoE = cursor.fetchone()[0]

or use tuple assignment with just one variable:

autoE, = cursor.fetchone()

The latter throws an error if there is more than one result in the row, which can aid finding bugs when your query changes.

answered Apr 3, 2016 at 9:58

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

Collectives™ on Stack Overflow

Use multi=True in cursor.execute(query) mysql.connector Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related