loading DB schema with MySQLdb

[sf_mike]

I'm trying to load a database schema file (output of mysqldump -d) programmatically, but when I execute the "source path/to/file.sql", I get an exception. Here's the exception:

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "fixture.py", line 17, in setup
dbc.execute("source %s" % schema_file)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'source ../../sql/test.sql' at line 1")

Here's the relevant lines of code:

dbc = dbh.cursor()
db_name = "test_%d" % os.getpid()
dbc.execute("create database %s" % db_name)
dbc.execute("use " + db_name)
dbc.execute("set foreign_key_checks=0")
dbc.execute("source %s" % schema_file)

The MySQL error log shows that all of the queries above are being sent to MySQL. First guess is that MySQLdb doesn't see a result set for "source" and throws an exception. Using dbh.query() produces the same result.

I'd greatly appreciate any help.

Thanks.

[Andy Dustman]

Obviously the "source" statement is causing the problem. I don't think "source" is valid outside of the mysql command-line client, and that's why you get a syntax error.

If your schema_file contains a single SQL statement (probably not, but let's pretend), you could do this:

dbc.execute(open(schema_file).read())

If your schema_file contains multiple statements, then you can enable them when you create the connection with:

import MySQLdb
from MySQLdb.constants import CLIENT

db = MySQLdb.connect(..., client_flag=CLIENT.MULTI_STATEMENTS)

I think you need at least MySQL-4.1 for this to work.