最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

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

SQLAlchemy crifan 7491浏览 0评论

折腾:

[已解决]Flask中对于SQLAlachemy中检索特定时间内的条目

期间,需要搞懂:

filter_by时一次传入多个参数

此处可能是两个datetime的限定条件

sqlalchemy filter by multiple

python – sqlalchemy filter multiple columns – Stack Overflow

from sqlalchemy import or_
query = meta.Session.query(User).filter(or_(User.firstname.like(searchVar),
                                            User.lastname.like(searchVar)))
query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
                                 filter(User.lastname.like(searchVar2))

Column Elements and Expressions — SQLAlchemy 1.1 Documentation

from sqlalchemy import or_
stmt = select([users_table]).where(
                or_(
                    users_table.c.name == ‘wendy’,
                    users_table.c.name == ‘jack’
                )
            )

python – Flask-SQLAlchemy: multiple filters through one relation – Stack Overflow

-》还是用

or_(condition1, condition2)

吧。

sqlalchemy – Query for multiple values at once – Stack Overflow

sqlalchemy multiple filter_by

Query API — SQLAlchemy 1.1 Documentation

结果出错:

[已解决]SQLAlchemy中查询出错:SyntaxError: keyword can’t be an expression

结果:

[已解决]SQLAlchemy查询出错:SyntaxError: non-keyword arg after keyword arg

出错:

  File “/root/html/SIPEvents/sipevents/views.py”, line 395, in index
    and_(user_openid == curUser.openid, start_date >= todayStart, start_date <= todayEnd)).all()
NameError: global name ‘user_openid’ is not defined

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

去试试:

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

终于可以了。

最后是:

    dateFormat = “%Y-%m-%d”
    timeFormat = “%H:%M:%S”
    datetimeFormat = dateFormat + ” ” + timeFormat
    app.logger.debug(‘dateFormat=%s, timeFormat=%s, datetimeFormat=%s’, dateFormat, timeFormat, datetimeFormat)
    dateToday = datetime.today()
    dateTodayStr = dateToday.strftime(dateFormat)
    app.logger.debug(‘dateToday=%s, dateTodayStr=%s’, dateToday, dateTodayStr)
    todayStartStr = dateTodayStr + ” 00:00:00″
    todayEndStr = dateTodayStr + ” 23:59:59″
    app.logger.debug(‘todayStartStr=%s, todayEndStr=%s’, todayStartStr, todayEndStr)
    todayStart = datetime.strptime(todayStartStr, datetimeFormat)
    todayEnd   = datetime.strptime(todayEndStr, datetimeFormat)
    app.logger.debug(‘todayStart=%s, todayEnd=%s’, todayStart, todayEnd)
todayEventList = Event.query.filter(and_(Event.user_openid == curUser.openid, Event.start_date >= todayStart, Event.start_date <= todayEnd)).all()

输出:

<div–<——————————————————————————
DEBUG in views [/root/html/SIPEvents/sipevents/views.py:381]:
dateFormat=%Y-%m-%d, timeFormat=%H:%M:%S, datetimeFormat=%Y-%m-%d %H:%M:%S

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in views [/root/html/SIPEvents/sipevents/views.py:384]:
dateToday=2016-08-29 14:43:57.614846, dateTodayStr=2016-08-29

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in views [/root/html/SIPEvents/sipevents/views.py:387]:
todayStartStr=2016-08-29 00:00:00, todayEndStr=2016-08-29 23:59:59

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in views [/root/html/SIPEvents/sipevents/views.py:390]:
todayStart=2016-08-29 00:00:00, todayEnd=2016-08-29 23:59:59

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in views [/root/html/SIPEvents/sipevents/views.py:398]:
todayEventList=[<Event id=5 user_openid=oswjmv4X0cCXcfkIwjoDfCkeTVVY title=u’\u4eca\u5929\u505a\u5f00\u53d1′>]

<div–<——————————————————————————

[总结]

此处,想要在查询query中,使用filter去传入多个参数的话

注意:

此处好像不支持filter_by传入多个参数,只支持filter传入同时传入多个参数。

具体做法是:

使用query后面,加上filter_by,然后传入多多个参数,是用Python代码类似的语法

操作类的变量的方式,

用 TableClassName.ColumnName的方式,用 两个等号 去判断是否等于

如果是逻辑或,则用or_(condition1, condition2, …)

如果是逻辑与,则用and_(condition1, condition2, …)

此处例子是:

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

其中Event是table的类名,user_openid和start_date,end_date分别是column名

关于具体的逻辑,网上很多解释,其中官网也有很多解释,但是只有这个页面,解释的最清楚:

Object Relational Tutorial — SQLAlchemy 1.1 Documentation

[关于多个参数的写法]

看官网的例子:

OR:
from sqlalchemy import or_
query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))

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

其实也可以写成这种:

query.filter((User.name == ‘ed’) | (User.name == ‘wendy’))

和:

AND:
# use and_()
from sqlalchemy import and_
query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))
# or send multiple expressions to .filter()
query.filter(User.name == ‘ed’, User.fullname == ‘Ed Jones’)
# or chain multiple filter()/filter_by() calls
query.filter(User.name == ‘ed’).filter(User.fullname == ‘Ed Jones’)

可见:

对于逻辑与,AND,的实现,可以用:

query.filter(and_(condition1, condition2, …))

也可以用:

query.filter(condition1).filter(condition2)….

的链式写法。

也可以,直接把多个条件,直接用逗号分开:

query.filter(condition1, condition2,…)

逻辑上都是等价的,都是AND的意思。

[关于filter相关的过滤条件]

直接把官网的内容:

Object Relational Tutorial — SQLAlchemy 1.1 Documentation

拷贝过来,供参考:

Common Filter Operators

Here’s a rundown of some of the most common operators used in filter():

  • equals:

    query.filter(User.name == ‘ed’)

  • not equals:

    query.filter(User.name != ‘ed’)

  • LIKE:

    query.filter(User.name.like(%ed%’))

  • IN:

    query.filter(User.name.in_([‘ed’, ‘wendy’, ‘jack’]))

    # works with query objects too:

    query.filter(User.name.in_(

    session.query(User.name).filter(User.name.like(%ed%’))

    ))

  • NOT IN:

    query.filter(~User.name.in_([‘ed’, ‘wendy’, ‘jack’]))

  • IS NULL:

    query.filter(User.name == None)

    # alternatively, if pep8/linters are a concern

    query.filter(User.name.is_(None))

  • IS NOT NULL:

    query.filter(User.name != None)

    # alternatively, if pep8/linters are a concern

    query.filter(User.name.isnot(None))

  • AND:

    # use and_()

    from sqlalchemy import and_

    query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))

    # or send multiple expressions to .filter()

    query.filter(User.name == ‘ed’, User.fullname == ‘Ed Jones’)

    # or chain multiple filter()/filter_by() calls

    query.filter(User.name == ‘ed’).filter(User.fullname == ‘Ed Jones’)

Note

Make sure you use and_() and not the Python and operator!

  • OR:

    from sqlalchemy import or_

    query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))

Note

Make sure you use or_() and not the Python or operator!

  • MATCH:

    query.filter(User.name.match(‘wendy’))

Note

match() uses a database-specific MATCH or CONTAINS function; its behavior will vary by backend and is not available on some backends such as SQLite.

转载请注明:在路上 » [已解决]SQLAlchemy中filer_by中一次传入多个条件和参数

发表我的评论
取消评论

表情

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

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
89 queries in 0.173 seconds, using 22.12MB memory