使用 python 数据集访问和关闭 postgres 数据库的最佳方法

2022-01-21 00:00:00 python postgresql dataset

问题描述

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()

我使用这段代码来访问一个 postgres 数据库并且有时会写入它.最后，我关闭它.上面的代码是访问和关闭它的最佳方式吗?或者，下面的代码更好吗?

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()

特别是，我希望 100% 确保在完成这段代码后不会连接到 postgres 数据库.实现它的更好方法是什么?选项 1 还是选项 2?

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?

解决方案

目前，主要问题是选项 2 中使用的上下文管理器(with 语句)没有处理连接，只有 事务(提交/回滚块的末尾).

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).

(这个问题已经报告给Github repo，也许这个行为会改变?)

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

所以你应该在选项 2 中将 db.commit() 替换为 db.executable.close():

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

现在连接已关闭:

# 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

因此，您可能希望销毁所有内容以防止这种情况发生(db = None 或 db.metadata = None)

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

最后一个行为也发生在 SQLAlchemy 中:

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

(感谢 Ilja Everilä 的评论，以及对 的关注文档)

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

最好调用 meta = MetaData(connection) 以便在引擎处理时关闭连接，这将在上面的示例中引发错误，连接已关闭.

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.

相关文章