SQLite的Online Backup备份处理

SQLite的python自带接口里面,是没有在线备份功能的。

所以,一般来说,备份方案使用这种方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlite3
from StringIO import StringIO
def init_sqlite_db(app):
# Read database to tempfile
con = sqlite3.connect(app.config['SQLITE_DATABASE'])
tempfile = StringIO()
for line in con.iterdump():
tempfile.write('%s\n' % line)
con.close()
tempfile.seek(0)
# Create a database in memory and import from tempfile
app.sqlite = sqlite3.connect(":memory:")
app.sqlite.cursor().executescript(tempfile.read())
app.sqlite.commit()
app.sqlite.row_factory = sqlite3.Row

大概的核心,也是使用connection的iterdump功能。

不过,如果引用一个库,sqlitebck,使用起来会简单很多。

1
$ pip install sqlitebck

调用接口:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# Basic usage example - memory database saved into file:
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> curr = conn.cursor()
# Create table and put there some data:
>>> curr.execute('CREATE TABLE foo (bar INTEGER)')
<sqlite3.Cursor object at 0xb73b2800>
>>> curr.execute('INSERT INTO foo VALUES (123)')
<sqlite3.Cursor object at 0xb73b2800>
>>> curr.close()
>>> conn.commit()
>>> import sqlitebck
# Save in memory database (conn) into file:
>>> conn2 = sqlite3.connect('/tmp/in_memory_sqlite_db_save.db')
>>> sqlitebck.copy(conn, conn2)
>>> conn.close()
>>> curr2 = conn2.cursor()
# Check if data is in file database:
>>> curr2.execute('SELECT * FROM foo');
<sqlite3.Cursor object at 0xb73b2860>
>>> curr2.fetchall()
[(123,)]
# If you want to load file database into memory, just call:
>>> sqlitebck.copy(conn2, conn)

另,为了提高性能,sqlite的内存数据库名字,可以使用保留的:memory: