在最近的工作中遇到一个问题，要将两个字段相似的表里的数据统一起来展示在一个统计页面中。如果是单纯的展示数据那很简单，两个表查出来之后组合一下就完事了，但是有坑的地方就是分页和按照时间搜索，这两个功能决定了不可能单独查询两张表。在同事的建议下，使用了union的联合查询，最终完成这个功能。做一个简单的demo，记录下这个功能。
数据库和sqlalchemy安装请参考另一篇文章 sqlalchemy数据库查询小集合
定义数据表
定义 两张表，字段类型相同，但名称不同。
#coding:utf-8 from sqlalchemy import Column,CHAR,INTEGER from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker Base = declarative_base() class User(Base): __tablename__ = "user" id = Column(CHAR(20),primary_key = True) name = Column(CHAR(20)) age = Column(INTEGER) class Teacher(Base): __tablename__ = "teacher" id = Column(CHAR(20),primary_key = True) tec_name = Column(CHAR(20)) tec_age = Column(INTEGER) engine = create_engine('mysql+mysqldb://root:12345678@localhost:3306/test') def create_table(table_name): table_name.metadata.create_all(engine) print "创建成功" def insert_data(): DBSession = sessionmaker(bind=engine) session = DBSession() for x in range(10): temp = {} temp['id'] = x temp['name'] = 'user_' + str(x) temp['age'] = x user = User(**temp) session.add(user) for x in range(15): temp = {} temp['id'] = x temp['tec_name'] = 'tec_' + str(x) temp['tec_age'] = x * 2 tec = Teacher(**temp) session.add(tec) session.commit() session.close() print 'success' if __name__ = '__main__': create_table(User) create_table(Teacher) insert_data()
User表字段：
mysql> desc user; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | char(20) | NO | PRI | NULL | | | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
User表数据：
mysql> select * from user; +----+--------+------+ | id | name | age | +----+--------+------+ | 0 | user_0 | 0 | | 1 | user_1 | 1 | | 2 | user_2 | 2 | | 3 | user_3 | 3 | | 4 | user_4 | 4 | | 5 | user_5 | 5 | | 6 | user_6 | 6 | | 7 | user_7 | 7 | | 8 | user_8 | 8 | | 9 | user_9 | 9 | +----+--------+------+ 10 rows in set (0.00 sec)
Teacher表字段：
mysql> desc teacher; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | id | char(20) | NO | PRI | NULL | | | tec_name | char(20) | YES | | NULL | | | tec_age | int(11) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
teacher表数据：
mysql> select * from teacher; +----+----------+---------+ | id | tec_name | tec_age | +----+----------+---------+ | 0 | tec_0 | 0 | | 1 | tec_1 | 2 | | 10 | tec_10 | 20 | | 11 | tec_11 | 22 | | 12 | tec_12 | 24 | | 13 | tec_13 | 26 | | 14 | tec_14 | 28 | | 2 | tec_2 | 4 | | 3 | tec_3 | 6 | | 4 | tec_4 | 8 | | 5 | tec_5 | 10 | | 6 | tec_6 | 12 | | 7 | tec_7 | 14 | | 8 | tec_8 | 16 | | 9 | tec_9 | 18 | +----+----------+---------+ 15 rows in set (0.00 sec)
查询
首先做一个简单的查询，将两个表的数据分别查出来
def select(): DBSession = sessionmaker(bind=engine) session = DBSession() table_data = session.query(User).all() session.close() for x in table_data: print x.name,'------>',x.age table_data = session.query(Teacher).all() session.close() for x in table_data: print x.tec_name,'------>',x.tec_age
查询结果：
Desktop � python union_one.py /home/ljk/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2514: Warning: '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead cursor.execute('SELECT @@tx_isolation') user_0 ------> 0 user_1 ------> 1 user_2 ------> 2 user_3 ------> 3 user_4 ------> 4 user_5 ------> 5 user_6 ------> 6 user_7 ------> 7 user_8 ------> 8 user_9 ------> 9 ----------------------------------- tec_0 ------> 0 tec_1 ------> 2 tec_10 ------> 20 tec_11 ------> 22 tec_12 ------> 24 tec_13 ------> 26 tec_14 ------> 28 tec_2 ------> 4 tec_3 ------> 6 tec_4 ------> 8 tec_5 ------> 10 tec_6 ------> 12 tec_7 ------> 14 tec_8 ------> 16 tec_9 ------> 18
union查询
union 查询的关键字是 union ，首先将第一张表的数据全部查询出来，然后将第二张表的数据全部查询出来，最后将两个数据使用union联合成一张新表，这张新表可以再次被筛选过滤，分页等。
def select(): DBSession = sessionmaker(bind=engine) session = DBSession() table_data = session.query(User).all() session.close() # for x in table_data: # print x.name,'------>',x.age # table_data = session.query(Teacher).all() # session.close() # for x in table_data: # print x.tec_name,'------>',x.tec_age user_data = session.query(User.name,User.age) tec_data = session.query(Teacher.tec_name.label('name'), Teacher.tec_age.label('age')) result = user_data.union_all(tec_data) for x in result: print x.name,'------>',x.age
在上面的查询中需要有一个注意点就是label，可以看到tec_data的查询语句中使用了label这个属性，该属性的作用是将Teacher这张表查询出来的tec_name 字段名称变成name，已达到和User表字段的统一，只有两张表的字段名称一致，类型一致的情况下才能联合查询。
另外还使用了一个union_all 字段，该字段的意思是如果两张表存在相同的记录也要全部展示出来，想要让相同的记录合并起来使用union即可
查询结果如下：
Desktop � python union_one.py /home/ljk/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2514: Warning: '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead cursor.execute('SELECT @@tx_isolation') user_0 ------> 0 user_1 ------> 1 user_2 ------> 2 user_3 ------> 3 user_4 ------> 4 user_5 ------> 5 user_6 ------> 6 user_7 ------> 7 user_8 ------> 8 user_9 ------> 9 tec_0 ------> 0 tec_1 ------> 2 tec_10 ------> 20 tec_11 ------> 22 tec_12 ------> 24 tec_13 ------> 26 tec_14 ------> 28 tec_2 ------> 4 tec_3 ------> 6 tec_4 ------> 8 tec_5 ------> 10 tec_6 ------> 12 tec_7 ------> 14 tec_8 ------> 16 tec_9 ------> 18
往往查询出来还不是最终目的，还需要对查询出来的数据过滤。查询出来的数据不是一张正真的表，如果使用字段去匹配过滤条件呢？以查询出age 大于 5为例 ，有两种过滤方式：
1.使用User.age 作为筛选条件
2.使用Teacher.age 作为筛选条件
规则就是使用两张表里任意一张表的原始字段过滤即可，该过滤条件会在联合查询出来的结果起上作用。
使用 User 表字段
def select(): DBSession = sessionmaker(bind=engine) session = DBSession() table_data = session.query(User).all() session.close() # for x in table_data: # print x.name,'------>',x.age # table_data = session.query(Teacher).all() # session.close() # for x in table_data: # print x.tec_name,'------>',x.tec_age user_data = session.query(User.name,User.age) tec_data = session.query(Teacher.tec_name.label('name'), Teacher.tec_age.label('age')) # result = user_data.union_all(tec_data) # for x in result: # print x.name,'------>',x.age result = user_data.union_all(tec_data).filter(User.age > 5) for x in result: print x.name,'------>',x.age
Desktop � python union_one.py /home/ljk/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2514: Warning: '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead cursor.execute('SELECT @@tx_isolation') user_6 ------> 6 user_7 ------> 7 user_8 ------> 8 user_9 ------> 9 tec_10 ------> 20 tec_11 ------> 22 tec_12 ------> 24 tec_13 ------> 26 tec_14 ------> 28 tec_3 ------> 6 tec_4 ------> 8 tec_5 ------> 10 tec_6 ------> 12 tec_7 ------> 14 tec_8 ------> 16 tec_9 ------> 18
使用 Teacher 表字段
def select(): DBSession = sessionmaker(bind=engine) session = DBSession() table_data = session.query(User).all() session.close() # for x in table_data: # print x.name,'------>',x.age # table_data = session.query(Teacher).all() # session.close() # for x in table_data: # print x.tec_name,'------>',x.tec_age user_data = session.query(User.name,User.age) tec_data = session.query(Teacher.tec_name.label('name'), Teacher.tec_age.label('age')) result = user_data.union_all(tec_data).filter(Teacher.tec_age>5) for x in result: print x.name,'------>',x.age
Desktop � python union_one.py /home/ljk/.local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2514: Warning: '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead cursor.execute('SELECT @@tx_isolation') user_6 ------> 6 user_7 ------> 7 user_8 ------> 8 user_9 ------> 9 tec_10 ------> 20 tec_11 ------> 22 tec_12 ------> 24 tec_13 ------> 26 tec_14 ------> 28 tec_3 ------> 6 tec_4 ------> 8 tec_5 ------> 10 tec_6 ------> 12 tec_7 ------> 14 tec_8 ------> 16 tec_9 ------> 18
关于union联合查询有一个说法很形象：join查询就像是横向扩展，将多张表的数据横向组合在一起，而union像是纵向扩展，将多张表数据纵向排列起来