How to query with raw SQL using Session or engine

Question

With Parent and Child tables:

from sqlalchemy import Column, ForeignKey, String, create_engine, desc, asc
from sqlalchemy.ext.declarative import declarative_base
import uuid

Base = declarative_base()
class Parent(Base):
    __tablename__ = 'parents'
    uuid = Column(String(64), primary_key=True, unique=True)
    def __init__(self):  
        self.uuid = uuid.uuid4()   

class Child(Base):
    __tablename__ = 'children'
    uuid = Column(String(64), primary_key=True, unique=True)
    parent_uuid = Column(String(64), ForeignKey('parents.uuid'))
    def __init__(self, parent_uuid=None):  
        self.uuid = uuid.uuid4()   
        self.parent_uuid = parent_uuid

I can go ahead and create a Parent entity:

engine = create_engine('mysql://root:pass@localhost/dbname', echo=False)
session = scoped_session(sessionmaker()) 
session.remove()
session.configure(bind=engine, autoflush=False, expire_on_commit=False)

parent = Parent()
session.add(parent)
session.commit()
session.close()

The resulting parent variable is a regular Python ORM object.

If I would query a database instead of creating one the result of query would be a list of ORM objects:

result = session.query(Parent).order_by(desc(Parent.uuid)).all()

But there are times when we need to query database using a raw Sql command. Is there a way to run a raw SQL command using session object so to ensure that the resulting query return is a ORM object or a list of objects?

Answer 1

You can use the execute() method of Session:

session.execute('select * from table')

The execute method's documentation can be found here:

http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.execute

Please note this does not protect against SQL Injection.