01查询结果
上节课使用query从数据库中查询到了结果,但是query返回的对象是直接可用的吗?
首先导入模块
from connect import session
from user_modules import User
query返回对象
rs = session.query(User).filter(User.username=='wu')
print(rs)
print(type(rs))
SELECT user.id AS user_id, user.username AS user_username, user.password AS user_password, user.creatime AS user_creatime
FROM user
WHERE user.username = %(username_1)s
<class 'sqlalchemy.orm.query.Query'>
根据返回结果来看, rs 是一个 Query 对象,打印出来可以看到转化的 SQL
使用 SQLAlchemy 时,如果想要查看最终在数据库中执行的 sql ,可以通过上述方式来查看
rs=session.query(User.username).filter(User.username=='wu')
print(rs)
SELECT user.username AS user_username
FROM user
WHERE user.username = %(username_1)s
整表查询 User
all
返回所有符合条件的数据
rs = session.query(User).all()
print(rs)
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=3,username=qq,password=qwe,creatime=2018-08-21 18:42:54)>, <User(id=4,username=lala,password=aaa,creatime=2018-08-21 18:42:54)>, <User(id=5,username=1wua,password=qwe123,creatime=2018-08-26 18:58:30)>, <User(id=6,username=1qqa,password=qwe,creatime=2018-08-26 18:58:30)>, <User(id=7,username=1lalaa,password=aaa,creatime=2018-08-26 18:58:30)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
session.query(User).filter(User.username=='wu').all()
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
first
返回所有符合条件数据的第一条数据
rs = session.query(User).first()
print(rs)
<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>
session.query(User).filter(User.username=='wu').first()
<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>
如果没有符合条件的数据则会返回None
session.query(User).filter(User.username=='wua').first()
None
[0]
session.query(User).filter(User.username=='wu')[0]
[0] 和 first 类似,但是如果没有符合条件的数据则会报错
session.query(User).filter(User.username=='wua')[0]
<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>
Traceback (most recent call last):
File "search_module.py", line 20, in <module>
rs = session.query(User).filter(User.username=='wua')[0]
File "/home/pyvip/.virtualenvs/py3env/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2685, in __getitem__
return list(self[item:item + 1])[0]
IndexError: list index out of range
query返回结果取值方式
rs = session.query(User).filter(User.username=='wu').all()
print(getattr(rs[0],'username'))
print(rs[0].username)
getattr(rs[0], 'username'), rs[0].username 这两种方式可以取到具体的数据值
hasattr(rs[0],'username') 判断有没有username这个属性
wu
wu
查询某个属性 User.username
all
session.query(User.username).filter(User.username=='wu').all()
session.query(User.username, User.id).all()
这里,在 query 中查询对象的某个属性值 ( 对应为查询表中某个字段的值 ),返回的结果不再是一个 Query 对象,而是一个列表
[('wu',), ('wu',)]
[('wu', 1), ('qq', 2), ('lala', 3)]
first
session.query(User.username).filter(User.username=='wu').first()
session.query(User.username, User.id).filter(User.username=='wu').first()
同理,但是 first 返回结果是一个元组
('wu',)
('wu', 1)
[0]
session.query(User.username).filter(User.username=='wu')[0]
session.query(User.username, User.id).first()
[0] 和 first 类似,但是如果没有符合条件的数据则会报错
('wu',)
('wu', 1)
返回对象
必须掌握:
不同查询方式,返回对象的区别。
如果是整表查询则返回 Module的一个实例,<class 'user_module.User'>
如果是查询某个属性,返回的则是具体的数据
返回结果取值
必须掌握:
不同的返回结果,取值方式也会有所区别,
如果是 Module 实例,需要通过属性访问的方式取值,getattr()
同时也要注意 all 和 first 返回的结果的区别
02条件查询
在实际的工作中,查询的时候会有很多的要求,通过不同的条件筛选出精准的数据,那在 SQLAlchemy中该如何进行条件查询呢?
过滤函数
filter
session.query(User).filter(User.username=='wu').all()
filter 是一个过滤函数,过滤条件都可以书写在此函数中,不同的条件之间用 逗号 分隔
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
filter_by
session.query(User).filter_by(username='wu').all()
filter_by 也是一个过滤函数,但是功能要弱一些
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
filter 和 filter_by 的区别
二者都是 过滤函数,但是使用有如下差别:
1. filter 中需要添加 类对象User,filter_by不需要
2. filter_by 中只能添加等于的条件,不能添加 不等于、大于小于等条件, filter没有这个限制
模糊查询
like 和 notlike
session.query(User.id).filter(User.username.like('w%')).all()
session.query(User).filter(User.username.like('w%')).all()
session.query(User.id).filter(User.username.notlike('w%')).all()
session.query(User).filter(User.username.notlike('w%')).all()
like 是模糊查询,和数据库中的 like 用法一样
notlike 和 like 作用相反
[(1,), (8,)]
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
[(3,), (4,), (5,), (6,), (7,)]
[<User(id=3,username=qq,password=qwe,creatime=2018-08-21 18:42:54)>, <User(id=4,username=lala,password=aaa,creatime=2018-08-21 18:42:54)>, <User(id=5,username=1wua,password=qwe123,creatime=2018-08-26 18:58:30)>, <User(id=6,username=1qqa,password=qwe,creatime=2018-08-26 18:58:30)>, <User(id=7,username=1lalaa,password=aaa,creatime=2018-08-26 18:58:30)>]
in_ 和 notin_
session.query(User.id).filter(User.username.in_(['wu', 'qq'])).all()
session.query(User).filter(User.username.in_(['wu', 'qq'])).all()
session.query(User.id).filter(User.username.notin_(['wu', 'qq'])).all()
in_ 和 notin_ 是范围查找
[(1,), (3,), (8,)]
[<User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>, <User(id=3,username=qq,password=qwe,creatime=2018-08-21 18:42:54)>, <User(id=8,username=wu,password=2,creatime=2018-08-27 09:21:05)>]
[(4,), (5,), (6,), (7,)]
is_ 和 isnot
session.query(User.id).filter(User.username.is_(None)).all()
session.query(User.id).filter(User.username.isnot(None)).all()
判断为空还可以使用:
session.query(User.id).filter(User.username==None).all()
is_ 和 isnot 精确查找
[]
[(1,), (3,), (4,), (5,), (6,), (7,), (8,)]
查询结果数
all
session.query(User).filter(User.username!='wu').all()
session.query(User.username).filter(User.username!='wu').all()
先用 all 查看所有的数据
[<User(id=3,username=qq,password=qwe,creatime=2018-08-21 18:42:54)>, <User(id=4,username=lala,password=aaa,creatime=2018-08-21 18:42:54)>, <User(id=5,username=1wua,password=qwe123,creatime=2018-08-26 18:58:30)>, <User(id=6,username=1qqa,password=qwe,creatime=2018-08-26 18:58:30)>, <User(id=7,username=1lalaa,password=aaa,creatime=2018-08-26 18:58:30)>]
[('qq',), ('lala',), ('1wua',), ('1qqa',), ('1lalaa',)]
limit
session.query(User.username).filter(User.username!='wu').limit(2).all()
limit 查看前两条数据
[('qq',), ('lala',)]
offset
session.query(User.username).filter(User.username!='wu').offset(1).all()
offset 偏移一条记录 前1条不要了
[('lala',), ('1wua',), ('1qqa',), ('1lalaa',)]
rs = session.query(User.username).filter(User.username!='wu').offset(3).all()
print(rs)
[('1qqa',), ('1lalaa',)]
slice
session.query(User.username).filter(User.username!='wu').slice(1,3).all()
slice 对查询出来的数据进行切片取值
(包前不包后 下标0开始)
[('lala',), ('1wua',)]
one
rs = session.query(User.username).filter(User.username == 'qq').one()
print(rs)
('qq',)
one 查询一条数据,如果存在多条则报错
排序
导入
from sqlalchemy import desc
order_by
session.query(User.username,User.id).filter(User.username!='wu').order_by(User.id).all()
order_by 对查询出来的结果进行排序,默认是顺序
[('qq', 3), ('lala', 4), ('1wua', 5), ('1qqa', 6), ('1lalaa', 7)]
desc
session.query(User.username).filter(User.username!='wu').order_by(desc(User.username)).all()
session.query(User.username,User.id).filter(User.username!='wu').order_by(desc(User.id)).all()
desc 是倒序排序
[('qq',), ('lala',), ('1wua',), ('1qqa',), ('1lalaa',)]
[('1lalaa', 7), ('1qqa', 6), ('1wua', 5), ('lala', 4), ('qq', 3)]
order_by 和 limit
session.query(User.username).filter(User.username!='wu').order_by(User.username).limit(3).all()
order_by 和 limit 一起使用的时候,可以通过如上方式
[('1lalaa',), ('1qqa',), ('1wua',)]
#查询用户名不是wu的 并按照姓名升序排序 显示前3个
select user.username
from user
where user.username!='wu'
order by user.username
limit 3
函数
导入
from sqlalchemy import func,extract
func.count
session.query(User.password,func.count(User.id)).group_by(User.password).all()
session.query(Usertest.username,func.count('*')).group_by(Usertest.username).all()
使用函数时,需要导入 func, group_by 和 order_by 一样,是可以直接使用的,不需要导入
[('1', 1), ('2', 1), ('aaa', 2), ('qwe', 2), ('qwe123', 1)]
#查询密码出现的次数
select user.password,count(user.id)
from user
group by user.password
session.query(User.password, func.count(User.id)).group_by(User.password).having(func.count(User.id)>1).all()
having 也可以直接使用,使用方法也和 SQL 中使用类似
[('aaa', 2), ('qwe', 2)]
#查询出现次数超过1次的密码
select user.password,count(user.id)
from user
group by user.password
having count(user.id)>1
聚合函数
func.sum
session.query(User.password, func.sum(User.id)).group_by(User.password).all()
sum 求和
[('1', Decimal('1')), ('2', Decimal('8')), ('aaa', Decimal('11')), ('qwe', Decimal('9')), ('qwe123', Decimal('5'))]
# 查询使用同一个密码的id之和
select user.password,sum(user.id)
from user
group by user.password
+----------+--------------+
| 1 | 1 |
| 2 | 8 |
| aaa | 11 |
| qwe | 9 |
| qwe123 | 5 |
+----------+--------------+
decimal在数据库中存储精确的数值
func.max
session.query(User.password, func.max(User.id)).group_by(User.password).all()
max 求最大值
[('1', 1), ('2', 8), ('aaa', 7), ('qwe', 6), ('qwe123', 5)]
func.min
ession.query(User.password, func.min(User.id)).group_by(User.password).all()
min 求最小值
[('1', 1), ('2', 8), ('aaa', 4), ('qwe', 3), ('qwe123', 5)]
extract
session.query(extract('minute',Usertest.creatime).label('minute')).all()
session.query(extract('minute',User.creatime).label('minute'), func.count(User.id)).group_by('minute').all()
extract 提取对象中的数据,这里提取分钟,并把提取出来的结果用 label 命名别名,之后就可以使用 group_by 来分组
[(21,), (32, 1), (42,), (42,), (58,),(58,),(58,)]
[(21, 1), (32, 1), (42, 2), (58, 3)]
session.query(extract('day',User.creatime).label('day'), func.count('*')).group_by('day').all()
count 里面同样可以使用 *
这里只是给出一个演示实例,如果在今后需要使用其他的函数,导入即可
选择条件
导入
from sqlalchemy import or_
or_
session.query(User.username).filter(or_(User.username.isnot(None), User.password=='qwe123')).all()
session.query(Usertest.id,Usertest.username,Usertest.password).filter(or_(Usertest.username=='wu',Usertest.password=='123')).all()
---------------------------------------------------------------------------------------
[('wu',), ('qq',), ('lala',), ('1wua',), ('1qqa',), ('1lalaa',), ('wu',)]
[(1, 'wu', '123'), (3, 'e', '123'), (5, 'wu', '555')]
03多表查询
在刚才演示了查询中的常用方法,但是都是一张表去查询,实际工作中经常会要多个表来查询,这种情况改怎么做呢?
多表查询
新建 Module
from sqlalchemy import ForeignKey
class UserDetails(Base):
__tablename__='user_details'
id = Column(Integer, primary_key=True, autoincrement=True)
id_card = Column(Integer,nullable=True,unique=True)
lost_login = Column(DateTime)
login_num = Column(Integer,default=0)
user_id = Column(Integer,ForeignKey('user.id'))
def __repr__(self):
return '<UserDetails(id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s)>'% (
self.id,
self.id_card,
self.lost_login,
self.login_num,
self.user_id
)
user_module.py
from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from connect import Base
# 实体类User,对应数据库中的user表
class User(Base):
# 表的名字:
__tablename__ = 'user'
# 表的结构:
id = Column(Integer, primary_key=True, autoincrement=True) # 主键 自增
username = Column(String(20), nullable=False) # 不为空
password = Column(String(50))
creatime = Column(DateTime, default=datetime.now)
def __repr__(self):
return "<User(id=%s,username=%s,password=%s,creatime=%s)>" % (
self.id,
self.username,
self.password,
self.creatime
)
class UserDetails(Base):
__tablename__ = 'user_details'
id = Column(Integer, primary_key=True, autoincrement=True)
id_card = Column(Integer, nullable=True, unique=True)
lost_login = Column(DateTime)
login_num = Column(Integer, default=0)
user_id = Column(Integer, ForeignKey('user.id'))
def __repr__(self):
return '<UserDetails(id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s)>' % (
self.id,
self.id_card,
self.lost_login,
self.login_num,
self.user_id
)
if __name__ == '__main__':
# 找到BaseModel的所有子类,并在数据库中建立这些表
Base.metadata.create_all()
导入
from user_modules import UserDetails
内连接cross join ,join , inner join
把两个表乘起来 第一张表的每个值与第二张表的每个值进行匹配
join ,inner join, cross join在MySQL中三者意义一样
无条件内连接:
交叉连接/笛卡尔连接
第一张表种的每一向会和另一张表的每一项依次组合
有条件内连接
在无条件的内连接基础上,加上一个ON子句
当连接的时候,筛选出那些有实际意义的记录行来进行拼接
SELECT *
FROM user
JOIN user_details;
SELECT *
FROM user
INNER JOIN user_details;
SELECT *
FROM user
CROSS JOIN user_details;
session.query(UserDetails,User)
通过打印的 SQL 来看,这种是 cross join 笛卡尔乘积
SELECT user_details.id AS user_details_id, user_details.id_card AS user_details_id_card, user_details.lost_login AS user_details_lost_login, user_details.login_num AS user_details_login_num, user_details.user_id AS user_details_user_id, user.id AS user_id, user.username AS user_username, user.password AS user_password, user.creatime AS user_creatime
FROM user_details, user
select *
from user_details, user
示例
session.query(UserDetails, User).filter(UserDetails.id==User.id).all()
session.query(UserDetails.id, User.id).filter(UserDetails.id==User.id).all()
[(<UserDetails(id=1,id_card=1,last_login=2018-08-27 13:55:34,login_num=0,user_id=1)>, <User(id=1,username=wu,password=1,creatime=2018-08-21 18:32:09)>)]
[1,1]
inner join 等值连接
只返回两个表中联结字段相等的行
session.query(User.username, UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id)
----------------------------------------------------------------------------------
SELECT user.username AS user_username, user_details.lost_login AS user_details_lost_login
FROM user
INNER JOIN user_details
ON user_details.id = user.id
-------------------------------------------------------------------------------
通过打印的 SQL 来看,上面这种方式是 inner join(等值连接)
select *
from A
innerjoin B
on A.aID = B.bID
-----------------------------------------------------------------------------------
session.query(User, UserDetails).join(UserDetails, UserDetails.id == User.id)
----------------------------------------------------------------------------------
SELECT user.id AS user_id, user.username AS user_username, user.password AS user_password, user.creatime AS user_creatime, user_details.id AS user_details_id, user_details.id_card AS user_details_id_card, user_details.lost_login AS user_details_lost_login, user_details.login_num AS user_details_login_num, user_details.user_id AS user_details_user_id
FROM user
INNER JOIN user_details
ON user_details.id = user.id
示例
session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id).all()
[('wu', datetime.datetime(2018, 8, 30, 14, 10, 29)), ('qq', datetime.datetime(2018, 8, 30, 14, 10, 31))]
left join 左外连接
接收左表的所有行,并用这些行与右表进行匹配
session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id)
------------------------------------------------------------------------------------
SELECT user.id AS user_id, user_details.lost_login AS user_details_lost_login
FROM user
LEFT OUTER JOIN user_details
ON user_details.id = user.id
------------------------------------------------------------------------------------
session.query(UserDetails.lost_login, User.id).outerjoin(User, UserDetails.id == User.id)
------------------------------------------------------------------------------------
SELECT user_details.lost_login AS user_details_lost_login, user.id AS user_id
FROM user_details
LEFT OUTER JOIN user
ON user_details.id = user.id
------------------------------------------------------------------------------------
通过上面打印的 SQL 来看, 上面这种方式采用的是left join
select *
from user
left join user_details
on user_details.id = user.id
left join与left outer join没有区别
示例
session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all()
[('wu', datetime.datetime(2018, 8, 30, 14, 10, 29)), ('qq', datetime.datetime(2018, 8, 30, 14, 10, 31)), ('lala', None)]
------------------------------------------------------------------------------------
session.query(UserDetails.lost_login, User.id).outerjoin(User, UserDetails.id == User.id).all()
[(datetime.datetime(2018, 8, 30, 14, 10, 29), 'wu'), (datetime.datetime(2018, 8, 30, 14, 10, 31), 'qq')]
union 联合查询(去重)
r1 = session.query(User.id)
r2 = session.query(UserDetails.id)
print(r1.union(r2).all())
------------------------------------------------------------------------------------
[(1,), (3,), (4,), (5,), (6,), (7,), (8,), (2,)]
------------------------------------------------------------------------------------
SELECT user.id
FROM user
UNION
SELECT user_details.id
FROM user_details
------------------------------------------------------------------------------------
+----+
| id |
+----+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 2 |
+----+
union 是联合查询,有自动去重的功能,对应的还有 union_all
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
union_all 联合查询
r1 = session.query(User.id)
r2 = session.query(UserDetails.id)
print(r1.union_all(r2).all())
------------------------------------------------------------------------------------
[(1,), (3,), (4,), (5,), (6,), (7,), (8,), (1,), (2,), (3,)]
------------------------------------------------------------------------------------
SELECT user.id
FROM user
UNION ALL
SELECT user_details.id
FROM user_details
------------------------------------------------------------------------------------
+----+
| id |
+----+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 1 |
| 2 |
| 3 |
+----+
子表查询
查询中还有查询
就是一条select语句结果作为另外一条select语法的一部分(查询结果,查询条件,表等)
声明子表
sql_0 = session.query(UserDetails.lost_login).subquery()
subquery 声明子表
使用
session.query(User, sql_0.c.lost_login).all()
注意:子表使用时要注意使用方法
sql_0 = session.query(UserDetails.lost_login).subquery()
print(session.query(User, sql_0.c.lost_login))
------------------------------------------------------------------------------------
SELECT user.id AS user_id, user.username AS user_username, user.password AS user_password, user.creatime AS user_creatime, anon_1.lost_login AS anon_1_lost_login
FROM user, (
SELECT user_details.lost_login AS lost_login
FROM user_details
) AS anon_1
两个表相当于cross join 交叉连接
sql_0 = session.query(UserDetails.id).subquery()
print(session.query(User.id, sql_0.c.id).all())
-------------------------------------------------------------------------------------
[(1, 1), (1, 2), (1, 3), (3, 1), (3, 2), (3, 3), (4, 1), (4, 2), (4, 3), (5, 1), (5, 2), (5, 3), (6, 1), (6, 2), (6, 3), (7, 1), (7, 2), (7, 3), (8, 1), (8, 2), (8, 3)]
-------------------------------------------------------------------------------------
print(session.query(User.id, UserDetails.id).all())
-------------------------------------------------------------------------------------
[(1, 1), (1, 2), (1, 3), (3, 1), (3, 2), (3, 3), (4, 1), (4, 2), (4, 3), (5, 1), (5, 2), (5, 3), (6, 1), (6, 2), (6, 3), (7, 1), (7, 2), (7, 3), (8, 1), (8, 2), (8, 3)]
04原生SQL查询
SQLAlchemy 虽然可以不用担心SQL问题,但有些情况下难免看上去比较麻烦,这个时候用原生的SQL会更加方便,这个可以实现吗?
原生SQL
SQL
sql_1='''
select * from `user`
'''
查询
row = session.execute(sql_1)
取值
row.fetchone()
(1, 'wu', '1', datetime.datetime(2018, 8, 21, 18, 32, 9))
---------------------------------------------------------------------
row.fetchmany()
(3, 'qq', 'qwe', datetime.datetime(2018, 8, 21, 18, 42, 54))
(4, 'lala', 'aaa', datetime.datetime(2018, 8, 21, 18, 42, 54))
(5, '1wua', 'qwe123', datetime.datetime(2018, 8, 26, 18, 58, 30))
(6, '1qqa', 'qwe', datetime.datetime(2018, 8, 26, 18, 58, 30))
(7, '1lalaa', 'aaa', datetime.datetime(2018, 8, 26, 18, 58, 30))
(8, 'wu', '2', datetime.datetime(2018, 8, 27, 9, 21, 5))
---------------------------------------------------------------------
row.fetchall()
[(1, 'wu', '1', datetime.datetime(2018, 8, 21, 18, 32, 9)), (3, 'qq', 'qwe', datetime.datetime(2018, 8, 21, 18, 42, 54)), (4, 'lala', 'aaa', datetime.datetime(2018, 8, 21, 18, 42, 54)), (5, '1wua', 'qwe123', datetime.datetime(2018, 8, 26, 18, 58, 30)), (6, '1qqa', 'qwe', datetime.datetime(2018, 8, 26, 18, 58, 30)), (7, '1lalaa', 'aaa', datetime.datetime(2018, 8, 26, 18, 58, 30)), (8, 'wu', '2', datetime.datetime(2018, 8, 27, 9, 21, 5))]
循环取值
for i in row:
print(i)
(1, 'wu', '1', datetime.datetime(2018, 8, 21, 18, 32, 9))
(3, 'qq', 'qwe', datetime.datetime(2018, 8, 21, 18, 42, 54))
(4, 'lala', 'aaa', datetime.datetime(2018, 8, 21, 18, 42, 54))
(5, '1wua', 'qwe123', datetime.datetime(2018, 8, 26, 18, 58, 30))
(6, '1qqa', 'qwe', datetime.datetime(2018, 8, 26, 18, 58, 30))
(7, '1lalaa', 'aaa', datetime.datetime(2018, 8, 26, 18, 58, 30))
(8, 'wu', '2', datetime.datetime(2018, 8, 27, 9, 21, 5))