Python - Bulk Select then Insert from one DB to another

Question

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

Basically I have a process which downloads a DB (temp) and then needs to insert it's records into a 2nd identical DB (the main db).. and at the same time ignore/bypass any possible duplicate key errors

I was thinking of two scenarios but am unsure how to best do this in Python

Option 1:

• create 2 connections and cursor objects, 1 to each DB

• select from DB 1 eg:

  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 as I'm not sure how to do it properly :)

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

• SELECT and INSERT in 1 statement

Also, I have 4 tables within the DB's each with varying columns, can I skip naming the columns on the INSERT statement?

As far as the duplicate keys go, I have read I can use 'ON DUPLICATE KEY' to handle eg.

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

• pajton: This question is too broad. Option 2 is impossible AFAIK, you should be able to go with option 1. Try doing option 1 and post the code if your having problems.
• MikeM: Well my testing code is basically as I wrote for option 1 - table and column names changed to make my post simple. I get an error attempting to do it like that - 'ValueError: parameters are of unsupported type'. So I'm assuming I'm not passing in the values in proper format
• MikeM: Ah.. I should update to say my option 1 does work (I had something stupid in my code that conflicted with it).. the answer from unutbu is my preferred choice and works perfect!

Answer 1

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

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 there is in MySQL. This SO question contains alternatives.

So to do the bulk insert in one sql statement, you could use something like

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

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

• MikeM: Very nice, thanks! I should note that testing this I could not use 'main' as the name for my attached DB, must be a reserved word - "OperationalError: database main is already in use"
• MikeM: I'm also giving INSERT OR IGNORE a try and see how it performs with my test data, I think this is my preferred route as data in the master db could have been modified slightly and I don't want to lose those changes.. thanks for the links, very helpful!

Answer 2

The code for option 1 looks correct.

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