You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
201 lines
6.9 KiB
Python
201 lines
6.9 KiB
Python
################################################################################
|
|
# SQLite Merge Script #
|
|
# #
|
|
# @author Charles Duso #
|
|
# @description Merges databases that have the same tables and schema. #
|
|
# @date August 7th, 2016 #
|
|
################################################################################
|
|
|
|
############################# Import Libraries #################################
|
|
################################################################################
|
|
|
|
import sqlite3
|
|
import time
|
|
import sys
|
|
from time import gmtime, strftime
|
|
|
|
############################ Global Variables ##################################
|
|
################################################################################
|
|
|
|
dbCount = 0 # Variable to count the number of databases
|
|
listDB = [] # Variable to store the names of the databases
|
|
listTable = [] # Variable to store table names
|
|
|
|
|
|
############################ Function Definitions ##############################
|
|
################################################################################
|
|
|
|
# Attaches a database to the currently connected database
|
|
#
|
|
# @param db_name the name of the database file (i.e. "example.db")
|
|
# @return none
|
|
def attach_database(db_name):
|
|
global dbCount
|
|
global listDB
|
|
print("Attaching database: %s" % db_name)
|
|
curs.execute("ATTACH DATABASE ? as ? ;", (db_name, 'db' + str(dbCount)))
|
|
listDB.append('db' + str(dbCount))
|
|
dbCount += 1
|
|
|
|
|
|
# Closes the current database connection
|
|
#
|
|
# @return none
|
|
def close_connection():
|
|
curs.close()
|
|
conn.close()
|
|
|
|
|
|
# Gets the table names of a database
|
|
#
|
|
# @param db_name the name of the database file (i.e. "example.db")
|
|
# @return a string array of the table names
|
|
def get_table_names():
|
|
curs.execute("SELECT name FROM sqlite_master WHERE type='table';")
|
|
temp = curs.fetchall()
|
|
tables = []
|
|
for i in range(0, len(temp)):
|
|
tables.append(temp[i][0])
|
|
return tables
|
|
|
|
|
|
# Gets the column names of a table
|
|
#
|
|
# @param db_name the name of the database file (i.e. "example.db")
|
|
# @return a string array of the column names - strips primary ids column
|
|
def get_column_names(table_name):
|
|
curs.execute("PRAGMA table_info(%s);" % str(table_name))
|
|
temp = curs.fetchall()
|
|
columns = []
|
|
for i in range(0, len(temp)):
|
|
if ("id" in temp[i][1]) | ("ID" in temp[i][1]):
|
|
continue
|
|
else:
|
|
columns.append(temp[i][1])
|
|
return columns
|
|
|
|
|
|
# Compares two lists to see if they have identical data
|
|
#
|
|
# @param list1 the first list parameter for comparison
|
|
# @param list2 the second list parameter for comparison
|
|
# @return will return a boolean (0 lists !=, 1 lists ==)
|
|
def compare_lists(list1, list2):
|
|
if len(list1) != len(list2):
|
|
return 0
|
|
else:
|
|
for i in range(0, len(list1)):
|
|
if list1[i] != list2[i]:
|
|
return 0
|
|
return 1
|
|
|
|
|
|
# Converts a list of string objects to a string of comma separated items.
|
|
#
|
|
# @param listObj the list to convert
|
|
# @return a string containing the list items - separated
|
|
# by commas.
|
|
def list_to_string(list_obj):
|
|
list_string = ""
|
|
for i in range(0, len(list_obj)):
|
|
if i == (len(list_obj) - 1):
|
|
list_string = list_string + list_obj[i]
|
|
else:
|
|
list_string = list_string + list_obj[i] + ", "
|
|
return list_string
|
|
|
|
|
|
# Merges a table from an attached database to the source table
|
|
#
|
|
# @param table_name the name of the table to merge
|
|
# @param column_names the names of the columns to include in the merge
|
|
# @param db_name_table_name the name of the attached database and the table
|
|
# i.e. "db_name.table_name"
|
|
# @return none
|
|
def merge_table(table_name, column_names, db_name):
|
|
db_name_table_name = db_name + "." + table_name
|
|
try:
|
|
curs.execute("INSERT INTO %s (%s) SELECT %s FROM %s;" %
|
|
(table_name, column_names, column_names, db_name_table_name))
|
|
conn.commit()
|
|
except:
|
|
pass
|
|
|
|
|
|
############################## Input Parameters ################################
|
|
################################################################################
|
|
|
|
mainDB = '' # This is where the main database is
|
|
# referenced. Where all items will be
|
|
# merged to.
|
|
|
|
otherDBs = []
|
|
|
|
if len(otherDBs) == 0:
|
|
print("ERROR: No databases have been added for merging.")
|
|
sys.exit()
|
|
|
|
############################## Merge Script ####################################
|
|
################################################################################
|
|
|
|
# Initialize Connection and get main list of tables
|
|
conn = sqlite3.connect(mainDB) # Connect to the main database
|
|
curs = conn.cursor() # Connect a cursor
|
|
listTable = get_table_names() # Get the table names
|
|
close_connection()
|
|
|
|
# Compare databases
|
|
startTime = time.time()
|
|
print("Comparing databases. Started at: " + strftime("%H:%M", gmtime()))
|
|
|
|
for i in range(0, len(otherDBs)):
|
|
conn = sqlite3.connect(otherDBs[i])
|
|
curs = conn.cursor()
|
|
temp = get_table_names() # Get the current list of tables
|
|
if len(listTable) > len(temp):
|
|
print("Table is missing from non-primary database: %s" % otherDBs[i])
|
|
print("Database will NOT BE MERGED with the main database.")
|
|
otherDBs.remove(otherDBs[i]) # Remove the table to avoid errors
|
|
continue
|
|
|
|
if len(listTable) < len(temp):
|
|
print("Extra table(s) in non-primary database: %s" % otherDBs[i])
|
|
print("TABLES that are NOT in main database will NOT be added.")
|
|
|
|
if listTable != temp:
|
|
print("Tables do not match in non-primary database: %s" % otherDBs[i])
|
|
print("The database will NOT BE MERGED with the main database.")
|
|
otherDBs.remove(otherDBs[i]) # Remove the table to avoid errors
|
|
continue
|
|
|
|
close_connection()
|
|
|
|
if len(otherDBs) == 0:
|
|
print("ERROR: No databases to merge. Databases were either removed due to \
|
|
inconsistencies, or databases were not added properly.")
|
|
sys.exit()
|
|
|
|
print("Finished comparing databases. Time elapsed: %.3f" % (time.time() -
|
|
startTime))
|
|
|
|
# Attach databases
|
|
startTime = time.time()
|
|
print("Merging databases. Started at: " + strftime("%H:%M", gmtime()))
|
|
|
|
conn = sqlite3.connect(mainDB) # Attach main database
|
|
curs = conn.cursor() # Attach cursor
|
|
|
|
for i in range(0, len(otherDBs)):
|
|
attach_database(otherDBs[i]) # Attach other databases
|
|
|
|
# Merge databases
|
|
for i in range(0, len(listDB)):
|
|
for j in range(0, len(listTable)):
|
|
columns = list_to_string(get_column_names(listTable[j])) # get columns
|
|
merge_table(listTable[j], columns, listDB[i])
|
|
|
|
conn.commit() # Commit changes
|
|
close_connection() # Close connection
|
|
print("Databases finished merging. Time elapsed: %.3f" % (time.time() -
|
|
startTime))
|