SQLAlchemy-对象关系教程ORM-Relationship

query.filter(User.name == <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>)

2) not equals

query.filter(User.name != <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>)

3) LIKE 是区分大小写的

query.filter(User.name.like(<span style="color: #800000">‘</span><span style="color: #800000">%ed%</span><span style="color: #800000">‘</span>))

4) ILIKE 不区分大小写的

query.filter(User.name.ilike(<span style="color: #800000">‘</span><span style="color: #800000">%ed%</span><span style="color: #800000">‘</span>))

5) IN

query.filter(User.name.in_([<span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">wendy</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">jack</span><span style="color: #800000">‘</span>]))

# works with query objects too:
query.filter(User.name.in_(
session.query(User.name).filter(User.name.like(‘%ed%‘))
))

6) NOT IN

query.filter(~User.name.in_([<span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">wendy</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">jack</span><span style="color: #800000">‘</span>]))

7) IS NULL

query.filter(User.name ==<span style="color: #000000"> None)
query.filter(User.name.is_(None))</span>

8)IS NOT NULL

query.filter(User.name !=<span style="color: #000000"> None)
query.filter(User.name.isnot(None))</span>

9) AND

<span style="color: #008000">#</span><span style="color: #008000"> use and_()</span>
<span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> and_
query.filter(and_(User.name </span>== <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>, User.fullname == <span style="color: #800000">‘</span><span style="color: #800000">Ed Jones</span><span style="color: #800000">‘</span><span style="color: #000000">))
</span><span style="color: #008000">#</span><span style="color: #008000"> or send multiple expressions to .filter()</span>
query.filter(User.name == <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>, User.fullname == <span style="color: #800000">‘</span><span style="color: #800000">Ed Jones</span><span style="color: #800000">‘</span><span style="color: #000000">)
</span><span style="color: #008000">#</span><span style="color: #008000"> or chain multiple filter().filter_by() calls</span>
query.filter(User.name == <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>).filter(User.fullname == <span style="color: #800000">‘</span><span style="color: #800000">Ed Jones</span><span style="color: #800000">‘</span>)

10) OR

<span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> or_
query.filter(or_(User.name </span>== <span style="color: #800000">‘</span><span style="color: #800000">ed</span><span style="color: #800000">‘</span>, User.name == <span style="color: #800000">‘</span><span style="color: #800000">wendy</span><span style="color: #800000">‘</span>))

11)MATCH

<span style="color: #000000">match()使用一个特定于数据库的 MATCH或 CONTAINS函数;它的行为会有所不同后端和不可用等后端SQLite。
query.filter(User.name.match(</span><span style="color: #800000">‘</span><span style="color: #800000">wendy</span><span style="color: #800000">‘</span>))

二:返回结果

　　Query发出SQL语句和返回包括数据库结果一个值

<span style="color: #0000ff">print</span>(session.query(User.name).filter(User.fullname==<span style="color: #800000">"</span><span style="color: #800000">shi jun2</span><span style="color: #800000">"</span><span style="color: #000000">))
</span>--------------------------------------------<span style="color: #000000">
SELECT users.name AS users_name 
FROM users 
WHERE users.fullname </span>= %(fullname_1)s

 　  1）all()取出所的记录

　　2）first()返回第一条记录

　　3）one():不存在，或者有多行记录的时候报错（no items found，multiple items found

　　4）one_or_none()：如果有一条记录就返回一条记录，如果没有记录就返回NONE

　　5）scalar()调用 one()方法,成功返回只有一行结果的第1列

query=<span style="color: #000000">session.query(User.name).first()
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
(</span><span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span><span style="color: #000000">,)
query</span>=<span style="color: #000000">session.query(User.name).all()
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
[(</span><span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span>,), (<span style="color: #800000">‘</span><span style="color: #800000">shi2</span><span style="color: #800000">‘</span><span style="color: #000000">,)]
query</span>=<span style="color: #000000">session.query(User.name).one()
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found </span><span style="color: #0000ff">for</span><span style="color: #000000"> one()
query</span>=session.query(User.name).filter(User.name==<span style="color: #800000">"</span><span style="color: #800000">shi</span><span style="color: #800000">"</span>).one()<span style="color: #008000">#</span><span style="color: #008000">这里查询出的结果只有1条，所以不会报错</span>
<span style="color: #0000ff">print</span><span style="color: #000000">(query)
(</span><span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span><span style="color: #000000">,)
如果有一条记录就返回一条记录，如果没有记录就返回NONE
query</span>=session.query(User.name).filter(User.name==<span style="color: #800000">"</span><span style="color: #800000">shi</span><span style="color: #800000">"</span><span style="color: #000000">).one_or_none()
(</span><span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span><span style="color: #000000">,)
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
query</span>=session.query(User.name).filter(User.name==<span style="color: #800000">"</span><span style="color: #800000">sh3</span><span style="color: #800000">"</span><span style="color: #000000">).one_or_none()
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
None
query</span>=session.query(User.name).filter(User.name==<span style="color: #800000">"</span><span style="color: #800000">shi</span><span style="color: #800000">"</span><span style="color: #000000">).scalar()
</span><span style="color: #0000ff">print</span><span style="color: #000000">(query)
shi</span>

三：Query通过text()可以使用字符串

<span style="color: #008000">#</span><span style="color: #008000"> for user in session.query(User).filter(text("id<224")).order_by(text("id")).all():</span><span style="color: #008000">
#</span><span style="color: #008000">     print(user)</span>
<span style="color: #008000">#</span><span style="color: #008000">通过params传递参数,使用:来分开参数</span>
<span style="color: #008000">#</span><span style="color: #008000"> for user in session.query(User).filter(text("id>:value and name=:name")).params(value=1,name="fred").order_by(User.id).one():</span><span style="color: #008000">
#</span><span style="color: #008000">     print(user)</span>
<span style="color: #0000ff">for</span> user <span style="color: #0000ff">in</span> session.query(User).from_statement(text(<span style="color: #800000">"</span><span style="color: #800000">select * from users where name=:name</span><span style="color: #800000">"</span>)).params(name=<span style="color: #800000">"</span><span style="color: #800000">shi</span><span style="color: #800000">"</span><span style="color: #000000">).all():
    </span><span style="color: #0000ff">print</span>(user)

四：count()方法用于确定SQL语句将返回多少行

<span style="color: #0000ff">print</span>(session.query(User).filter(User.name.like(<span style="color: #800000">"</span><span style="color: #800000">shi%</span><span style="color: #800000">"</span><span style="color: #000000">)).count())
    使用func.count()统计
</span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> func
</span><span style="color: #0000ff">for</span> row <span style="color: #0000ff">in</span><span style="color: #000000"> session.query(func.count(User.name),User.name).group_by(User.name).all():
    </span><span style="color: #0000ff">print</span><span style="color: #000000">(row)
    －－－－－－－－－－－－
    (</span>1, <span style="color: #800000">‘</span><span style="color: #800000">shi</span><span style="color: #800000">‘</span><span style="color: #000000">)
(</span>1, <span style="color: #800000">‘</span><span style="color: #800000">shi2</span><span style="color: #800000">‘</span><span style="color: #000000">)
select count(</span>*) <span style="color: #0000ff">from</span><span style="color: #000000"> table
</span>==session.query(func.count(<span style="color: #800000">‘</span><span style="color: #800000">*</span><span style="color: #800000">‘</span>)).select_from(User).scalar()

SQLAlchemy-对象关系教程ORM-Relationship

标签：统计   特定   tab   一个   logs   通过   rom   ext   multi