Execute sql query with Elixir

Question

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 http://stackoverflow.com/questions/1017463/postgresql-how-to-run-vacuum-from-code-outside-transaction-block. It's not ideal, but it works.

Answer 1

If you have access to SQLAlchemy session, you can execute arbitrary SQL statements via its execute method:

session.execute("VACUUM FULL ANALYZE table")

Answer 2

(Depending on the Postgres version) you most likely do not want to run "VACUUM FULL".

Answer 3

UnboundExecutionError says that your session is not bound to an engine and there is no way to discover engine from query passed to execute(). You can either use engine.execute() directly or pass additional mapper parameter (either mapper or mapped model corresponding to table used in query) to session.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 calling commit()? If so, just call commit() or rollback() method to close transaction before doing VACUUM. Also note, that there are several parameter to sessionmaker() that tell SQLAlchemy when transaction should be started.

Answer 4

You need to bind the session to an engine

session.bind = metadata.bind
session.execute('YOUR SQL STATEMENT')

Answer 5

Dammit. I knew the answer was going to be right under my nose. Assuming you setup your connection like I did.

metadata.bind = 'postgres://user:pw@host/db'

The solution to this was as simple as

conn = metadata.bind.engine.connect()

old_lvl = conn.connection.isolation_level
conn.connection.set_isolation_level(0)
conn.execute('vacuum analyze table')
conn.connection.set_isolation_level(old_lvl)

This is similar to what was suggested here http://stackoverflow.com/questions/1017463/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.