使用 Elixir 执行 sql 查询
我在一个连接到 postgres 数据库的项目中使用 Elixir。我想在我连接的数据库上运行以下查询,但我不确定如何执行,因为我对 Elixir 和 SQLAlchemy 还很陌生。有人知道怎么做吗?
VACUUM FULL ANALYZE 表
更新
错误是:“UnboundExecutionError:无法找到在 SQL 表达式或此会话上配置的绑定”。与之前发出的 session.close() 的结果相同。我确实尝试过执行metadata.bind.execute(),这适用于简单的选择。但对于 VACUUM,它说 - “InternalError:(InternalError)VACUUM 无法在事务块内运行”,所以现在我试图弄清楚如何将其关闭。
更新 2
我可以执行查询,但仍然遇到相同的错误 - 即使我创建一个新会话并关闭前一个会话。
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# ... insert stuff
old_session.commit()
old_session.close()
new_sess = sessionmaker(autocommit=True)
new_sess.configure(bind=create_engine('postgres://user:pw@host/db', echo=True))
sess = new_sess()
sess.execute('VACUUM FULL ANALYZE table')
sess.close()
我得到的输出是
2009-12-10 10:00:16,769 INFO sqlalchemy.engine.base.Engine.0x...05ac VACUUM FULL ANALYZE table
2009-12-10 10:00:16,770 INFO sqlalchemy.engine.base.Engine.0x...05ac {}
2009-12-10 10:00:16,770 INFO sqlalchemy.engine.base.Engine.0x...05ac ROLLBACK
finishing failed run, (InternalError) VACUUM cannot run inside a transaction block
'VACUUM FULL ANALYZE table' {}
Update 3
感谢所有回复的人。 我无法找到我想要的解决方案,但我想我只会采用此处描述的解决方案PostgreSQL - 如何从事务块之外的代码运行 VACUUM?。这并不理想,但它有效。
I'm using Elixir in a project that connects to a postgres database. I want to run the following query on the database I'm connected to, but I'm not sure how to do it as I'm rather new to Elixir and SQLAlchemy. Anyone know how?
VACUUM FULL ANALYZE table
Update
The error is: "UnboundExecutionError: Could not locate a bind configured on SQL expression or this Session". And the same result with session.close() issued before. I did try doing metadata.bind.execute() and that worked for a simple select. But for the VACUUM it said - "InternalError: (InternalError) VACUUM cannot run inside a transaction block", so now I'm trying to figure out how to turn that off.
Update 2
I can get the query to execute, but I'm still getting the same error - even when I create a new session and close the previous one.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# ... insert stuff
old_session.commit()
old_session.close()
new_sess = sessionmaker(autocommit=True)
new_sess.configure(bind=create_engine('postgres://user:pw@host/db', echo=True))
sess = new_sess()
sess.execute('VACUUM FULL ANALYZE table')
sess.close()
and the output I get is
2009-12-10 10:00:16,769 INFO sqlalchemy.engine.base.Engine.0x...05ac VACUUM FULL ANALYZE table
2009-12-10 10:00:16,770 INFO sqlalchemy.engine.base.Engine.0x...05ac {}
2009-12-10 10:00:16,770 INFO sqlalchemy.engine.base.Engine.0x...05ac ROLLBACK
finishing failed run, (InternalError) VACUUM cannot run inside a transaction block
'VACUUM FULL ANALYZE table' {}
Update 3
Thanks to everyone who responded. I wasn't able to find the solution I wanted, but I think I'm just going to go with the one described here PostgreSQL - how to run VACUUM from code outside transaction block?. It's not ideal, but it works.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
该死。我知道答案就在我眼皮底下。假设您像我一样设置连接。
解决方案很简单,
这与此处建议的类似PostgreSQL - 如何从事务块外部的代码运行 VACUUM?
因为在这一切之下,sqlalchemy 使用 psycopg 来连接到 postgres。 Connection.connection 是 psycopg 连接的代理。当我意识到这一点时,这个问题再次出现在我的脑海中,我决定再次解决它。
希望这对某人有帮助。
Dammit. I knew the answer was going to be right under my nose. Assuming you setup your connection like I did.
The solution to this was as simple as
This is similar to what was suggested here PostgreSQL - how to run VACUUM from code outside transaction block?
because underneath it all, sqlalchemy uses psycopg to make the connection to postgres. Connection.connection is a proxy to the psycopg connection. Once I realized this, this problem came back to mind and I decided to take another whack at it.
Hopefully this helps someone.
您需要将会话绑定到引擎
You need to bind the session to an engine
UnboundExecutionError
表示您的会话未绑定到引擎,并且无法从传递给execute()
的查询中发现引擎。您可以直接使用engine.execute()
或将附加mapper
参数(映射器或与查询中使用的表相对应的映射模型)传递给session.execute( )
帮助 SQLAlchemy 发现合适的引擎。InternalError
表示您正在尝试在显式(使用 BEGIN 语句)启动的事务中执行此语句。您是否在没有调用commit()
的情况下在它之前发出了一些语句?如果是这样,只需在执行 VACUUM 之前调用commit()
或rollback()
方法来关闭事务。另请注意,sessionmaker()
有几个参数告诉 SQLAlchemy 事务应何时启动。UnboundExecutionError
says that your session is not bound to an engine and there is no way to discover engine from query passed toexecute()
. You can either useengine.execute()
directly or pass additionalmapper
parameter (either mapper or mapped model corresponding to table used in query) tosession.execute()
to help SQLAlchemy discover proper engine.The
InternalError
says that you are trying to execute this statement inside explicitly (with BEGIN statement) started transaction. Have you issued some statements before it without callingcommit()
? If so, just callcommit()
orrollback()
method to close transaction before doing VACUUM. Also note, that there are several parameter tosessionmaker()
that tell SQLAlchemy when transaction should be started.如果您有权访问 SQLAlchemy 会话,则可以通过其
execute
方法执行任意 SQL 语句:If you have access to SQLAlchemy session, you can execute arbitrary SQL statements via its
execute
method:(取决于 Postgres 版本)您很可能不这样做想要运行“VACUUM FULL”。
(Depending on the Postgres version) you most likely do not want to run "VACUUM FULL".