第1章 Pymysql模块
1.1 Pymysql模块介绍
pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。
1.2 安装Pymysql
1.2.1 命令行安装
pip3 install pymysql
1.2.2 PyCharm安装
1.3 使用方法
1.3.1 连接数据库
【语法】:
import pymysql db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接 cursor.execute("SELECT VERSION()") # 使用 execute() 方法执行 SQL 查询 data = cursor.fetchone() # 使用 fetchone() 方法获取单条数据 print ("Database version : %s " % data) db.close() # 关闭数据库连接
【更多参数】:
import pymysql conn = pymysql.connect( host='localhost', user='root', password="root", database='db', port=3306, charset='utf-8', ) cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # 获取字典类型的查询结果
1.3.2 数据库操作
【示例】:创建表
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL,如果表存在则删除 cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # 使用预处理语句创建表 sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql) # 关闭数据库连接 db.close()
【示例】:插入操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句:单条插入 sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" # SQL 插入语句:多条插入 sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES (%s, %s, %s, %s, %s )" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: cursor.execute(sql) # 执行sql语句 db.commit() # 提交到数据库执行 except: db.rollback() # 如果发生错误则回滚 # 关闭数据库连接 db.close()
【示例】:查询操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: cursor.execute(sql) # 执行SQL语句 results = cursor.fetchall() # 获取所有记录列表 for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income )) except: print ("Error: unable to fetch data") # 关闭数据库连接 db.close()
【解析】:
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
- fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
- fetchall(): 接收全部的返回结果行.
- rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数
【示例】:更新操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 更新语句 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: cursor.execute(sql) # 执行SQL语句 db.commit() # 提交到数据库执行 except db.rollback() # 发生错误时回滚 # 关闭数据库连接 db.close()
【示例】:删除操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try cursor.execute(sql) # 执行SQL语句 db.commit() # 提交修改 except db.rollback() # 发生错误时回滚 # 关闭连接 db.close()
1.3.3 执行SQL语句示例
【示例】:
#!/usr/bin/env python # !-*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test') # 创建游标 cursor = conn.cursor() # 执行SQL,并返回收影响行数 effect_row_insert = cursor.executemany("insert into in1(nid,name,email,extra)VALUES(%s,%s,%s,%s)",[(1,"老大","[email protected]","abc"),(2,"老二","[email protected]","def"),(3,"老三","[email protected]","ghi")]) effect_row_select = cursor.execute("select * from in1") # 提交,不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() # 获取最新自增ID new_id = cursor.lastrowid print(effect_row_insert,effect_row_select) print(new_id)
【运行结果】:
# PyChram返回结果 3 3 3 # 数据库查询结果 mysql> select * from in1; +-----+--------+-----------------+-------+ | nid | name | email | extra | +-----+--------+-----------------+-------+ | 1 | 老大 | [email protected] | abc | | 2 | 老二 | [email protected] | def | | 3 | 老三 | [email protected] | ghi | +-----+--------+-----------------+-------+ 3 rows in set (0.00 sec)
1.3.4 获取查询结果示例
【示例】:获取元组类型结果
#!/usr/bin/env python # !-*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test') cursor = conn.cursor() cursor.execute("select * from in1") # 获取第一行数据 row_1 = cursor.fetchone() # 获取前n行数据 row_2 = cursor.fetchmany(2) # 获取所有数据 row_3 = cursor.fetchall() conn.commit() cursor.close() conn.close() print(row_1) print(row_2) print(row_3)
【运行结果】:
(1, '老大', '[email protected]', 'abc') ((2, '老二', '[email protected]', 'def'), (3, '老三', '[email protected]', 'ghi')) ((4, '老四', '[email protected]', 'jkl'), (5, '老五', '[email protected]', 'mno'))
Tips:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
【示例】:获取字典类型结果
import pymysql conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("select * from in1") row_1 = cursor.fetchall() conn.commit() cursor.close() conn.close() print(row_1)
【运行结果】:
[{'nid': 1, 'name': '老大', 'email': '[email protected]', 'extra': 'abc'}, {'nid': 2, 'name': '老二', 'email': '[email protected]', 'extra': 'def'}, {'nid': 3, 'name': '老三', 'email': '[email protected]', 'extra': 'ghi'}, {'nid': 4, 'name': '老四', 'email': '[email protected]', 'extra': 'jkl'}, {'nid': 5, 'name': '老五', 'email': '[email protected]', 'extra': 'mno'}]
Tips:字典类型的查询结果同时会显示表的列名,方便后面程序的调用。
第2章 ORM框架之SQLAlchemy
2.1 SQLAlchemy介绍
SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[ <options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[ key=value&key=value...]
Tips:更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html
2.2 安装SQLAchemy
2.2.1 命令行安装
pip3 install SQLAlchemy
2.2.2 PyCharm安装
2.3 内部处理使用方法
使用 Engine/ConnectionPooling/Dialect 进行数据库操作,Engine使用ConnectionPooling连接数据库,然后再通过Dialect执行SQL语句。
【示例】:
#!/usr/bin/env python # !-*- coding:utf-8 -*- from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:root@localhost:3306/test", max_overflow=5) # 执行SQL cur = engine.execute("insert into in1 (nid,name,email,extra) VALUES (6,'老六','[email protected]','pqr')") cur = engine.execute("insert into in1(nid,name,email,extra)VALUES(%s,%s,%s,%s)", [(7,"老七","[email protected]","stu"), (8,"老八","[email protected]","vwx")]) cur = engine.execute("insert into in1(nid,name,email,extra)VALUES(%(nid)s,%(name)s,%(email)s,%(extra)s)", nid='9',name='老九',email='[email protected]',extra='yz') # 新插入行自增ID new_id = cur.lastrowid cur = engine.execute('select * from in1') # 获取数据 row_1 = cur.fetchone() row_2 = cur.fetchmany(3) row_3 = cur.fetchall() print(new_id) print(row_1) print(row_2) print(row_3)
【执行结果】:
9 (1, '老大', '[email protected]', 'abc') [(2, '老二', '[email protected]', 'def'), (3, '老三', '[email protected]', 'ghi'), (4, '老四', '[email protected]', 'jkl')] [(5, '老五', '[email protected]', 'mno'), (6, '老六', '[email protected]', 'pqr'), (7, '老七', '[email protected]', 'stu'), (8, '老八', '[email protected]', 'vwx'), (9, '老九', '[email protected]', 'yz')]
2.4 ORM功能使用
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象,对象转换成SQL,执行SQL。
2.4.1 创建表结构
【示例】:
#!/usr/bin/env python # !-*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine # 固定格式 Base = declarative_base() ################ 创建表结构 ############### # 创建单表 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) def __repr__(self): return "%s-%s" %(self.id, self.name) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) # 与生成表结构无关,仅用于查询方便 favor = relationship("Favor", backref='pers') # 多对多 class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) group = relationship("Group", backref='s2g') server = relationship("Server", backref='s2g') class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) # group = relationship('Group',secondary=ServerToGroup,backref='host_list') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine)
Tips:在面向对象编程中,类相当于数据库的“表”;对象相当于“表中的行”。
2.4.2 数据库操作
【示例】:
################ 创建连接数据库 ############### # echo=True打印输出执行原始SQL的语句 engine = create_engine("mysql+pymysql://root:root@localhost:3306/test", max_overflow=5 ,echo=True) Base.metadata.create_all(engine) # 执行此句后创建表结构 Session = sessionmaker(bind=engine) session = Session() ################ 数据库操作-增 ############### obj = Users(name="leonshadow", extra='abc') session.add(obj) session.add_all([ Users(name="leon", extra='def'), Users(name="shadow", extra='ghi'), ]) session.commit() ################ 数据库操作-查 ############### ret_1 = session.query(Users).all() ret_2 = session.query(Users.name, Users.extra).all() ret_3 = session.query(Users).filter_by(name='leon').all() ret_4 = session.query(Users).filter_by(name='leon').first() # ret_5 = session.query(Users).filter(text("id<:value and name=:name")).params(value=2, name='leon').order_by(User.id).all() # ret_6 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='leon').all() print(ret_1) print(ret_2) print(ret_3) print(ret_4)
【运行结果】:
[2-leon, 1-leonshadow, 3-shadow] [('leon', 'def'), ('leonshadow', 'abc'), ('shadow', 'ghi')] [2-leon] 2-leon
【示例】:
################ 数据库操作-改 ############### session.query(Users).filter(Users.id > 2).update({"name" : "666"}) # UPDATE users SET name=%(name)s WHERE users.id > %(id_1)s # {'name': '666', 'id_1': 2} session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "888"}, synchronize_session=False) # UPDATE users SET name=(concat(users.name, %(name_1)s)) WHERE users.id > %(id_1)s # {'name_1': '888', 'id_1': 2} # session.query(Users).filter(Users.id > 2).update({"extra": Users.extra + 1}, synchronize_session="evaluate") # 以数字方式相加 # UPDATE users SET extra=(users.extra + %(extra_1)s) WHERE users.id > %(id_1)s # {'extra_1': 1, 'id_1': 2} session.commit() ################ 数据库操作-查 ############### ret_1 = session.query(Users).all() # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # {} ret_2 = session.query(Users.name, Users.extra).all() # SELECT users.name AS users_name, users.extra AS users_extra # FROM users # {} ret_3 = session.query(Users).filter_by(name='leon').all() # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.name = %(name_1)s # {'name_1': 'leon'} ret_4 = session.query(Users).filter_by(name='leon').first() # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.name = %(name_1)s # LIMIT %(param_1)s # {'name_1': 'leon', 'param_1': 1} print(ret_1) print(ret_2) print(ret_3) print(ret_4)
【运行结果】:
[3-666888, 2-leon, 1-leonshadow] [('666888', 'ghi'), ('leon', 'def'), ('leonshadow', 'abc')] [2-leon] 2-leon
【示例】:
################ 数据库操作-删 ############### session.query(Users).filter(Users.id > 2).delete() # DELETE FROM users WHERE users.id > %(id_1)s # {'id_1': 2} session.commit() ret_1 = session.query(Users).all() print(ret_1)
【运行结果】:
[2-leon, 1-leonshadow]
2.4.3 其他查询操作
【示例】:条件查询
# 条件查询,filter内部默认为and条件 ret = session.query(Users).filter_by(name='leon').all() print(session.query(Users).filter_by(name='leon')) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.name = %(name_1)s # [2-leon, 5-leon] ret = session.query(Users).filter(Users.id > 1, Users.name == 'leon').all() print(session.query(Users).filter(Users.id > 1, Users.name == 'leon')) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id > %(id_1)s AND users.name = %(name_1)s # [2-leon, 5-leon] ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'leon').all() print(session.query(Users).filter(Users.id.between(1, 3), Users.name == 'leon')) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id BETWEEN %(id_1)s AND %(id_2)s AND users.name = %(name_1)s # [2-leon] ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() print(session.query(Users).filter(Users.id.in_([1,3,4]))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id IN (%(id_1)s, %(id_2)s, %(id_3)s) # [1-leonshadow, 4-leonshadow] ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() print(session.query(Users).filter(~Users.id.in_([1,3,4]))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id NOT IN (%(id_1)s, %(id_2)s, %(id_3)s) # [2-leon, 5-leon, 6-shadow] ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='leon'))).all() print(session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='leon')))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id IN (SELECT users.id AS users_id # FROM users # WHERE users.name = %(name_1)s) # [2-leon, 5-leon] # 使用and或or查询 from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 2, Users.name == 'leon')).all() print(session.query(Users).filter(and_(Users.id > 2, Users.name == 'leon'))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id > %(id_1)s AND users.name = %(name_1)s # [5-leon] from sqlalchemy import and_, or_ ret = session.query(Users).filter(or_(Users.id < 4, Users.name == 'leon')).all() print(session.query(Users).filter(or_(Users.id < 4, Users.name == 'leon'))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id < %(id_1)s OR users.name = %(name_1)s # [2-leon, 5-leon, 1-leonshadow] from sqlalchemy import and_, or_ ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'leon', Users.id > 3), Users.extra != "" )).all() print(session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'leon', Users.id > 3), Users.extra != "" ))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.id < %(id_1)s OR users.name = %(name_1)s AND users.id > %(id_2)s OR users.extra != %(extra_1)s # [2-leon, 5-leon, 1-leonshadow, 4-leonshadow, 6-shadow]
【示例】:通配符查询
ret = session.query(Users).filter(Users.name.like('l%')).all() print(session.query(Users).filter(Users.name.like('l%'))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.name LIKE %(name_1)s # [2-leon, 5-leon, 1-leonshadow, 4-leonshadow] ret = session.query(Users).filter(~Users.name.like('l%')).all() print(session.query(Users).filter(~Users.name.like('l%'))) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # WHERE users.name NOT LIKE %(name_1)s # [6-shadow]
【示例】:限制查询
ret = session.query(Users)[1:2] print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users # LIMIT %(param_1)s, %(param_2)s # {'param_1': 1, 'param_2': 1} # [5-leon]
【示例】:排序查询
ret = session.query(Users).order_by(Users.name.desc()).all() print(session.query(Users).order_by(Users.name.desc())) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users ORDER BY users.name DESC # [6-shadow, 4-leonshadow, 1-leonshadow, 5-leon, 2-leon] ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() print(session.query(Users).order_by(Users.name.desc(), Users.id.asc())) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users ORDER BY users.name DESC, users.id ASC # [6-shadow, 1-leonshadow, 4-leonshadow, 2-leon, 5-leon]
【示例】:分组查询
ret = session.query(Users).group_by(Users.extra).all() print(session.query(Users).group_by(Users.extra)) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra # FROM users GROUP BY users.extra # [1-leonshadow, 2-leon, 6-shadow] from sqlalchemy.sql import func ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() print(session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name)) print(ret) # SELECT max(users.id) AS max_1, sum(users.id) AS sum_1, min(users.id) AS min_1 # FROM users GROUP BY users.name # [(5, Decimal('7'), 2), (4, Decimal('5'), 1), (6, Decimal('6'), 6)] from sqlalchemy.sql import func ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() print(session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2)) print(ret) # SELECT max(users.id) AS max_1, sum(users.id) AS sum_1, min(users.id) AS min_1 # FROM users GROUP BY users.name # HAVING min(users.id) > %(min_2)s # [(6, Decimal('6'), 6)]
【示例】:连表操作
ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() print(session.query(Users, Favor).filter(Users.id == Favor.nid)) print(ret) # SELECT users.id AS users_id, users.name AS users_name, users.extra AS users_extra, favor.nid AS favor_nid, favor.caption AS favor_caption # FROM users, favor # WHERE users.id = favor.nid # [] ret = session.query(Person).join(Favor).all() print(session.query(Person).join(Favor)) print(ret) # SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id # FROM person INNER JOIN favor ON favor.nid = person.favor_id # [] ret = session.query(Person).join(Favor, isouter=True).all() print(session.query(Person).join(Favor, isouter=True)) print(ret) # SELECT person.nid AS person_nid, person.name AS person_name, person.favor_id AS person_favor_id # FROM person LEFT OUTER JOIN favor ON favor.nid = person.favor_id # []
【示例】:组合查询
q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() print(q1.union(q2)) print(ret) # SELECT anon_1.users_name AS anon_1_users_name # FROM (SELECT users.name AS users_name # FROM users # WHERE users.id > %(id_1)s UNION SELECT favor.caption AS favor_caption # FROM favor # WHERE favor.nid < %(nid_1)s) AS anon_1 # [('leonshadow',), ('leon',), ('shadow',)] q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all() print(q1.union_all(q2)) print(ret) # SELECT anon_1.users_name AS anon_1_users_name # FROM (SELECT users.name AS users_name # FROM users # WHERE users.id > %(id_1)s UNION ALL SELECT favor.caption AS favor_caption # FROM favor # WHERE favor.nid < %(nid_1)s) AS anon_1 # [('leonshadow',), ('leon',), ('shadow',)]
