在Python中阻止从数组到SQLITE的插入 [英] Block insert from array to SQLITE in python
问题描述
所以我读了这篇文章，说在块中插入一次击败了一行:
哪个更快:多个单个INSERT或一个多个行插入?
So I read this article that says inserting in blocks beats one row at a time:
Which is faster: multiple single INSERTs or one multiple-row INSERT?
我读了这篇文章，建议如何获取文本行并将其插入到mysql存储中:
如何使用python mysqldb在以下位置插入许多行一次
And I read this article that suggests how to take rows of text and insert them into a mysql store:
How to use python mysqldb to insert many rows at once
我的过程是这样的:
- 从输入源获取大桶的字符串
- 弹出感兴趣的几块(十个)
- 用它们填充一个数组，一个块连续一个单元格
- 重复执行直到阵列装满
- 尝试使用输入命令将行放入存储中
我的问题是，当我尝试将以下代码用于具有100多个行的数组时
my problem is that when I try to use the following code for an array with ~100+ rows
cur.executemany("INSERT INTO OpStatus VALUES('?',?,...,?)", my_array)
我目前有一个?"表中的每一列.
I currently have one "?" for each column in the table.
它给了我以下错误.
TypeError: not enough arguments for format string
我的问题是:
如何重整数组或输入语句，以便可以将其成批插入(而不是行)到SQLITE表中?
My question is:
How do I reform my array or input statement so that I can make this a batch (not row) insert into the SQLITE table?
免责声明:自从我使用python已有几年了.如果您需要为我指明方向或提出澄清的问题，请放心.
DISCLAIMER: It has been a few years since I used python. If you need to point me in a direction, or ask clarifying questions, please feel free.
推荐答案
我认为您的做法正确.在交互式会话中，我可以执行以下操作:
I think you are on the right track. In an interactive session I can do:
conn=sqlite3.connect(':memory:')
cur=conn.cursor()
cur.execute('''CREATE TABLE array (f0 real, f1 real, f2 real)''')
插入一行
cur.execute("INSERT INTO array VALUES (?,?,?)", np.ones((3,)))
插入2
cur.executemany("INSERT INTO array VALUES (?,?,?)", np.zeros((2,3)))
创建一个数组并将其插入
make an array and insert it
A=np.arange(12.).reshape(4,3)
cur.executemany("INSERT INTO array VALUES (?,?,?)", A)
显示
for row in cur.execute('SELECT * from array'):print (row)
在 Numpy ValueError:设置数组带有序列的元素我了解到fetchall()返回一个元组列表.我认为，基于此，INSERT输入也必须是一个元组列表，这是结构化数组会产生的那种东西.但是，只要数组的列数与?的数目匹配，二维数组就可以正常工作.
In Numpy ValueError: setting an array element with a sequence I learned that fetchall() returns a list of tuples. I thought, based on that, the INSERT input also had to be a list a tuples, the kind of thing that a structured array would produce. But it looks like a 2d array will work fine, as long as the number of columns of the array match the number of ?.
您最初的问题是关于使用('%s',%s,...,%s)" % my_array的问题.
Your initial question asked about using ('%s',%s,...,%s)" % my_array.
'%s,%s,%s'%tuple(A[0,:]) # ok
'%s,%s,%s'%A[0,:] # not enough arguments error
请先发表我的评论.但是显然您找到了指向首选的(?,?,?)语法的文档.
Hence my initial comment. But apparently you found documentation that pointed to the prefered (?,?,?) syntax.
结构化数组也可以使用，尽管我必须注意dtype和形状:
Structured arrays also work, though I have to be careful about the dtype and shape:
cur.executemany("INSERT INTO array VALUES (?,?,?)", np.ones((1,),dtype='f8,f8,f8'))
这篇关于在Python中阻止从数组到SQLITE的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
