Here are the examples of the python api sqlalchemy.between taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
2 Examples
0
Example 1
Project: datanommer Source File: __init__.py
@classmethod
def grep(cls, start=None, end=None,
page=1, rows_per_page=100,
order="asc", msg_id=None,
users=None, not_users=None,
packages=None, not_packages=None,
categories=None, not_categories=None,
topics=None, not_topics=None,
contains=None,
defer=False):
""" Flexible query interface for messages.
Arguments are filters. start and end should be :mod:`datetime` objs.
Other filters should be lists of strings. They are applied in a
conjunctive-normal-form (CNF) kind of way
for example, the following::
users = ['ralph', 'lmacken']
categories = ['bodhi', 'wiki']
should return messages where
(user=='ralph' OR user=='lmacken') AND
(category=='bodhi' OR category=='wiki')
Furthermore, you can use a negative version of each argument.
users = ['ralph']
not_categories = ['bodhi', 'wiki']
should return messages where
(user == 'ralph') AND
NOT (category == 'bodhi' OR category == 'wiki')
----
If the `defer` argument evaluates to True, the query won't actually
be executed, but a SQLAlchemy query object returned instead.
"""
users = users or []
not_users = not_users or []
packages = packages or []
not_packs = not_packages or []
categories = categories or []
not_cats = not_categories or []
topics = topics or []
not_topics = not_topics or []
contains = contains or []
query = Message.query
# A little argument validation. We could provide some defaults in
# these mixed cases.. but instead we'll just leave it up to our caller.
if (start != None and end == None) or (end != None and start == None):
raise ValueError("Either both start and end must be specified "
"or neither must be specified")
if start and end:
query = query.filter(between(Message.timestamp, start, end))
if msg_id:
query = query.filter(Message.msg_id == msg_id)
# Add the four positive filters as necessary
if users:
query = query.filter(or_(
*[Message.users.any(User.name == u) for u in users]
))
if packages:
query = query.filter(or_(
*[Message.packages.any(Package.name == p) for p in packages]
))
if categories:
query = query.filter(or_(
*[Message.category == category for category in categories]
))
if topics:
query = query.filter(or_(
*[Message.topic == topic for topic in topics]
))
if contains:
query = query.filter(or_(
*[Message._msg.like('%%%s%%' % contain)
for contain in contains]
))
# And then the four negative filters as necessary
if not_users:
query = query.filter(not_(or_(
*[Message.users.any(User.name == u) for u in not_users]
)))
if not_packs:
query = query.filter(not_(or_(
*[Message.packages.any(Package.name == p) for p in not_packs]
)))
if not_cats:
query = query.filter(not_(or_(
*[Message.category == category for category in not_cats]
)))
if not_topics:
query = query.filter(not_(or_(
*[Message.topic == topic for topic in not_topics]
)))
# Finally, tag on our pagination arguments
total = query.count()
query = query.order_by(getattr(Message.timestamp, order)())
if rows_per_page is None:
pages = 1
else:
pages = int(math.ceil(total / float(rows_per_page)))
query = query.offset(rows_per_page * (page - 1)).limit(rows_per_page)
if defer:
return total, page, query
else:
# Execute!
messages = query.all()
return total, pages, messages
0
Example 2
Project: designate Source File: base.py
@staticmethod
def _apply_criterion(table, query, criterion):
if criterion is not None:
for name, value in criterion.items():
column = getattr(table.c, name)
# Wildcard value: '%'
if isinstance(value, six.string_types) and '%' in value:
query = query.where(column.like(value))
elif (isinstance(value, six.string_types) and
value.startswith('!')):
queryval = value[1:]
query = query.where(column != queryval)
elif (isinstance(value, six.string_types) and
value.startswith('<=')):
queryval = value[2:]
query = query.where(column <= queryval)
elif (isinstance(value, six.string_types) and
value.startswith('<')):
queryval = value[1:]
query = query.where(column < queryval)
elif (isinstance(value, six.string_types) and
value.startswith('>=')):
queryval = value[2:]
query = query.where(column >= queryval)
elif (isinstance(value, six.string_types) and
value.startswith('>')):
queryval = value[1:]
query = query.where(column > queryval)
elif (isinstance(value, six.string_types) and
value.startswith('BETWEEN')):
elements = [i.strip(" ") for i in
value.split(" ", 1)[1].strip(" ").split(",")]
query = query.where(between(
column, elements[0], elements[1]))
elif isinstance(value, list):
query = query.where(column.in_(value))
else:
query = query.where(column == value)
return query