sqlite 将不匹配的记录插入到另一个数据库连接

pod7payv 于 2022-11-24 发布在 SQLite

关注(0)|答案(1)|浏览(162)

我有两个数据库连接。我希望将每个连接中的单个表相互比较。如果存在不匹配的记录，我希望将它们添加到缺少它们的表数据库中。
这是我想出来的，但id似乎不做插入部分。我是新的python原谅代码谢谢。

# establishing connections and querying the database

import sqlite3
con1 = sqlite3.connect("database1.db")
cur1 = con1.cursor()
table1 = cur1.execute("SELECT * FROM table1")
fetch_table1 = table1.fetchall()
mylist = list(table1)

con2 = sqlite3.connect("database2.db")
cur2 = con2.cursor()
table2= cur2.execute("SELECT * FROM table2")
table2 = table2.fetchall()
mylist2 = list(table2). 

# finding unmatched eliminates and inserting them to the database

def non_match_elements(mylist2, mylist):
    non_match = []
    for i in mylist2:
        if i not in mylist:
            non_match.append(i)
            non_match = non_match_elements(mylist2, mylist)
        cur1.executemany("""INSERT INTO table 1 VALUES (?,?,?)""", non_match)

con1.commit()
res = cur1.execute("select column from table1")
print(res.fetchall())

再次感谢各位

sqlite

来源：https://stackoverflow.com/questions/73635684/insert-unmatched-records-to-another-database-connection

1条答案

按热度按时间

y1aodyip1#

我建议使用ATTACH将一个连接连接到另一个连接，这样就有两个INSERT INTO table SELECT * FROM table2 WHERE查询可以从一个表插入到另一个表。
下面是一个示例/演示（不是ATTACH DATABASE，而是对齐两个具有相同模式但具有不同数据的表）：

/* Cleanup - just in case*/
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
/* Create the two tables */
CREATE TABLE IF NOT EXISTS table1 (val1 TEXT, val2 TEXT, val3 TEXT);
CREATE TABLE IF NOT EXISTS table2 (val1 TEXT, val2 TEXT, val3 TEXT);
/*****************************************************************/
/* load the two different sets of data and also some common data */
INSERT INTO table1 VALUES ('A','AA','AAA'),('B','BB','BBB'),('C','CC','CCC'),('M','MM','MMM');
INSERT INTO table2 VALUES ('X','XX','XXX'),('Y','YY','YYY'),('Z','ZZ','ZZZ'),('M','MM','MMM');
/*************************************************************/
/* Macth each table to the other using an INSERT .... SELECT */
/*************************************************************/
INSERT INTO table1 SELECT * FROM table2 WHERE val1||val2||val3 NOT IN (SELECT(val1||val2||val3) FROM table1);
INSERT INTO table2 SELECT * FROM table1 WHERE val1||val2||val3 NOT IN (SELECT(val1||val2||val3) FROM table2);
/* Output both tables */
SELECT 'T1',* FROM table1;
SELECT 'T2',* FROM table1;
/* Cleanup */
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;

2次选择的结果为：