MySQLdb breaks syntax IF [NOT] EXISTS for me

[Ofer Nave]

I'm new to Python, and just started using the MySQLdb module today. I'm trying to get this SQL statement to work:

'CREATE DATABASE IF NOT EXISTS foo'

I wanted to verify that it doesn't cause an error when database foo already exists. The weird thing is that it succeeds when I run it manually, but fails when I use it in my python script. I'm using the same username/password to connect to mysql either way (root, no pw), so it's not a permissions issue. I used the python script to successfully create the database the first time, so I know that at the very least my script is successfully able to connect to mysql and execute a CREATE statement.

[details]

MySQL: 5.0.27-max
Python: 2.3.4
MySQLdb: 1.2.2

Here's a test script I wrote:

---

import MySQLdb
db = MySQLdb.connect(user='root')
c = db.cursor()
if (sys.argv[1] == '1'):
c.execute('CREATE DATABASE IF NOT EXISTS foodb')
elif (sys.argv[1] == '2'):
c.execute('USE foodb')
c.execute('CREATE TABLE IF NOT EXISTS footable (foo_id INT)')
elif (sys.argv[1] == '3'):
c.execute('USE foodb')
c.execute('DROP TABLE IF EXISTS footable')
elif (sys.argv[1] == '4'):
c.execute('DROP DATABASE IF EXISTS foodb')

---

Here's a log of me using it:

---

[ofer@rnd01 ~/]$ test.py 1
[ofer@rnd01 ~/]$ test.py 1
/home/ofer/test.py:11: Warning: Can't create database 'foodb'; database exists
c.execute('CREATE DATABASE IF NOT EXISTS foodb')
[ofer@rnd01 ~/]$ test.py 2
[ofer@rnd01 ~/]$ test.py 2
/home/ofer/test.py:14: Warning: Table 'footable' already exists
c.execute('CREATE TABLE IF NOT EXISTS footable (foo_id INT)')
[ofer@rnd01 ~/]$ test.py 3
[ofer@rnd01 ~/]$ test.py 3
/home/ofer/test.py:17: Warning: Unknown table 'footable'
c.execute('DROP TABLE IF EXISTS footable')
[ofer@rnd01 ~/]$ test.py 4
[ofer@rnd01 ~/]$ test.py 4
/home/ofer/test.py:19: Warning: Can't drop database 'foodb'; database doesn't exist
c.execute('DROP DATABASE IF EXISTS foodb')

---

Here's a log of me executing the same commands manually through the mysql command without encountering errors:

---

mysql> create database if not exists foodb;
Query OK, 1 row affected (0.00 sec)

mysql> create database if not exists foodb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use foodb;
Database changed
mysql> create table if not exists footable (foo_id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists footable (foo_id int);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists footable;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists footable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database if exists foodb;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database if exists foodb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

---

Any ideas?

[Andy Dustman]

Looks like it works to me. A Warning is just that: A Warning. It doesn't mean something failed. It is not the same as an exception, though it's possible to raise it as an exception. But that's not the case here. If there were an actual error/exception, there'd be a traceback.

To prove this, print something at the end of your program. Or otherwise, you can have this:

db.select_db("foodb")

Oh, and you shouldn't execute "USE somedb" as SQL; use db.select_db() instead.

[Ofer Nave]

You're right that its a warning, not an error. I suppose I misrepresented the situation by using the phrase "it fails". But it still shouldn't be generating a warning. Certainly MySQL doesn't. I'm interested to know why a warning is generated, and if there's a way I can prevent it.

As for the db.select_db(), thanks for the tip! I'll switch to that.

[Andy Dustman]

Yes, it does. Try your commends in the command-line client again, but after each statement, add this statement:

SHOW WARNINGS;

[Ofer Nave]

You're right. Didn't know about that MySQL command.

So I guess the only question is how to prevent the warnings from being output by MySQLdb. I read through PEP 249 carefully, and the only mention of warnings I could find was a cursor object attributed called .messages, which contains all the warnings, but I believe they are appended to it at the same time they are printed to STDERR, so .messages is irrelevant to my purpose.

I read through the MySQLdb User's Guide, but the only promising avenue was this:

"info()
Returns some information about the last query. Normally you don't need to check this. If there are any MySQL warnings, it will cause a Warning to be issued through the Python warning module. By default, Warning causes a message to appear on the console. However, it is possible to filter these out or cause Warning to be raised as exception. See the MySQL docs for mysql_info(), and the Python warning module. (Non-standard)"

Filtering the warnings out is exactly what I want, but this paragraph points the reader to the MySQL API docs if they want to figure out how. So there I went:

"Retrieves a string providing information about the most recently executed statement, but only for the statements listed here."
-- http://dev.mysql.com/doc/refman/5.0/en/mysql-info.html

The page listed the applicable statements. CREATE/DROP were not among them. In addition, the docs say nothing about how to use this function to filter out warnings.

So, I'm still stuck.

[Andy Dustman]

The warnings are safe to ignore. However, if you really can't stand to look at them, read up on the standard Python warnings module.

http://docs.python.org/lib/module-warnings.html

This will tell you how to filter warnings.

The docs for info() really do have a typo, though: They refer the warning module, but it's warnings. And it doesn't "point the reader to the MySQL API docs" to figure out how to filter warnings; it points you to the Python docs. mysql_info() has nothing to do with filtering warnings, only finding out if you have any.