MySQL 'IF EXISTS' command causes an error when used in python

Question

I'm trying to learn how to use SQL in python with MySQL (since all my projects use MySQL), but it seems to have issues with the IF EXISTS statement.

from command line:

DROP TABLE IF EXISTS accessLogs;

returns:

Query ok, 0 rows affected, 1 warning (o.00 sec)

and the table is successfully dropped. However if I use python's execute() method:

cursor.execute("DROP TABLE IF EXISTS accessLogs")

I get this error:

pydbCreate.py:12: Warning: Unknown table 'accessLogs'
    cursor.execute("DROP TABLE IF EXISTS accessLogs")

Is there a way to get around this error? And do any other MySQL commands cause similar issues?

Answer 1

Every warning that MySQL generates will be raised as a Warning by MySQLdb 1.2, unless you're using a use-result cursor. There is no code that discriminates between different warnings.

MySQLdb does not provide enough information to let Python's warnings module filter things out yourself in any way except by a regexp on the message. In particular, the level and code are already lost by the time the warnings filter gets to it.

So, here are your options:

---

Use something more flexible than MySQLdb. Its successor moist may still not be ready, but competitors like PyMySQL are.

---

Ignore warnings around every call that you know might print warnings you don't care about:

with warnings.catch_warnings():
    warnings.simplefilter('ignore')
    cursor.execute('DROP TABLE IF EXISTS sdfdsfds') # will not warn

---

Ignore warnings by message string regexp:

warnings.filterwarnings('ignore', 'unknown table')

---

Turn off MySQLdb's warning raising entirely, e.g., using the quick&dirty hack of setting the cursor._defer_warnings = True (see the code to see why this works). (Note that you can also use this flag to turn it back on and back off again, so you only skip warnings around certain commands. But if you're going to do that, use the warnings module.)

---

Fork, monkeypatch, or subclass MySQLdb to override its Cursor._warning_check function to discriminate in some way based on the warning level and/or code.