最新消息:20190717 VPS服务器:Vultr新加坡,WordPress主题:大前端D8,统一介绍入口:关于

[整理]SQLAlchemy中query中filter和filter_by的区别

SQLAlchemy crifan 3942浏览 0评论

折腾:

[已解决]SQLAlchemy中filer_by中一次传入多个条件

期间,去查查,到底filter和filter_by,到底有何区别

Query API — SQLAlchemy 1.1 Documentation

->

Column Elements and Expressions — SQLAlchemy 1.1 Documentation

好像是:

在column的名字前加上:users_table.c

python – sqlalchemy filter multiple columns – Stack Overflow

-》又好像是:

应该写成类名加上column名?

sqlalchemy filter_by multiple parameters

python – How do I construct a slightly more complex filter using or_ or and_ in sqlalchemy – Stack Overflow

改为:

    todayEventList = Event.query.filter_by(and_(Event.user_openid == curUser.openid, Event.start_date >= todayStart, Event.start_date <= todayEnd)).all()

结果:

  File “/root/html/SIPEvents/sipevents/views.py”, line 396, in index
    todayEventList = Event.query.filter_by(and_(Event.user_openid == curUser.openid, Event.start_date >= todayStart, Event.start_date <= todayEnd)).all()
TypeError: filter_by() takes exactly 1 argument (2 given)

估计是要把filter_by改为filter?

Query API — SQLAlchemy 1.1 Documentation

说是filter_by也支持多个参数啊。

SQL Expression Language Tutorial — SQLAlchemy 1.1 Documentation

sqlalchemy 教程 query

query.filter_by

使用SQLAlchemy – 廖雪峰的官方网站

<code>user = session.query(User).filter(User.id=='5').one()
</code>

SQLAlchemy入门 | 野猪林

<code>jack = session.query(User).filter_by(name='jack').one()
</code>

SQLAlchemy教程(一) – 小明明s à domicile

<code>statement = user_table.select(and_( user_table.c.created &gt;= date(2007,1,1), user_table.c.created &lt; date(2008,1,1))
result = statement.execute()  #检索
</code>

query filter_by sqlalchemy

->filter_by(), which uses keyword arguments

for name, in session.query(User.name).\
…             filter_by(fullname=’Ed Jones’):
…    print(name)

filter(), which uses more flexible SQL expression language constructs. These allow you to use regular Python operators with the class-level attributes on your mapped class:

for name, in session.query(User.name).\
…             filter(User.fullname==’Ed Jones’):
…    print(name)

python – What’s the difference between filter and filter_by in SQLAlchemy? – Stack Overflow

Object Relational Tutorial — SQLAlchemy 1.1 Documentation

->filter中,要用:类名.列名 两个等号 去判断

而filter_by中,只需要用:(不带类名的)列名 单个等号 就可以判断

-》filter中,语法更加贴近于,类似于,Python的语法。

-》此处好像是:

or_() 和 and_() 都只针对于 filter

[总结]

SQLAlchemy中的query,有filter和filter_by两个函数,其中:

  • filter -》 column == expression
    • 传入参数的写法,要用:类名.列名 两个等号 去判断
    • 举例:
    • query(User.name).filter(User.fullname==’Ed Jones’)
    • 且更复杂的查询的语法,比如_and(),or_()等多个条件的查询,只支持filter
    • 举例:
    • query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))
    • query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))
  • filter_by -》keyword = expression
    • 传入参数的写法,只需要用:(不带类名的)列名 单个等号 就可以判断。
    • -》filter中,语法更加贴近于,类似于,Python的语法。
    • 举例:
    • query(User.name).filter_by(fullname=’Ed Jones’)

【后记】

Query API — SQLAlchemy 1.1 Documentation

filter(*criterion)

apply the given filtering criterion to a copy of this Query, using SQL expressions.
e.g.:
session.query(MyClass).filter(MyClass.name == ‘some name’)
Multiple criteria may be specified as comma separated; the effect is that they will be joined together using the and_() function:
session.query(MyClass).\
    filter(MyClass.name == ‘some name’, MyClass.id > 5)
The criterion is any SQL expression object applicable to the WHERE clause of a select. String expressions are coerced into SQL expression constructs via the text() construct.
See also
Query.filter_by() – filter on keyword expressions.

filter_by(**kwargs)

apply the given filtering criterion to a copy of this Query, using keyword expressions.
e.g.:
session.query(MyClass).filter_by(name = ‘some name’)
Multiple criteria may be specified as comma separated; the effect is that they will be joined together using the and_() function:
session.query(MyClass).\
    filter_by(name = ‘some name’, id = 5)
The keyword expressions are extracted from the primary entity of the query, or the last entity that was the target of a call to Query.join().
See also
Query.filter() – filter on SQL expressions.

从上述的官网的介绍中,很明显可以看出来:

  • filter:使用的是SQL的查询语句
    • -》所以写法是ClassName.PropertyName == XXX
  • filter_by:使用的是关键字表达式
    • -》所以写法是:PropertyName=xxx

转载请注明:在路上 » [整理]SQLAlchemy中query中filter和filter_by的区别

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
60 queries in 0.086 seconds, using 18.36MB memory