Best way to access and close a postgres database using python dataset

Question

import dataset    
from sqlalchemy.pool import NullPool

db = dataset.connect(path_database, engine_kwargs={'poolclass': NullPool})

table_f1 = db['name_table']
# Do operations on table_f1

db.commit()
db.executable.close()

I use this code to access a postgres database and sometimes write to it. Finally, I close it. Is the above code the best way to access and close it? Alternatively, is the code below better?

import dataset    
from sqlalchemy.pool import NullPool

with dataset.connect(path_database, engine_kwargs={'poolclass': NullPool}) as db:
    table_f1 = db['name_table']
    # Do operations on table_f1

    db.commit()

In particular, I want to make 100% sure that there is no connection to the postgres database once this piece of code is done. Which is the better way to achieve it? option 1 or option 2?

Answer 1

For now, the main issue is that the context manager used in Option 2 (with statement) doesn't handle the connection, only the transaction (commit/rollback at the end of the block).

(This question is already reported to the Github repo, maybe the behavior will change ?)

So you should replace db.commit() by db.executable.close() in Option 2:

import dataset    
from sqlalchemy.pool import NullPool

with dataset.connect(path_database, engine_kwargs={'poolclass': NullPool}) as db:
    table_f1 = db['name_table']
    print(db.local.conn.closed) # >>>False

    # Do operations on table_f1
    # end of the context manager, trying to commit 

db.executable.close()
print(db.local.conn.closed) # >>>True

Now connection is closed :

# db['name_table'].all() ==> throws an error due to closed connection

BUT... you can still create new tables in the database (because of Metadata ?) :

# db['new_table'] ==> enough to add a new table 

So you may want to destroy everything to prevent this (db = None, or db.metadata = None)

---

This last behavior happens in SQLAlchemy too:

from sqlalchemy import *
from sqlalchemy.pool import NullPool

engine = create_engine('postgresql:///datatest', poolclass=NullPool) 

connection = engine.connect()
meta = MetaData(engine)
t1 = Table('Table_1', meta,
           Column('id', Integer, primary_key=True),
           Column('name',String))
t1.create()
connection.close()

t2 = Table('Table_2', meta,
           Column('id', Integer, primary_key=True),
           Column('name',String))
t2.create()
# table_2 is created in database

EDIT:

(thanks to Ilja Everilä's comment, and a focus on the doc)

Better call meta = MetaData(connection) in order to close the connection at the engine disposal, this will raise an error in the above example, connection IS closed.