在开发过程中，经常会遇到几张表结构相同，仅仅表名不一样。这在直接使用SQL语句进行查询的环境中处理起来很简单，但如果使用了SQLAlchemy ORM之后，因在model定义时就确定了表名，就需要用其他方法进行表名的变更。
假定数据库中有两张表：user，user_1，下面用一个简单程序展示如何在查询时变更表名。
1. 直接使用mapper函数建立映射时的变更
from sqlalchemy import create_engine
engine = create_engine('mysql://root:@127.0.0.1/test?charset=utf8', echo=True)
from sqlalchemy import Table, Column, Integer, String, MetaData
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String),
Column('password', String)
)
class User(object):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
# 查询表1
from sqlalchemy.orm import mapper, clear_mappers
mapper(User, users_table)
user = session.query(User).filter(User.name=='myname').all()
# 生成SQL语句：
# SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
# FROM users
# WHERE users.name = %s
# 变更表名，查询表2
users_table.name = 'users_1'
User1Class = type('User', (User,), {})
mapper(User1Class, users_table)
user2 = session.query(User1Class).filter(User1Class.name=='myname').all()
# 生成SQL语句：
# SELECT users_1.id AS users_id, users_1.name AS users_name, users_1.fullname AS users_fullname, users_1.password AS users_password
# FROM users_1
# WHERE users_1.name = %s
2. 使用declarative_base定义的model
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker
metadata = MetaData()
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'
}
id = Column(Integer, primary_key=True)
name = Column(String(30))
fullname = Column(String(120))
password = Column(String(30))
engine = create_engine('mysql://root:@127.0.0.1/test?charset=utf8', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
# 查询表1
user = session.query(User).filter(User.name=='myname').all()
# 生成SQL：
# SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
# FROM users
# WHERE users.name = %s
# 查询表2
User.__table__.name = 'users_1'
user = session.query(User).filter(User.name=='myname').all()
# 生成SQL：
# SELECT users_1.id AS users_id, users_1.name AS users_name, users_1.fullname AS users_fullname, users_1.password AS users_password
# FROM users_1
# WHERE users_1.name = %s
Posts
