将Python字典/列表插入SQL数据库的最有效方法是什么？

Question

假设你有一个带有大量项的键值对Python字典（或列表）。假设你正在读取一个更大的JSON文件，并且你想将其内容存储到MySQL表中，其中键作为列名，值作为值本身。

JSON示例：

"display_location": {
    "city":"Bratislava",
    "state_name":"Slovakia",
    "country_iso3166":"SK",
    "latitude":"48.20000076",
    "longitude":"17.20000076",
}

那么手动编写类似这样的SQL插入语句是相当低效的:

INSERT INTO TABLE (city, state_name, country_iso3166, latitude, longitude) VALUES('%s','%s','%s','%s','%s')
% (Bratislava, Slovakia, SK, 48.20000076, 17.20000076);

（好吧，对于五个值来说还可以，但是想象一下如果有五百个值呢。）

是否有任何Python类/方法可以进行有效和短字符串的SQL插入？我编写了这段代码：

for key,value in list.iteritems():
  value_type = type(value)
    if value_type is unicode:
      vars_to_sql.append(value.encode('ascii', 'ignore'))
      keys_to_sql.append(key.encode('ascii', 'ignore'))
    else:
      vars_to_sql.append(value)
      keys_to_sql.append(key)

keys_to_sql = ', '.join(keys_to_sql)

之后插入操作看起来更加简单：

INSERT INTO conditions_bratislava(%s) VALUES %r" % (keys_to_sql,  tuple(vars_to_sql),)

即使有成千上万的值，使用这个插入语句仍然没有问题。

请注意该条件将解码Unicode字符串，因此每个值之前都不会有“u”字母。

那么，是否存在更有效和准备好的类或方法来用短INSERT语句以相同简单的方式插入多个值？

- user1575813

---

如果您确实想使用关系型数据库，也许可以考虑使用即将推出的 PostgreSQL 9.2 版本，该版本具有原生的 JSON 数据类型。 - user330315

Answer 1

如果您的数据结构是这样的，更适合使用面向文档的数据库（Mongo/Couch等...）。

您可以像这样做...我认为使用repr有点太聪明了...

insert_sql = 'INSERT INTO conditions_bratislava(%s) values(%s)'
cols = ', '.join(somedict)
vals = ', '.join('?' * len(somedict)) # or whatever qparam is required
to_execute = insert_sql % (cols, vals)
some_cursor.execute(to_execute, somedict.values())

顺便提一下：

value_type = type(value)
if value_type is unicode:

应该这样写：

if isinstance(value, unicode):

- Jon Clements

---

如果在第二个线程中修改了字典，这可能会导致值的顺序不正确或值过多。我会选择使用命名参数，并将整个字典传递给cursor.execute。 - XORcist

@möter，我提供了OP所做的“更正确”的版本。虽然一般情况下使用命名参数会更好，但我同意这种做法。我也希望任何使用线程的人都足够警惕，以便使用锁定。 - Jon Clements