pythonpandasms-accesssqlalchemy-access

MS Access database locked by Python

I'm writing a dataframe to an MS Access database using Python. It works, but I can't seem to close the connection (i.e. the database is locked by Python).

Here's my code:

# imports
import urllib
import pyodbc
import pandas as pd
from sqlalchemy import create_engine

# connection
connection_string = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=M:\Larry\Access\test2.accdb;'
    r'ExtendedAnsiSQL=1;'
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

# create dataframe
d = {'name':['daryl','other daryl'],'team':['a','b']}
df = pd.DataFrame(d)

# write to database
df.to_sql('test',engine,if_exists='replace',index=False)

I tried adding this after the create engine line:

conn = engine.connect()

and this after the dataframe is written to the database:

conn.close()

I don't get an error from these additions but the database is still locked. How do I close the connection so that the lock is removed?

Solution

  • Since you are maintaining a connection instance of the database with engine object, after operations, consider Engine.dispose() to release the resource:

    ...
# write to database 
df.to_sql('test', engine, if_exists='replace', index=False)

engine.dispose()