this is my first post in Stackoverflow.com

This is the process I'm following:

1. Make a connection to dB
2. Make a query to the dB to check if the register exists
3. If the register does NOT exist iterate over a loop
4. Add the registers in the dB

My code:

conn = sqlite3.connect('serps.db')
c = conn.cursor()

# 1) Make the query
c.execute("SELECT fecha FROM registros WHERE fecha=? AND keyword=?", (fecha, q))

# 2) Check if exists
exists = c.fetchone()
conn.commit()

if not exists:

    for data in json:
        ...

        c.execute("INSERT INTO registros VALUES (?, ?, ?, ?, ?, ?)", (fecha, hora, q, rank, url, title))
        conn.commit()

I get the following error:

---> conn.commit()
OperationalError: database is locked

I think if I close the database after checking if the register exists, I could open it again and it will work.

But should I close and open connections when INSERT after SELECT?

CodePudding user response：

SQLite is meant to be a lightweight database, and thus can't support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.

So try switching to another database backend.

CodePudding user response：

You should learn what transactions are, what .commit() does, and how to use .executemany().

How come you have .commit after .fetchone!? Do NOT place .commit() inside a loop. In fact, you should avoid placing INSERTs in a loop as well. Prepare a list of tuples or dicts for insertions in the loop and call the db just once.