how to close pandas read_sql connection

Question

The following code yields an error that says the database is locked:

    df_chunks = pd.read_sql('cdr', engine, chunksize=100000)
    engine.dispose()
    with engine.connect() as conn:
        trans = conn.begin()
        query = """
        CREATE TABLE test (row_id BIGINT,ego_id BIGINT,alter_id BIGINT)
        """
        print(query)
        conn.execute(query)
        trans.commit()
        conn.close()

but if I do it the other way around:

    with engine.connect() as conn:
        trans = conn.begin()
        query = """
        CREATE TABLE test (row_id BIGINT,ego_id BIGINT,alter_id BIGINT)
        """
        print(query)
        conn.execute(query)
        trans.commit()
        conn.close()
    df_chunks = pd.read_sql('cdr', engine, chunksize=100000)

it works and I am able to generate the new table. So from here, it seems that the problem is that pd.read_sql (see docs) locks the database. I found this question but engine.dispose() didn't work for me. What's the way to go around this problem?

Answer 1

Because chunksize builds a generator of data frames and you never do anything with df_chunks, this object may still hold a pointer to source object, engine, and therefore "locks" the database. To resolve your first attempt, consider running an iteration on the df_chunks:

with engine.connect() as conn:
    trans = conn.begin()
    query = """CREATE TABLE test (row_id BIGINT,ego_id BIGINT,alter_id BIGINT)
            """
    print(query)
    conn.execute(query)
    trans.commit()

df_chunks = pd.read_sql('cdr', engine, chunksize=100000)

for df in df_chunks:
    # DO SOMETHING WITH EACH df

engine.dispose()                     # ALLOWED SINCE GENERATOR IS EXHAUSTED AFTER for LOOP

Alternatively, to resolve your second attempt, integrate your read_sql call inside with block and use the conn object.

with engine.connect() as conn:
    trans = conn.begin()
    query = """CREATE TABLE test (row_id BIGINT,ego_id BIGINT,alter_id BIGINT)
            """
    print(query)
    conn.execute(query)
    trans.commit()

    # INDENT LINE AND USE conn OBJECT
    df_chunks = pd.read_sql('cdr', conn, chunksize=100000)

    for df in df_chunks:
        # DO SOMETHING WITH EACH df

engine.dispose()                     # CLOSE engine OBJECT NOT conn

Also, when using with as a context manager, it is unnecessary to call close: conn.close().