python mysql connector multiple statements

Question

import mysql.connector

connection = mysql.connector.connect(user="REMOVED", 
                                     password="REMOVED", 
                                     host="REMOVED", 
                                     database="REMOVED")

cur = connection.cursor()

# Latitude - remove letter A
cur.execute("UPDATE tau._inm_exportados_test_csv SET latitud = REPLACE (latitud, 'a=','');")
print("Latitude change remove letter A - executed!")

# Longitude - remove letter A
cur.execute("UPDATE tau._inm_exportados_test_csv SET longitud = REPLACE (longitud, 'a=','');")
print("Longitude change remove letter A - executed!")

# Latitude - MODIFY COLUMN
cur.execute("ALTER TABLE tau._inm_exportados_test_csv MODIFY COLUMN latitud DECIMAL(10,6);")
print("Latitude - MODIFY COLUMN - executed!")

# Longitude - MODIFY COLUMN
cur.execute("ALTER TABLE tau._inm_exportados_test_csv MODIFY COLUMN longitud DECIMAL(10,6);")
print("Longitude - MODIFY COLUMN - executed!")




# Post Code data type change
cur.execute("ALTER TABLE tau._inm_exportados_test_csv MODIFY COLUMN codigo_postal varchar(255);)")
print("Post Code data type change to varchar(255) - executed!")

connection.commit()
cur.close()
connection.close()

I'm trying to make this simple list of statements work without success. What makes it more confusing is that the first four statements work whereas the final one doesn't work even when I comment out the rest! The final statement gets the following reponse:

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

The datatype for codigo_postal is int(11) unlike latitud and longitud which are varchar.

I have tried creating new connections, new cursors, new connections AND cursors. I have tried adding multi="True" and combining statements into one operation. I have tried adding multi="True" to each cur.execute() as both the second and third parameter. I have run the statement in Workbench to ensure the statement is valid and it works.

No success with it here though...

Answer 1

I had the same problem. I wanted my code to be clean and I wanted to have all my commands in a list and just run them in a sequence.

I found this link and this link and finally was able to write this code:

import mysql.connector as sql
from mysql.connector import Error
        
commands = [
    '''
    USE sakila;
    SELECT * FROM actor;
    ''',
    '''
    USE sakila;
    SELECT * FROM actor WHERE actor_id < 10;
    '''
]  

connection_config_dict = {
    'user': 'username',
    'password': 'password',
    'host': '127.0.0.1',
}

try:
    connection = sql.connect(**connection_config_dict)
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info, '\n')
        cursor = connection.cursor()
        
        for command in commands:
            for result in cursor.execute(command, multi=True):
                if result.with_rows:
                    print("Rows produced by statement '{}':".format(
                    result.statement))
                    print(result.fetchall())
                else:
                    print("Number of rows affected by statement '{}': {}".format(
                    result.statement, result.rowcount), '\n')
        record = cursor.fetchall()
        
except Error as e:
    print("Error while connecting to MySQL", e, '\n')

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed", '\n')

Answer 2

You can use commit after you executed DML (Data Manipulation Language) commands. Also using multi=True can be more convenient to complete this job, but you need to run the generator which created by execute. doc.

Ordinary method:

cur = connection.cursor()

def alter(state,msg):
    try:
        cur.execute(state)
        connection.commit()     
    except Exception as e:
        connection.rollback()
        raise e
    print(msg)

alter("ALTER TABLE address MODIFY COLUMN id int(15);","done")
alter("ALTER TABLE address MODIFY COLUMN email varchar(35);","done")
alter("ALTER TABLE address MODIFY COLUMN person_id int(35);","done")

With multi=True:

cur = connection.cursor()

def alter(state,msg):
    result = cur.execute(state,multi=True)
    result.send(None)
    print(msg,result)
try:
    alter("ALTER TABLE address MODIFY COLUMN id int(45)","done")
    alter("ALTER TABLE address MODIFY COLUMN email varchar(25)","done")
    alter("ALTER TABLE address MODIFY COLUMN person_id int(25);","done")
    connection.commit()
except Exception as e:
    connection.rollback()
    raise e