Merge tables from two different databases - sqlite3/Python

Question

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.

Answer 1

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.

Example:

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.