将python字典集合到sqlite3中

Question

场景：

1. SQLite3数据库中的一个表有14列。在
2. 15本字典，有不同数量的键（10-14）。在
3. 每个键（表中的列）都有一个list值，正好有400个元素，即每个list值的元素5将在第5行中。在

我需要将这些字典中的数据插入到前面提到的表中，考虑到有些字典不会使用表中的所有14列。在

最有效的方法是什么？在

Answer 1

假设您的数据已经是sql安全的，并且每个dict的键都足以插入一行：

import sqlite3
from itertools import izip, repeat

conn = sqlite3.connect('mydb.sqlite3')
table_name = 'test'
cursor = conn.cursor()

# Create test table.
cursor.execute('DROP TABLE IF EXISTS {}'.format(table_name));
cursor.execute('CREATE TABLE {} (a1 integer, a2 integer, a3 integer)'.format(table_name));
conn.commit()

my_dicts = [
    { 'a1': repeat(1, 5), 'a2': repeat(2, 5), 'a3': repeat(3, 5) },
    { 'a2': repeat(4, 2) },
    { 'a3': repeat(7, 7) },
]

for my_dict in my_dicts:
    # Key <=> Items order has to be preserved.
    # .keys() and .values() not used to be sure
    # the order is preserved.
    keys = []
    items = []
    for k, v in my_dict.iteritems():
        keys.append(k)
        items.append(v)

    sql = "INSERT INTO {} ({}) VALUES ({})".format(table_name, ','.join(keys), ','.join(repeat('?', len(keys))))
    cursor.executemany(sql, izip(*items))
    conn.commit()

cursor.execute('SELECT * FROM {}'.format(table_name));
conn.commit()

>>> print cursor.fetchall()
[(1, 2, 3), (1, 2, 3), (1, 2, 3), (1, 2, 3), (1, 2, 3), (None, 4, None), (None, 4, None), (None, None, 7), (None, None, 7), (None, None, 7), (None, None, 7), (None, None, 7), (None, None, 7), (None, None, 7)]