import dataset    
from sqlalchemy.pool import NullPooldb = dataset.connect(path_database, engine_kwargs={'poolclass': NullPool})table_f1 = db['name_table']
# Do operations on table_f1db.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 NullPoolwith dataset.connect(path_database, engine_kwargs={'poolclass': NullPool}) as db:table_f1 = db['name_table']# Do operations on table_f1db.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?

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 NullPoolwith 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 NullPoolengine = 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.