sqlite + pandas: database table is locked

Question

I'm getting an unexpected error when using sqlite3 in python with pandas. I'm using a sqlite database for an analysis I'm doing, so it's single-user, single-computer. I'm in Python 3.9.1, with sqlite 3.33.0 and pandas 1.2.1.

The short description is that I'm trying to loop over rows of Table1, and for each row, insert data into Table2 based on an API request using an ID stored in Table1. The API gets me a lot more columns than I need for Table2, so I do the following to insert it into a new temporary table, then copy over the columns I need into Table1:

my_dataframe.to_sql("tmp", conn, if_exists="replace", index=False)
cur.execute("INSERT INTO Table1 (col1, col2) SELECT col1, col2 FROM Table2")

The problem is, on the second iteration of the loop, I get an error when pandas tries to drop the tmp table. Here is the full code:

def get_data(api_id, conn):
    my_dataframe = call_to_api(api_id)
    my_dataframe.to_sql("tmp", conn, if_exists="replace", index=False)
    cur.execute("INSERT INTO Table1 (col1, col2) SELECT col1, col2 FROM Table2")

for chunk in pd.read_sql_query("SELECT id_for_api FROM Table1", conn, chunksize=10):
    ids = chunk["id_for_api"].values

    for api_id in ids:
        get_data(api_id, conn)

The error I get is:

DatabaseError: Execution failed on sql 'DROP TABLE "tmp"': database table is locked

which is raised by this line:

pd.DataFrame(data).to_sql("tmp", conn, if_exists="replace", index=False)

I've tried everything I could think of to fix this:

changing the connection to be isolation_level=None (autocommit)
adding conn.commit() after the INSERT statement
creating a new cursor within the get_data function (cur = conn.cursor())
creating a new connection for use in the outer loop with read_sql_query (conn2 = sqlite3.connect('mydb.db'))

What am I missing? Is there something about sqlite isolation levels or locking that I don't understand?

user_4541045 · Answer

When you make your connection, set autocommit=True

@contextlib.contextmanager
def database_connect():
    db_conn = pyodbc.connect(
        autocommit=True,  # needed to prevent locks in DB with SPs
    )
    try:
        yield db_conn
    finally:
        db_conn.close()

...
    with database_connect() as db_conn:
        df = pd.read_sql_query(
            f"EXEC {sp_table}.{sp_name} " + ",".join(f"@{a}=?" for a in kwargs.keys()),
            db_conn,
            params=kwargs.values()
        )

sqlite + pandas: database table is locked

1 Answers