Can't get MySQL source query to work using Python mysqldb module

Question

I have the following lines of code:

sql = "source C:\\My Dropbox\\workspace\\projects\\hosted_inv\\create_site_db.sql"
cursor.execute (sql)

When I execute my program, I get the following error:

Error 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 C:\My Dropbox\workspace\projects\hosted_inv\create_site_db.sql' at line 1

Now I can copy and past the following into mysql as a query:

source C:\\My Dropbox\\workspace\\projects\\hosted_inv\\create_site_db.sql

And it works perfect. When I check the query log for the query executed by my script, it shows that my query was the following:

source C:\\My Dropbox\\workspace\\projects\\hosted_inv\\create_site_db.sql

However, when I manually paste it in and execute, the entire create_site_db.sql gets expanded in the query log and it shows all the sql queries in that file.

Am I missing something here on how mysqldb does queries? Am I running into a limitation. My goal is to run a sql script to create the schema structure, but I don't want to have to call mysql in a shell process to source the sql file.

Any thoughts? Thanks!

Answer 1

As others said, you cannot use the command source in MySQLdb Python API

So, instead of running that, load the file and execute it

Lets say your .sql file has

create database test;

Read the content like

sql=open("test.sql").read()

And then execute it

cursor.execute(sql);

You will get new database "test"

Answer 2

The source command is one of the built-in commands recognized only by the mysql command-line client. It is not supported as a statement you can execute via any API.

Some people think you can simply split an SQL script file on the ";" statement terminator and call execute() on each line you get. But there are numerous exception cases:

• Statements that are built-in commands like CONNECT, SOURCE, CHARSET, WARNINGS, QUIT, etc.
• Note that built-in commands don't need to terminate in ; for example DELIMITER.
• Statements that contain ; but not as a terminator, like CREATE TRIGGER.
• Statements that contain ; inside string literals or comments or even quoted identifiers.
• Comments lines.

To load an SQL script programmatically, you'd have to duplicate a fair amount of the functionality of the mysql client. So it's best if you just fork a process to actually execute that client program with the script as input.

See also:

• Loading .sql files from within PHP
• is it possible to call a sql script from a stored procedure in another sql script?
• composing multiple mysql scripts
• Running Database scripts in C#