二、sqlalchemy的基本操作

二、sqlalchemy的基本操作

以下示例使用的sql文件

Ⅰ、sqlalchemy的基本操作

查询全部类容

res = dbseesion.query(Users).all() 

等价于 select * from users    

查询全部参数

 res = dbseesion.query(Users.userid,Users.username).all()  

 等价于 select userid, username from users

or_ 查询 或 操作

from sqlalchemy import create_engine, or_  #导入or_
res = dbseesion.query(Users).filter(or_(Users.userid == 1,Users.nickname == '丹尼')).all()  
 
等价于 select * from users where userid=1 or nickname='丹妮'

查询几条数据

 res = dbseesion.query(Users).limit(3).all() 
  
 等价于  select * from users limit 3

查询几条数据,偏移量为几

res = dbseesion.query(Users).limit(5).offset(3).all()  
# 查询5条，偏移量为3，但从第4条开始 
等价于  select * from users limit 3，5

查询条数

res = dbseesion.query(Users).filter(Users.userid > 3).count()  

等价于  5 select count(*) from users where ..

去重

res = dbseesion.query(Users.qq).distinct(Users.qq).all()  

等价于 select distinct(qq) from users

排序 （默认升序）

res = dbseesion.query(Users).order_by(Users.userid).all()  #降序
dbseesion.query(Users).order_by(Users.userid.desc()).all() #降序  

等价于 select * from users order by desc

模糊查询

res = dbseesion.query(Users).filter(Users.username.like('%qiang%')).all()  
 
等价于 select * from users where username like '%qiang%'

对某个字段进行分组

res = dbseesion.query(Users).group_by(Users.role).all()   #对role字段进行分组
 
等价于 select * from users group by role

对分组的内容再加条件

res = dbseesion.query(Users).group_by(Users.role).having(Users.userid > 2).all() 
  
等价于 select * from users group by role having userid>2

聚合函数（sum,min,max）

from sqlalchemy import func
res = dbseesion.query(func.sum(Users.credit)).first()  #需要用func.聚合的函数
等价于 select sum(credit) from users  

Ⅱ、sqlalchemy的多表连接查询

内连接

#连接Article, Users表，并展示两表的所有数据
res = dbseesion.query(Article, Users).join(Users, Article.userid==Users.userid).filter(Article.articleid==1).all()  # query(Article, Users)中的Article为主表 join(Users, Users为从表

等价于 select * from article inner join users on article.userid=users.userid where article.articleid=1

#连接Article, Users表，并展示Users表的nickname字段和Article的所有数据 
res = dbseesion.query(Article, Users.nickname).join(Users, Article.userid == Users.userid).filter(Article.articleid == 1).all()

等价于 select *, Users.nickname from article inner join users on article.userid=users.userid where article.articleid=1

Ⅲ、其他操作

外连接 默认左连接 ，右连接表互换即可

res = dbseesion.query(Users.userid,Users.nickname,func.sum(Article.readcount)).outerjoin(Article,Users.userid==Article.userid).group_by(Users.userid).all()

等价于 select users.userid,users.nickname,sum(article.readcount) as total from users left join article on users.userid=article.articleid group by users.userid

复杂查询

# and 和or 混用filter默认and  username like '%qiang%' or (userid>3 and nickname='reader3)
res = dbseesion.query(Users).filter(or_(Users.username.like('%qiang%'),and_(Users.userid>3,Users.nickname=='reader3'))).all()

等价于 select * from users where username like("%qiang%'") or (userid>3 and nickname='reader3')

 # 获取上一篇的文章id
row = dbsession.query(Article).filter(Article.hide == 0, Article.drafted == 0, Article.checked == 1,Article.recommended == 1, Article.articleid < articleid).order_by( Article.articleid.desc()).limit(1).first()

# 获取下一篇的文章id
row = dbsession.query(Article).filter(Article.hide == 0, Article.drafted == 0, Article.checked == 1,Article.recommended == 1, Article.articleid >articleid).order_by(Article.articleid).limit(1).first()

在这里插入代码片

三表链接

 res = dbseesion.query(Comment,Users).join(Users,Comment.userid==Users.userid).join(Article,Article.articleid==Comment.articleid).all()

执行原生的sql

res = dbseesion.execute("select * from users where userid>5").fetchall()

res = dbseesion.execute("delete from users where userid=10")
dbseesion.commit()