Pyramid and SqlAlchemy without ORM.Use of Session

Question

This is in further extension to this.

I am building project in pyramid using sqlalchemy without its ORM. I find the session feature to be nice as I wont have to commit everytime a request is over.This is how I am implementing it:

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )

from zope.sqlalchemy import ZopeTransactionExtension

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

Question:To execute only SQL statement do i need to create a session?

If yes,how can I execute sqlstatement using DBSession.

If no,how can I do the same and take care of all the functionality offered by session and zopeTransactionExtension()

Sergey Sergey · Accepted Answer · 2014-07-15T20:25:31

Yes, you do need to create a session if you want to use the "commit on success, roll back on failure" behaviour of a session configured with ZopeTransactionExtension.

def my_view(request):
    session = DBSession()

    result = session.execute("""SELECT spam, eggs FROM blah WHERE moo='foo'""")
    data = []
    for row in result:
        data.append({
           'spam': row.spam, 
           'eggs': row.eggs
        })
    return data

With SQLAlchemy it is also possible to get access to the underlying "engine" and "connection" used by the session, so you can run queries outside of a transaction block, or using a specific DB connection, but it perhaps only useful in very specific circumstances.

I don't think there's any measurable overhead in just using session.execute() everywhere.

Pyramid and SqlAlchemy without ORM.Use of Session

1 Answers