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

Argument Name Default Description
user (username*) The user name used to authenticate with the MySQL server.
password (passwd*) The password to authenticate the user with the MySQL server.
database (db*) The database name to use when connecting with the MySQL server.
host 127.0.0.1 The host name or IP address of the MySQL server.
port 3306 The TCP/IP port of the MySQL server. Must be an integer.
unix_socket The location of the Unix socket file.
auth_plugin Authentication plugin to use. Added in 1.2.1.
use_unicode True Whether to use Unicode.
charset utf8 Which MySQL character set to use.
collation utf8_general_ci Which MySQL collation to use.
autocommit False Whether to autocommit transactions.
time_zone Set the time_zone session variable at connection time.
sql_mode Set the sql_mode session variable at connection time.
get_warnings False Whether to fetch warnings.
raise_on_warnings False Whether to raise an exception on warnings.
connection_timeout Timeout for the TCP and Unix socket connections.
client_flags MySQL client flags.
buffered False Whether cursor objects fetch the results immediately after executing queries.
raw False Whether MySQL results are returned as is, rather than converted to Python types.
consume_results False Whether to automatically read result sets.
ssl_ca File containing the SSL certificate authority.
ssl_cert File containing the SSL certificate file.
ssl_key File containing the SSL key.
ssl_verify_cert False When set to True, checks the server certificate against the certificate file specified by the ssl_ca option. Any mismatch causes a ValueError exception.
force_ipv6 False When set to True, uses IPv6 when an address resolves to both IPv4 and IPv6. By default, IPv4 is used in such cases.
dsn Not supported (raises NotSupportedError when used).
pool_name Connection pool name. Added in 1.1.1.
pool_size 5 Connection pool size. Added in 1.1.1.
pool_reset_session True Whether to reset session variables when connection is returned to pool. Added in 1.1.5.
compress False Whether to use compressed client/server protocol. Added in 1.1.2.
converter_class Converter class to use. Added in 1.1.2.
failover Server failover sequence. Added in 1.2.1.
option_files Which option files to read. Added in 2.0.0.
option_groups [‘client’, ‘connector_python’] Which groups to read from option files. Added in 2.0.0.
allow_local_infile True Whether to enable LOAD DATA LOCAL INFILE. Added in 2.0.0.
use_pure True Whether to use pure Python or C Extension. Added in 2.1.1.

相關文章

2 comments

  1. This solved my issue:

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

    Not sure why it needed the loop, I was able to get it to work without the loop a couple times but it wasn’t consistently working.

    Thank you

寫留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *