【sqlite】python備份資料庫

標籤：

備份整個資料庫的方法：

# coding=utf-8import sqlite3def testBakSqlite():    conn = sqlite3.connect("sqlite_db_mine/testDB.db")    with open(‘testDB.sql.bak‘,‘w‘) as f:        for line in conn.iterdump():            data = line + ‘\n‘            data = data.encode("utf-8")            f.write(data)    testBakSqlite()

 

 

如果想要備份其中的一個表，沒有很好的辦法。下面是一些網上的討論。

http://stackoverflow.com/questions/6677540/how-do-i-dump-a-single-sqlite3-table-in-python

You can copy only the single table in an in memory db:

import sqlite3def getTableDump(db_file, table_to_dump):    conn = sqlite3.connect(‘:memory:‘)        cu = conn.cursor()    cu.execute("attach database ‘" + db_file + "‘ as attached_db")    cu.execute("select sql from attached_db.sqlite_master "               "where type=‘table‘ and name=‘" + table_to_dump + "‘")    sql_create_table = cu.fetchone()[0]    cu.execute(sql_create_table);    cu.execute("insert into " + table_to_dump +               " select * from attached_db." + table_to_dump)    conn.commit()    cu.execute("detach database attached_db")    return "\n".join(conn.iterdump())TABLE_TO_DUMP = ‘table_to_dump‘DB_FILE = ‘db_file‘print getTableDump(DB_FILE, TABLE_TO_DUMP)

Pro: Simplicity and reliability: you don‘t have to re-write any library method, and you are more assured that the code is compatible with future versions of the sqlite3 module.

Con: You need to load the whole table in memory, which may or may not be a big deal depending on how big the table is, and how much memory is available.

【sqlite】python備份資料庫