总结自DavidM的回答中Nabble帖子:
attach 'c:\test\b.db3' as toMerge;
BEGIN;
insert into AuditRecords select * from toMerge.AuditRecords;
COMMIT;
detach toMerge;
根据Mike的评论,添加detach toMerge;
。
根据需要重复执行。
Ctrl + O
添加您所有的数据库文件import sqlite3
import os
def merge_databases(db1, db2):
con3 = sqlite3.connect(db1)
con3.execute("ATTACH '" + db2 + "' as dba")
con3.execute("BEGIN")
for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table'"):
combine = "INSERT OR IGNORE INTO "+ row[1] + " SELECT * FROM dba." + row[1]
print(combine)
con3.execute(combine)
con3.commit()
con3.execute("detach database dba")
def read_files(directory):
fname = []
for root,d_names,f_names in os.walk(directory):
for f in f_names:
c_name = os.path.join(root, f)
filename, file_extension = os.path.splitext(c_name)
if (file_extension == '.sqlitedb'):
fname.append(c_name)
return fname
def batch_merge(directory):
db_files = read_files(directory)
for db_file in db_files[1:]:
merge_databases(db_files[0], db_file)
if __name__ == '__main__':
batch_merge('/directory/to/database/files')
merge_databases
函数尝试合并历史表时,我遇到了一个 sqlite3.OperationalError: near "-": syntax error
错误。我不得不更改以下行:for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table' AND name NOT LIKE 'History_%'" ):
- emilazmerge_databases
函数尝试合并历史表时,这导致我遇到了一个sqlite3.OperationalError: near "-": syntax error
错误。我不得不更改以下行:for row in con3.execute("SELECT * FROM dba.sqlite_master WHERE type='table' AND name NOT LIKE 'History_%'" ):
。 - undefined
#!/usr/bin/python
import sys, sqlite3
class sqlMerge(object):
"""Basic python script to merge data of 2 !!!IDENTICAL!!!! SQL tables"""
def __init__(self, parent=None):
super(sqlMerge, self).__init__()
self.db_a = None
self.db_b = None
def loadTables(self, file_a, file_b):
self.db_a = sqlite3.connect(file_a)
self.db_b = sqlite3.connect(file_b)
cursor_a = self.db_a.cursor()
cursor_a.execute("SELECT name FROM sqlite_master WHERE type='table';")
table_counter = 0
print("SQL Tables available: \n===================================================\n")
for table_item in cursor_a.fetchall():
current_table = table_item[0]
table_counter += 1
print("-> " + current_table)
print("\n===================================================\n")
if table_counter == 1:
table_to_merge = current_table
else:
table_to_merge = input("Table to Merge: ")
return table_to_merge
def merge(self, table_name):
cursor_a = self.db_a.cursor()
cursor_b = self.db_b.cursor()
new_table_name = table_name + "_new"
try:
cursor_a.execute("CREATE TABLE IF NOT EXISTS " + new_table_name + " AS SELECT * FROM " + table_name)
for row in cursor_b.execute("SELECT * FROM " + table_name):
print(row)
cursor_a.execute("INSERT INTO " + new_table_name + " VALUES" + str(row) +";")
cursor_a.execute("DROP TABLE IF EXISTS " + table_name);
cursor_a.execute("ALTER TABLE " + new_table_name + " RENAME TO " + table_name);
self.db_a.commit()
print("\n\nMerge Successful!\n")
except sqlite3.OperationalError:
print("ERROR!: Merge Failed")
cursor_a.execute("DROP TABLE IF EXISTS " + new_table_name);
finally:
self.db_a.close()
self.db_b.close()
return
def main(self):
print("Please enter name of db file")
file_name_a = input("File Name A:")
file_name_b = input("File Name B:")
table_name = self.loadTables(file_name_a, file_name_b)
self.merge(table_name)
return
if __name__ == '__main__':
app = sqlMerge()
app.main()
SRC: 将相同的SQLite3数据库合并的工具
自动合并每个数据库中的所有表格的Bash助手
这是一个简洁的Bash版本,循环遍历具有相同模式的给定数据库中的所有表格。https://dev59.com/GnVD5IYBdhLWcg3wHn2d#68526717:
sqlite-merge-dbs
#!/usr/bin/env bash
set -eu
outdb="$1"
shift
indb0="$1"
shift
cp "$indb0" "$outdb"
for table in $(sqlite3 "$outdb" "SELECT name FROM sqlite_master WHERE type='table'"); do
echo "table: $table"
for db in "$@"; do
echo "db: $db"
sqlite3 "$outdb" "attach '$db' as 'db2'" "insert into \"$table\" select * from \"db2\".\"$table\""
done
done
sqlite_master
表中提取表名来实现。sqlite-merge-dbs out.sqlite in0.sqlite in1.sqlite in2.sqlite
rm -f in0.sqlite in1.sqlite in2.sqlite
sqlite3 in0.sqlite 'create table t(i integer, j integer)'
sqlite3 in1.sqlite 'create table t(i integer, j integer)'
sqlite3 in2.sqlite 'create table t(i integer, j integer)'
sqlite3 in0.sqlite 'insert into t values (1, -1), (2, -2)'
sqlite3 in1.sqlite 'insert into t values (3, -3), (4, -4)'
sqlite3 in2.sqlite 'insert into t values (5, -5), (6, -6)'
sqlite3 in0.sqlite 'create table s(k integer, l integer)'
sqlite3 in1.sqlite 'create table s(k integer, l integer)'
sqlite3 in2.sqlite 'create table s(k integer, l integer)'
sqlite3 in0.sqlite 'insert into s values (11, -11), (12, -12)'
sqlite3 in1.sqlite 'insert into s values (13, -13), (14, -14)'
sqlite3 in2.sqlite 'insert into s values (15, -15), (16, -16)'
./sqlite-merge-dbs out.sqlite in0.sqlite in1.sqlite in2.sqlite
sqlite3 out.sqlite 'select * from t'
echo
sqlite3 out.sqlite 'select * from s'
输出:
1|-1
2|-2
3|-3
4|-4
5|-5
6|-6
11|-11
12|-12
13|-13
14|-14
15|-15
16|-16
#!/usr/bin/env python
import os
import argparse
import sqlite3
import shutil
parser = argparse.ArgumentParser()
parser.add_argument('out')
parser.add_argument('ins', nargs='+')
args = parser.parse_args()
shutil.copyfile(args.ins[0], args.out)
con = sqlite3.connect(args.out)
cur = con.cursor()
tables = list(map(lambda e: e[0], cur.execute("SELECT name FROM sqlite_master WHERE type='table'")))
for db2 in args.ins[1:]:
cur.execute(f"attach '{db2}' as 'db2'")
for table in tables:
cur.execute(f"insert into {table} select * from db2.{table}")
con.commit()
cur.execute("detach database db2")
帮助Bash脚本,同时跳过自增列
如果表中存在重叠的自增主键列,sqlite-merge-dbs
脚本可能会出现意外的问题。
以下帮助程序通过查看pragma table_info
来判断列是否为自增列,并跳过这些列的合并,最后重新自增。
sqlite-merge-dbs-id
#!/usr/bin/env bash
set -eu
outdb="$1"
shift
indb0="$1"
shift
cp "$indb0" "$outdb"
for table in $(sqlite3 "$outdb" "SELECT name FROM sqlite_master WHERE type='table'"); do
echo "table: $table"
cols="$(sqlite3 "$outdb" "pragma table_info($table)" | awk -F\| '{ if ( $3!="INTEGER" || $6=="0" ) { print $2 } else { print "NULL" } }' | paste -sd , -)"
echo $cols
for db in "$@"; do
echo "db: $db"
sqlite3 "$outdb" "attach '$db' as 'db2'" "insert into \"$table\" select $cols from \"db2\".\"$table\""
done
done
rm -f in0.sqlite in1.sqlite in2.sqlite
sqlite3 in0.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in1.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in2.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in0.sqlite 'insert into t values (NULL, 1, -1), (NULL, 2, -2)'
sqlite3 in1.sqlite 'insert into t values (NULL, 3, -3), (NULL, 4, -4)'
sqlite3 in2.sqlite 'insert into t values (NULL, 5, -5), (NULL, 6, -6)'
sqlite3 in0.sqlite 'create table s(id integer primary key, k integer, l integer)'
sqlite3 in1.sqlite 'create table s(id integer primary key, k integer, l integer)'
sqlite3 in2.sqlite 'create table s(id integer primary key, k integer, l integer)'
sqlite3 in0.sqlite 'insert into s values (NULL, 11, -11), (NULL, 12, -12)'
sqlite3 in1.sqlite 'insert into s values (NULL, 13, -13), (NULL, 14, -14)'
sqlite3 in2.sqlite 'insert into s values (NULL, 15, -15), (NULL, 16, -16)'
./sqlite-merge-dbs-id out.sqlite in0.sqlite in1.sqlite in2.sqlite
sqlite3 out.sqlite 'select * from t'
echo
sqlite3 out.sqlite 'select * from s'
输出:
1|1|-1
2|2|-2
3|3|-3
4|4|-4
5|5|-5
6|6|-6
1|11|-11
2|12|-12
3|13|-13
4|14|-14
5|15|-15
6|16|-16
pragma table_info
的文档位于:https://www.sqlite.org/pragma.html#pragma_table_info
结果集中的列包括:"name"(列名);"type"(数据类型,如果有的话,否则为空);"notnull"(列是否可以为NULL);"dflt_value"(列的默认值);以及"pk"(对于不是主键的列,值为零;对于主键内的列,值为基于1的索引)。
我们可以通过以下示例进行观察:
sqlite3 in0.sqlite 'pragma table_info(t)'
输出结果为:
0|id|INTEGER|0||1
1|i|INTEGER|0||0
2|j|INTEGER|0||0
INTEGER
又是主键的内容。#!/usr/bin/env python
import os
import argparse
import sqlite3
import shutil
parser = argparse.ArgumentParser()
parser.add_argument('out')
parser.add_argument('ins', nargs='+')
args = parser.parse_args()
shutil.copyfile(args.ins[0], args.out)
con = sqlite3.connect(args.out)
cur = con.cursor()
tables = list(map(lambda e: e[0], cur.execute("SELECT name FROM sqlite_master WHERE type='table'")))
table_to_pk_col = {}
table_to_insert = {}
table_to_cols = {}
for table in tables:
cols = cur.execute(f'pragma table_info({table})').fetchall()
table_to_cols[table] = cols
for row in cols:
col_name = row[1]
type_ = row[2]
pk = row[5]
if type_ == 'INTEGER' and pk != 0:
if table in table_to_pk_col:
del table_to_pk_col[table]
else:
table_to_pk_col[table] = col_name
table_to_insert = { table: ','.join(list(map(
lambda c: 'NULL' if c[1] == table_to_pk_col.get(table, None) else c[1], table_to_cols[table]
))) for table in tables }
for db2 in args.ins[1:]:
cur.execute(f"attach '{db2}' as 'db2'")
cur.execute(f"begin")
for table in tables:
cur.execute(f"insert into {table} select {table_to_insert[table]} from db2.{table}")
con.commit()
cur.execute("detach database db2")
sqlite-merge-dbs-id-ref.py
#!/usr/bin/env python
import os
import argparse
import sqlite3
import shutil
parser = argparse.ArgumentParser()
parser.add_argument('out')
parser.add_argument('ins', nargs='+')
args = parser.parse_args()
shutil.copyfile(args.ins[0], args.out)
con = sqlite3.connect(args.out)
cur = con.cursor()
tables = list(map(lambda e: e[0], cur.execute("SELECT name FROM sqlite_master WHERE type='table'")))
table_to_pk_col = {}
table_to_insert = {}
table_to_cols = {}
table_to_pk_count = {}
table_to_col_to_foreign = {}
for table in tables:
col_to_foreign = {}
table_to_col_to_foreign[table] = col_to_foreign
cols = cur.execute(f'pragma foreign_key_list({table})').fetchall()
for col in cols:
col_name = col[3]
target_table = col[2]
col_to_foreign[col_name] = target_table
for table in tables:
cols = cur.execute(f'pragma table_info({table})').fetchall()
table_to_cols[table] = cols
for row in cols:
col_name = row[1]
type_ = row[2]
pk = row[5]
if type_ == 'INTEGER' and pk != 0:
if table in table_to_pk_col:
del table_to_pk_col[table]
else:
table_to_pk_col[table] = col_name
if table in table_to_pk_col:
table_to_pk_count[table] = cur.execute(f'select max({table_to_pk_col[table]}) from {table}').fetchone()[0]
else:
table_to_pk_count[table] = cur.execute(f'select count(*) from {table}').fetchone()[0]
def inc_str(table, col):
if table in table_to_col_to_foreign:
col_to_foreign = table_to_col_to_foreign[table]
if col in col_to_foreign:
return f'+{table_to_pk_count[col_to_foreign[col]]}'
return ''
for db2 in args.ins[1:]:
cur.execute(f"attach '{db2}' as 'db2'")
table_to_pk_count_inc = {}
for table in tables:
table_to_insert = {
table: ','.join(list(map(
lambda c: 'NULL' if c[1] == table_to_pk_col.get(table, None) else \
c[1] + inc_str(table, c[1]),
table_to_cols[table]
))) for table in tables
}
cur.execute(f"insert into {table} select {table_to_insert[table]} from db2.{table}")
table_to_pk_count_inc[table] = cur.rowcount
for table in tables:
table_to_pk_count[table] += table_to_pk_count_inc[table]
con.commit()
cur.execute("detach database db2")
INTEGER PRIMARY KEY
会自动递增。t
和s
,通过表ref
连接,它们都有外键。rm -f in0.sqlite in1.sqlite in2.sqlite
sqlite3 in0.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in1.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in2.sqlite 'create table t(id integer primary key, i integer, j integer)'
sqlite3 in0.sqlite 'insert into t values (1, 1, -1), (2, 2, -2), (3, 0, 0)'
sqlite3 in1.sqlite 'insert into t values (1, 3, -3), (2, 4, -4), (3, 0, 0)'
sqlite3 in2.sqlite 'insert into t values (1, 5, -5), (2, 6, -6), (3, 0, 0)'
sqlite3 in0.sqlite 'create table s(id integer primary key, i integer, j integer)'
sqlite3 in1.sqlite 'create table s(id integer primary key, i integer, j integer)'
sqlite3 in2.sqlite 'create table s(id integer primary key, i integer, j integer)'
sqlite3 in0.sqlite 'insert into s values (1, 1, -1), (2, 2, -2)'
sqlite3 in1.sqlite 'insert into s values (1, 3, -3), (2, 4, -4)'
sqlite3 in2.sqlite 'insert into s values (1, 5, -5), (2, 6, -6)'
for i in 0 1 2; do
sqlite3 "in$i.sqlite" <<EOF
create table ref(
k integer,
l integer,
primary key(k, l),
foreign key(k) references t(id),
foreign key(l) references s(id)
)
EOF
done
sqlite3 in0.sqlite 'insert into ref values (1, 2)'
sqlite3 in1.sqlite 'insert into ref values (1, 2)'
sqlite3 in2.sqlite 'insert into ref values (1, 2)'
./sqlite-merge-dbs-id-ref.py out.sqlite in0.sqlite in1.sqlite in2.sqlite
echo t
sqlite3 out.sqlite 'select * from t'
echo s
sqlite3 out.sqlite 'select * from s'
echo ref
sqlite3 out.sqlite 'select * from ref'
输出:
t
1|1|-1
2|2|-2
3|0|0
4|3|-3
5|4|-4
6|0|0
7|5|-5
8|6|-6
9|0|0
+ echo
s
1|1|-1
2|2|-2
3|3|-3
4|4|-4
5|5|-5
6|6|-6
+ echo
ref
1|2
4|4
7|6
t
和 s
的主键像以前一样递增,但是对于 ref
来说,发生了更复杂的情况:脚本根据主键的递增相应地递增外键,因此我们正确地维护了关系。4|4
ref
链接上升4|3|-3
从t
和4|4|-4
从s
。这些值在合并之前已经链接起来了。sqlite3 in1.sqlite 'insert into ref values (1, 2)'
which linked up values from:
sqlite3 in1.sqlite 'insert into t values (1, 3, -3), (2, 4, -4), (3, 0, 0)'
sqlite3 in1.sqlite 'insert into s values (1, 3, -3), (2, 4, -4)'
forign_key_list
pragma来列出外键https://www.sqlite.org/pragma.html#pragma_foreign_key_list,例如:sqlite3 in0.sqlite 'pragma foreign_key_list(ref)'
给出:
0|0|s|l|id|NO ACTION|NO ACTION|NONE
1|0|t|k|id|NO ACTION|NO ACTION|NONE
table_info
的返回结果为:sqlite3 in0.sqlite 'pragma table_info(s)'
给:
0|k|INTEGER|0||1
1|l|INTEGER|0||2
如果您已经浏览到此反馈的底部,但仍未找到解决方案,则还有一种方法可以合并2个或多个sqlite数据库的表格。
首先尝试下载和安装{{link1:DB browser for sqlite database}}。然后尝试在2个窗口中打开您的数据库,并尝试通过简单地拖放从一个表格到另一个表格来合并它们。但问题是您只能一次拖放一个表格,因此这不是针对此答案的真正解决方案,但如果您的数据库很小,它可以用来节省进一步搜索的时间。
毫不冒犯,作为一名开发者对另一位开发者说,我担心你的想法似乎非常低效。
在我看来,与其合并SQLite数据库,你应该将几个表存储在同一个数据库文件中。
但是如果我错了,我想你可以附加这些数据库,然后使用视图简化查询。或者创建一个内存表并复制所有数据(但从性能角度来看,这甚至更糟糕,特别是如果你有大型数据库)。
toMerge
分离出来:detach toMerge;
。 - mike