Python - Bulk Select, then paste from one database to another

Question

I am looking for some help on how to do this in Python using sqlite3

Basically I have a process that loads DB (temp), and then it needs to insert it into the 2nd identical DB (main db) .. and at the same time ignore / bypass any possible repeated key errors

I was thinking of two scenarios but not sure how best to do this in Python

Option 1:

• create 2 joins and cursor objects, 1 for each DB

• select from DB 1, for example:

  dbcur.executemany('SELECT * from table1') rows = dbcur.fetchall() 

• insert them into DB 2:

   dbcur.execute('INSERT INTO table1 VALUES (:column1, :column2)', rows) dbcon.commit() 

This, of course, does not work, since I'm not sure how to do it correctly :)

Option 2 (which I would prefer, but not sure how to do this):

• SELECT and INSERT in statement 1

Also, I have 4 tables in a DB database with different columns, can I skip the column names in the INSERT statement?

Regarding duplicate keys, I read that I can use "ON DUPLICATE KEY" for processing for example.

 INSERT INTO table1 VALUES (:column1, :column2) ON DUPLICATE KEY UPDATE set column1=column1 

Answer 1

You can ATTACH two databases to the same connection with this code:

 import sqlite3 connection = sqlite3.connect('/path/to/temp.sqlite') cursor=connection.cursor() cursor.execute('ATTACH "/path/to/main.sqlite" AS master') 

There is no ON DUPLICATE KEY syntax in sqlite, as in MySQL. This SO question contains alternatives.

So, to do bulk insertion in a single SQL statement, you can use something like

 cursor.execute('INSERT OR REPLACE INTO master.table1 SELECT * FROM table1') 

See this page for information on REPLACE and other ON CONFLICT options.

Answer 2

The code for option 1 looks right.

If you need filtering to bypass duplicate keys, paste in a temporary table and then use SQL commands to eliminate duplicates and merge them into the target table.