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)]
$
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. [...]
是否在每次数据库更改后的过程结束时调用
conn.commit()
一次取决于几个因素。并发读者看到的
这是每个人第一眼看到的想法:当提交对数据库的更改时,它对其他连接可见。除非它被提交,否则它只在进行更改的连接的本地可见。由于
sqlite
的并发特性有限,因此只能在事务打开时读取数据库。您可以通过运行following script并调查其输出来调查发生了什么:
输出:
所以,这取决于你是否能忍受这样的情况:一个cuncurrent阅读器,无论是在同一个脚本中,还是在另一个程序中,每次都会关闭两个。
当要进行大量更改时,还有两个方面进入场景:
性能
数据库的性能会发生显著的变化,这取决于您如何进行更改。它已经被称为FAQ:
理解这里的细节是绝对有帮助的,所以请毫不犹豫地按照link进行深入研究。另请参见awsome analysis。它是用C语言编写的,但是如果在Python中也这样做,结果会很相似。
注意:虽然这两个资源都引用
INSERT
,但是对于相同的参数,UPDATE
的情况将非常相同。以独占方式锁定数据库
如上所述,打开(未提交)事务将阻止并发连接的更改。因此,通过执行数据库中的许多更改并联合提交全部更改,将这些更改捆绑到单个事务中是有意义的。
不幸的是,有时,计算更改可能需要一些时间。当并发访问是一个问题时,您不希望将数据库锁定那么长时间。由于以某种方式收集挂起的
UPDATE
和INSERT
语句可能会变得相当棘手,这通常会让您在性能和独占锁定之间进行权衡。相关问题 更多 >
编程相关推荐