使用 python MySQLdb 执行 *.sql 文件

execute *.sql file with python MySQLdb

提问by Mykola Kharechko

How can execute sql script stored in *.sql file using MySQLdb python driver. I was trying

如何使用 MySQLdb python 驱动程序执行存储在 *.sql 文件中的 sql 脚本。我在尝试

cursor.execute(file(PATH_TO_FILE).read())

but this doesn't work because cursor.executecan run only one sql command at once. My sql script contains several sql statements instead. Also I was trying

但这不起作用，因为cursor.execute一次只能运行一个 sql 命令。我的 sql 脚本包含几个 sql 语句。我也在尝试

cursor.execute('source %s'%PATH_TO_FILE)

but also with no success.

但也没有成功。

采纳答案by Thomas K

for line in open(PATH_TO_FILE):
    cursor.execute(line)

This assumes you have one SQL statement per line in your file. Otherwise you'll need to write some rules to join lines together.

这假设您的文件中每行有一个 SQL 语句。否则，您需要编写一些规则来将行连接在一起。

回答by jdferreira

From python, I start a mysql process to execute the file for me:

从 python，我启动一个 mysql 进程来为我执行文件：

from subprocess import Popen, PIPE
process = Popen(['mysql', db, '-u', user, '-p', passwd],
                stdout=PIPE, stdin=PIPE)
output = process.communicate('source ' + filename)[0]

回答by Aleksi Torhamo

At least MySQLdb1.2.3 seems to allow this out of the box, you just have to call cursor.nextset()to cycle through the returned result sets.

至少MySQLdb1.2.3 似乎允许开箱即用，您只需调用cursor.nextset()以循环访问返回的结果集。

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)

回答by nonbeing

I also needed to execute a SQL file, but the catch was that there wasn't one statement per line, so the accepted answer didn't work for me.

我还需要执行一个 SQL 文件，但问题是每行没有一个语句，所以接受的答案对我不起作用。

The SQL file I wanted to execute looked like this:

我要执行的 SQL 文件如下所示：

-- SQL script to bootstrap the DB:
--
CREATE USER 'x'@'%' IDENTIFIED BY 'x';
GRANT ALL PRIVILEGES ON mystore.* TO 'x'@'%';
GRANT ALL ON `%`.* TO 'x'@`%`;
FLUSH PRIVILEGES;
--
--
CREATE DATABASE oozie;
GRANT ALL PRIVILEGES ON oozie.* TO 'oozie'@'localhost' IDENTIFIED BY 'oozie';
GRANT ALL PRIVILEGES ON oozie.* TO 'oozie'@'%' IDENTIFIED BY 'oozie';
FLUSH PRIVILEGES;
--
USE oozie;
--
CREATE TABLE `BUNDLE_ACTIONS` (
  `bundle_action_id` varchar(255) NOT NULL,
  `bundle_id` varchar(255) DEFAULT NULL,
  `coord_id` varchar(255) DEFAULT NULL,
  `coord_name` varchar(255) DEFAULT NULL,
  `critical` int(11) DEFAULT NULL,
  `last_modified_time` datetime DEFAULT NULL,
  `pending` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `bean_type` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`bundle_action_id`),
  KEY `I_BNDLTNS_DTYPE` (`bean_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
--

Some statements in the above file lie on a single line and some statements also span multiple lines (like the CREATE TABLE at the end). There are also a few SQL inline comment lines that begin with "--".

上面文件中的一些语句位于一行，一些语句也跨越多行（如末尾的 CREATE TABLE）。还有一些 SQL 内联注释行以“--”开头。

As suggested by ThomasK, I had to write some simple rules to join lines into a statement. I ended up with a function to execute a sql file:

正如 ThomasK 所建议的，我必须编写一些简单的规则来将行连接到语句中。我最终得到了一个执行 sql 文件的函数：

def exec_sql_file(cursor, sql_file):
    print "\n[INFO] Executing SQL script file: '%s'" % (sql_file)
    statement = ""

    for line in open(sql_file):
        if re.match(r'--', line):  # ignore sql comment lines
            continue
        if not re.search(r';$', line):  # keep appending lines that don't end in ';'
            statement = statement + line
        else:  # when you get a line ending in ';' then exec statement and reset for next statement
            statement = statement + line
            #print "\n\n[DEBUG] Executing SQL statement:\n%s" % (statement)
            try:
                cursor.execute(statement)
            except (OperationalError, ProgrammingError) as e:
                print "\n[WARN] MySQLError during execute statement \n\tArgs: '%s'" % (str(e.args))

            statement = ""

I'm sure there's scope for improvement, but for now it's working pretty well for me. Hope someone finds it useful.

我确信还有改进的余地，但现在它对我来说效果很好。希望有人觉得它有用。

回答by Антон Дзык

Load mysqldump file:

加载mysqldump文件：

for line in open(PATH_TO_FILE).read().split(';\n'):
    cursor.execute(line)

回答by Yuhao

The accepted answer will encounter problems when your sql script contains empty lines and your query sentence spans multiple lines. Instead, using the following approach will solve the problem:

当您的 sql 脚本包含空行并且您的查询语句跨越多行时，接受的答案将遇到问题。相反，使用以下方法将解决问题：

f = open(filename, 'r')
query = " ".join(f.readlines())
c.execute(query)

回答by SilentSteel

Here's a code snippet that will import a typical .sqlthat comes from an export. (I used it with exports from Sequel Pro successfully.) Deals with multi-line queries and comments (#).

这是一个代码片段，它将导入.sql来自导出的典型。（我成功地将它用于 Sequel Pro 的导出。）处理多行查询和评论 ( #)。

• Note 1: I used the initial lines from Thomas K's response but added more.
• Note 2: For newbies, replace the DB_HOST, DB_PASS etc with your database connection info.

• 注 1：我使用了 Thomas K 回复中的初始行，但添加了更多内容。
• 注意 2：对于新手，将 DB_HOST、DB_PASS 等替换为您的数据库连接信息。

---

---

import MySQLdb
from configdb import DB_HOST, DB_PASS, DB_USER, DB_DATABASE_NAME

db = MySQLdb.connect(host=DB_HOST,    # your host, usually localhost
                     user=DB_USER,         # your username
                     passwd=DB_PASS,  # your password
                     db=DB_DATABASE_NAME)        # name of the data base

cur = db.cursor()

PATH_TO_FILE = "db-testcases.sql"

fullLine = ''

for line in open(PATH_TO_FILE):
  tempLine = line.strip()

  # Skip empty lines.
  # However, it seems "strip" doesn't remove every sort of whitespace.
  # So, we also catch the "Query was empty" error below.
  if len(tempLine) == 0:
    continue

  # Skip comments
  if tempLine[0] == '#':
    continue

  fullLine += line

  if not ';' in line:
    continue

  # You can remove this. It's for debugging purposes.
  print "[line] ", fullLine, "[/line]"

  try:
    cur.execute(fullLine)
  except MySQLdb.OperationalError as e:
    if e[1] == 'Query was empty':
      continue

    raise e

  fullLine = ''

db.close()

回答by Yohan Obadia

Another solution that allows to leverage on the MySQL interpreter without any parsing is to use the os.systemcommand to run a MySQL prompt command directly inside python:

另一个允许在不进行任何解析的情况下利用 MySQL 解释器的解决方案是使用该os.system命令直接在 python 中运行 MySQL 提示命令：

from os import system
USERNAME = "root"
PASSWORD = "root"
DBNAME = "pablo"
HOST = "localhost"
PORT = 3306
FILE = "file.sql"
command = """mysql -u %s -p"%s" --host %s --port %s %s < %s""" %(USERNAME, PASSWORD, HOST, PORT, DBNAME, FILE)
system(command)

It avoids any parsing error when for example you would have a string variable with a smiley ;-)in it or if you check for the ;as the last character, if you have comments afterward like SELECT * FROM foo_table; # selecting data

它避免了任何解析错误，例如，当你有一个带有笑脸的字符串变量，;-)或者如果你检查;最后一个字符，如果你之后有评论，比如SELECT * FROM foo_table; # selecting data

回答by BuvinJ

Many of the answers here have serious flaws...

这里的许多答案都有严重的缺陷......

First don't try to parse an open ended sql script yourself! If you think that is easily done, you aren't aware of how robust and complicated sql can be. Serious sql scripts certainly involve statements and procedure definitions spanning multiple lines. It is also common to explicitly declare and change delimiters the in middle of your scripts. You can also nest source commands within each other. For so many reasons, you want to run the script through the MySQL client and allow it to handle the heavy lifting. Trying to reinvent that is fraught peril and a huge waste of time. Maybe if you are the only one writing these scripts, and you are not writing anything sophisticated you could get away with that, but why limit yourself to such a degree? What about machine generated scripts, or those written by other developers?

首先不要试图自己解析一个开放式的 sql 脚本！如果您认为这很容易完成，那么您就不会意识到 sql 的健壮性和复杂性。严肃的 sql 脚本肯定涉及跨越多行的语句和过程定义。在脚本中间显式声明和更改分隔符也很常见。您还可以在彼此之间嵌套源命令。出于多种原因，您希望通过 MySQL 客户端运行脚本并允许它处理繁重的工作。试图重新发明那是充满危险和浪费时间的。也许如果你是唯一一个写这些脚本的人，而且你没有写任何复杂的东西，你可以逃脱，但为什么要把自己限制在这样的程度呢？机器生成的脚本或其他开发人员编写的脚本呢？

The answer from @jdferreira is on the right track, but also has problems and weaknesses. The most significant is that a security hole is being opened up by sending the connection parameters to the process in that manner.

@jdferreira 的答案是正确的，但也存在问题和弱点。最重要的是，通过以这种方式向进程发送连接参数，打开了一个安全漏洞。

Here's a solution / example for your copy & paste pleasure. My extended discussion follows:

这是您复制和粘贴乐趣的解决方案/示例。我的扩展讨论如下：

First, create a separate config file to save your user name and password.

首先，创建一个单独的配置文件来保存您的用户名和密码。

db-creds.cfg

db-creds.cfg

[client]
user     = XXXXXXX
password = YYYYYYY

Slap the right file system permissions on that, so the python process can read from it, but no one can view that who should not be able to.

为其设置正确的文件系统权限，以便 python 进程可以从中读取，但没有人可以查看谁不应该能够读取。

Then, use this Python (in my example case the creds file is adjacent to the py script):

然后，使用此 Python（在我的示例中，creds 文件与 py 脚本相邻）：

#!/usr/bin/python

import os
import sys
import MySQLdb
from subprocess import Popen, PIPE, STDOUT

__MYSQL_CLIENT_PATH = "mysql"

__THIS_DIR = os.path.dirname( os.path.realpath( sys.argv[0] ) )

__DB_CONFIG_PATH    = os.path.join( __THIS_DIR, "db-creds.cfg" )
__DB_CONFIG_SECTION = "client"

__DB_CONN_HOST = "localhost"
__DB_CONN_PORT = 3306

# ----------------------------------------------------------------

class MySqlScriptError( Exception ):

    def __init__( self, dbName, scriptPath, stdOut, stdErr ):
        Exception.__init__( self )
        self.dbName = dbName
        self.scriptPath = scriptPath
        self.priorOutput = stdOut
        self.errorMsg = stdErr                
        errNumParts = stdErr.split("(")        
        try : self.errorNum = long( errNumParts[0].replace("ERROR","").strip() )
        except: self.errorNum = None        
        try : self.sqlState = long( errNumParts[1].split(")")[0].strip() )
        except: self.sqlState = None

    def __str__( self ): 
        return ("--- MySqlScriptError ---\n" +
                "Script: %s\n" % (self.scriptPath,) +
                "Database: %s\n" % (self.dbName,) +
                self.errorMsg ) 

    def __repr__( self ): return self.__str__()

# ----------------------------------------------------------------

def databaseLoginParms() :        
    from ConfigParser import RawConfigParser
    parser = RawConfigParser()
    parser.read( __DB_CONFIG_PATH )   
    return ( parser.get( __DB_CONFIG_SECTION, "user" ).strip(), 
             parser.get( __DB_CONFIG_SECTION, "password" ).strip() )

def databaseConn( username, password, dbName ):        
    return MySQLdb.connect( host=__DB_CONN_HOST, port=__DB_CONN_PORT,
                            user=username, passwd=password, db=dbName )

def executeSqlScript( dbName, scriptPath, ignoreErrors=False ) :       
    scriptDirPath = os.path.dirname( os.path.realpath( scriptPath ) )
    sourceCmd = "SOURCE %s" % (scriptPath,)
    cmdList = [ __MYSQL_CLIENT_PATH,                
               "--defaults-extra-file=%s" % (__DB_CONFIG_PATH,) , 
               "--database", dbName,
               "--unbuffered" ] 
    if ignoreErrors : 
        cmdList.append( "--force" )
    else:
        cmdList.extend( ["--execute", sourceCmd ] )
    process = Popen( cmdList 
                   , cwd=scriptDirPath
                   , stdout=PIPE 
                   , stderr=(STDOUT if ignoreErrors else PIPE) 
                   , stdin=(PIPE if ignoreErrors else None) )
    stdOut, stdErr = process.communicate( sourceCmd if ignoreErrors else None )
    if stdErr is not None and len(stdErr) > 0 : 
        raise MySqlScriptError( dbName, scriptPath, stdOut, stdErr )
    return stdOut

If you want to test it out, add this:

如果要测试它，请添加以下内容：

if __name__ == "__main__": 

    ( username, password ) = databaseLoginParms()
    dbName = "ExampleDatabase"

    print "MySQLdb Test"
    print   
    conn = databaseConn( username, password, dbName )
    cursor = conn.cursor()
    cursor.execute( "show tables" )
    print cursor.fetchall()
    cursor.close()
    conn.close()
    print   

    print "-----------------"
    print "Execute Script with ignore errors"
    print   
    scriptPath = "test.sql"
    print executeSqlScript( dbName, scriptPath, 
                            ignoreErrors=True )
    print   

    print "-----------------"
    print "Execute Script WITHOUT ignore errors"                            
    print   
    try : print executeSqlScript( dbName, scriptPath )
    except MySqlScriptError as e :        
        print "dbName: %s" % (e.dbName,)
        print "scriptPath: %s" % (e.scriptPath,)
        print "errorNum: %s" % (str(e.errorNum),)
        print "sqlState: %s" % (str(e.sqlState),)
        print "priorOutput:"        
        print e.priorOutput
        print
        print "errorMsg:"
        print e.errorMsg           
        print
        print e
    print   

And for good measure, here's an example sql script to feed into it:

为了更好地衡量，这里有一个示例 sql 脚本可以输入它：

test.sql

测试文件

show tables;
blow up;
show tables;

So, now for some discussion.

所以，现在进行一些讨论。

First, I illustrate how to use MySQLdb along with this external script execution, while storing the creds in one shared file you can use for both.

首先，我将说明如何将 MySQLdb 与此外部脚本执行一起使用，同时将凭据存储在一个共享文件中，您可以同时使用这两个文件。

By using --defaults-extra-fileon the command line you can SECURELY pass your connection parameters in.

通过--defaults-extra-file在命令行上使用，您可以安全地传递连接参数。

The combination of either --forcewith stdin streaming the source command OR --executerunning the command on the outside let's you dictate how the script will run. That is by ignoring errors and continuing to run, or stopping as soon as an error occurs.

任相结合，--force与标准输入流的源命令或--execute在外面让运行命令是你决定了脚本的运行方式。那是通过忽略错误并继续运行，或者在发生错误时立即停止。

The order in which the results comeback will also be preserved via --unbuffered. Without that, your stdout and stderr streams will be jumbled and undefined in their order, making it very hard to figure out what worked and what did not when comparing that to the input sql.

结果返回的顺序也将通过--unbuffered. 否则，您的 stdout 和 stderr 流将按顺序混乱和未定义，因此在将其与输入 sql 进行比较时，很难弄清楚哪些有效，哪些无效。

Using the Popen cwd=scriptDirPathlet's you nest source commands within one another using relative paths. If your scripts will all be in the same directory (or a known path relative to it), doing this let's you reference those relative to where the top level script resides.

使用 Popen，cwd=scriptDirPath您可以使用相对路径将源命令相互嵌套。如果您的脚本都在同一个目录中（或相对于它的已知路径），这样做让您可以引用与顶级脚本所在位置相关的那些。

Finally, I threw in an exception class which carries all the info you could possibly want about what happened. If you are not using the ignoreErrors option, one of these exceptions will be thrown in your python when something goes wrong and script has stopped running upon that error.

最后，我加入了一个异常类，它包含您可能想要的关于发生的事情的所有信息。如果您没有使用 ignoreErrors 选项，那么当出现问题并且脚本因该错误而停止运行时，这些异常之一将在您的 Python 中抛出。

回答by madogan

This worked for me:

这对我有用：

with open('schema.sql') as f:
    cursor.execute(f.read().decode('utf-8'), multi=True)