关于批量插入：Python sqlite

py3study

发布于 2020-01-06 10:20:37

2.5K0

发布于 2020-01-06 10:20:37

文章被收录于专栏：python3python3

个人遇到了一个需要互操作 Access 与 sqlite 的项目，其中涉及了诸多 join 和 relation。最重要的是数据的互相流动，这个项目使用了 pypyodbc 与 sqlobject，不免涉及了批量操作的问题。我将详细记录 sqlite 中的批量操作问题。

pypyodbc

作为数据源的 access 我将不会在本文中讨论

SQLObject

SQLObject 的操作速度一直是为人所诟病的，在官方的 FAQ 中对于其不支持 Lazy Insert 的原因，其解释道：

The reason for this limit is that each object needs a database ID,
不支持(lazy insert)的原因是每个对象都需要一个数据库标识（db id）
and in many databases you cannot attain an ID until you create a row.
而在很多数据库上只有插入一行后才能得到它（ID）

它既然这么说也无可厚非，纯粹的架构问题。

在随后的一个问题：How to do mass-insertion? 中它又提出使用 SQLBuilder(官方:lower but much faster API) 来进行批量操作，例如用 sqlbuilder.Insert 和 sqlrepr() 生成 query 后，在 connection.query() 中执行，类似的 queryAll() 调用相同的数据库方法，只是结果返回 fetchALL()，但是最终结果让我不能仍受，插入100行几乎就像无响应一样，代码如下：

from sqlobject.sqlbuilder import *
import sqlobject
sqlite_file = os.path.abspath('D:/db.sqlite')
conn = sqlobject.connectionForURI('sqlite:/'+sqlite_file)
_values = {}
for row in datas:
    # 在这里我设置了 _values 的内容，like：
    # _values[column] = data
    insert = Insert('records', values=_values)
    a_query = conn.sqlrepr(insert)
    conn.query(a_query)

当时无论怎么看都是每次 query 都是 autocommit 的，果不其然非常之慢，执行了将近十秒，这开始让我怀疑可能是我误解的文档的意图，我在邮件列表上提问，目前还没有收到答复。

sqlite3

随后我尝试了内置的 sqlite3，由于直接执行 sql 和手动提交，它的速度很少有人抱怨，我没有花太多力气就在官方文档中找到了批量操作的方法：

c = conn.cursor()
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

插入947行用了2秒，虽然需要手动编写 insert SQL，但是这点代价可以接受

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-09-20 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sqlite