CODEWITHSUNDEEP
pythonmysql
Vantur Antonel-Ovidiu
Vantur Antonel-Ovidiu

Reputation: 89

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

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!

Upvotes: 1

Views: 4761

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121924

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.

Upvotes: 1

Related Questions