I’m trying to better understand the interactions between python and a SQL Server database, utilizing Pandas and SQL Alchemy.

I typically just use what is probably a pretty vanilla approach.

engine = create_engine(fast_executemany=True)
df.to_sql(con=engine)

Now I would like to be a little more refined in my approach. Pandas documentation states for the con parameter:

Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable. See here. If passing a sqlalchemy.engine.Connection which is already in a transaction, the transaction will not be committed. If passing a sqlite3.Connection, it will not be possible to roll back the record insertion.

The first line that gets me is:

The user is responsible for engine disposal and connection closure.

So in my vanilla approach, is .to_sql opening a connection to insert with and then never closing it? Just leaving this “thing” hanging out there in the ether to be picked up by some sort of idle timeout, or garbage collection? If this is the case would you handle it in some strange manner like:

engine = create_engine()
conn = engine.connect()
df.to_sql(con=engine)
conn.close()

We then get to:

if passing a sqlalchemy.engine.Connection which is already in a transaction, the transaction will not be commited.

It seems SQLAlchemy defines a transaction as using the begin() method on an a connected engine. Would you then pass the transaction to .to_sql()?

engine = create_engine()
transaction = engine.connect()
df.to_sql(con=transaction)
transaction.commit()
transaction.close()

What happens if I pass a Connection object which isn’t in a transaction?

engine = create_engine()
with engine.connect() as connection:
    df.to_sql(con=connection)

What about?

engine = create_engine()
with engine.connect() as connection:
    with connection.begin() as transaction:
        df.to_sql(con=transaction)

I could probably do a bunch of investigation on my own but am just curious to hear if anyone has expert opinions on this, and the best way to handle it?