Efficient insert of multiple rows with SQLAlchemy/SQLite3 when duplicate entries exist

Question

I'm inserting multiple rows into an SQLite3 table using SQLAlchemy, and frequently the entries are already in the table. It is very slow to insert the rows one at a time, and catch the exception and continue if the row already exists. Is there an efficient way to do this? If the row already exists, I'd like to do nothing.

Answer 1

You can use an SQL statement

INSERT OR IGNORE INTO ... etc. ...

to simply ignore the insert if it is a duplicate. Learn about the IGNORE conflict clause here

Perhaps you can use OR IGNORE as a prefix in your SQLAlchemy Insert -- the documentation for how to place OR IGNORE between INSERT and INTO in your SQL statement is here

Answer 2

If you are happy to run 'native' sqlite SQL you can just do:

REPLACE INTO my_table(id, col2, ..) VALUES (1, 'value', ...);
REPLACE INTO my_table(...);
...
COMMIT

However, this won't be portable across all DBMS's and is therefore the reason that its not found in the general sqlalchemy dialect.

Another thing you could do is use the SQLAlchemy ORM, define a 'domain model' -- a python class which maps to your database table. Then you can create many instances of your domain class and call session.save_or_update(domain_object) on each of the items you wish to insert (or ignore) and finally call session.commit() when you want to insert (or ignore) the items to your database table.

This question looks like a duplicate of SQLAlchemy - INSERT OR REPLACE equivalent