使用MySQLdb在Python中执行.sql文件 [英] Execute .sql file in Python with MySQLdb

问题描述

我有一个.sql文件，其中包含一堆SQL查询，每个查询跨越多行.我想使用MySQLdb通过Python在MySQL中执行这些查询.

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为此目的具有非标准快捷方式"，称为executescript() ，但MySQLdb中似乎没有任何等效功能.

sqlite3 has "a nonstandard shortcut" for this purpose called executescript(), but there doesn't seem to be any equivalent function in MySQLdb.

我注意到了 2年前的这个老问题，它提出了相同的要求事情，但我发现答案并不令人满意.答案基本上是:

I noticed this old question from 2 years ago which asks the same thing, but I found the answers unsatisfying. The answers are basically:

使用subprocess运行mysql命令并将其发送给您的.sql文件.

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.

但是，如何?我的意思是，我可以很轻松地破解一些东西，因此您无需在这里回答半熟的答案，也许这就是我最终要做的事情，但是已经有一个建立好的库可以做到这一点吗?有了全面而正确的解决方案，而不是hack，我会感到更自在.

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.

推荐答案

MySQLdb似乎允许开箱即用，您只需要调用cursor.nextset()即可循环浏览返回的结果集.

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)

这篇关于使用MySQLdb在Python中执行.sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！