使用MySQLdb执行几个SQL查询 [英] Executing several SQL queries with MySQLdb

问题描述

您将如何使用python执行几个SQL语句(脚本模式)?

How would you go about executing several SQL statements (script mode) with python?

尝试执行以下操作:

import MySQLdb
mysql = MySQLdb.connect(host='host...rds.amazonaws.com', db='dbName', user='userName', passwd='password')
sql = """
insert into rollout.version (`key`, `value`) VALUES ('maxim0', 'was here0');
insert into rollout.version (`key`, `value`) VALUES ('maxim1', 'was here1');
insert into rollout.version (`key`, `value`) VALUES ('maxim2', 'was here1');
"""
mysql.query(sql)

失败:

ProgrammingError:(2014年，发出命令 同步你不能运行这个命令 现在")

ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

我正在编写一个部署引擎，该引擎将接受来自多个人的SQL delta更改，并将其应用于版本部署中的数据库.

I'm writing an deployment engine that would accept SQL delta changes from several people and apply them to the DB on version deployment.

我已经研究了此代码 http://sujitpal.blogspot .com/2009/02/python-sql-runner.html 并实现了__sanitize_sql:

I've looked into this code http://sujitpal.blogspot.com/2009/02/python-sql-runner.html and implemented __sanitize_sql:

def __sanitize_sql(sql):
    # Initial implementation from http://sujitpal.blogspot.com/2009/02/python-sql-runner.html
    sql_statements = []

    incomment = False
    in_sqlcollect = False

    sql_statement = None
    for sline in sql.splitlines():
        # Remove white space from both sides.
        sline = sline.strip()

        if sline.startswith("--") or len(sline) == 0:
            # SQL Comment line, skip
            continue

        if sline.startswith("/*"):
            # start of SQL comment block
            incomment = True
        if incomment and sline.endswith("*/"):
            # end of SQL comment block
            incomment = False
            continue

        # Collect line which is part of 
        if not incomment:
            if sql_statement is None:
                sql_statement = sline
            else:
                sql_statement += sline

            if not sline.endswith(";"):
                in_sqlcollect = True

            if not in_sqlcollect:
                sql_statements.append(sql_statement)
                sql_statement = None
                in_sqlcollect = False

    if not incomment and not sql_statement is None and len(sql_statement) != 0:
        sql_statements.append(sql_statement)

    return sql_statements

if __name__ == "__main__":
    sql = sql = """update tbl1;
/* This
is my
beautiful 
comment*/
/*this is comment #2*/
some code...;
-- comment
sql code
"""
    print __sanitize_sql(sql)

不知道这是否是最好的解决方案，但似乎对解析SQL语句不太复杂有用.

Don't know if it's the best solution but seems to work for not too complex to parse SQL statements.

现在的问题是如何运行此代码，我可以做类似

The question now how to run this code, I can do something like this dude but it seems ugly, I'm not a python expert (we've been doing python here for just the past 2 weeks) but it seems that abusing cursor this way is hackish and not a good practice.

想法/博客文章会有所帮助.

Ideas / blog posts would be helpful.

谢谢你，
马克西姆.

Thanks you,
Maxim.

推荐答案

以下是使用executemany()的方法:

import MySQLdb
connection = MySQLdb.connect(host='host...rds.amazonaws.com', db='dbName', user='userName', passwd='password')
cursor = connection.cursor()

my_data_to_insert = [['maxim0', 'was here0'], ['maxim1', 'was here1'], ['maxim2', 'was here1']]
sql = "insert into rollout.version (`key`, `value`) VALUES (%s, %s);"

cursor.executemany(sql, my_data_to_insert)

connection.commit()
connection.close()

这篇关于使用MySQLdb执行几个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！