从python将值列表插入到SQLite列中 [英] Inserting a list of value into SQLite column from python

问题描述

假设我有以下值的列表:

Say I have a list of following values:

listA = [1,2,3,4,5,6,7,8,9,10]

我想使用 executemany 命令将这个列表的每个值放在名为 formatteddate 的列中，而不是遍历整个列表并分别插入每个值.

I want to put each value of this list in a column named formatteddate in my SQLite database using executemany command rather than loop through the entire list and inserting each value separately.

如果要插入多列数据，我知道该怎么做.例如，如果我必须插入 listA,listB,listC，那么我可以创建一个像 (listA[i],listB[i],listC[i]) 的元组.是否可以在没有循环的情况下插入一个值列表.还假设插入值是整数.

I know how to do it if I had multiple columns of data to insert. For instance, if I had to insert listA,listB,listC then I could create a tuple like (listA[i],listB[i],listC[i]). Is it possible to insert one list of values without a loop. Also assume the insert values are integers.

更新:根据提供的答案，我尝试了以下代码:

UPDATE: Based on the answer provided I tried the following code:

def excutemanySQLCodewithTask(sqlcommand,task,databasefilename):
    # create a database connection
    conn = create_connection(databasefilename)
    with conn:
        cur = conn.cursor()
        cur.executemany(sqlcommand,[(i,) for i in task])
        return cur.lastrowid

tempStorage = [19750328, 19750330, 19750401, 19750402, 19750404, 19750406, 19751024, 19751025, 19751028, 19751030]

excutemanySQLCodewithTask("""UPDATE myTable SET formatteddate = (?) ;""",tempStorage,databasefilename)

它仍然需要很长时间(大约 10 个小时).我在 tempStorage 中有 150,000 个项目.我试过 INSERT INTO 也很慢.似乎不可能制作整数元组列表.

It still takes too long (roughly 10 hours). I have 150,000 items in tempStorage. I tried INSERT INTO and that was slow as well. It seems like it isn't possible to make a list of tuple of integers.

推荐答案

正如您所说，您需要一个元组列表.所以你可以这样做:

As you say, you need a list of tuples. So you can do:

cursor.executemany("INSERT INTO my_table VALUES (?)", [(a,) for a in listA])

这篇关于从python将值列表插入到SQLite列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！