CODEWITHSUNDEEP
pythonsqliteupsert
etayluz
etayluz

Reputation: 16436

SQLITE: Bulk insert or replace (upsert)

I have an application that inserts (or replaces) records with a unique key into a SQLite database table. It is expected to do about 1500 records per second but it's slower than this due to performance issues.

Currently I'm doing it as such (in Python):

for item in items:
    CONN.execute("INSERT OR REPLACE INTO ITEMS (ITEM_ID) VALUES ("+item['itemId']+")")

What I would like is a bulk insert or replace to improve performance.

I know there is a way to do bulk inserts: Is it possible to insert multiple rows at a time in an SQLite database?

But is there a way to do bulk insert or replace? I know that insert or replace is NOT the same as upsert - that's OK.

Upvotes: 6

Views: 1804

Answers (1)

guy_fawkes
guy_fawkes

Reputation: 965

Try something like this

INSERT OR REPLACE INTO items (id, link, added) VALUES (19122727, "https://instant.page/", COALESCE((SELECT added FROM items WHERE id = 19122727), 1549742777)),(19123506, "http://hades.mech.northwestern.edu/index.php/Modern_Robotics", COALESCE((SELECT added FROM items WHERE id = 19123506), 1549742777)),(19123352, "https://dendibakh.github.io/blog/2019/02/09/Top-Down-performance-analysis-methodology", COALESCE((SELECT added FROM items WHERE id = 19123352), 1549742777))

Upvotes: -2

Related Questions