技术

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: