Sqlite3数据库合并

0x00 引言

再简单的事情，也需要有人去做。

0x01 需求

存在多个数据库，需要合并，数据库中相同表的结构是一样的。 主要是使用了Sqlite3的系统表sqlite_master。

0x02 Python 代码

# coding=utf-8
import sys, sqlite3, os


def merge_tables(tables_file_a, tables_file_b, con_file_a):
    print("Start Merge Tables")
    j = 0
    while j < len(tables_file_b):
        if not tables_file_b[j] in tables_file_a:
            cur_table_file_a = con_file_a.cursor()
            try:
                cur_table_file_a.execute(tables_file_b[j][3:-3])
            except:
                print("Error: " + tables_file_b[j][3:-3])
            j += 1
        else:
            j += 1
    print("All Tables Merged!")


def merge_index(tables_file_a, tables_file_b, con_file_a):
    print("Start Merge Index")
    j = 0
    while j < len(tables_file_b):
        if not tables_file_b[j] in tables_file_a:
            cur_table_file_a = con_file_a.cursor()
            try:
                cur_table_file_a.execute(tables_file_b[j][3:-3])
            except:
                print("Error: " + tables_file_b[j][3:-3])
            j += 1
        else:
            j += 1
    print("All Index Merged!")


def merge_data(con_current_data, con_des_data, tables_current_data):
    m = 0
    cur_current_data = con_current_data.cursor()
    while m < len(tables_current_data):
        sql_statement = "select * from " + tables_current_data[m][3:-3]
        cur_current_data.execute(sql_statement)
        temp_data_list = cur_current_data.fetchall()

        n = 0
        cur_des_data = con_des_data.cursor()
        while n < len(temp_data_list):
            sql_exist_statement = "select * from " \
                                  + str(tables_current_data[m])[3:-3] + \
                                  ' where COL_UID="' + temp_data_list[n][0] + '"'
            cur_des_data.execute(sql_exist_statement)
            temp_des_data_list = cur_des_data.fetchall()

            cur_des_next_data = con_des_data.cursor()
            if len(temp_des_data_list) == 0:
                sql_temp = ""
                nn = 0
                try:
                    while nn < len(temp_data_list[n]):
                        sql_statement += '"' + temp_data_list[n][nn] + '",'
                        nn += 1
                except:
                    print("Error " + sql_temp)

                try:
                    cur_des_next_data.execute("insert into "
                                              + str(tables_current_data[m])[3:-3]
                                              + " values(" + sql_temp[0:-1] + ")")
                    cur_des_next_data.commit()
                except:
                    print("Error " + sql_temp)
                n += 1
            else:
                n += 1

        m += 1
    print("All Tables Data Merged!")


con_file_a = sqlite3.connect("~/a.db")
con_file_b = sqlite3.connect("~/b.db")

# 处理表
sql_table_statement = "select sql From sqlite_master Where type='table'"
cur_file_a = con_file_a.cursor()
cur_file_a.execute(sql_table_statement)
tables_file_a = cur_file_a.fetchall()

cur_file_b = con_file_b.cursor()
cur_file_b.execute(sql_table_statement)
tables_file_b = cur_file_b.fetchall()
merge_tables(tables_file_a, tables_file_b, con_file_a)

# 处理索引
sql_index_statement = "select sql From sqlite_master Where type='index'"
cur_file_a = con_file_a.cursor()
cur_file_a.execute(sql_index_statement)
tables_file_a = cur_file_a.fetchall()

cur_file_b = con_file_b.cursor()
cur_file_b.execute(sql_index_statement)
tables_file_b = cur_file_b.fetchall()
merge_index(tables_file_a, tables_file_b, con_file_a)

con_file_a.close()
con_file_b.close()

sql_data_statement = "SELECT name FROM sqlite_master WHERE type='table'"
cur_file_a = con_file_a.cursor()
cur_file_a.execute(sql_data_statement)

cur_file_b = con_file_b.cursor()
cur_file_b.execute(sql_data_statement)
tables_file_b = cur_file_b.fetchall()
merge_data(con_file_b, con_file_a, tables_file_b)

print("---------All Data Finished!--------")

0x03 后记

开始用Python做了这一版，后来用 Qt 重做了，所以这个最初版就放出来了。

0x04 UPDATE

2019-01-13