SqlAlchemy: dynamic queries

How to make dynamic queries in SqlAlchemy ORM (if it is a correct name for them).

I used SqlAlchemy as abstraction for database, with queries in python code, but what if I need to generate these queries dynamically, not only set the parameters of query like "id"?

For example, I need to gen开发者_开发技巧erate query from list (table names, column names, joined columns) that links three tables like "organisation", "people", "staff". How can I do it properly?

For example, i meant this list: [{'table':'organisation', 'column':'staff_id'}, {'table':'staff', 'column':'id'}]

And output for example may contain: organisation.id, organisation.name, organisation.staff_id, staff.id, staff.name (name column is presented only in output, because I need simple example, recieving all columns of tables, and array must just set joins)

---

You can use mapper on the result of a call to sqlalchemy.sql.join and/or sqlalchemy.select. This is roughly equivalent to using mapper on a database view; you can query against such classes naturally, but not necessarily create new records. You can also use sqlalchemy.orm.column_property to map computed values to object attributes. As I read your question, a combination of these three techniques should meet your needs.

---

Haven't tested, but it with the SQLAlchemy ORM, you can link tables together like:

from sqlalchemy import create_engine, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey
from sqlalchemy.orm import relationship
from asgportal.database import Session

Engine = create_engine('mysql+mysqldb://user:password@localhost:3306/mydatabase', pool_recycle=3600)
Base = declarative_base(bind=Engine)
session = Session()
session.configure(bind=Engine)

class DBOrganization(Base):
    __tablename__ = 'table_organization'
    id = Column(Integer(), primary_key=True)
    name = Column(ASGType.sa(ASGType.STRING))

class DBEmployee(Base):
    __tablename__ = 'table_employee'
    id = Column(Integer(), primary_key=True)
    name = Column(String(255))

    organization_id = Column(Integer(), ForeignKey('table_organization.id'))
    # backref below will be an array[] unless you specify uselist=False
    organization = relationship(DBOrganization, backref='employees')

Base.metadata.create_all()

# From here, you can query:
rs = session.query(DBEmployee).join(DBEmployee.organization).filter(DBOrganization.name=='my organization')

for employees in rs:
    print '{0} works for {1}'.format(employees.name,employees.organization.name)