Add MySQL Table with python connector fails to add table without throwing error with multi=True

Question

I have run into a very strange issue where my code below was working (I was able to add a table into the DB). After dropping a test table via the mysql workbench I am able to add a database and the code continues to run with no errors but no table is added. I have slimmed the SQL right down and attempted on different dbs. I have also restarted. Am I doing something wrong here? Any help is greatly appreciated.

import mysql.connector
import os

dirname = os.path.abspath('')
sql_filename = dirname + '\SQL_Creation\Test.sql'

class connectionsetup:
    def __init__(self):
      self.mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      password="")
    
      try:
        self.mycursor = self.mydb.cursor()
        print('Conneciton Succesful')
        self.mycursor.execute("CREATE DATABASE AP_Application_Db_test")
        print('Database Created')
        self.mycursor.execute('USE ap_application_db_test; Create TABLE test (SERIAL_NUMBER VARCHAR(255),VIOLATION_STATUS VARCHAR(255))', multi=True)
        self.mydb.commit()
        self.mydb.close()

      except mysql.connector.Error as err:
        print(err)
        print("Error Code:", err.errno)
        print("SQLSTATE", err.sqlstate)
        print("Message", err.msg   )
        
    
connectionsetup()

snakecharmerb · Accepted Answer · 2020-12-13 07:36:24Z

2

According to the docs, cursor.execute(..., multi=True)

[...] returns an iterator that enables processing the result of each statement

So the code needs to be like this:

for _ in self.mycursor.execute(multiple_sql_statements, multi=True):
    pass

answered Dec 13, 2020 at 7:36

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

1 Comment

Chris Over a year ago

Thanks @snakecharmerb that now works consistently.

Collectives™ on Stack Overflow

Add MySQL Table with python connector fails to add table without throwing error with multi=True

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related