Tornado 数据库_ORM_SQLAlchemy_查询操作

上节课使用query从数据库中查询到了结果，但是query返回的对象是直接可用的吗？

query：
from connect import session
from user_module import User

result = session.query(User)

print(result,type(result)) #返回是一个query对象，打印出来可以看到转化过后的SQL
image.png

根据以上返回是一个query对象，打印出来可以看到转化过后的SQL。

filter：
from connect import session
from user_module import User

result = session.query(User).filter(User.username=='cainiao')

print(result,type(result)) #返回是一个query对象，打印出来可以看到转化过后的SQL
image.png

根据以上返回是一个query对象，打印出来可以看到转化过后的SQL。

all：

all 是返回所有符合条件的数据。

result = session.query(User).filter(User.username=='cainiao').all()
print(result,type(result)) ##返回的是一个list,列表里面是一个类
image.png

根据以上返回的是一个list,列表里面是一个类。

first：

first 是返回所有符合条件数据的第一条数据。

result = session.query(User).filter(User.username=='cainiao').first()
print(result,type(result)) #返回类的实例化

根据以上返回的是类的实例化

索引
result1 = session.query(User)
print(result1,type(result1)) #返回是一个query对象，打印出来可以看到转化过后的SQL

result2 = session.query(User)[0]
print(result2,type(result2))    #返回类的实例化
result3 = session.query(User).all()[0]
print(result3,type(result3))  #返回类的实例化

result4= session.query(User).first()
print(result4,type(result4))  #返回类的实例化
image.png

索引为0的值，类似于first，但是不等同于，空列表用这个会报错！！

取值

对于类的实例化可以用.和getattr来获取字段值

#对于类的实例化可以用.来获取字段值
print(result2.username)
print(result3.username)
print(result4.username)

#也可以用getattr方法
print(getattr(result2,'username'))
print(getattr(result2,'username'))
print(getattr(result2,'username'))
image.png

在query里面指定了某个属性值，返回是直接查询User.username的值，所以就返回了包括元组的列表

result = session.query(User.username).all()
print(result,type(result))  #在query里面指定了某个属性值，返回是直接查询User.username的值，所以就返回了包括元组的列表

print(session.query(User.username).first()) #返回元祖
image.png

过滤函数

filter是一个过滤函数，过滤条件都可以书写在次函数中，不同的条件之间用逗号隔开。filter_by 也是一个过滤函数，但是功能要弱一些。

result1 = session.query(User).filter(User.username=='cainiao',User.password=='1').all()
print(result1)
result2 = session.query(User).filter_by(username='cainiao').all()
print(result2)

区别：
二者都是 过滤函数，但是使用有如下差别：

  1. filter 中需要添加 类对象，filter_by不需要。
  2. filter_by 中只能添加等于的条件，不能添加 不等于、大于小于等条件，filter没有这个限制。

模糊查询

like和notlike

like 是模糊查询，和数据库中的 like 用法一样，notlike 和 like 作用相反。

#like 是模糊查询，和数据库中的 like 用法一样，notlike 和 like 作用相反。
result1 = session.query(User.id).filter(User.username.like('cainiao%')).all()
result2 = session.query(User.id).filter(User.username.notlike('cainiao%')).all()
print(result1,result2)
image.png
in_和notin_

in_ 和 notin_ 是范围查找

result1 = session.query(User.id).filter(User.username.in_(['cainiao', 'cainiao1'])).all()
result2 = session.query(User.id).filter(User.username.notin_(['cainiao2', 'cainiao3'])).all()
print(result1,result2)
image.png
is_和isnot

is_ 和 isnot精确查找

#is_  和 isnot精确查找
result1 = session.query(User.id).filter(User.username.is_(None)).all()
result2 = session.query(User.id).filter(User.username.isnot(None)).all()
print(result1,result2)

#判断为空还可以使用：
result3 = session.query(User.id).filter(User.username==None).all()
print(result3)
image.png

查询结果

#先用 all 查看所有的数据
result1 = session.query(User.username).filter(User.username!='cainiao%').all()
#查看前2条数据
result2 = session.query(User.username).filter(User.username!='cainiao').limit(2).all()
#偏移一条记录，偏移1个，前面1个数据不看，从第2个数据开始查询
result3 = session.query(User.username).filter(User.username!='cainiao').offset(1).all()

#slice   对查询出来的数据进行切片取值,#切片，索引0到1的数据
result4 = session.query(User.username).filter(User.username!='cainiao').slice(0,2).all()

#one  查询一条数据，如果存在多条则报错,可以用来测试是否只有一条
result5 = session.query(User.username).filter(User.username!='cainiao').one()

排序


#导入模块
from sqlalchemy import desc,asc

#默认正序，asc也是正序
result1 = session.query(User.id).filter(User.username=='cainiao').order_by(User.id).all()
result2 = session.query(User.id).filter(User.username=='cainiao').order_by(asc(User.id)).all()
#desc 是倒序排序
result3 = session.query(User.id).filter(User.username=='cainiao').order_by(desc(User.id)).all()
#order_by  和 limit 一起使用的时候
result4 = session.query(User.id).filter(User.username=='cainiao').order_by(User.id).limit(2).all()
print(result1,type(result1))
print(result2,type(result2))
print(result3,type(result3))
print(result4,type(result4))
image.png

默认为正序，asc。desc为倒序。

函数

#导入
from sqlalchemy import func,extract

result1 = session.query(func.count(User.id)).all() #select count(id) from users
#用到group_by ，查询的列是分组的列，或者某个列的聚合函数
result2 = session.query(User.username).group_by(User.username).all() #select username from users group by username
result3 = session.query(User.username,func.count(User.id)).group_by(User.username).all() #select username,count(id) from users group by username
result4 = session.query(User.username).group_by(User.username).having(func.count(User.id)<4).all()

#group_by 和 order_by 一样，是可以直接使用的，不需要导入,having 也可以直接使用，使用方法也和 SQL 中使用类似。

print(result1,type(result1))
print(result2,type(result2))
print(result3,type(result3))
print(result4,type(result4))
image.png

用到group_by ，查询的列是分组的列，或者某个列的聚合函数。

聚合函数

#sum 求和

result1 = session.query(func.sum(User.id)).filter(User.username=='cainiao').all()
#max 求最大值

result2 = session.query(func.max(User.id)).filter(User.username=='cainiao').all()

#min 求最小值
result3 = session.query(func.min(User.id)).filter(User.username=='cainiao').all()

print(result1)
print(result2)
print(result3)
image.png

常用聚合函数：count 、 sum avg 、 max 、 min

extract
#extract 提取对象中的数据，这里提取分钟，并把提取出来的结果用 label 命名别名，之后就可以使用 group_by 来分组

session.query(extract('minute',User.creatime).label('minute'), func.count(User.id)).group_by('minute').all()

选择条件

from sqlalchemy import or_
result1 = session.query(User.id).filter(User.username=='cainiao').all()
result2 = session.query(User.id).filter(User.id==3).all()
result3 = session.query(User.id).filter(or_(User.username=='cainiao',User.id==3)).all()

print(result1)
print(result2)
print(result3)
image.png

多表查询

新建module
class UserDetails(Base):
    __tablename__ = 'user_details'
    id = Column(Integer,primary_key=True,autoincrement=True)
    id_card = Column(Integer,nullable=True,unique=True)
    last_login =Column(DateTime)
    login_num = Column(Integer,default=0)
    user_id = Column(Integer,ForeignKey('users.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.last_login,self.login_num,
            self.user_id)

cross join笛卡尔积

cross join交叉连接。交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。

from user_module import UserDetails
result1 = session.query(UserDetails,User) #cross join 第一张表每一项与第二张表交叉连接
print(result1,type(result1))
image.png

inner join内连接

内连接inner join，内连接，只查匹配行。

result2 = session.query(User.username, UserDetails.last_login).join(UserDetails,UserDetails.id==User.id) #内连接
print(result2)
image.png

外连接

外连接，至少有一方保留全集，没有匹配行用NULL代替。外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。

  1. 左外连接：左表不加限制，保留左表的数据，匹配右表，右表没有匹配2到的行中的列显示为null。
  2. 右外连接：右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为null。
  3. 完全外连接：左右表都不加限制。即右外连接的结果为：左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据。
#左连接 left join结果集保留左表的所有行，但只包含第二个表与第一表匹配的行。第二个表相应的空行被放入NULL值。
result3 = session.query(User.username, UserDetails.last_login).outerjoin(UserDetails,UserDetails.id==User.id) #左连接
print(result3,type(result3))

#sqlalchemy没有right join，右连接将表反过来写即可。
result4 = session.query(UserDetails.last_login,User.username).outerjoin(User,User.id==UserDetails.id)

print(result4,type(result4))
image.png

union

union 是联合查询，有自动去重的功能，对应的还有 union_all,UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

#union 是联合查询，有自动去重的功能，对应的还有 union_all,UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
result1 = q1.union(q2)
result2 = q1.union_all(q2)

print(q1.all())
print(q2.all())
print(result1.all(),type(result1))
print(result2.all(),type(result2))
image.png

union:联合的意思，即把两次或多次查询结果合并起来。
要求：两次查询的列数必须一致
推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样
可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。
如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。
如果不想去掉重复的行，可以使用union all。

子表查询

是指出现在其他SQL语句内的SELECT字句。嵌套在查询内部，且必须始终出现在圆括号内。子查询里面只能查询一个东西，不能查询多个，因为外面接收的只能接收一个。

#子表查询
#subquery声明子表
sql_0 =session.query(UserDetails.last_login).subquery()

#使用
result2 = session.query(User,sql_0.c.last_login)
print(result2)
image.png

原生sql查询


sql = "select * from users"
row = session.execute(sql)

print(row.fetchall()) #注意类似游标指针一次查询指针会指到本次查询的末尾处
print(row.fetchone())
print(row.fetchmany())

#循环获取
for i in row:
    print(i)
image.png
pycharm快速添加数据

点击右边Database：


image.png

点击添加：


image.png

设置配置信息：
image.png

测试连接成功，点击保存：


image.png

点击右边就可以在左边进行相关操作了
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,919评论 6赞 502
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,567评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,316评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,294评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,318评论 6赞 390
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,245评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,120评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,964评论 0赞 275
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,376评论 1赞 313
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,592评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,764评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,460评论 5赞 344
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,070评论 3赞 327
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,697评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,846评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,819评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,665评论 2赞 354

推荐阅读更多精彩内容