union_all, literal_column

SQL에는 스키마가 동일한 두 테이블을 합쳐 쿼리하는 UNION과, SELECT 절에 리터럴한 expression을 두는 Literal Select라는 개념이 존재한다.

	SELECT 'app' AS origin, id, name, created_at FROM app_users
	UNION ALL
	SELECT 'web', id, name, created_at FROM web_users
	ORDER BY created_at DESC;

view raw .sql hosted with ❤ by GitHub

SQLAlchemy에서는 Query 객체의 union(*q), union_all(*q)로 각각 UNION/UNION ALL을, sqlalchemy.sql.expression.literal_column 함수를 통해 literal select를 표현할 수 있다. 위에서 예를 든 union 쿼리를 SQLAlchemy로 표현해 보자.

	from sqlalchemy import Column, DateTime, String
	from sqlalchemy import create_engine, declarative_base, sessionmaker
	from sqlalchemy import literal_column
	Base = declarative_base()
	class TblAppUsers(Base):
	__tablename__ = 'app_users'
	id = Column(String(64))
	name = Column(String(64))
	created_at = Column(DateTime)
	class TblWebUsers(Base):
	__tablename__ = 'web_users'
	id = Column(String(64))
	name = Column(String(64))
	created_at = Column(DateTime)
	engine = create_engine('...')
	Session = sessionmaker(bind=engine)
	session = Session()
	app_user_select_query = session.query(TblAppUsers.id, TblAppUsers.name, TblAppUsers.created_at, literal_column('app'))
	web_user_select_query = session.query(TblWebUsers.id, TblWebUsers.name, TblWebUsers.created_at, literal_column('web'))
	query = app_user_select_query.union_all(web_user_select_query).order_by('created_at')
	result = query.all()

view raw .py hosted with ❤ by GitHub