如何使用sqlalchemy构造SQL语句

sqlalchemy作为ORM层工具可以方便地编程式操作数据库, 但有时与外部系统交互时需要生成raw sql语句, 利用sqlalchemy可能方便地动态构造成sql语句.

假设我们有两个类代表User, Address两张表.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)


class Address(Base):
    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", lazy=False)

利用Query构造生需要的语句, 使用compile^[1]输出query代表的sql语句.

from sqlalchemy.orm import Query

filters = {'user_id': 1}
query = Query(Address).filter_by(**filters)
query = query.statement.compile(compile_kwargs={'literal_binds': True})
print query

输出

1
2
3

SELECT addresses.id, addresses.email_address, addresses.user_id, users_1.id, users_1.name, users_1.fullname, users_1.password
FROM addresses LEFT OUTER JOIN users AS users_1 ON users_1.id = addresses.user_id 
WHERE addresses.user_id = 1

注意输出中包含了Address.user字段代表的join操作, 如果不需要这些字段可以lazyload^[2]取消.

from sqlalchemy.orm import lazyload

filters = {'user_id': 1}
query = Query(Address).filter_by(**filters)
query = query.options(lazyload('*'))
query = query.statement.compile(compile_kwargs={'literal_binds': True})
print query

输出

1
2
3

SELECT addresses.id, addresses.email_address, addresses.user_id
FROM addresses 
WHERE addresses.user_id = 1