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

一時疏乎，同一個 cursor.execute 下了 2個 sql command 就出錯了。

除了可以分成 2 次下 cursor.execute() 也可以在 cursor.execute 時增加 multi=True 參數。

合在一起下的  example:

Using a for loop at cursor.execute with multi=True worked. I don’t know why we need to loop through.

for result in cursor.execute(SQL, multi=True):
    pass

Without loop just cursor.execute(SQL, multi=True) did not do any changes in the database.

import mysql.connector

cnx = mysql.connector.connect(user='scott', database='test')
cursor = cnx.cursor()

SQL = '''
    update my_table 
    set 
    LAY = 'P6682'
    , BLK = 'P6682'
    , ANI = 'P6682'
    where
    Shot = 'SH01';

    update my_table 
    set 
    LAY = '1863'
    , BLK = '1863'
    , ANI = '1863'
    where
    Shot = 'SH02'
'''

for result in cursor.execute(SQL, multi=True):
    pass

cnx.commit()
cur.close()
cnx.close()
cnx.disconnect()

from:

https://stackoverflow.com/questions/15288594/update-database-with-multiple-sql-statments

PS: multi=True is not necessary in a loop.

認識所有的參數：

Connector/Python Connection Arguments
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

Table 7.1 Connection Arguments for Connector/Python