#多條件查詢
#原生sql:select * from mysql.hello_word where id >2 and id < 19
data
=
session.query(User).
filter
(Use.
id
>
2
).
filter
(Use.
id
<
19
).
all
()
<br>
#通配符
#原生sql:select * from mysql.hello_word where name like "test%" #"test_"、%test%
data
=
session.query(User).
filter
(User.name.like(
'test%'
)).
all
()
#匹配以test開頭,而后跟多個字符
data
=
session.query(User).
filter
(User.name.like(
'test_'
)).
all
()
#匹配以test開頭,而后跟一個字符
data
=
session.query(User).
filter
(~User.name.like(
'e%'
)).
all
()
#加~后,忽略like(),直接匹配所有
#原生sql select count(name) from mysql.hello_word where name like "%test%"
data
=
session.query(User).
filter
(User.name.like(
"%qigao%"
)).count()
# 模糊匹配并計數(shù)
<br>
#分組
from
sqlalchemy
import
func
#導(dǎo)入func 進(jìn)行函數(shù)操作
#原生sql:select count(name),name from mysql.hello_word group by name
data
=
session.query(func.count(User.name),User.name).group_by(User.name).
all
()
#根據(jù)User.name分組
#原生sql:select max(id),sum(id),min(id) from mysql.hello_word group by name #根據(jù)name 分組
data
=
session.query(func.
max
(User.
id
),func.
sum
(User.
id
),func.
min
(User.
id
)).group_by(User.name).
all
()
#原生sql:select max(id),sum(id),min(id) from mysql.hello_word group by name having min(id > 2) # 根據(jù)name分組且id>2
data
=
session.query(func.
max
(User.
id
),func.
sum
(User.
id
),func.
min
(User.
id
)).group_by(User.name).having(func.
min
(User.
id
) >
2
).
all
()
<br><br>
#排序
#原生sql:select * from mysql.hello_word order by id asc
data
=
session.query(User).order_by(User.
id
.asc()).
all
()
#將所有數(shù)據(jù)根據(jù) “列” 從小到大排列
#原生sql:select * from mysql.hello_word order by id desc, id asc
data
=
session.query(User).order_by(User.
id
.desc(), User.
id
.asc()).
all
()
#將所有數(shù)據(jù)根據(jù) “列1” 從大到小排列,如果相同則按照“列2”由小到大排列
#條件表達(dá)式 in、between、 and 、or
data
=
session.query(User).filter_by(name
=
'test'
).
all
()
data
=
session.query(User).
filter
(User.
id
>
1
, Users.name
=
=
'test'
).
all
()
data
=
session.query(User).
filter
(User.
id
.between(
1
,
3
), Users.name
=
=
'test'
).
all
()
data
=
session.query(User).
filter
(User.
id
.in_([
1
,
3
,
4
])).
all
()
data
=
session.query(User).
filter
(~User.
id
.in_([
1
,
3
,
4
])).
all
()
data
=
session.query(User).
filter
(Users.
id
.in_(session.query(User.
id
).filter_by(name
=
'test'
))).
all
()
from
sqlalchemy
import
and_, or_
data
=
session.query(User).
filter
(and_(User.
id
>
3
, Users.name
=
=
'test'
)).
all
()
data
=
session.query(User).
filter
(or_(User.
id
<
2
, Users.name
=
=
'test'
)).
all
()
data
=
session.query(User).
filter
(or_(User.
id
<
2
,and_(User.name
=
=
'test'
,User.
id
>
3
),User.password !
=
"")).
all
()