合并来自两个不同数据库的表-sqlite3/Python [英] Merge tables from two different databases - sqlite3/Python
问题描述
我有两个不同的SQLite数据库XXX和YYY. XXX包含表A,YYY包含表B. A和B具有相同的结构(列). 如何在Python中附加A中B的行-SQLite API. 附加A后包含A行和B行.
I have two different SQLite databases XXX and YYY. XXX contains table A and YYY contains B respectively. A and B have same structure(columns). How to append the rows of B in A in Python - SQLite API. After appending A contains rows of A and rows of B.
推荐答案
您首先使用sqlite3.connect
与数据库建立连接,然后创建一个游标,以便执行sql.一旦有了游标,就可以执行任意的sql命令.
You first get a connection to the database using sqlite3.connect
, then create a cursor so you can execute sql. Once you have a cursor, you can execute arbitrary sql commands.
示例:
import sqlite3
# Get connections to the databases
db_a = sqlite3.connect('database_a.db')
db_b = sqlite3.connect('database_b.db')
# Get the contents of a table
b_cursor = db_b.cursor()
b_cursor.execute('SELECT * FROM mytable')
output = b_cursor.fetchall() # Returns the results as a list.
# Insert those contents into another table.
a_cursor = db_a.cursor()
for row in output:
a_cursor.execute('INSERT INTO myothertable VALUES (?, ?, ...etc..., ?, ?)', row)
# Cleanup
db_a.commit()
a_cursor.close()
b_cursor.close()
注意:我实际上还没有测试过,所以其中可能包含一些错误,但是我认为基本思路是合理的.
Caveat: I haven't actually tested this, so it might have a few bugs in it, but the basic idea is sound, I think.
这篇关于合并来自两个不同数据库的表-sqlite3/Python的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!