6

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?

grim_v3.0
  • 387
  • 1
  • 4
  • 11

2 Answers2

11

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.

abarnert
  • 354,177
  • 51
  • 601
  • 671
  • 1
    @grim_v3.0: Which "that"? I gave a half-dozen different suggestions; it might be useful for future readers to know which one you followed (and why) so they know that one will work for them. – abarnert Nov 19 '13 at 22:31
  • @TML: Your edit removed all the code formatting, making the answer unreadable. – abarnert Nov 19 '13 at 23:15
  • Fair enough - they don't really render on my UA, so I didn't realize they were there. – TML Nov 19 '13 at 23:21
  • 1
    @TML: I've tried to find some way to put (multiline) code underneath other formatting options, but (except for quotes) it seems to be either impossible or beyond me… – abarnert Nov 19 '13 at 23:42
  • @abarnert: sorry, the warnings.filterwarnings('ignore', 'unknown table') is what fixed it. – grim_v3.0 Nov 23 '13 at 03:52
  • @abarnert, How will the monkey patch work when a future version of mysql change the text contents of the warning message? – Pacerier Mar 20 '15 at 04:52
  • @Pacerier: since the monkey patch involves "discriminate in some way based on the warning level and/or code", it won't be affected at all by the text contents. Of course it's still relying on internals of the implementation, but when you're monkeypatching an outdated module you hopefully already know that. – abarnert Mar 21 '15 at 07:58
  • @abarnert. still facing the same issue. Is there any other way to do run the `DROP TABLE IF EXISTS` command without any warning? – Arijit Panda Feb 13 '19 at 15:35
-2

You need to specify the database if you have not specified it with the USE statement.

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

edit

It appears now that the concern is with the warning itself. The warning is just that - a warning. As noted by abarnert, MySQLdb reports MySQL warnings to Python's warnings module. The default behavior is to print to the stderr stream.

This isn't an error condition.

Use one of abarnert's suggestions for filtering the warning so it doesn't appear in the stderr stream.

Jeremy Brown
  • 17,880
  • 4
  • 35
  • 28
  • I assumed that was what db = MySQLdb.connect("localhost","user","password","database_name") did. – grim_v3.0 Nov 19 '13 at 19:22
  • Assuming that the positions of the arguments are correct (MySQLdb documentation discourages positional args in favor of kwargs), then yes it should. Are you receiving that warning when that table no longer exists? If so, then is the problem that you are seeing the warning printed? – Jeremy Brown Nov 19 '13 at 19:33
  • If so, you can suppress warnings. See http://stackoverflow.com/questions/14463277/how-to-disable-python-warnings – Jeremy Brown Nov 19 '13 at 19:33
  • I thought it was just a warning too, but when I check the database through command line, the table didn't show up. – grim_v3.0 Nov 19 '13 at 22:17
  • I'm not a mysql expert, but AFAICT, it *is* just a warning - note that the mysql command-line also gave a warning: Query ok, 0 rows affected, *1 warning* (0.00 sec) – TML Nov 19 '13 at 22:35
  • @TML: Yes, it is just a warning. And `SHOW WARNINGS` in the mysql command line will show you the details of the warning—level, code, and message, where the message is exactly what you see in the Python warning. But it's reasonable to want your code to run without spamming scary warnings out to stderr. – abarnert Nov 19 '13 at 23:17