current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
 
That did the trick, thanks. –  grim_v3.0 Nov 19 '13 at 22:27
 
@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
show 1 more comment
up vote -1 down vote

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.

share|improve this answer
 
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 stackoverflow.com/questions/14463277/… –  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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.