使用Python生成SQL语句，批量插入多行到MySQL数据库中

Question

我正在尝试生成一个变量，这样我就可以用它来将多行数据插入到MySQL数据库中，或者保存到文件里。

因为我刚开始学Python，现在脑子里充满了各种新概念，我希望能得到一些确认，看看我的方法是否正确。

在SQL中，插入多条记录到表里的语法是这样的：

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

这是我写的代码：

import shelve

shelf = shelve.open('test.db', flag='r')

sql = "INSERT INTO 'myTableName' ( "
sql += ", ".join(shelf.itervalues().next())
sql = " ) VALUES "
for s in shelf: 

  sql += "( "
  sql += ', '.join(['\'%s\'' % ( value ) for (key, value) in shelf[s].items()])
  sql += " ),"

shelf.close()

print sql

这个代码几乎可以工作（最后的SQL语句多了一个逗号，而不是分号），但我相信一定有更简洁的方法。你能告诉我是什么吗？

Answer 1

因为这个问题特别问的是如何生成一个 SQL 插入语句，而不是如何将数据插入到 SQL 数据库，所以我提供以下代码：

def sterilize(s):
    if type(s) is str:
        return s.replace("'", "`").replace("\n", " ")
    else:
        return s

class insert_writer:
    def __init__(self, table_name, file_name, batch_size=42069):
        self.table_name = table_name
        self.file_name = file_name
        self.count = 0
        self.rows = 0
        self.batch_size = batch_size
        self.schema = []

    def __enter__(self):
        self.out_stream = open(self.file_name, "w")
        return self

    def __exit__(self, *args):
        self.out_stream.write(";\n")
        self.out_stream.close()

    def add_row(self, row_data):
        items = list(row_data.items())
        items.sort()
        keys = [x[0] for x in items]
        values = ["'%s'" % sterilize(x[1]) for x in items]
        output = ""
        if self.rows is 0:
            self.schema = keys

        if keys != self.schema:
            print(f"row {self.rows}: {keys} mismatches {self.schema}\n")

        if self.count is 0:
            output += ";\nINSERT INTO "
            output += self.table_name
            output += "(" + ", ".join(keys) + ") VALUES "
            output += "\n(" + ", ".join(values) + ")"
        else:
            output += ",\n(" + ", ".join(values) + ")"

        self.count = self.count + 1 if self.count < self.batch_size - 1 else 0
        self.rows += 1
        self.out_stream.write(output)

Answer 2

不要通过把字符串拼接起来来生成SQL语句。应该使用SQL参数来代替：

cursor = connection.cursor()

cursor.executemany('INSERT INTO 'tablename' ('column1', 'column2') VALUES (%s, %s)',
        [sub.values() for sub in shelf.values()])

这样数据库就可以重复使用这个INSERT语句（它会为这个语句准备一个查询计划），而且数据库客户端会帮你处理引号的问题，这样你也能防止SQL注入攻击。