5

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.

Community
  • 1
  • 1
dumbmatter
  • 9,351
  • 7
  • 41
  • 80
  • I had submitted an answer that invovled parsing the file and building the queries but I think that's what you meant by "a hack" so I deleted it. – acattle May 15 '12 at 06:57

1 Answers1

3

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)
Aleksi Torhamo
  • 6,452
  • 2
  • 34
  • 44
  • 1
    MySQL .sql scripts contain some [builtin commands](http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html) that are processed by the `mysql` client, but are not recognized by the SQL parser in the server. So even though MySQLdb can support multi-query, you can't feed it any arbitrary .sql script as input. – Bill Karwin Oct 22 '14 at 22:58
  • @aleksi you assumes dangerous assumptions: i don't think i will work on a large file (x100MB +), not even smaller. There's no generator on the commands. Even setting up a command generator is difficult since you need a SQL file parser; line by line wouldn't necessarily work. spliiting by semicolon is bad. etc. – Berry Tsakala Nov 18 '15 at 18:04
  • @BerryTsakala: I'm having a really hard time making sense of your comment. That said; Yes, I do make assumptions, this method won't work for every use case (as already nicely noted by Bill above), but it's more or less exactly what this question asked for. And what [on earth] do you mean by "dangerous"? This is pretty much as safe as it gets, unlike trying to parse the SQL queries yourself. Yes, this won't work for huge files, you'd bump into the MySQL maximum packet size. Why are you talking about parsing SQL since this method explicitly **avoids** that? – Aleksi Torhamo Nov 21 '15 at 21:40