Python Orm Sqlalchemy Core Use Example

count

1
2
3
4
5
select(func.count())
.select_from(table)
.where(
table.c.status == 1,
)

count if

1
2
3
4
5
6
7
func.COUNT(
func.IF(
user.c.province == "重庆",
True,
None,
)
).label("province"),

exists

1
2
3
4
5
6
7
8
9
select(["*"])
.select_from(table1)
.where(
table1.c.status == 1,
exists()
.where(
table1.c.id == table2.c.table1_id,
),
)

如果要使用 not exists 只需要在 exists() 前加上 ~ 变成 ~exists()

动态条件

1
2
3
conditions = []
conditions.append(user.c.sex == 1)
select(["*"]).select_from(user).where(and_(*conditions))

update

1
2
3
4
5
user.update()
.where(
user.c.id == 1
)
.values(score=user.c.score + 1)

order_by

1
2
3
4
#  升序
.order_by(user.c.id)
# 降序
.order_by(user.c.id.desc())

未完待续…