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

'source' is not an SQL command, but an internal command of the mysql command line client.

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 SOURCE or USE or even QUIT.
• Statements that don't terminate in ; like DELIMITER.
• Statements that contain ; but not as a terminator, like CREATE TRIGGER.
• Comments.

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#

Answer 3

I believe the "source" command is specific to the mysql shell executable - it is not an sql command and cannot be interpreted correctly when executed as an sql statement.

To achieve your goal, you probably need to read your script file and parse it into individual sql statements, then execute them one at a time with your cursor.

Answer 4

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 5

I ran into the same problem!

As a solution I installed the library sqlparse and used the sqlparse.split( sql ) results. I had to check that sql_parts don't include blank lines as solo statements... Otherwise "WOW" sqlparse is pretty great and exactly what I needed!

import sqlparse 
....
sql = open("test.sql").read()
sql_parts = sqlparse.split( sql )
for sql_part in sql_parts:
    if sql_part.strip() ==  '':
        continue 
    cursor.execute( sql_part )

FYI: If you do not run each statement on its own you may get the error "Commands out of sync; you can't run this command now". I only got this error after I added some more queries to my sql file - not the first time around.