Python數(shù)據(jù)庫(kù)連接怎樣進(jìn)行數(shù)據(jù)庫(kù)的備份與恢復(fù)

小樊
81
2024-11-09 14:21:52

在Python中，可以使用sqlite3庫(kù)來(lái)操作SQLite數(shù)據(jù)庫(kù)，因?yàn)檫@個(gè)庫(kù)是Python標(biāo)準(zhǔn)庫(kù)的一部分，所以不需要額外安裝。以下是使用sqlite3庫(kù)進(jìn)行數(shù)據(jù)庫(kù)備份和恢復(fù)的步驟：

  1. 導(dǎo)入必要的模塊：
import sqlite3
import shutil
import os
  1. 創(chuàng)建一個(gè)函數(shù)來(lái)執(zhí)行數(shù)據(jù)庫(kù)備份：
def backup_database(source_db, target_db):
    # 連接到源數(shù)據(jù)庫(kù)
    conn = sqlite3.connect(source_db)
    cursor = conn.cursor()

    # 獲取源數(shù)據(jù)庫(kù)的名稱
    source_name = os.path.basename(source_db)

    # 創(chuàng)建目標(biāo)數(shù)據(jù)庫(kù)文件
    if not os.path.exists(target_db):
        with sqlite3.connect(target_db) as target_conn:
            target_conn.execute(f"CREATE TABLE {source_name} (id INTEGER PRIMARY KEY, data TEXT);")

    # 復(fù)制表結(jié)構(gòu)和數(shù)據(jù)
    for table_name in cursor.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
        cursor.execute(f"SELECT * FROM {table_name[0]};")
        rows = cursor.fetchall()
        column_names = [description[0] for description in cursor.description]
        with sqlite3.connect(target_db) as target_conn:
            target_conn.executemany(f"INSERT INTO {table_name[0]} VALUES ({','.join(['?']*len(column_names))});", rows)

    # 關(guān)閉源數(shù)據(jù)庫(kù)連接
    cursor.close()
    conn.close()

    print(f"Backup of {source_db} completed and saved to {target_db}")
  1. 創(chuàng)建一個(gè)函數(shù)來(lái)執(zhí)行數(shù)據(jù)庫(kù)恢復(fù)：
def restore_database(source_db, target_db):
    # 連接到源數(shù)據(jù)庫(kù)
    conn = sqlite3.connect(source_db)
    cursor = conn.cursor()

    # 獲取源數(shù)據(jù)庫(kù)的名稱
    source_name = os.path.basename(source_db)

    # 檢查目標(biāo)數(shù)據(jù)庫(kù)是否存在，如果存在則刪除
    if os.path.exists(target_db):
        os.remove(target_db)

    # 創(chuàng)建目標(biāo)數(shù)據(jù)庫(kù)文件
    with sqlite3.connect(target_db) as target_conn:
        target_conn.execute(f"CREATE TABLE {source_name} (id INTEGER PRIMARY KEY, data TEXT);")

    # 復(fù)制表結(jié)構(gòu)和數(shù)據(jù)
    for table_name in cursor.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
        cursor.execute(f"SELECT * FROM {table_name[0]};")
        rows = cursor.fetchall()
        column_names = [description[0] for description in cursor.description]
        with sqlite3.connect(target_db) as target_conn:
            target_conn.executemany(f"INSERT INTO {table_name[0]} VALUES ({','.join(['?']*len(column_names))});", rows)

    # 關(guān)閉源數(shù)據(jù)庫(kù)連接
    cursor.close()
    conn.close()

    print(f"Restore of {source_db} completed and saved to {target_db}")
  1. 使用這些函數(shù)來(lái)備份和恢復(fù)數(shù)據(jù)庫(kù)：
# 備份數(shù)據(jù)庫(kù)
backup_database('source.db', 'backup.db')

# 恢復(fù)數(shù)據(jù)庫(kù)
restore_database('backup.db', 'restored_source.db')

請(qǐng)注意，這些示例適用于SQLite數(shù)據(jù)庫(kù)。對(duì)于其他數(shù)據(jù)庫(kù)系統(tǒng)（如MySQL、PostgreSQL等），你需要使用相應(yīng)的Python庫(kù)（如mysql-connector-python、psycopg2等）并遵循相應(yīng)的備份和恢復(fù)過(guò)程。

0 踩