Search code examples
python-3.xmysql-connector-python

mysql/connector Execute does nothing

Please give me any advise, stuck quite deep... Here is my code, and it does nothing. Database untouched, no errors, nothing

query = """DROP TABLE test;
    CREATE TABLE test (first_row text);"""
cursor.execute(query, multi=True)
db.commit()

Interesting thing is that query can even contain syntax error in second statement, code still executes without errors. Also, without multi there is traceback "InterfaceError: Use multi=True when executing multiple statements"

query = """DROP TABLE test;
    CREATE TABBBBBBLE test (first_row text);"""
cursor.execute(query, multi=True)
db.commit()

Separated queries works perfect, database actually changes, so there is no mistake in db and cursor objects creation:

query1 = "DROP TABLE test"
query2 = "CREATE TABLE test (first_row text)"
cursor.execute(query1)
cursor.execute(query2)
db.commit()

This is my very first experience with mysql/connector. Server mysql just installed with defaults. In console mysql> everything works fine as it should. But python script doesn't work. And even worse - there is NO TRACEBACK. Yes I can run only single statements... but it is not good .)

Got any idea how I should debug this problem?

Solution

  • The reason you are not seeing any traceback is because what is returned when you use multi=Trueis a python iterator of cursors which enables processing the result of each statement. The easiest way to access the results (or lack thereof or errors) of the queries is to loop through the returned iterator of cursors and fetch their corresponding rows or handle the errors.

    Further, if the first query of your multiple queries is not executable (i.e. causes some kind of error), the remaining queries will not get executed. Therefore, if you try to loop through the returned iterator, an exception will be raised with the error message being the error raised by the MySQL server. In general, the queries will get executed until there is a query that cannot be successfully executed,

    Using the code you provided, the following snippets should serve as an illustration:

    # Multiple queries
query = """DROP TABLE test; CREATE TABLE test (first_col text)"""

# Loop through the results
for rslt in cursor.execute(query, multi=True):
    print(rslt.fetchone())

    This should raise a mysql.connector.errors.ProgrammingError exception since the first query will not execute successfully. However, if you were to reverse the order of the queries, you would not encounter any exceptions because the queries will execute successfully:

    query = """CREATE TABLE test (first_col text); DROP TABLE test"""

# Loop through the results
for rslt in cursor.execute(query, multi=True):
    print(rslt.fetchone())

    The code should run just fine, even though you would see Nones printed.

    In general, when working with the multi parameter set to True, save the returned iterator and loop through your results one at a time. The following should always tell you something when an error occurs:

    # Set three queries (2 good and the last one bad)
query = """CREATE TABLE test (first_col text); DROP TABLE test; select * from test"""

# Save the returned iterator in a variable
results = cursor.execute(query, multi=True)
db.commit()

# Do some looping with exception handling
while True:
    try:
        rslt = next(results)
        print(rslt.fetchall())
    except Exception as e:
        print("Error encountered: %s" % e)
        break

    This code will run the first two queries successfully. The table will get created and then deleted. However, the third will fail because there will be no table named test to query from. Therefore you should see something like Error encountered: 1051 (42S02): Unknown table 'test'.

    In sum, you should loop through the returned results in order to both get your results and check for errors. The combination of while True and exception handling should work fairly well in most cases.

    Clarification about iterators:

    The variables in the iterator are lazily evaluated when the next() method is called. This means that if you are using while True and next(results), it is the next(results) operation that triggers the execution of the query. However, if you were using a for loop, then the queries would get executed as you loop. In any case, when using CUR.execute(query, multi=True), the queries only truly execute when you're looping.

    I hope this proves useful.