def user_vote_change_comments(period=None, user=None): rel = Vote.rel(Account, Comment) type = tdb.rel_types_id[rel._type_id] # rt = rel table # dt = data table rt, account_tt, comment_tt, dt = type.rel_table aliases = tdb.alias_generator() author_dt = dt.alias(aliases.next()) amount = sa.cast(rt.c.name, sa.Integer) cols = [ author_dt.c.value, sa.func.sum(sa.case([(amount > 0, amount)], else_=0)), sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)), ] query = sa.and_( author_dt.c.thing_id == rt.c.rel_id, author_dt.c.key == "author_id", comment_tt.c.thing_id == rt.c.thing2_id ) if period is not None: earliest = datetime.now(g.tz) - timedelta(0, period) query.clauses.extend((rt.c.date >= earliest, comment_tt.c.date >= earliest)) if user is not None: query.clauses.append(author_dt.c.value == str(user._id)) s = sa.select(cols, query, group_by=author_dt.c.value) rows = s.execute().fetchall() return [(int(r[0]), (r[1], r[2])) for r in rows]
def user_karma_adjustments(period=None, user=None): acct_info = tdb.types_id[Account._type_id] acct_thing, acct_data = acct_info.thing_table, acct_info.data_table[0] adj_info = tdb.types_id[KarmaAdjustment._type_id] adj_thing, adj_data = adj_info.thing_table, adj_info.data_table[0] aliases = tdb.alias_generator() adj_data_2 = adj_data.alias(aliases.next()) amount = sa.cast(adj_data_2.c.value, sa.Integer) cols = [ adj_data.c.value, sa.func.sum(sa.case([(amount > 0, amount)], else_=0)), sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)), ] query = sa.and_( adj_data.c.thing_id == adj_thing.c.thing_id, adj_data.c.key == "account_id", adj_data.c.thing_id == adj_data_2.c.thing_id, adj_data_2.c.key == "amount", ) if period is not None: earliest = datetime.now(g.tz) - timedelta(0, period) query.clauses.append(adj_thing.c.date >= earliest) if user is not None: query.clauses.append(adj_data.c.value == str(user._id)) s = sa.select(cols, query, group_by=adj_data.c.value) rows = s.execute().fetchall() return [(int(r[0]), (r[1], r[2])) for r in rows]
def query_person_contacts(): return ( DBSession.query( Person.id.label('person_id'), func.array_to_string( func.array_agg( case([(Contact.contact_type == 'phone', Contact.contact)]) ), ', ' ).label('phone'), func.array_to_string( func.array_agg( case([(Contact.contact_type == 'email', Contact.contact)]) ), ', ' ).label('email'), func.array_to_string( func.array_agg( case([(Contact.contact_type == 'skype', Contact.contact)]) ), ', ' ).label('skype'), ) .join(Contact, Person.contacts) .group_by(Person.id) )
def _manage_position_gap(self, connection, session_objs, tree_id, target, size): """Manages spaces in the tree identified by ``tree_id`` by changing the values of the left and right columns by ``size`` after the given ``target`` point.""" options = self._tree_options connection.execute( options.table.update() .values({ options.left_field: sqlalchemy.case( [(options.left_field > target, options.left_field + size)], else_ = options.left_field), options.right_field: sqlalchemy.case( [(options.right_field > target, options.right_field + size)], else_ = options.right_field), }) .where( (options.tree_id_field == tree_id) & ((options.left_field > target) | (options.right_field > target)) )) for obj in session_objs: obj_tree_id = getattr(obj, options.tree_id_field.name) if obj_tree_id != tree_id: continue obj_left = getattr(obj, options.left_field.name) obj_right = getattr(obj, options.right_field.name) if obj_left > target: setattr(obj, options.left_field.name, obj_left + size) if obj_right > target: setattr(obj, options.right_field.name, obj_right + size)
def before_insert(mapper, connection, instance): if not instance.parent: instance.left = 1 instance.right = 2 else: personnel = mapper.mapped_table right_most_sibling = connection.scalar( select([personnel.c.rgt]). where(personnel.c.emp == instance.parent.emp) ) connection.execute( personnel.update( personnel.c.rgt >= right_most_sibling).values( lft=case( [(personnel.c.lft > right_most_sibling, personnel.c.lft + 2)], else_=personnel.c.lft ), rgt=case( [(personnel.c.rgt >= right_most_sibling, personnel.c.rgt + 2)], else_=personnel.c.rgt ) ) ) instance.left = right_most_sibling instance.right = right_most_sibling + 1
def _ops_for_date_range(self, balance_uids, start_date, end_date, tags = [], change_categories = []): model = self.get_sa_model() db = self.get_sa_session() conditions = [or_(*[model.BalanceChange.balance_uid == balance_uid for balance_uid in balance_uids])] if start_date: conditions.append(model.BalanceChange.occurred_on >= start_date) if end_date: conditions.append(model.BalanceChange.occurred_on <= end_date) if isinstance(tags, list) and len(tags) > 0: conditions.extend([model.BalanceChange.tags.any(tag=tag.strip().lower()) for tag in tags if tag is not None and tag.strip() != '']) if isinstance(change_categories, list) and len(change_categories) > 0: conditions.extend([model.BalanceChange.change_category_uid == value.strip() for value in change_categories if value is not None and value.strip() != '']) try: summary = db.execute(select([ func.coalesce(func.sum( case([[model.BalanceChange.amount<0, model.BalanceChange.amount]], else_=0).label("expenses"))), func.coalesce(func.sum( case([[model.BalanceChange.amount>0, model.BalanceChange.amount]], else_=0).label("incomes"))) ], and_(*conditions), from_obj=[model.balance_changes_table])).fetchone() return { "expenses": summary[0], "incomes": summary[1], } except: log.error(_("Can't get summary"), exc_info=1) return 0
def generate(): users = db_session.query(User.first_name, User.last_name, case([(User.email != None, User.email)], else_=''), case([(User.dni != None, User.dni)], else_=''), case([(User.phone != None, User.phone)], else_=''), case([(User.enabled == '1', 'yes')], else_='no'), case([(Department.name != None, Department.name)], else_=''), func.date_format(User.created_at, '%Y-%m-%d %H:%i:%s') ).outerjoin(Department) row_columns = ('Name', 'Lastname', 'Email', 'Dni', 'Phone', 'Complete', 'Department', 'Created_at',) if enabled: if enabled == '1': users = users.filter(User.enabled == True) if enabled == '0': users = users.filter(User.enabled == False) yield ','.join(row_columns) + '\n' for row in users.all(): yield ','.join(row) + '\n'
def _unparent_and_close_gap(self, node, connection, table): gap_size = node.rgt - node.lft + 1 lft_rgt_change = gap_lft = node.lft - 1 maxrgt = connection.scalar(func.max(table.c.rgt)) connection.execute( table.update().values( lft = case([ (and_(table.c.lft >= node.lft, table.c.lft <= node.rgt), table.c.lft - lft_rgt_change), (table.c.lft > gap_lft, table.c.lft - gap_size), ], else_ = table.c.lft ), rgt = case([ (and_(table.c.rgt >= node.lft, table.c.rgt <= node.rgt), table.c.rgt - lft_rgt_change), (table.c.rgt > gap_lft, table.c.rgt - gap_size) ], else_ = table.c.rgt ), parent_id = case([(table.c.id == node.id, None)], else_ = table.c.parent_id ) ) ) node.lft = maxrgt - 1 node.rgt = maxrgt
def reports(self): c.title = "Sales Dashboard" c.items = c.user.programs if len(c.items) > 0: nullToken = c.nullToken """Each row is (program_id, date, numPurchases, numAffiliatePurchases, affiliateTotal, purchaseTotal """ #JOIN was unnecessary #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).join((Product, and_(Order.seller_user_id==c.user.id, Order.product_id==Product.id))).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all() c.orders = Session_.query(Order.program_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('numPurchases'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases'), func.sum(case([(Order.affiliate_user_id != None, Order.amount)], else_=0)).label('affiliateTotal'), func.sum(Order.amount).label('purchaseTotal'), ).filter(Order.merchant_user_id==c.user.id).filter(Order.date >= c.lowerDate).filter(Order.date <= c.upperDate).group_by(Order.program_id).group_by(Order.date).all() #c.days = Session_.query(func.datediff(Transaction.date, c.lowerDate), func.sum(Transaction.amount)).filter(Transaction.user_id==c.user.id).filter(Transaction.date >= c.lowerDate).filter(Transaction.date < c.upperDate).group_by(Transaction.date).all() """SINGLE PRODUCT FOR OWNER""" #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).filter(Order.product_id==c.product.id).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all() #c.impressions = Session_.query(Impression.product_id, func.datediff(Impression.date, c.lowerDate), func.count('*').label('total'), func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), func.sum(case([(Impression.order_ts != nullToken, 1)],else_=0)), func.sum(case([(and_(Impression.affiliate_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('buyConversions'), func.sum(case([(and_(Impression.purchase_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('purchaseConversions')).filter(Impression.product_id==c.product.id).filter(Impression.date >= c.lowerDate).filter(Impression.date < c.upperDate).group_by(Impression.product_id).group_by(Impression.date).all() """Each row is (product_id, date, affiliateViews, conversionTime)""" c.impressions = Session_.query(Impression.program_id, func.datediff(Impression.date, c.lowerDate), func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), func.avg(case([(and_(Impression.purchase_ts != nullToken, Impression.affiliate_ts != nullToken), func.time_to_sec(func.timediff(Impression.purchase_ts,Impression.affiliate_ts)))],else_=0), ).label('purchaseConversions')).join((Program, and_(c.user.id==Program.merchant_user_id, Impression.program_id==Program.id))).filter(Impression.date >= c.lowerDate).filter(Impression.date <= c.upperDate).group_by(Impression.program_id).group_by(Impression.date).all() logging.info(c.impressions) self.__Temp(c) return render('/account/accountSummary.mak')
def testcase_with_dict(self): query = select( [ case( { info_table.c.pk < 3: 'lessthan3', info_table.c.pk >= 3: 'gt3', }, else_='other'), info_table.c.pk, info_table.c.info ], from_obj=[info_table]) assert query.execute().fetchall() == [ ('lessthan3', 1, 'pk_1_data'), ('lessthan3', 2, 'pk_2_data'), ('gt3', 3, 'pk_3_data'), ('gt3', 4, 'pk_4_data'), ('gt3', 5, 'pk_5_data'), ('gt3', 6, 'pk_6_data') ] simple_query = select( [ case( {1: 'one', 2: 'two', }, value=info_table.c.pk, else_='other'), info_table.c.pk ], whereclause=info_table.c.pk < 4, from_obj=[info_table]) assert simple_query.execute().fetchall() == [ ('one', 1), ('two', 2), ('other', 3), ]
def strategy_outline(name,goal): P=models.Projects.query.all() project=models.Projects.query.filter_by(id=name).first() pgoal=models.Goals.query.filter_by(id=goal).first() S=pgoal.strategies.all() sform=strategy_form(request.values) delete_form=DeleteRow_form() q_sum = (db.session.query( Projects.id.label("project_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), Strategies.id.label("strategy_id"), Goals.id.label("goal_id"), ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id,Strategies.id).filter(Goals.id == goal) ) if request.method == 'POST' and sform.submit.data: print sform.validate() if sform.validate() == False: flash('Failed Field validation.') flash_errors(sform) return redirect(url_for('strategy_outline',name=name,goal=goal)) else: p=models.Strategies(strategy=sform.strategy.data,goa=pgoal) db.session.add(p) db.session.commit() return redirect(url_for('strategy_outline',name=name,goal=goal)) if request.method == 'POST' and delete_form.submitd.data: pstratrow = delete_form.row_id.data pstrat=models.Strategies.query.filter_by(id=pstratrow).first() db.session.delete(pstrat) db.session.commit() return redirect(url_for('strategy_outline',name=name,goal=goal)) return render_template("index_for_strategy.html",project=project,S=S,sform=sform,pgoal=pgoal,P=P,zipit=zip(S,q_sum),delete_form=delete_form)
def project_outline(name): # name=request.args.get('name') P=models.Projects.query.all() project=models.Projects.query.filter_by(id=name).first() G=project.goals.all() gform=goal_form(request.values) delete_form=DeleteRow_form() q_sum = (db.session.query( Projects.id.label("project_id"), Goals.id.label("goal_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id).filter(Projects.id == name) ) if request.method == 'POST' and gform.submit.data: if gform.validate() == False: flash('Failed Field validation.') flash_errors(gform) return redirect(url_for('project_outline', name=name)) else: p=models.Goals(goal=gform.goal.data,proj=project) db.session.add(p) db.session.commit() return redirect(url_for('project_outline', name=name)) if request.method == 'POST' and delete_form.submitd.data: pstratrow = delete_form.row_id.data pstrat=models.Goals.query.filter_by(id=pstratrow).first() db.session.delete(pstrat) db.session.commit() return redirect(url_for('project_outline',name=name)) # if request.method == 'POST' and delete_form.submit.data: # delete_row= return render_template("index_for_goal.html",project=project,G=G,gform=gform,P=P,zipit=zip(G,q_sum),delete_form=delete_form)
def context_relationship_query(contexts): """Load a list of objects related to the given contexts Args: contexts (list(int)): A list of context ids Returns: objects (list((id, type, None))): Related objects """ if not len(contexts): return [] _context = aliased(all_models.Context, name="c") _relationship = aliased(all_models.Relationship, name="rl") headers = (case([ (_relationship.destination_type == _context.related_object_type, _relationship.source_id.label('id')) ], else_=_relationship.destination_id.label('id')), case([ (_relationship.destination_type == _context.related_object_type, _relationship.source_type.label('type')) ], else_=_relationship.destination_type.label('type')), literal(None)) return db.session.query(*headers).join(_context, and_( _context.id.in_(contexts), _relationship.destination_id == _context.related_object_id, _relationship.destination_type == _context.related_object_type, )).union(db.session.query(*headers).join(_context, and_( _context.id.in_(contexts), _relationship.source_id == _context.related_object_id, _relationship.source_type == _context.related_object_type, ))).all()
def before_insert(self, mapper, connection, instance): if instance.lft and instance.rgt: return table = instance.nested_object_table() if not instance.parent_id: max = connection.scalar(func.max(table.c.rgt)) instance.lft = max + 1 instance.rgt = max + 2 else: right_most_sibling = connection.scalar( select([table.c.rgt]).where(table.c.id==instance.parent_id) ) connection.execute( table.update(table.c.rgt>=right_most_sibling).values( lft = case( [(table.c.lft>right_most_sibling, table.c.lft + 2)], else_ = table.c.lft ), rgt = case( [(table.c.rgt>=right_most_sibling, table.c.rgt + 2)], else_ = table.c.rgt ) ) ) instance.lft = right_most_sibling instance.rgt = right_most_sibling + 1
def _get_dep_statuses(self, ti, session, dep_context): TI = airflow.models.TaskInstance TR = airflow.models.TriggerRule # Checking that all upstream dependencies have succeeded if not ti.task.upstream_list: yield self._passing_status( reason="The task instance did not have any upstream tasks.") raise StopIteration if ti.task.trigger_rule == TR.DUMMY: yield self._passing_status(reason="The task had a dummy trigger rule set.") raise StopIteration # TODO(unknown): this query becomes quite expensive with dags that have many # tasks. It should be refactored to let the task report to the dag run and get the # aggregates from there. qry = ( session .query( func.coalesce(func.sum( case([(TI.state == State.SUCCESS, 1)], else_=0)), 0), func.coalesce(func.sum( case([(TI.state == State.EXCLUDED, 1)], else_=0)), 0), func.coalesce(func.sum( case([(TI.state == State.SKIPPED, 1)], else_=0)), 0), func.coalesce(func.sum( case([(TI.state == State.FAILED, 1)], else_=0)), 0), func.coalesce(func.sum( case([(TI.state == State.UPSTREAM_FAILED, 1)], else_=0)), 0), func.count(TI.task_id), ) .filter( TI.dag_id == ti.dag_id, TI.task_id.in_(ti.task.upstream_task_ids), TI.execution_date == ti.execution_date, TI.state.in_([ State.SUCCESS, State.FAILED, State.EXCLUDED, State.UPSTREAM_FAILED, State.SKIPPED]), ) ) successes, excluded, skipped, failed, upstream_failed, done = qry.first() # Add excluded tasks into successful tasks as they are equivalent for # dependency purposes. This is done in this way, not using the # state_for_dependents function, due to the constraints of SQLAlchemy # queries. successes = successes + excluded for dep_status in self._evaluate_trigger_rule( ti=ti, successes=successes, skipped=skipped, failed=failed, upstream_failed=upstream_failed, done=done, flag_upstream_failed=dep_context.flag_upstream_failed, session=session): yield dep_status
def before_insert(mapper, connection, instance): print "making adjustments before insertion" # If the new term has no parent, connect to root if instance.parent == None: category = mapper.mapped_table values = connection.execute(select([category]).where(category.c.name == "TREE_ROOT")).first().values() parent = Category() parent.name = values[0] parent.level = values[2] parent.left = values[3] parent.right = values[4] instance.parent = parent category = mapper.mapped_table # Find right most sibling's right value right_most_sibling = connection.scalar(select([category.c.rgt]).where(category.c.name == instance.parent.name)) # Update all values greater than rightmost sibiling connection.execute( category.update(category.c.rgt >= right_most_sibling).values( # Update if left bound in greater than rightmost sibling lft=case([(category.c.lft > right_most_sibling, category.c.lft + 2)], else_=category.c.lft), # Update if right bound is greater than right most sibling rgt=case([(category.c.rgt >= right_most_sibling, category.c.rgt + 2)], else_=category.c.rgt), ) ) instance.left = right_most_sibling instance.right = right_most_sibling + 1 instance.level = instance.parent.level + 1
def test_text_doesnt_explode(self): for s in [ select( [ case( [ ( info_table.c.info == 'pk_4_data', text("'yes'"))], else_=text("'no'")) ]).order_by(info_table.c.info), select( [ case( [ ( info_table.c.info == 'pk_4_data', literal_column("'yes'"))], else_=literal_column("'no'") )] ).order_by(info_table.c.info), ]: if testing.against("firebird"): eq_(s.execute().fetchall(), [ ('no ', ), ('no ', ), ('no ', ), ('yes', ), ('no ', ), ('no ', ), ]) else: eq_(s.execute().fetchall(), [ ('no', ), ('no', ), ('no', ), ('yes', ), ('no', ), ('no', ), ])
def upgrade(): op.create_check_constraint('client_user_id_or_org_id', 'client', sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1 # NOQA ) op.create_check_constraint('permission_user_id_or_org_id', 'permission', sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1 # NOQA )
def _join_relationships(cls, id_): """Make a self-join of Relationship table to find common mappings. Returns a query with results for [(related_type, similar_id, similar_type)] where similar_id and similar_type describe a second-tier mapped object and related_type is the type of a common mapped object between "object" and "similar". """ # get all Relationships for self object_to_related = db.session.query(Relationship).filter( or_(and_(Relationship.source_type == cls.__name__, Relationship.source_id == id_), and_(Relationship.destination_type == cls.__name__, Relationship.destination_id == id_))).subquery() # define how to get id and type of "related" objects related_id_case = (case([(and_(object_to_related.c.source_id == id_, object_to_related.c.source_type == cls.__name__), object_to_related.c.destination_id)], else_=object_to_related.c.source_id) .label("related_id")) related_type_case = (case([(and_(object_to_related.c.source_id == id_, object_to_related.c.source_type == cls.__name__), object_to_related.c.destination_type)], else_=object_to_related.c.source_type) .label("related_type")) related_to_similar = aliased(Relationship, name="related_to_similar") # self-join Relationships to get "similar" id and type; save "related" type # to get the weight of this relationship later return [ db.session.query( related_type_case, related_to_similar.destination_id.label("similar_id"), related_to_similar.destination_type.label("similar_type"), ).join( related_to_similar, and_(related_id_case == related_to_similar.source_id, related_type_case == related_to_similar.source_type), ).filter( related_to_similar.source_type != "Snapshot" ), db.session.query( related_type_case, related_to_similar.source_id.label("similar_id"), related_to_similar.source_type.label("similar_type"), ).join( related_to_similar, and_(related_id_case == related_to_similar.destination_id, related_type_case == related_to_similar.destination_type), ).filter( related_to_similar.destination_type != "Snapshot" ) ]
def graphs_stats(): P=models.Projects.query.all() q_sum = (db.session.query( Projects.id.label("project_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), ).outerjoin(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id)) return render_template("graph_stats.html", P=P,q_sum=q_sum,zipit=zip(P,q_sum))
def _inter_tree_move_and_close_gap(self, connection, session_objs, node, new_tree_id, left_right_change, depth_change, parent_id=None): """Removes ``node`` from its current tree, with the given set of changes being applied to ``node`` and its descendants, closing the gap left by moving ``node`` as it does so. If ``parent_id`` is ``None``, this indicates that ``node`` is being moved to a brand new tree as its root node, and will thus have its parent field set to ``NULL``. Otherwise, ``node`` will have ``parent_id`` set for its parent field.""" options = self._tree_options tree_id = getattr(node, options.tree_id_field.name) left = getattr(node, options.left_field.name) right = getattr(node, options.right_field.name) depth = getattr(node, options.depth_field.name) gap_size = right - left + 1 connection.execute( options.table.update() .values({ options.parent_id_field: sqlalchemy.case( [(options.pk_field == getattr(node, options.pk_field.name), parent_id)], else_ = options.parent_id_field), options.tree_id_field: sqlalchemy.case( [((options.left_field >= left) & (options.left_field <= right), new_tree_id)], else_ = options.tree_id_field), options.left_field: sqlalchemy.case( [((options.left_field >= left) & (options.left_field <= right), options.left_field + left_right_change), ((options.left_field > right), options.left_field - gap_size)], else_ = options.left_field), options.right_field: sqlalchemy.case( [((options.right_field >= left) & (options.right_field <= right), options.right_field + left_right_change), ((options.right_field > right), options.right_field - gap_size)], else_ = options.right_field), options.depth_field: sqlalchemy.case( [((options.left_field >= left) & (options.left_field <= right), options.depth_field + depth_change)], else_ = options.depth_field), }) .where(options.tree_id_field == tree_id)) for obj in session_objs: obj_tree_id = getattr(obj, options.tree_id_field.name) if obj_tree_id != tree_id: continue obj_left = getattr(obj, options.left_field.name) obj_right = getattr(obj, options.right_field.name) obj_depth = getattr(obj, options.depth_field.name) if obj_left >= left and obj_left <= right: setattr(obj, options.tree_id_field.name, new_tree_id) setattr(obj, options.left_field.name, obj_left + left_right_change) setattr(obj, options.depth_field.name, obj_depth + depth_change) elif obj_left > right: setattr(obj, options.left_field.name, obj_left - gap_size) if obj_right >= left and obj_right <= right: setattr(obj, options.right_field.name, obj_right + left_right_change) elif obj_right > right: setattr(obj, options.right_field.name, obj_right - gap_size) setattr(node, options.parent_id_field.name, parent_id)
def _get_permissions_query(self, session, identifier): """ select domain, json_agg(parts) as permissions from (select domain, row_to_json(r) as parts from (select domain, action, array_agg(distinct target) as target from (select (case when domain is null then '*' else domain end) as domain, (case when target is null then '*' else target end) as target, array_agg(distinct (case when action is null then '*' else action end)) as action from permission group by domain, target ) x group by domain, action) r) parts group by domain; """ thedomain = case([(Domain.name == None, "*")], else_=Domain.name) theaction = case([(Action.name == None, "*")], else_=Action.name) theresource = case([(Resource.name == None, "*")], else_=Resource.name) action_agg = func.array_agg(theaction.distinct()) stmt1 = ( session.query( Permission.domain_id, thedomain.label("domain"), Permission.resource_id, theresource.label("resource"), action_agg.label("action"), ) .select_from(User) .join(role_membership_table, User.pk_id == role_membership_table.c.user_id) .join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id) .join(Permission, role_permission_table.c.permission_id == Permission.pk_id) .outerjoin(Domain, Permission.domain_id == Domain.pk_id) .outerjoin(Action, Permission.action_id == Action.pk_id) .outerjoin(Resource, Permission.resource_id == Resource.pk_id) .filter(User.identifier == identifier) .group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name) ).subquery() stmt2 = ( session.query(stmt1.c.domain, stmt1.c.action, func.array_agg(stmt1.c.resource.distinct()).label("resource")) .select_from(stmt1) .group_by(stmt1.c.domain, stmt1.c.action) ).subquery() stmt3 = ( session.query(stmt2.c.domain, func.row_to_json(as_row(stmt2)).label("parts")).select_from(stmt2) ).subquery() final = ( session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text)) .select_from(stmt3) .group_by(stmt3.c.domain) ) return final
def _get_future_sids_for_root_symbol(self, root_symbol, as_of_date_ns): fc_cols = self.futures_contracts.c return list(map( itemgetter('sid'), sa.select((fc_cols.sid,)).where( (fc_cols.root_symbol == root_symbol) & # Filter to contracts that are still valid. If both # exist, use the one that comes first in time (i.e. # the lower value). If either notice_date or # expiration_date is NaT, use the other. If both are # NaT, the contract cannot be included in any chain. sa.case( [ ( fc_cols.notice_date == pd.NaT.value, fc_cols.expiration_date >= as_of_date_ns ), ( fc_cols.expiration_date == pd.NaT.value, fc_cols.notice_date >= as_of_date_ns ) ], else_=( sa.func.min( fc_cols.notice_date, fc_cols.expiration_date ) >= as_of_date_ns ) ) ).order_by( # If both dates exist sort using minimum of # expiration_date and notice_date # else if one is NaT use the other. sa.case( [ ( fc_cols.expiration_date == pd.NaT.value, fc_cols.notice_date ), ( fc_cols.notice_date == pd.NaT.value, fc_cols.expiration_date ) ], else_=( sa.func.min( fc_cols.notice_date, fc_cols.expiration_date ) ) ).asc() ).execute().fetchall() ))
def test_literal_interpretation(self): t = table('test', column('col1')) assert_raises(exc.ArgumentError, case, [("x", "y")]) self.assert_compile( case([("x", "y")], value=t.c.col1), "CASE test.col1 WHEN :param_1 THEN :param_2 END") self.assert_compile( case([(t.c.col1 == 7, "y")], else_="z"), "CASE WHEN (test.col1 = :col1_1) THEN :param_1 ELSE :param_2 END")
def test_literal_interpretation(self): t = table("test", column("col1")) self.assert_compile( case([("x", "y")], value=t.c.col1), "CASE test.col1 WHEN :param_1 THEN :param_2 END", ) self.assert_compile( case([(t.c.col1 == 7, "y")], else_="z"), "CASE WHEN (test.col1 = :col1_1) THEN :param_1 ELSE :param_2 END", )
def related_assignables(): """Header for the mapped_objects join""" return db.session.query( case([ (rel2.destination_type == rel1.destination_type, rel2.source_id) ], else_=rel2.destination_id).label('id'), case([ (rel2.destination_type == rel1.destination_type, rel2.source_type) ], else_=rel2.destination_type).label('type'), rel1.context_id if context_not_role else literal('R') ).select_from(rel1)
def build_column(self, sql_table): if self.column_name: expr = case(value=sql_table.c[self.column_name], whens=self.whens, else_=self.else_) else: whens = {} for when, then in self.whens.items(): if isinstance(then, basestring): whens[text(when)] = text(then) else: whens[text(when)] = then expr = case(whens=whens, else_=self.else_) return self.aggregate_fn(expr).label(self.alias)
def add_column(conn, table, column_name, default_value=None): """ Adds given column to `audit.activity` table jsonb data columns. In the following example we reflect the changes made to our schema to activity table. :: import sqlalchemy as sa from alembic import op from postgresql_audit import add_column def upgrade(): op.add_column('article', sa.Column('created_at', sa.DateTime())) add_column(op, 'article', 'created_at') :param conn: An object that is able to execute SQL (either SQLAlchemy Connection, Engine or Alembic Operations object) :param table: The table to remove the column from :param column_name: Name of the column to add :param default_value: The default value of the column """ activity_table = get_activity_table() data = {column_name: default_value} query = ( activity_table.update() .values( old_data=sa.case( [(activity_table.c.old_data.isnot(None), jsonb_merge(activity_table.c.old_data, data))], else_=None ), changed_data=sa.case( [ ( sa.and_(activity_table.c.changed_data.isnot(None), activity_table.c.verb != "update"), jsonb_merge(activity_table.c.changed_data, data), ) ], else_=activity_table.c.changed_data, ), ) .where(activity_table.c.table_name == table) ) return conn.execute(query)
def specified_month_all( self, trans, **kwd ): params = util.Params( kwd ) msg = '' monitor_email = params.get( 'monitor_email', '*****@*****.**' ) year, month = map( int, params.get( 'month', datetime.utcnow().strftime( "%Y-%m" ) ).split( "-" ) ) start_date = date( year, month, 1 ) end_date = start_date + timedelta( days=calendar.monthrange( year, month )[1] ) month_label = start_date.strftime( "%B" ) year_label = start_date.strftime( "%Y" ) q = sa.select( ( sa.func.date( galaxy.model.Job.table.c.create_time ).label( 'date' ), sa.func.sum( sa.case( [( galaxy.model.User.table.c.email == monitor_email, 1 )], else_=0 ) ).label( 'monitor_jobs' ), sa.func.count( galaxy.model.Job.table.c.id ).label( 'total_jobs' ) ), whereclause = sa.and_( galaxy.model.Job.table.c.create_time >= start_date, galaxy.model.Job.table.c.create_time < end_date ), from_obj = [ sa.outerjoin( galaxy.model.Job.table, galaxy.model.History.table ).outerjoin( galaxy.model.User.table ) ], group_by = [ 'date' ], order_by = [ sa.desc( 'date' ) ] ) jobs = [] for row in q.execute(): jobs.append( ( row.date.strftime( "%A" ), row.date, row.total_jobs - row.monitor_jobs, row.monitor_jobs, row.total_jobs, row.date.strftime( "%d" ) ) ) return trans.fill_template( 'jobs_specified_month_all.mako', month_label=month_label, year_label=year_label, month=month, jobs=jobs, msg=msg )
def test_searched_case(self): self.con.table("alltypes") st = self.con.meta.tables["alltypes"] expr = self._case_search_case() cases = [(expr, sa.case([(st.c.f > L(0), st.c.d * L(2)), (st.c.c < L(0), st.c.a * L(2))], else_=sa.null()))] self._check_expr_cases(cases)
def e_mag(cls): """The error on the magnitude of the photometry point.""" return sa.case( [( sa.and_(cls.flux != 'NaN', cls.flux > 0, cls.fluxerr > 0), # noqa: E711 2.5 / sa.func.ln(10) * cls.fluxerr / cls.flux, )], else_=None, )
def _regex_extract(t, expr): string, pattern, index = map(t.translate, expr.op().args) result = sa.case( [( sa.func.textregexeq(string, pattern), sa.func.regex_extract(string, pattern, index + 1), )], else_='', ) return result
def snr(self): """Signal-to-noise ratio of this Photometry point.""" return sa.case( [( sa.and_(self.flux != 'NaN', self.fluxerr != 'NaN', self.fluxerr != 0), # noqa self.flux / self.fluxerr, )], else_=None, )
def mag(cls): """The magnitude of the photometry point in the AB system.""" return sa.case( [ ( sa.and_(cls.flux != 'NaN', cls.flux > 0), # noqa -2.5 * sa.func.log(cls.flux) + PHOT_ZP, ) ], else_=None, )
def test_nested_labels(engine, table): col = table.c.integer exprs = [ sqlalchemy.func.sum( sqlalchemy.func.sum(col.label("inner")).label("outer")).over(), sqlalchemy.func.sum( sqlalchemy.case([[sqlalchemy.literal(True), col.label("inner")]]).label("outer")), sqlalchemy.func.sum( sqlalchemy.func.sum( sqlalchemy.case([[ sqlalchemy.literal(True), col.label("inner") ]]).label("middle")).label("outer")).over(), ] for expr in exprs: sql = str(expr.compile(engine)) assert "inner" not in sql assert "middle" not in sql assert "outer" not in sql
def addBinStatusStr(query): return query.add_column(sqlalchemy.case([ ((ProBin.status == 0), ProBin.BinStatuses[0]), ((ProBin.status == 1), ProBin.BinStatuses[1]), ((ProBin.status == 2), ProBin.BinStatuses[2]), ((ProBin.status == 3), ProBin.BinStatuses[3]), ((ProBin.status == 4), ProBin.BinStatuses[4]), ((ProBin.status == 5), ProBin.BinStatuses[5]), ((ProBin.status == 6), ProBin.BinStatuses[6]), ], else_ = 'Unknown').label('binstatusstr'))
def test_searched_case(self): self.con.table('alltypes') st = self.con.meta.tables['alltypes'] expr = self._case_search_case() cases = [ (expr, sa.case([(st.c.f > L(0), st.c.d * L(2)), (st.c.c < L(0), st.c.a * L(2))], else_=sa.null())), ] self._check_expr_cases(cases)
def aggregate_upvotes(): """Aggregate upvotes and downvotes by feedback URL. """ rows = Feedback.query.with_entities( Feedback.url, sa.func.sum(sa.case([(Feedback.upvote == True, 1) ])).label('upvotes'), # noqa sa.func.sum(sa.case([(Feedback.upvote == False, 1) ])).label('downvotes'), # noqa sa.func.count(Feedback.upvote).label('count'), ).group_by(Feedback.url) return jsonify({ 'results': { row.url: { 'upvotes': row.upvotes, 'downvotes': row.downvotes, } for row in rows } })
def build_type_query(type_, result_spec): model = ggrc.models.get_model(type_) mapper = model._sa_class_manager.mapper columns = [] columns_indexes = {} if len(list(mapper.self_and_descendants)) == 1: type_column = sqlalchemy.literal(mapper.class_.__name__) else: # Handle polymorphic types with CASE type_column = sqlalchemy.case( value=mapper.polymorphic_on, whens={ val: mapper.class_.__name__ for val, mapper in mapper.polymorphic_map.items() }) columns.append(type_column) columns_indexes['type'] = 0 columns.append(model.id) columns_indexes['id'] = 1 columns.append(mapper.c.context_id) columns_indexes['context_id'] = 2 columns.append(mapper.c.updated_at) columns_indexes['updated_at'] = 3 conditions = {} for keys, vals in result_spec.items(): for key in keys: if key not in columns_indexes: columns_indexes[key] = len(columns) columns.append(mapper.c[key]) conditions.setdefault(keys, []).extend(vals.keys()) where_clauses = [] for keys, vals in conditions.items(): if len(keys) == 1: # If the key is singular, use `IN (...)` where_clauses.append(columns[columns_indexes[keys[0]]].in_( [v[0] for v in vals])) else: # If multiple keys, build `OR` of multiple `AND` clauses clauses = [] cols = [columns[columns_indexes[k]] for k in keys] for val in vals: # Now build OR clause with (key, val) pairs clause = [] for i, _ in enumerate(val): clause.append(cols[i] == val[i]) clauses.append(sqlalchemy.and_(*clause)) where_clauses.append(sqlalchemy.or_(*clauses)) where_clause = sqlalchemy.or_(*where_clauses) query = db.session.query(*columns).filter(where_clause) return columns_indexes, query
def _argmax(self, result): if len(result.cols) == 1: return Result([sa.literal(0)], self.from_obj) features = self._make_cte(result) max_val = Result([_max(features.cols)], features.from_obj) max_val = self._make_cte(max_val, ['_max']) argmax = sa.case([(col == max_val.cols[0], i) for i, col in enumerate(features.cols[:-1])], else_=len(features.cols) - 1) return Result([argmax], _merge(features.from_obj, max_val.from_obj))
def inner_wrapper(self, column, mostly=None, result_format=None, *args, **kwargs): if result_format is None: result_format = self.default_expectation_args["result_format"] result_format = parse_result_format(result_format) if result_format['result_format'] == 'COMPLETE': unexpected_count_limit = None else: unexpected_count_limit = result_format['partial_unexpected_count'] expected_condition = func(self, column, *args, **kwargs) count_query = sa.select([ sa.func.count().label('element_count'), sa.func.sum( sa.case([(sa.column(column) == None, 1)], else_=0) ).label('null_count'), sa.func.sum( sa.case([(sa.not_(expected_condition), 1)], else_=0) ).label('unexpected_count') ]).select_from(sa.table(self.table_name)) count_results = self.engine.execute(count_query).fetchone() unexpected_query_results = self.engine.execute( sa.select([sa.column(column)]).select_from(sa.table(self.table_name)).where(sa.not_(expected_condition)).limit(unexpected_count_limit) ) nonnull_count = count_results['element_count'] - count_results['null_count'] maybe_limited_unexpected_list = [x[column] for x in unexpected_query_results.fetchall()] success_count = nonnull_count - count_results['unexpected_count'] success, percent_success = self._calc_map_expectation_success(success_count, nonnull_count, mostly) return_obj = self._format_column_map_output( result_format, success, count_results['element_count'], nonnull_count, maybe_limited_unexpected_list, None ) return return_obj
def test_simple_case(self): self.con.table('alltypes') st = self.con.meta.tables['alltypes'] expr = self._case_simple_case() cases = [ (expr, sa.case([(st.c.g == L('foo'), L('bar')), (st.c.g == L('baz'), L('qux'))], else_='default')), ] self._check_expr_cases(cases)
def _typeof(t, expr): arg, = expr.op().args sa_arg = t.translate(arg) typ = sa.cast(sa.func.pg_typeof(sa_arg), sa.TEXT) # select pg_typeof('asdf') returns unknown so we have to check the child's # type for nullness return sa.case([ ((typ == 'unknown') & (arg.type() != dt.null), 'text'), ((typ == 'unknown') & (arg.type() == dt.null), 'null'), ], else_=typ)
def stat_plural(stats, count): plural = sqlalchemy.func.coalesce( stats.c.plural, sqlalchemy.func.coalesce(stats.c.singular, stats.c.string_id).concat("s")) if count is not None: return sqlalchemy.case( {1: sqlalchemy.func.coalesce(stats.c.singular, stats.c.string_id)}, value=count, else_=plural, ) return plural
def _assignees(self): """Attribute that is used to load all assigned People eagerly.""" rel = relationship.Relationship assignee_id = sa.case( [(rel.destination_type == person.Person.__name__, rel.destination_id)], else_=rel.source_id, ) assignable_id = sa.case( [(rel.destination_type == person.Person.__name__, rel.source_id)], else_=rel.destination_id, ) return db.relationship( person.Person, primaryjoin=lambda: self.id == assignable_id, secondary=rel.__table__, secondaryjoin=lambda: person.Person.id == assignee_id, viewonly=True, )
def test_null_in_empty_set_is_false(faux_conn): stmt = select([ sqlalchemy.case( [( sqlalchemy.null().in_( sqlalchemy.bindparam("foo", value=(), expanding=True)), sqlalchemy.true(), )], else_=sqlalchemy.false(), ) ]) in_(faux_conn.execute(stmt).fetchone()[0], (False, 0))
def _get_pairs(self, basicPairs): """ get cash_on_hand_end_period info from the latest financial report per candidate_id/candidate_election_year/cmte_id When newlest financial report not filed yet, the financial data columns will be null, take the data from the previous filing for calculation. However, when the latest fincial report filed, but COH is null (the null will be converted to 0 in final calculation) should be used. """ # Window params window_p = { 'partition_by': [ basicPairs.c.candidate_id, basicPairs.c.candidate_election_year, basicPairs.c.committee_id, ], 'order_by': [ sa.case( [ ( basicPairs.c.coverage_end_date.isnot(None), basicPairs.c.two_year_period, ) ] ) .desc() .nullslast() ], } pairs = db.session.query( basicPairs.c.candidate_id, basicPairs.c.name, basicPairs.c.party_full, basicPairs.c.incumbent_challenge_full, basicPairs.c.office, basicPairs.c.two_year_period, basicPairs.c.candidate_election_year, basicPairs.c.committee_id, basicPairs.c.receipts.label('receipts'), basicPairs.c.disbursements.label('disbursements'), sa.func.first_value(basicPairs.c.last_cash_on_hand_end_period) .over(**window_p) .label('last_cash_on_hand_end_period'), sa.func.first_value(basicPairs.c.coverage_end_date) .over(**window_p) .label('coverage_end_date'), basicPairs.c.candidate_pcc_id, ) return pairs
def new_codes(): delimiter = sa.bindparam("delimiter", " – ") places = sa.func.regexp_split_to_array( sa.func.trim( sa.func.regexp_replace(service.c.description, r"\((.*)\)", " ")), delimiter) places.type = sa.ARRAY(sa.Text, dimensions=1) s0 = sa.select([service.c.id, places.label("places"), service.c.line]).alias("s0") length = sa.func.array_length(s0.c.places, 1) short_desc = sa.case([(length == 1, s0.c.places[1])], else_=s0.c.places[1] + delimiter + s0.c.places[length]) s1 = sa.select([s0.c.id, short_desc.label("short_desc"), s0.c.line]).alias("s1") line = sa.func.regexp_replace(sa.func.lower(s1.c.line), r"[^A-Za-z0-9\.]+", "-", "g") desc = sa.func.regexp_replace(sa.func.lower(s1.c.short_desc), r"[^A-Za-z0-9\.]+", "-", "g") code = sa.case([(sa.func.length(line) <= 5, line + "-" + desc)], else_=line) s2 = sa.select([s1.c.id, s1.c.short_desc, code.label("code")]).alias("s2") row_num = sa.func.row_number().over(partition_by=s2.c.code, order_by=s2.c.id) with_row_num = s2.c.code + "-" + sa.cast(row_num, sa.Text) new_code = sa.case([(row_num == 1, s2.c.code)], else_=with_row_num) query = sa.select([ s2.c.id.label("id"), s2.c.short_desc.label("short_description"), new_code.label("code") ]).alias("dest_codes") return query
def store_challenge_results(users, challenge, stats): with model.engine.connect() as conn: conn.execute(model.challenges.update().values( num_games=model.challenges.c.num_games + 1, status=sqlalchemy.case( [ (model.challenges.c.num_games >= 30, model.ChallengeStatus.FINISHED.value), ], else_=model.ChallengeStatus.CREATED.value, ), ).where(model.challenges.c.id == challenge)) for user in users: # 4 points for 1st place, 3 for 2nd, etc. points = len(users) - user["rank"] + 1 if len(users) == 2: points += 2 if user["player_tag"] in stats.players: ships_produced = stats.players[ user["player_tag"]].ships_produced attacks_made = stats.players[user["player_tag"]].attacks_total else: ships_produced = attacks_made = 0 conn.execute(model.challenge_participants.update().values( points=model.challenge_participants.c.points + points, ships_produced=model.challenge_participants.c.ships_produced + ships_produced, attacks_made=model.challenge_participants.c.attacks_made + attacks_made, ).where( (model.challenge_participants.c.challenge_id == challenge) & (model.challenge_participants.c.user_id == user["user_id"]))) challenge_row = conn.execute( model.challenges.select( model.challenges.c.id == challenge)).first() if challenge_row and \ challenge_row["status"] == model.ChallengeStatus.FINISHED.value: winner = conn.execute( model.challenge_participants.select().order_by( model.challenge_participants.c.points.desc(), model.challenge_participants.c.ships_produced.desc(), model.challenge_participants.c.attacks_made.desc(), )).first() conn.execute(model.challenges.update().values( finished=sqlalchemy.sql.func.now(), winner=winner["user_id"], ).where(model.challenges.c.id == challenge))
def sql_read(self, column): # 3. Reading - SQL layer - append with call to .AsBinaryZM() to convert MS binary to WKB. # POINT EMPTY is handled specially since SQL Server returns WKB(MULTIPOINT EMPTY) for (POINT EMPTY).AsBinaryZM() return sa.case( ( sa.and_( InstanceFunction("STGeometryType", column) == "Point", InstanceFunction("STIsEmpty", column) == 1, ), sa.literal_column(self.EMPTY_POINT_WKB, type_=self), ), else_=InstanceFunction("AsBinaryZM", column, type_=self), )
def get_type_select_column(model): """Get column name,taking into account polymorphic types.""" mapper = model._sa_class_manager.mapper # pylint: disable=protected-access if mapper.polymorphic_on is None: type_column = sa.literal(mapper.class_.__name__) else: # Handle polymorphic types with CASE type_column = sa.case(value=mapper.polymorphic_on, whens={ val: m.class_.__name__ for val, m in mapper.polymorphic_map.items() }) return type_column
def printf(cls, bibfile, bibitem, *, label: str = 'printf'): return sa.case( (sa.and_(cls.pages != None, cls.trigger != None), sa.func.printf('**%s:%s**:%s<trigger "%s">', bibfile.name, bibitem.bibkey, cls.pages, cls.trigger)), (cls.pages != None, sa.func.printf('**%s:%s**:%s', bibfile.name, bibitem.bibkey, cls.pages)), (cls.trigger != None, sa.func.printf('**%s:%s**<trigger "%s">', bibfile.name, bibitem.bibkey, cls.trigger)), else_=sa.func.printf('**%s:%s**', bibfile.name, bibitem.bibkey)).label(label)
def _manage_space(self, size, target, tree_id, session): """Manages spaces in the tree identified by ``tree_id`` by changing the values of the left and right columns by ``size`` after the given ``target`` point.""" options = self._tree_options expr = options.table.update() \ .values({ options.left_field: sqlalchemy.case( [(options.left_field > target, options.left_field + size)], else_ = options.left_field), options.right_field: sqlalchemy.case( [(options.right_field > target, options.right_field + size)], else_ = options.right_field), }) \ .where( (options.tree_id_field == tree_id) & ((options.left_field > target) | (options.right_field > target)) ) session.execute(expr)
def expect_column_median_to_be_between(self, column, min_value=None, max_value=None, result_format=None, include_config=False, catch_exceptions=None, meta=None): if min_value is None and max_value is None: raise ValueError("min_value and max_value cannot both be None") # Inspiration from https://stackoverflow.com/questions/942620/missing-median-aggregate-function-in-django count_query = self.engine.execute( sa.select([ sa.func.count().label("element_count"), sa.func.sum(sa.case([(sa.column(column) == None, 1)], else_=0)).label('null_count') ]).select_from(sa.table(self.table_name))) element_values = self.engine.execute( sa.select(column).order_by(column).where( sa.column(column) != None).select_from( sa.table(self.table_name))) # Fetch the Element count, null count, and sorted/null dropped column values elements = count_query.fetchone() column_values = list(element_values.fetchall()) # The number of non-null/non-ignored values nonnull_count = elements['element_count'] - elements['null_count'] if nonnull_count % 2 == 0: # An even number of column values: take the average of the two center values column_median = ( column_values[nonnull_count // 2 - 1][0] + # left center value column_values[nonnull_count // 2][0] # right center value ) / 2.0 # Average center values else: # An odd number of column values, we can just take the center value column_median = column_values[nonnull_count // 2][0] # True center value return { 'success': ((min_value is None) or (min_value <= column_median)) and ((max_value is None) or (column_median <= max_value)), 'result': { 'observed_value': column_median } }
def bulk_update_ehr_status_with_session(session, parameter_sets): query = (sqlalchemy.update(ParticipantSummary).where( ParticipantSummary.participantId == sqlalchemy.bindparam('pid')).values({ ParticipantSummary.ehrStatus.name: EhrStatus.PRESENT, ParticipantSummary.ehrUpdateTime: sqlalchemy.bindparam('receipt_time'), ParticipantSummary.ehrReceiptTime: sqlalchemy.case([(ParticipantSummary.ehrReceiptTime == None, sqlalchemy.bindparam('receipt_time'))], else_=ParticipantSummary.ehrReceiptTime), })) return session.execute(query, parameter_sets)
def printf(cls): return sa.case([ (sa.and_(cls.pages != None, cls.trigger != None), sa.func.printf('**%s:%s**:%s<trigger "%s">', cls.bibfile, cls.bibkey, cls.pages, cls.trigger)), (cls.pages != None, sa.func.printf('**s:%s**:%s', cls.bibfile, cls.bibkey, cls.pages)), (cls.trigger != None, sa.func.printf('**%s:%s**<trigger "%s">', cls.bibfile, cls.bibkey, cls.trigger)), ], else_=sa.func.printf('**%s:%s**', cls.bibfile, cls.bibkey))
def load(): engine = sa.create_engine(CONNECTION) metadata = sa.MetaData(bind=engine) table = metadata.Table("customer", reflect=True) target_type = sa.case(CUSTOMER_TYPE_MAPPING, else_="unknown") selection = [table.c.id, table.c.name, table.c.type] source = sa.select(selection, from_obj=table) ...
def top_users(): type = tdb.types_id[Account._type_id] tt, dt = type.thing_table, type.data_table[0] aliases = tdb.alias_generator() karma = dt.alias(aliases.next()) cases = [(karma.c.key.like('%_link_karma'), sa.cast(karma.c.value, sa.Integer) * g.post_karma_multiplier)] for subreddit in subreddits_with_custom_karma_multiplier(): key = "%s_link_karma" % subreddit.name cases.insert(0, (karma.c.key == key, sa.cast(karma.c.value, sa.Integer) * subreddit.post_karma_multiplier)) s = sa.select([tt.c.thing_id], sa.and_(tt.c.spam == False, tt.c.deleted == False, karma.c.thing_id == tt.c.thing_id, karma.c.key.like('%_karma')), group_by=[tt.c.thing_id], order_by=sa.desc( sa.func.sum( sa.case(cases, else_=sa.cast(karma.c.value, sa.Integer)))), limit=10) # Translation of query: # SELECT # reddit_thing_account.thing_id # FROM # reddit_thing_account, # reddit_data_account # WHERE # (reddit_thing_account.spam = 'f' AND # reddit_thing_account.deleted = 'f' AND # reddit_thing_account.thing_id = reddit_data_account.thing_id AND # reddit_data_account.key LIKE '%_karma') # GROUP BY # reddit_thing_account.thing_id # ORDER BY # sum( # CASE # WHEN reddit_data_account.key = 'lesswrong_link_karma' THEN # CAST(reddit_data_account.value AS INTEGER) * 10 # ELSE CAST(reddit_data_account.value AS INTEGER) # END # ) DESC # LIMIT 10 rows = s.execute().fetchall() return [r.thing_id for r in rows]
def jsonf(cls, *, sort_keys: bool, optional: bool = False, label: str = 'jsonf'): mapping = json_object(isohid=cls.isohid, comment_type=cls.comment_type, ethnologue_versions=cls.ethnologue_versions, comment=cls.comment, sort_keys_=sort_keys) if optional: return sa.case((cls.languoid_id == None, None), else_=mapping).label(label) return mapping.label(label)
def fetch_payroll_report(): from app.models import TimeLog return (db.select([ TimeLog.employee_id.label('employee_id'), sa.func.year(TimeLog.date).label('year'), sa.func.month(TimeLog.date).label('month'), (sa.func.day(TimeLog.date) <= 15).label('first_half'), sa.func.sum( sa.case(value=TimeLog.job_group, whens={ 'A': TimeLog.hours_worked * 20.0, 'B': TimeLog.hours_worked * 30.0 })).label('amount_paid') ]).group_by('employee_id', 'year', 'month', 'first_half'))