使用Python快速将许多列插入Sqlite \ Mysql [英] Using Python quick insert many columns into Sqlite\Mysql

问题描述

如果Newdata是x列的列表，那么How将如何获得唯一列数-第一个元组的成员数. (Len并不重要.)更改数字?"匹配列并使用以下语句插入.

If Newdata is list of x columns, How would get the number unique columns--number of members of first tuple. (Len is not important.) Change the number of "?" to match columns and insert using the statement below.

csr = con.cursor()
csr.execute('Truncate table test.data')
csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)', Newdata)
con.commit()

推荐答案

通过"Newdata是x列的列表"，我想您的意思是x 元组，从那时起您一直在谈论第一个元组".如果Newdata是元组列表，则y = len(Newdata[0])是这些元组中第一个元组的项数.

By "Newdata is list of x columns", I imagine you mean x tuples, since then you continue to speak of "the first tuple". If Newdata is a list of tuples, y = len(Newdata[0]) is the number of items in the first one of those tuples.

假设这是您想要的数字(所有元组最好具有相同的项目数，否则executemany 将失败！)，@ Nathan答案中的一般想法是正确的:构建带有适当数量的逗号分隔的问号的字符串:

Assuming that's the number you want (and all tuples had better have the same number of items, otherwise executemany will fail!), the general idea in @Nathan's answer is right: build the string with the appropriate number of comma-separated question marks:

holders = ','.join('?' * y)

然后将其插入到SQL语句的其余部分中. @Nathan的插入方式适用于大多数Python 2.any版本，但是，如果您有2.6或更高版本，

then insert it in the rest of the SQL statement. @Nathan's way to insert is right for most Python 2.any versions, but if you have 2.6 or better,

sql = 'INSERT INTO testdata VALUES({0})'.format(holders)

当前是首选(它也可以在Python 3.any中使用.)

is currently preferred (it also works in Python 3.any).

最后，

csr.executemany(sql, Newdata)

将做您想要的.完成后，请记住要提交事务！-)

will do what you desire. Remember to commit the transaction once you're done!-)

这篇关于使用Python快速将许多列插入Sqlite \ Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！