Execute .sql file in Python with MySQLdb
mysql, mysql-python, python
Solution
`MySQLdb` seems to allow this out of the box, you just have to call `cursor.nextset()` to cycle through the returned result sets.
db = conn.cursor()
db.execute('SELECT 1; SELECT 2;')
more = True
while more:
print db.fetchall()
more = db.nextset()
If you want to be absolutely sure the support for this is enabled, and/or disable the support, you can use something like this:
MYSQL_OPTION_MULTI_STATEMENTS_ON = 0
MYSQL_OPTION_MULTI_STATEMENTS_OFF = 1
conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON)
# Multiple statement execution here...
conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)
Problem
I have a .sql file containing a bunch of SQL queries, with each query spanning multiple lines. I want to execute these queries in MySQL via Python using `MySQLdb`. `sqlite3` has "a nonstandard shortcut" for this purpose called `executescript()`, but there doesn't seem to be any equivalent function in `MySQLdb`. I noticed this old question from 2 years ago which asks the same thing, but I found the answers unsatisfying. The answers are basically: Use `subprocess` to run the `mysql` command and send it your .sql file. This works, but it is rather inelegant, and it introduces unwanted complexity with error handling and such. If each query is on a single line, just execute each line separately. But in my case, they span multiple lines, so this won't work. If each query is not on a single line, somehow join them. But, how? I mean, I can hack up something easily enough so there's no need for you to reply with half-baked answers here, and maybe that's what I'll end up doing, but is there already an established library that does this? I'd feel more comfortable with a comprehensive and correct solution rather than a hack.