def test_all_w_comparator(self, connection):
stuff = self.tables.stuff
stmt = select(stuff.c.id).where(
stuff.c.value >= all_(select(stuff.c.value).scalar_subquery())
)
eq_(connection.execute(stmt).fetchall(), [(5,)])
def test_all_w_comparator(self):
stuff = self.tables.stuff
stmt = select([stuff.c.id]).where(
stuff.c.value >= all_(select([stuff.c.value]))
)
eq_(testing.db.execute(stmt).fetchall(), [(5,)])
def __init__(self, column_name):
column = ColumnClause(column_name)
# Postgres allows the condition "<sth> <op> ALL (<array>)" that
# is true iff for all elements of array "<sth> <op> <element>".
# This works for (in)equality but, as the order of the operands
# is preserved, it doesn't work for regexp matching, where the
# syntax is "<text> ~ <pattern>". Our regexp operates on a per
# character basis so we can work around it by concatenating the
# items of the array (using array_to_string) and match the
# regexp on the result.
empty = literal_column("''")
super(FilenameListConstraint, self).__init__(
and_(
func.array_to_string(column, empty).op("~")(
literal_column("'^[A-Za-z0-9_.%-]*$'")),
empty != all_(column),
literal_column("'.'") != all_(column),
literal_column("'..'") != all_(column)))
# print( session.query(UserDetails,User).all() )
# print( session.query(UserDetails,User) ) #cross join
# print( session.query(UserDetails,User).filter(UserDetails.id==User.id).all() ) #
# print( session.query(UserDetails,User).filter(UserDetails.id==User.id) ) #cross join
# print( session.query(User.username,UserDetails.lost_login).\
# join(UserDetails,UserDetails.id==User.id) ) #inner join
# print( session.query(User.username,UserDetails.lost_login).\
# outerjoin(UserDetails,UserDetails.id==User.id).all() ) #left join
q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
# print(q1.union(q2).all())
from sqlalchemy import all_, any_
sql_0 = session.query(UserDetails.lost_login).subquery()
print(session.query(User).filter(User.creatime > all_(sql_0)).all())
print(session.query(User).filter(User.creatime > any_(sql_0)).all())
#原生sql
sql_1 = '''
select * from `user`
'''
row = session.execute(sql_1)
# print(row,dir(row))
# print(row.fetchone())
# print(row.fetchmany())
# print(row.fetchall())
for i in row:
pass
# print(i)
def yadcf_multi_select(expr, value):
options = map(lambda x: "%" + x + "%", value.split('|'))
logger.debug('yadcf_multi_select: in %s', options)
return expr.cast(Text).ilike(all_(options))
def recurse_availability_up_tree(channel_id):
bridge = Bridge(app_name=CONTENT_APP_NAME)
ContentNodeClass = bridge.get_class(ContentNode)
ContentNodeTable = bridge.get_table(ContentNode)
connection = bridge.get_connection()
node_depth = bridge.session.query(func.max(
ContentNodeClass.level)).scalar()
logging.info(
'Setting availability of ContentNode objects with children for {levels} levels'
.format(levels=node_depth))
child = ContentNodeTable.alias()
# start a transaction
trans = connection.begin()
# Go from the deepest level to the shallowest
start = datetime.datetime.now()
for level in range(node_depth, 0, -1):
available_nodes = select([child.c.available]).where(
and_(
child.c.available == True, # noqa
child.c.level == level,
child.c.channel_id == channel_id,
)).where(ContentNodeTable.c.id == child.c.parent_id)
# Create an expression that will resolve a boolean value for all the available children
# of a content node, whereby if they all have coach_content flagged on them, it will be true,
# but otherwise false.
# Everything after the select statement should be identical to the available_nodes expression above.
if bridge.engine.name == 'sqlite':
coach_content_nodes = select([all_(child.c.coach_content)]).where(
and_(
child.c.available == True, # noqa
child.c.level == level,
child.c.channel_id == channel_id,
)).where(ContentNodeTable.c.id == child.c.parent_id)
elif bridge.engine.name == 'postgresql':
coach_content_nodes = select(
[func.bool_and(child.c.coach_content)]).where(
and_(
child.c.available == True, # noqa
child.c.level == level,
child.c.channel_id == channel_id,
)).where(ContentNodeTable.c.id == child.c.parent_id)
logging.info(
'Setting availability of ContentNode objects with children for level {level}'
.format(level=level))
# Only modify topic availability here
connection.execute(ContentNodeTable.update().where(
and_(ContentNodeTable.c.level == level - 1,
ContentNodeTable.c.channel_id == channel_id,
ContentNodeTable.c.kind == content_kinds.TOPIC)).values(
available=exists(available_nodes)))
# Update all ContentNodes
connection.execute(ContentNodeTable.update().where(
and_(
# In this level
ContentNodeTable.c.level == level - 1,
# In this channel
ContentNodeTable.c.channel_id == channel_id,
# That are topics, and that have children that are flagged as available, with the coach content expression above
ContentNodeTable.c.kind == content_kinds.TOPIC)).where(
exists(available_nodes)).values(
coach_content=coach_content_nodes))
# commit the transaction
trans.commit()
elapsed = (datetime.datetime.now() - start)
logging.debug("Availability annotation took {} seconds".format(
elapsed.seconds))
bridge.end()