Raw SQL within Pylons app that uses SQLAlchemy?

Question

I've inherited a Pylons app that uses SQLAlchemy. I know nothing about SQLAlchemy and very little about Pylons :)

I need to run some raw SQL from within the app. The SQLAlchemy currently seems to be working in the following way (example code):

import myapp.model as model
model.Session.query(model.KeyValue) # existing code
            .join(model.Key)
            .filter(model.Key.name == name)
            ).count() == 0, name

How do I get it to run raw SQL? I see that I need an execute() statement, but how exactly do I run it? The following both fail:

model.Session.execute('create table hello_world;')
model.Connection.execute("""
create table hello_world;
""")

What's the magic invocation? There's no reference to a Connection object in the existing code, and I'm not sure how to create one.

Answer 1

My first impulse is to recommend trying the execute() method of an instance of Connection, instead of the execute() method of the class itself as your example code suggests that you're doing.

Are you working off of the Pylons Book examples ?

Answer 2

You can obtain connection that is used by Session by using its connection method:

connection = model.Session.connection()

Then you can issue your query:

connection.execute('create table hello_world;')

Note that in Pylons model.Session is not a sqlalchemy.orm.session.Session class. It's an instance of sqlalchemy.orm.scoping.ScopedSession. That's how it's created in model.meta module:

Session = scoped_session(sessionmaker())