-1

I was going crazy trying to solve this problem. After running the following code I could not delete another table of my database, with the error "database is locked". This is the code that locked my database:

con = sqlite3.connect('db/db.sqlite3')
cur=con.cursor()
query=f"SELECT * FROM data_temp LIMIT 1;"
res=cur.execute(query)
con.close()

I solved including a line to read the results:

con = sqlite3.connect('db/db.sqlite3')
cur=con.cursor()
query=f"SELECT * FROM data_temp LIMIT 1;"
res=cur.execute(query)
out=res.fetchall()  #<-------
con.close()

No idea why this works...anybody knows?

Rafael Romero
  • 13
  • 4

1 Answers1

0

The reason you experienced the "database is locked" error is related to how SQLite handles database connections and transactions.

When you execute a query using execute() in SQLite, it starts a transaction implicitly. The transaction remains active until it is committed or rolled back. If you don't explicitly commit or roll back the transaction, SQLite keeps it open.

In your first code snippet, you are executing the query but not fetching the results. Since the transaction is still open, the database connection is locked until the transaction is completed.

By adding res.fetchall() or any other fetch method (e.g., res.fetchone()) after executing the query, you are consuming the result of the query. This action signals to SQLite that you have finished working with the results and allows the transaction to be completed.

When the transaction is completed, the lock on the database connection is released, and you can perform other operations, such as deleting a table.

To ensure that the database connection is always properly closed, it's recommended to use a context manager (with statement) when working with SQLite connections. This ensures that the connection is closed even if an exception occurs. Here's an updated version of your code using a context manager:

query = "SELECT * FROM data_temp LIMIT 1;"

with sqlite3.connect('db/db.sqlite3') as con:
    cur = con.cursor()
    cur.execute(query)
    out = cur.fetchall()

By using the with statement, the connection is automatically closed once you exit the block, even if an exception is raised. This helps prevent database locking issues.

Martin
  • 46
  • 9