Need help on python sqlite?

Question

1.I have a list of data and a sqlite DB filled with past data along with some stats on each data. I have to do the following operations with them.

• Check if each item in the list is present in DB. if no then collect some stats on the new item and add them to DB.
• Check if each item in DB is in the list. if no delete it from DB.

I cannot just create a new DB, coz I have other processing to do on the new items and the missing items.
In short, i have to update the DB with the new data in list.
What is best way to do it?

2.I had to use sqlite with python threads. So I put a lock for every DB read and write operation. Now it has slowed down the DB access.
What is the overhead for thread lock operation? And Is there any other way to use the DB with multiple threads?
Can someone help me on this?I am using python3.1.

Answer 1

1. It does not need to check anything, just use INSERT OR IGNORE in first case (just make sure you have corresponding unique fields so INSERT would not create duplicates) and DELETE FROM tbl WHERE data NOT IN ('first item', 'second item', 'third item') in second case.

2. As it is stated in the official SQLite FAQ, "Threads are evil. Avoid them." As far as I remember there were always problems with threads+sqlite. It's not that sqlite is not working with threads at all, just don't rely much on this feature. You can also make single thread working with database and pass all queries to it first, but effectiveness of such approach is heavily dependent on style of database usage in your program.