sqlalchemy 入门(下)

建议从这里下载这篇文章对应的.ipynb文件和相关资源。这样你就能在Jupyter中边阅读,边测试文中的代码。

说明

sqlalchemy 入门(上),这篇会更详细的讲解和演示如果用SQLAlchemy完成Select, Update, Delete的操作。

准备

实验准备内容和sqlalchemy 入门(上)的内容一致,这里不在重复多说。

选择

选择全部列

选择全部的列

id name fullname
0 1 jack Jack Jones
1 2 wendy Wendy Williams

可以用下面方法得到涉及的列名

[‘id’, ‘name’, ‘fullname’]

另一种稍繁琐但是更通用的方法

id name fullname
0 1 jack Jack Jones
1 2 wendy Wendy Williams

选择指定列

可以在select的list中指定需要的列

id fullname
0 1 Jack Jones
1 2 Wendy Williams

也可以用下面这种语法指定要选择的列。好处是可以支持变量作为列名。

id fullname
0 1 Jack Jones
1 2 Wendy Williams

注意

上面的几个例子,并没有指定FROM条件,SQLAlchemy自动添加了FROM语句。。

修改列名

用label实现

user_id user_name
0 1 jack
1 2 wendy

选择计算后的结果

选择的时候也可以进行一些运算。例如字符串拼接。

id fullname
0 1 Fullname:Jack Jones
1 2 Fullname:Wendy Williams

下面是数值相加的例子。

id new_id
0 1 11
1 2 12

注意

上面的两个例子里,都使用了’+’号,SQLAlchemy根据数据类型自动决定编译后的结果是字符串连接还是数值相加。

使用sql function

可以使用func.func_name的形式应用函数,使用的时候只需要导入func模块,接上数据库中的函数名即可

user_id user_name
0 1 JACK
1 2 WENDY

注意应用函数的时候,label要放在在函数之外使用,否则是无效的,这是一个容易犯的错误。可以看到下面的例子里,name列采用了自动命名。

user_id upper(test.users.name)
0 1 JACK
1 2 WENDY

使用window function

使用func.func_name().over()的方式即可

id name num
0 1 jack 1
1 2 wendy 2

使用case

case_test
0 A
1 B

添加常数列

user_id constant null
0 1 AAAAAA None
1 2 AAAAAA None

指定limit and offset

例如用offset跳过1行,并且用limit只显示2行

id email_address
0 2 jack@msn.com
1 3 www@www.org

Order By排序

下面的例子中根据user.id和user.name排序

name
0 jack
1 wendy

如果要控制升序降序的话。可以调用desc()方法或者使用desc函数。

name
0 wendy
1 jack

name
0 wendy
1 jack

Group By

name count
0 wendy 2
1 jack 2

如果要根据多列去做group by的话、

name count
0 jack 2
1 wendy 2

使用Having条件

name count
0 wendy 2
1 jack 2

集合操作

这里至演示union_all。 union, except_, except_all, intersect, intersect_all同理,不再演示。

id user_id email_address
0 1 1 jack@yahoo.com

where条件

id name fullname user_id email_address
0 1 jack Jack Jones 1 jack@yahoo.com
1 1 jack Jack Jones 1 jack@msn.com
2 2 wendy Wendy Williams 2 www@www.org
3 2 wendy Wendy Williams 2 wendy@aol.com

常见的where条件

等于

大于

不等于

有些条件需要调用方法实现,例如表现SQL中的IN的时候,这样是不行的

False

而是应该用object本身提供的函数in_

类似的有between

字符串匹配like

特殊的算符

如果有一些非常规的operator,总是可以用.op方法取实现

逻辑连词

最常用的有and_, or_, not_

连续多个where连用也可以起到and的效果

也可以用python的&,|,~等逻辑连接符号代替and_(), or_(), not_

join表

自动Join

如果join时没有指定on条件。SQLAlchemy会去检查是否存在foreign key的定义。

helper.py的reset_tables函数中,已经定义了users.id是addresses.user_id的外键。所以下面的语句能自动添加test.users.id = test.addresses.user_id的条件。

注意

auto_load得到的表无法找到正确的foreign_key

看上去定义了foreign key的信息,但是运行的话会出错

select_from

如果要将join语句用于select的话,可以用select_from语句,功能相当于SQL中的FROM。

Left Join

如果要使用left outer join,把join换成outerjoin

Outer Join

如果要使用outer join,把join换成outerjoin,并且加上参数full = True

Alias

要把一个子查询结果像一张表那样被使用时,需要使用alias()给子查询命名。

使用SQLAlchemy的时候,由于可以通过python变量名来找到正确的查询,因此并不一定要去指定命名,SQLAlchemy会添加自动的命名。下面的例子里,SQLAlchemy采用了自动命名anon_1

关联子查询

基本概念

先用一个例子演示一下关联子查询,这个查询的功能是找出每个用户的id和拥有的邮箱的个数。

这里需要注意的是,子查询有以下几个特点,

  • 子查询中使用了test.users.id,但是子查询的FROM语句中并没有出现test.users。
  • 子查询返回的结果为一行,一列。

实际上,这种情况下子查询的test.users是由外层的test.users关联的,如果外层是id为1的user,那么子查询的test.users.id就是1;如果外层是id为2的user,那么子查询的test.users.id就是2。

为了方便理解,可以想象有一个根据传入的user id,查找拥有的邮箱数的函数email_count,那么下面的例子和上面的例子是等价的。

SQLAlchemy中的实现

上面例子中的查询方式称为关联子查询,下面演示怎么在SQLAlchemy中构造关联子查询。

这里要注意关联子查询的结尾必须是label,label一般用于给列重命名。 (实际上由于关联子查询返回一行一列,的确像是一个列。) 如果关联子查询最后没有加label而是alias,那么就不会编译成关联子查询。

可以看到上面编译后的SQL语句中,FROM语句添加了test.users。子查询本身就构成了一个完整的查询,统计了所有邮箱的计数,结果为固定的4。

关联行为的调整

可以通过correlate, correlate_except指定或排除sub query中需要关联的外层的table范围。例如通过correlate(None)或者correlate_except(users)禁止sub query关联外层的users

可以看到上面的例子里,sub query中自动在From语句中补充了users,成为完成的select语句,结果也变成了固定的4

join lateral

目前只有postgresql支持。 join lateral实际上可以看做另一种形式的关联子查询。

join lateral语句实际上也起到了遍历users,取出id和对应email_address个数的功能。

这段代码等价于

Update

最基本的update,不指定任何条件,相当于对所有的row做了遍历

选择单行并进行遍历

更新数据

对全数据更新

下面是最基本的update方式,在values方法中指定数据的更新方法。

如果不加where条件对的话,这个更新逻辑会应用与所有的数据。

对单行更新

也可以用dict的方式去指定更新方式。 如果用这种方式的话, object和列名都可以作为key。

对多行进行更新

用给定的数据进行更新

可以借助上一个教程提到过的bindparam。 编写针对单个row的update逻辑, 但是将需要更新的多条数据用list传入即可实现批量的更新。

不过update语句的参数数值只能在execute阶段传入,不能使用params()传入

用另一个select结果更新数据

关键是通过where条件去指定一下数据源(select结果)和待更新的表之间是怎么的匹配的。

也可以用类似关联子查询的方式那样去进行数据升级。

下面我们用每个人字典排序的第一个邮箱去替换原来的name。

和关联子查询一样,用这种方式修改数据时,返回的数据也必须是单行单列。

控制列操作的顺序

由于SQLAlchemy编译update语句的时候,默认是按照列在users表中的出现顺序去编译。

id name fullname
0 1 ***** jack
1 2 ***** wendy

编译的语句中,顺序是name=’*****’, fullname=test.users.name。如果我们需要精确的去控制编译后语句列更新的顺序,可以用下面的方式

id name fullname
0 1 ***** jack
1 2 ***** wendy

现在可以看到编译的语句是SET fullname=test.users.name, name=’*****’,反映了tuple list的顺序。通过这种方式我们就可以改变编译的SET语句操作列的顺序。

注意

对于postgresql,上面两种SET的顺序结果都是一致的。但是对于MYSQL数据库,如果编译结果是

在执行fullname=test.users.name部分的时候,会采用”*”作为name的数值,导致最后fullname也变成’*’,只有用

才能得到正确的结果。

这时就有必要去指定SET语句中列操作的顺序。

删除数据

掌握了修改数据的操作后, 删除的操作就非常简单。不过要注意约束条件。例如addresses的user_id是users表的外键,那么在删除addresses表的数据前,是不能删除users表的数据的。

删除全量数据

由于外键造成的约束,直接删除users表是会报错的。

需要先删除addresses, 再删除users

1 收藏 评论

相关文章

可能感兴趣的话题



直接登录
跳到底部
返回顶部