SQLAlchemy学习-2.query() 查询数据
前言
SQLAlchemy 使用 query() 方法查询数据
创建模型
接着前面一篇创建的模型
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
# 拼接配置dialect + driver://username:passwor@host:port/database
DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'
Base = declarative_base()
class Students(Base):
__tablename__ = 'students' # 数据库表名
id = Column(Integer, primary_key=True)
name = Column(String(20))
fullname = Column(String(30))
nickname = Column(String(30))
def __repr__(self):
return "<Students(name='%s', fullname='%s', nickname='%s')>" % (
self.name, self.fullname, self.nickname)
创建session实例
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from xx.xx import Students
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/web')
# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)
# 实例化
session = Session()
query() 查询
query() 查询会转换成对应的SQL 语句
# query()
r1 = session.query(Students)
print(r1)
等价于
SELECT students.id AS students_id, students.name AS students_name, students.fullname AS students_fullname, students.nickname AS students_nickname
FROM students
query()可以只查询某个字段，多个字段逗号隔开
r2 = session.query(Students.name)
print(r2)
等价于
SELECT students.name AS students_name
FROM students
all()查询全部数据
all()查询全部数据
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from xx.xx import Students
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/web')
# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)
# 实例化
session = Session()
all = session.query(Students).all()
print(all)
for item in all:
print(item.name, item.fullname)
运行结果
[<Students(name='yoyo', fullname='yoyoketang', nickname='yy')>,
<Students(name='yoyo1', fullname='yoyoketang1', nickname='yy1')>,
<Students(name='yoyo2', fullname='yoyoketang2', nickname='yy2')>]
yoyo yoyoketang
yoyo1 yoyoketang1
yoyo2 yoyoketang2
all 查询的结果是一个list of Students对象
first() 返回查询结果第一个数据
first = session.query(Students).first()
print(first)
运行结果
<Students(name='yoyo', fullname='yoyoketang', nickname='yy')>
filter()筛选过滤
filter()方法加筛选条件
# filter() 过滤
r1 = session.query(Students).filter(Students.name == 'yoyo')
print(r1)
等价于以下sql
SELECT students.id AS students_id, students.name AS students_name, students.fullname AS students_fullname, students.nickname AS students_nickname
FROM students
WHERE students.name = %(name_1)s
查询结果需加 all() 方法得到全部结果，或者 first() 方法得到第一个结果
# filter() 过滤
r1 = session.query(Students).filter(Students.name == 'yoyo').all()
print(r1) # [<Students(name='yoyo', fullname='yoyoketang', nickname='yy')>]
以上查询类似于 SELECT * FROM ...,如果查询结果只需要某个字段，可以在query()方法指定字段名称
# filter() 过滤
r1 = session.query(Students.name).filter(Students.name == 'yoyo').all()
print(r1) # [('yoyo',)]
r2 = session.query(Students.name, Students.fullname).filter(Students.name == 'yoyo').all()
print(r2) # [('yoyo', 'yoyoketang')]
字符串可以判断相等，数字类型的可以判断 >= 和 <=
r3 = session.query(Students.name).filter(Students.id >= 1).all()
print(r3) # [('yoyo',), ('yoyo1',), ('yoyo2',)]
and和or 多条件查询
在filter()中用,分隔多个条件表示and
r = session.query(Students.name).filter(Students.id >= 1, Students.name == 'yoyo').all()
print(r) # [('yoyo',)]
or_方法实现 or 条件查询
from sqlalchemy import or_
r = session.query(Students.name).filter(or_(Students.id >= 1, Students.name == 'yoyo')).all()
print(r) # [('yoyo',), ('yoyo1',), ('yoyo2',)]
order_by()排序
根据id倒叙
r4 = session.query(Students).order_by(Students.id.desc()).all() # desc()表示倒序
print(r4)
运行结果
[<Students(name='yoyo2', fullname='yoyoketang2', nickname='yy2')>,
<Students(name='yoyo1', fullname='yoyoketang1', nickname='yy1')>,
<Students(name='yoyo', fullname='yoyoketang', nickname='yy')>]
like 和 in
判断等于和不等于可以直接用 == 和 !=
like() 模糊匹配
r = session.query(Students.name).filter(Students.name.like('%yoyo%')).all()
print(r) # [('yoyo',), ('yoyo1',), ('yoyo2',)]
in_() 包含
r = session.query(Students.name).filter(Students.name.in_(['yoyo', 'yoyo1'])).all()
print(r) # [('yoyo',), ('yoyo1',)]
count() 计算个数
.count()方法统计查询个数
r = session.query(Students).count()
print(r) # 3
r1 = session.query(Students).filter(Students.name == 'yoyo').count()
print(r1) # 1
切片
all()方法返回的是一个list，那就可以通过切片取一部分数据
# 返回前2条数据
r = session.query(Students.name).all()[:2]
print(r) # [('yoyo',), ('yoyo1',)]
delete() 删除数据
根据查询结果，调用delete()方法删除对应数据，需要执行session.commit()提交事务。
session.query(Students).filter(Students.name == 'yoyo').delete()
session.commit()
update() 修改数据
update()方法，需要执行session.commit()提交事务
# 修改fullname
session.query(Students).filter(Students.name == 'yoyo1').update({'fullname': 'zhangsan'})
session.commit()
# 修改后查询
r = session.query(Students).filter(Students.name == 'yoyo1').all()
print(r) # [<Students(name='yoyo1', fullname='zhangsan', nickname='yy1')>]
从运行结果可以看到已经更新成功。
