目前,主要问题是选项 2 中使用的上下文管理器(有声明) 不处理连接,仅处理交易 https://github.com/pudo/dataset/blob/master/dataset/persistence/database.py#L123(在块末尾提交/回滚)。
(这个问题已经反馈给Github 仓库 https://github.com/pudo/dataset/issues/211,也许行为会改变?)
所以你应该更换db.commit()
by db.executable.close()
在选项 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
现在连接已关闭:
# db['name_table'].all() ==> throws an error due to closed connection
但是...您仍然可以在数据库中创建新表(因为元数据?):
# db['new_table'] ==> enough to add a new table
所以你可能想毁掉一切来防止这种情况发生(db = None
, or db.metadata = None
)
最后一个行为也发生在 SQLAlchemy 中:
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:
(感谢 Ilja Everilä 的评论,并重点关注the doc http://docs.sqlalchemy.org/en/latest/core/connections.html#engine-disposal)
最好打电话meta = MetaData(connection)
为了关闭引擎处置的连接,这将在上面的示例中引发错误,连接已关闭。