<p>我知道<code>executemany</code>可用于方便地向数据库添加新条目;与for循环中的单个<code>execute</code>相比,有助于减少Python方法调用开销。但是,我想知道这是否可以与SQLite的<code>UPDATE</code>一起工作。</p>
<p>更具体地说,请考虑以下设置:</p>
<pre><code>cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
for data in some_computation(path):
c.execute("UPDATE TABLENAME SET cont=? WHERE id=?", (data[1], data[0]))
cnx.commit()
cnx.close()
</code></pre>
<p>我甚至不确定下面的方法是否更快(必须对其进行基准测试),但问题是它不起作用,因为我做得不对,我想。在下面的代码片段中使用<code>executemany</code>来完成我在上面发布的任务有什么提示吗?</p>
<pre><code>cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
data_ids, data_conts = [], []
for data in some_computation(path):
if len(data_ids) >= CHUNKSIZE:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts, data_ids))
cnx.commit()
data_ids, data_conts = [], []
data_ids.<a href="https://www.cnpython.com/list/append" class="inner-link">append</a>(data[0])
data_conts.append(data[1])
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts, data_ids))
cnx.commit()
cnx.commit()
cnx.close()
</code></pre>
<p>非常感谢你的建议和见解!</p>
<p><strong>编辑1:</strong></p>
<p>下面例子的问题是:</p>
<pre><code>ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 50000 supplied.
</code></pre>
<p>(其中CHUNKSIZE=50000)</p>
<p><strong>编辑2:</strong></p>
<p>同样的错误也会发生</p>
<pre><code>cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
data_conts = []
for data in some_computation(path):
if len(data_ids) >= CHUNKSIZE:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts,))
cnx.commit()
data_conts = []
data_conts.append([data[1], data[0]])
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts,))
cnx.commit()
cnx.commit()
cnx.close()
</code></pre>
<p>但多亏了@falsetru,我才发现我的错误,应该是</p>
<pre><code>... WHERE id=?", data_conts)
</code></pre>
<p>而不是</p>
<pre><code>... WHERE id=?", (data_conts,))
</code></pre>