python sqlite3，我必须多久提交一次? [英] python sqlite3, how often do I have to commit?

问题描述

我有一个 for 循环，它使用我编写的 sqlite 管理器类对数据库进行了许多更改，但我不确定我必须多久提交一次...

I have a for loop that is making many changes to a database with a sqlite manager class I wrote, but I am unsure about how often I have to commit...

for i in list:
    c.execute('UPDATE table x=y WHERE foo=bar')
    conn.commit()
    c.execute('UPDATE table x=z+y WHERE foo=bar')
    conn.commit()

基本上我的问题是我是否必须在那里调用 commit 两次，或者我是否可以在进行两次更改后调用一次?

Basically my question is whether I have to call commit twice there, or if I can just call it once after I have made both changes?

推荐答案

是否在每次数据库更改后的过程结束时调用 conn.commit() 取决于几个因素.

Whether you call conn.commit() once at the end of the procedure of after every single database change depends on several factors.

这是每个人第一眼看到的:当对数据库的更改提交时，它对其他连接变得可见.除非它被提交，否则它只在本地对已完成更改的连接保持可见.由于sqlite的并发特性有限，数据库只能在事务打开时读取.

This is what everybody thinks of at first sight: When a change to the database is committed, it becomes visible for other connections. Unless it is committed, it remains visible only locally for the connection to which the change was done. Because of the limited concurrency features of sqlite, the database can only be read while a transaction is open.

您可以通过运行以下脚本来调查会发生什么并调查其输出:

You can investigate what happens by running the following script and investigating its output:

import os
import sqlite3

_DBPATH = "./q6996603.sqlite"

def fresh_db():
    if os.path.isfile(_DBPATH):
        os.remove(_DBPATH)
    with sqlite3.connect(_DBPATH) as conn:
        cur = conn.cursor().executescript("""
            CREATE TABLE "mytable" (
                "id" INTEGER PRIMARY KEY AUTOINCREMENT, -- rowid
                "data" INTEGER
            );
            """)
    print "created %s" % _DBPATH

# functions are syntactic sugar only and use global conn, cur, rowid

def select():
    sql = 'select * from "mytable"'
    rows = cur.execute(sql).fetchall()
    print "   same connection sees", rows
    # simulate another script accessing tha database concurrently
    with sqlite3.connect(_DBPATH) as conn2:
        rows = conn2.cursor().execute(sql).fetchall()
    print "   other connection sees", rows

def count():
    print "counting up"
    cur.execute('update "mytable" set data = data + 1 where "id" = ?', (rowid,))

def commit():
    print "commit"
    conn.commit()

# now the script
fresh_db()
with sqlite3.connect(_DBPATH) as conn:
    print "--- prepare test case"
    sql = 'insert into "mytable"(data) values(17)'
    print sql
    cur = conn.cursor().execute(sql)
    rowid = cur.lastrowid
    print "rowid =", rowid
    commit()
    select()
    print "--- two consecutive w/o commit"
    count()
    select()
    count()
    select()
    commit()
    select()
    print "--- two consecutive with commit"
    count()
    select()
    commit()
    select()
    count()
    select()
    commit()
    select()

输出:

$ python try.py 
created ./q6996603.sqlite
--- prepare test case
insert into "mytable"(data) values(17)
rowid = 1
commit
   same connection sees [(1, 17)]
   other connection sees [(1, 17)]
--- two consecutive w/o commit
counting up
   same connection sees [(1, 18)]
   other connection sees [(1, 17)]
counting up
   same connection sees [(1, 19)]
   other connection sees [(1, 17)]
commit
   same connection sees [(1, 19)]
   other connection sees [(1, 19)]
--- two consecutive with commit
counting up
   same connection sees [(1, 20)]
   other connection sees [(1, 19)]
commit
   same connection sees [(1, 20)]
   other connection sees [(1, 20)]
counting up
   same connection sees [(1, 21)]
   other connection sees [(1, 20)]
commit
   same connection sees [(1, 21)]
   other connection sees [(1, 21)]
$

因此，这取决于您是否能忍受当前阅读器(无论是在同一个脚本中还是在另一个程序中)有时会相差两个的情况.

So it depends whether you can live with the situation that a cuncurrent reader, be it in the same script or in another program, will be off by two at times.

当需要进行大量更改时，需要另外两个方面:

When a large number of changes is to be done, two other aspects enter the scene:

数据库性能的显着变化取决于您如何进行.它已被标记为常见问题解答:

The performance of database changes dramatically depends on how you do them. It is already noted as a FAQ:

实际上，SQLite 在普通台式计算机上每秒可以轻松执行 50,000 或更多 INSERT 语句.但它每秒只能进行几十次交易.[...]

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. [...]

了解此处的详细信息绝对有帮助，因此请不要犹豫，点击链接 并深入研究.另请参阅此很棒的分析.它是用 C 编写的，但如果用 Python 做同样的事情，结果会相似.

It is absolutely helpful to understand the details here, so do not hesitate to follow the link and dive in. Also see this awsome analysis. It's written in C, but the results would be similar would one do the same in Python.

注意:虽然这两个资源都引用了 INSERT，但对于相同的参数，UPDATE 的情况非常相似.

Note: While both resources refer to INSERT, the situation will be very much the same for UPDATE for the same arguments.

如上所述，打开(未提交)事务将阻止并发连接的更改.因此，通过执行和共同提交所有更改，将数据库的许多更改捆绑到单个事务中是有意义的.

As already mentioned above, an open (uncommitted) transaction will block changes from concurrent connections. So it makes sense to bundle many changes to the database into a single transaction by executing them and the jointly committing the whole bunch of them.

不幸的是，有时，计算更改可能需要一些时间.当并发访问是一个问题时，您不会希望将数据库锁定那么长时间.因为以某种方式收集挂起的 UPDATE 和 INSERT 语句可能会变得相当棘手，所以这通常会让您在性能和独占锁定之间进行权衡.

Unfortunately, sometimes, computing the changes may take some time. When concurrent access is an issue you will not want to lock your database for that long. Because it can become rather tricky to collect pending UPDATE and INSERT statements somehow, this will usually leave you with a tradeoff between performance and exclusive locking.

这篇关于python sqlite3，我必须多久提交一次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！