Geek asks and answers

Execute .sql file in Python with MySQLdb

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.

sqlite3has a "non-standard shortcut" for this purpose, calledexecutescript() , but there seems to be no equivalent function in MySQLdb.

I noticed this old question from 2 years ago that asks the same thing, but I found the answers unsatisfactory. The answers are mostly:

Use subprocessto run the command mysqland send it to the .sql file.

This works, but it is rather inelegant, and it introduces undesirable complexity in error handling, etc.

If each query is on the same line, just run each line separately.

But in my case, they span multiple lines, so this will not work.

If each request is not on the same line, somehow join them.

But how? I mean, I can hack something simple enough, so you don’t have to answer with semi-white answers here, and maybe this is what I will eventually do, but is there an already installed library that does this? I would feel more comfortable with a complete and correct solution, rather than with a hack.

+5
source share
1 answer

MySQLdbseems to allow this out of the box, you just need to call cursor.nextset()to loop back the returned result sets.

db = conn.cursor()
db.execute('SELECT 1; SELECT 2;')

more = True
while more:
    print db.fetchall()
    more = db.nextset()

, / , - :

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)
+3

More articles:


All Articles