折腾：
［已解决］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():
query.filter(User.name == ‘ed’)
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%’))
))
query.filter(~User.name.in_([‘ed’, ‘wendy’, ‘jack’]))
query.filter(User.name == None)
# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))
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!
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.
”