使用python数据集访问和关闭Postgres数据库的最佳方法 [英] Best way to access and close a postgres database using python dataset
问题描述
从sqlalchemy.pool导入数据集
导入NullPool
db = dataset.connect(path_database,engine_kwargs = {'poolclass':NullPool})
table_f1 = db ['name_table']
#对table_f1执行操作
db.commit()
db.executable.close()
我使用此代码访问postgres数据库,有时会对其进行写入。最后,我关闭它。上面的代码是访问和关闭它的最佳方法吗?或者,下面的代码更好吗?
从sqlalchemy.pool导入数据集
导入NullPool
with dataset.connect(path_database,engine_kwargs = {'poolclass':NullPool})as db:
table_f1 = db ['name_table']
#在table_f1上执行操作
db.commit()
特别是,我想100%确保没有连接这段代码完成后,将其添加到postgres数据库。哪个更好的方法来实现它?选项1或选项2?
目前,主要问题是选项2(带有语句)不处理连接,只有交易(在块末尾提交/回滚)。
(此问题已报告给 Github存储库,也许行为会改变吗?)
所以您应该替换 db.commit()
通过选项2中的 db.executable.close()
:
从sqlalchemy.pool导入数据集
使用数据集.connect(path_database,engine_kwargs = {'poolclass':NullPool})导入NullPool
为db:
table_f1 = db [' name_table']
print(db.local.conn.closed)#>>> False
#对table_f1进行操作
#上下文管理器的末尾,尝试提交
db.executable.close()
print(db.local.conn.closed)#>>>真实
现在连接已关闭:
#db [ 'name_table']。all()==>由于关闭连接而引发错误
但是...您仍然可以在数据库中创建新表(因为元数据?):
#db ['new_table'] ==>足以添加一个新表
所以您可能想要销毁所有内容以防止出现这种情况( db = None
或 db.metadata = None
)
这最后一个行为也发生在SQLAlchemy中:
from sqlalchemy import *
$ p $中创建的p>
from sqlalchemy.pool import NullPool
engine = create_engine('postgresql:/// datatest',poolclass = NullPool)
connection = engine.connect()
元= 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是在数据库
编辑:
(感谢IljaEverilä的评论,并重点介绍了文档)
更好的调用
meta = MetaData(connection)
以便在引擎处理时关闭连接,这将在上面的示例中引发错误,即连接IS已关闭。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?
解决方案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()
bydb.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
, ordb.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.这篇关于使用python数据集访问和关闭Postgres数据库的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!