【sqlite】python备份数据库

最新推荐文章于 2025-03-13 15:00:32 发布

转载最新推荐文章于 2025-03-13 15:00:32 发布 · 651 阅读

文章标签：

#数据库 #python

本文介绍了如何使用Python备份SQLite数据库中的整个数据库或单个表。对于备份整个数据库，可以通过连接到数据库并使用iterdump方法导出所有内容到文件实现。备份单个表则需要将目标表复制到内存数据库中，并从中导出表数据。

备份整个数据库的方法：

# coding=utf-8
import sqlite3

def 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 sqlite3

def 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.