class BoardWithColumnsQueryFilter: board_alias = alias(Board) columns_alias = alias(BoardColumn) @classmethod def base_query(cls): join = cls.board_alias.outerjoin( cls.columns_alias, cls.board_alias.c.id == cls.columns_alias.c.board_id) query = sa.select([ cls.board_alias.c.uuid.label("board_uuid"), cls.board_alias.c.name.label("board_name"), cls.columns_alias.c.name.label("column_name"), cls.columns_alias.c.uuid.label("column_uuid"), ]).select_from(join) return query @classmethod def board_with_columns_query(cls, board_uuid): query = cls.base_query().where(cls.board_alias.c.uuid == board_uuid) return query
def get_property_mod_flags_query( table, tracked_columns, mod_suffix='_mod', end_tx_column_name='end_transaction_id', tx_column_name='transaction_id', ): v1 = sa.alias(table, name='v') v2 = sa.alias(table, name='v2') primary_keys = [c.name for c in table.c if c.primary_key] return sa.select( columns=[ getattr(v1.c, column) for column in primary_keys ] + [ (sa.or_( getattr(v1.c, column) != getattr(v2.c, column), getattr(v2.c, tx_column_name).is_(None) )).label(column + mod_suffix) for column in tracked_columns ], from_obj=v1.outerjoin( v2, sa.and_( getattr(v2.c, end_tx_column_name) == getattr(v1.c, tx_column_name), *[ getattr(v2.c, pk) == getattr(v1.c, pk) for pk in primary_keys if pk != tx_column_name ] ) ) ).order_by(getattr(v1.c, tx_column_name))
class BoardParticipantsFilterQuery(AbstractBaseFilter): user_alias = alias(User, "users") user_in_board_alias = alias(UserOnBoard, "user_boards") board_alias = alias(Board) @classmethod def filter_user_uuid(cls, request: Request, query: Select, value: str): return query.where(cls.user_alias.c.uuid == value) @classmethod def filter_by_role(cls, request: Request, query: Select, value: str): return query.where(cls.user_in_board_alias.c.role == value) @classmethod def base_query(cls, board_uuid: str) -> Select: join = cls.user_in_board_alias.outerjoin( cls.board_alias, cls.user_in_board_alias.c.board_id == cls.board_alias.c.id).outerjoin( cls.user_alias, cls.user_in_board_alias.c.user_id == cls.user_alias.c.id) query = (sa.select([ cls.user_alias, cls.board_alias.c.uuid.label("board_uuid"), cls.user_in_board_alias.c.role, ]).select_from(join).where(cls.board_alias.c.uuid == board_uuid)) return query uuid = filter_user_uuid role = filter_by_role
def get_property_mod_flags_query( table, tracked_columns, mod_suffix='_mod', end_tx_column_name='end_transaction_id', tx_column_name='transaction_id', ): v1 = sa.alias(table, name='v') v2 = sa.alias(table, name='v2') primary_keys = [c.name for c in table.c if c.primary_key] return sa.select( columns=[getattr(v1.c, column) for column in primary_keys] + [(sa.or_( getattr(v1.c, column) != getattr(v2.c, column), getattr(v2.c, tx_column_name).is_(None))).label(column + mod_suffix) for column in tracked_columns], from_obj=v1.outerjoin( v2, sa.and_( getattr(v2.c, end_tx_column_name) == getattr(v1.c, tx_column_name), *[ getattr(v2.c, pk) == getattr(v1.c, pk) for pk in primary_keys if pk != tx_column_name ]))).order_by(getattr(v1.c, tx_column_name))
def teams(): team = alias(Team) subteam = alias(Team) query = db.session.query(team.c.id.label('id_1'), team.c.name.label('name_1')) \ .filter(team.c.parent_id.is_(None)) teams = get_team_hierarchy(query, team, subteam) search_term = request.args.get('search') if search_term: teams = teams.subquery(with_labels=True) name_cols = [c for c in teams.c if 'name' in c.name] teams = db.session.query(*(c.label(c.name) for c in teams.c)) \ .filter(or_(*(c.ilike(f'%%{search_term}%%') for c in name_cols))) if not teams.count(): return jsonify([]) teams = jsonify_teams(teams) teams = to_dict(teams) teams = denullify_teams(teams) return jsonify(teams)
def get_end_tx_column_query(table, end_tx_column_name='end_transaction_id', tx_column_name='transaction_id'): v1 = sa.alias(table, name='v') v2 = sa.alias(table, name='v2') v3 = sa.alias(table, name='v3') primary_keys = [c.name for c in table.c if c.primary_key] tx_criterion = sa.select([sa.func.min(getattr( v3.c, tx_column_name))]).where( sa.and_( getattr(v3.c, tx_column_name) > getattr(v1.c, tx_column_name), *[ getattr(v3.c, pk) == getattr(v1.c, pk) for pk in primary_keys if pk != tx_column_name ])) return sa.select( columns=[getattr(v1.c, column) for column in primary_keys] + [getattr(v2.c, tx_column_name).label(end_tx_column_name)], from_obj=v1.outerjoin( v2, sa.and_(getattr(v2.c, tx_column_name) == tx_criterion))).order_by( getattr(v1.c, tx_column_name))
async def select_order_by_id(conn, order_id): in_tx = sa.alias(Tx, name="in_tx") out_tx = sa.alias(Tx, name="out_tx") j_in = join(Order, in_tx, Order.in_tx == in_tx.c.id.label(name="in_tx_id")) j_out = join(j_in, out_tx, Order.out_tx == out_tx.c.id.label(name="out_tx_id")) where = Order.id == order_id q = (select([ Order.order_type, in_tx.c.coin.label("in_tx_coin"), in_tx.c.tx_id.label("in_tx_hash"), in_tx.c.from_address.label("in_tx_from"), in_tx.c.to_address.label("in_tx_to"), in_tx.c.amount.label("in_tx_amount"), in_tx.c.created_at.label("in_tx_created_at"), in_tx.c.error.label("in_tx_error"), in_tx.c.confirmations.label("in_tx_confirmations"), in_tx.c.max_confirmations.label("in_tx_max_confirmations"), out_tx.c.coin.label("out_tx_coin"), out_tx.c.tx_id.label("out_tx_hash"), out_tx.c.from_address.label("out_tx_from"), out_tx.c.to_address.label("out_tx_to"), out_tx.c.amount.label("out_tx_amount"), out_tx.c.created_at.label("out_tx_created_at"), out_tx.c.error.label("out_tx_error"), out_tx.c.confirmations.label("out_tx_confirmations"), out_tx.c.max_confirmations.label("out_tx_max_confirmations"), ]).select_from(j_in).select_from(j_out).where(where)) cursor = await conn.execute(q) order = await cursor.fetchone() return order
def _peaks_query(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) branch_ids = select([apeak.c.id]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(branch_peaks_join) root_ids = select([apeak.c.id]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(root_peaks_join) all_ids = root_ids.union_all(branch_ids) peaks = session.execute(all_ids).fetchall() return {p[0] for p in peaks}
def _get_allocations_by_provider_id(ctx, rp_id): allocs = sa.alias(_ALLOC_TBL, name="a") consumers = sa.alias(_CONSUMER_TBL, name="c") projects = sa.alias(_PROJECT_TBL, name="p") users = sa.alias(_USER_TBL, name="u") cols = [ allocs.c.id, allocs.c.resource_class_id, allocs.c.used, allocs.c.updated_at, allocs.c.created_at, consumers.c.id.label("consumer_id"), consumers.c.generation.label("consumer_generation"), sql.func.coalesce(consumers.c.uuid, allocs.c.consumer_id).label("consumer_uuid"), projects.c.id.label("project_id"), projects.c.external_id.label("project_external_id"), users.c.id.label("user_id"), users.c.external_id.label("user_external_id"), ] # TODO(jaypipes): change this join to be on ID not UUID consumers_join = sa.join(allocs, consumers, allocs.c.consumer_id == consumers.c.uuid) projects_join = sa.join(consumers_join, projects, consumers.c.project_id == projects.c.id) users_join = sa.join(projects_join, users, consumers.c.user_id == users.c.id) sel = sa.select(cols).select_from(users_join) sel = sel.where(allocs.c.resource_provider_id == rp_id) return [dict(r) for r in ctx.session.execute(sel)]
def test_double(self): """tests lazy loading with two relationships simulatneously, from the same table, using aliases. """ users, orders, User, Address, Order, addresses = ( self.tables.users, self.tables.orders, self.classes.User, self.classes.Address, self.classes.Order, self.tables.addresses) openorders = sa.alias(orders, 'openorders') closedorders = sa.alias(orders, 'closedorders') mapper(Address, addresses) mapper(Order, orders) open_mapper = mapper(Order, openorders, non_primary=True) closed_mapper = mapper(Order, closedorders, non_primary=True) mapper(User, users, properties=dict(addresses=relationship(Address, lazy=True), open_orders=relationship( open_mapper, primaryjoin=sa.and_( openorders.c.isopen == 1, users.c.id == openorders.c.user_id), lazy='select'), closed_orders=relationship( closed_mapper, primaryjoin=sa.and_( closedorders.c.isopen == 0, users.c.id == closedorders.c.user_id), lazy='select'))) q = create_session().query(User) assert [ User(id=7, addresses=[Address(id=1)], open_orders=[Order(id=3)], closed_orders=[Order(id=1), Order(id=5)]), User(id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)], open_orders=[], closed_orders=[]), User(id=9, addresses=[Address(id=5)], open_orders=[Order(id=4)], closed_orders=[Order(id=2)]), User(id=10) ] == q.all() sess = create_session() user = sess.query(User).get(7) eq_([Order(id=1), Order(id=5)], create_session().query(closed_mapper).with_parent( user, property='closed_orders').all()) eq_([Order(id=3)], create_session().query(open_mapper).with_parent( user, property='open_orders').all())
def create_ff_regression_df(session, team_stats_tbl, sched_tbl, ff_list): """Create and return a regression data frame of the four factors (ff) for each team in a matchup. Args: session: Sqlalchemy session object team_stats_tbl: mapped team stats table object sched_tbl: mapped schedule table object ff_list: List of the four factors variable Returns: A data frame with home('_h') and away('_a') stats and the margin of victory (mov). The mov is the target for a regression. The '_h' and '_a" stats are the home and away four factors in a specific matchup. """ home_stats = alias(team_stats_tbl, name='home') away_stats = alias(team_stats_tbl, name='away') sched = alias(sched_tbl, name='sched') home_stat_ff = [ getattr(home_stats.c, col) for col in ff_list if col in home_stats.c.keys() ] away_stat_ff = [ getattr(away_stats.c, col) for col in ff_list if col in away_stats.c.keys() ] sched_stats_query = session.query(sched, *home_stat_ff, *away_stat_ff).filter(sched.c['home_team_score'] > 0).\ join(home_stats, home_stats.c['id'] == sched.c['home_stats_id']).\ join(away_stats, away_stats.c['id'] == sched.c['away_stats_id']).subquery(with_labels=True) sched_stats = session.query(sched_stats_query) df = conversion.convert_sql_statement_to_table(session, sched_stats.statement) return df
def get_sharing_providers(ctx, rp_ids=None): """Returns a list of resource provider IDs (internal IDs, not UUIDs) that indicate that they share resource via an aggregate association. Shared resource providers are marked with a standard trait called MISC_SHARES_VIA_AGGREGATE. This indicates that the provider allows its inventory to be consumed by other resource providers associated via an aggregate link. For example, assume we have two compute nodes, CN_1 and CN_2, each with inventory of VCPU and MEMORY_MB but not DISK_GB (in other words, these are compute nodes with no local disk). There is a resource provider called "NFS_SHARE" that has an inventory of DISK_GB and has the MISC_SHARES_VIA_AGGREGATE trait. Both the "CN_1" and "CN_2" compute node resource providers and the "NFS_SHARE" resource provider are associated with an aggregate called "AGG_1". The scheduler needs to determine the resource providers that can fulfill a request for 2 VCPU, 1024 MEMORY_MB and 100 DISK_GB. Clearly, no single provider can satisfy the request for all three resources, since neither compute node has DISK_GB inventory and the NFS_SHARE provider has no VCPU or MEMORY_MB inventories. However, if we consider the NFS_SHARE resource provider as providing inventory of DISK_GB for both CN_1 and CN_2, we can include CN_1 and CN_2 as potential fits for the requested set of resources. To facilitate that matching query, this function returns all providers that indicate they share their inventory with providers in some aggregate. :param rp_ids: When present, returned resource providers are limited to only those in this value """ # The SQL we need to generate here looks like this: # # SELECT rp.id # FROM resource_providers AS rp # INNER JOIN resource_provider_traits AS rpt # ON rp.id = rpt.resource_provider_id # AND rpt.trait_id = ${"MISC_SHARES_VIA_AGGREGATE" trait id} # WHERE rp.id IN $(RP_IDs) sharing_trait = trait_obj.Trait.get_by_name( ctx, os_traits.MISC_SHARES_VIA_AGGREGATE) rp_tbl = sa.alias(_RP_TBL, name='rp') rpt_tbl = sa.alias(_RP_TRAIT_TBL, name='rpt') rp_to_rpt_join = sa.join( rp_tbl, rpt_tbl, sa.and_(rp_tbl.c.id == rpt_tbl.c.resource_provider_id, rpt_tbl.c.trait_id == sharing_trait.id)) sel = sa.select([rp_tbl.c.id]).select_from(rp_to_rpt_join) if rp_ids: sel = sel.where(rp_tbl.c.id.in_(rp_ids)) return set(r[0] for r in ctx.session.execute(sel))
def _as_array_query(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id ).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) branch_intensities = select([ apeak.c.intensity, anode.c.retention_time ]).where(ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id).select_from(branch_peaks_join) root_intensities = select([ apeak.c.intensity, anode.c.retention_time ]).where(ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id).select_from(root_peaks_join) all_intensities_q = root_intensities.union_all( branch_intensities).order_by(anode.c.retention_time) all_intensities = session.execute(all_intensities_q).fetchall() time = [] signal = [] current_signal = all_intensities[0][0] current_time = all_intensities[0][1] for intensity, rt in all_intensities[1:]: if abs(current_time - rt) < 1e-4: current_signal += intensity else: time.append(current_time) signal.append(current_signal) current_time = rt current_signal = intensity time.append(current_time) signal.append(current_signal) return np.array(time), np.array(signal)
def get_traits_by_provider_id(context, rp_id): t = sa.alias(_TRAIT_TBL, name='t') rpt = sa.alias(_RP_TRAIT_TBL, name='rpt') join_cond = sa.and_(t.c.id == rpt.c.trait_id, rpt.c.resource_provider_id == rp_id) join = sa.join(t, rpt, join_cond) sel = sa.select([t.c.id, t.c.name, t.c.created_at, t.c.updated_at]).select_from(join) return [dict(r) for r in context.session.execute(sel).fetchall()]
def test_double(self): """Eager loading with two relationships simultaneously, from the same table, using aliases.""" openorders = sa.alias(orders, 'openorders') closedorders = sa.alias(orders, 'closedorders') mapper(Address, addresses) mapper(Order, orders) open_mapper = mapper(Order, openorders, non_primary=True) closed_mapper = mapper(Order, closedorders, non_primary=True) mapper(User, users, properties=dict(addresses=relationship(Address, lazy='subquery', order_by=addresses.c.id), open_orders=relationship( open_mapper, primaryjoin=sa.and_( openorders.c.isopen == 1, users.c.id == openorders.c.user_id), lazy='subquery', order_by=openorders.c.id), closed_orders=relationship( closed_mapper, primaryjoin=sa.and_( closedorders.c.isopen == 0, users.c.id == closedorders.c.user_id), lazy='subquery', order_by=closedorders.c.id))) q = create_session().query(User).order_by(User.id) def go(): eq_([ User(id=7, addresses=[Address(id=1)], open_orders=[Order(id=3)], closed_orders=[Order(id=1), Order(id=5)]), User(id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)], open_orders=[], closed_orders=[]), User(id=9, addresses=[Address(id=5)], open_orders=[Order(id=4)], closed_orders=[Order(id=2)]), User(id=10) ], q.all()) self.assert_sql_count(testing.db, go, 4)
def test_double(self): """tests lazy loading with two relationships simulatneously, from the same table, using aliases. """ users, orders, User, Address, Order, addresses = (self.tables.users, self.tables.orders, self.classes.User, self.classes.Address, self.classes.Order, self.tables.addresses) openorders = sa.alias(orders, 'openorders') closedorders = sa.alias(orders, 'closedorders') mapper(Address, addresses) mapper(Order, orders) open_mapper = mapper(Order, openorders, non_primary=True) closed_mapper = mapper(Order, closedorders, non_primary=True) mapper(User, users, properties = dict( addresses = relationship(Address, lazy = True), open_orders = relationship(open_mapper, primaryjoin = sa.and_(openorders.c.isopen == 1, users.c.id==openorders.c.user_id), lazy='select'), closed_orders = relationship(closed_mapper, primaryjoin = sa.and_(closedorders.c.isopen == 0, users.c.id==closedorders.c.user_id), lazy='select') )) q = create_session().query(User) assert [ User( id=7, addresses=[Address(id=1)], open_orders = [Order(id=3)], closed_orders = [Order(id=1), Order(id=5)] ), User( id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)], open_orders = [], closed_orders = [] ), User( id=9, addresses=[Address(id=5)], open_orders = [Order(id=4)], closed_orders = [Order(id=2)] ), User(id=10) ] == q.all() sess = create_session() user = sess.query(User).get(7) assert [Order(id=1), Order(id=5)] == create_session().query(closed_mapper).with_parent(user, property='closed_orders').all() assert [Order(id=3)] == create_session().query(open_mapper).with_parent(user, property='open_orders').all()
def test_double(self): """Eager loading with two relationships simultaneously, from the same table, using aliases.""" openorders = sa.alias(orders, 'openorders') closedorders = sa.alias(orders, 'closedorders') mapper(Address, addresses) mapper(Order, orders) open_mapper = mapper(Order, openorders, non_primary=True) closed_mapper = mapper(Order, closedorders, non_primary=True) mapper(User, users, properties = dict( addresses = relationship(Address, lazy='subquery', order_by=addresses.c.id), open_orders = relationship( open_mapper, primaryjoin=sa.and_(openorders.c.isopen == 1, users.c.id==openorders.c.user_id), lazy='subquery', order_by=openorders.c.id), closed_orders = relationship( closed_mapper, primaryjoin=sa.and_(closedorders.c.isopen == 0, users.c.id==closedorders.c.user_id), lazy='subquery', order_by=closedorders.c.id))) q = create_session().query(User).order_by(User.id) def go(): eq_([ User( id=7, addresses=[Address(id=1)], open_orders = [Order(id=3)], closed_orders = [Order(id=1), Order(id=5)] ), User( id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)], open_orders = [], closed_orders = [] ), User( id=9, addresses=[Address(id=5)], open_orders = [Order(id=4)], closed_orders = [Order(id=2)] ), User(id=10) ], q.all()) self.assert_sql_count(testing.db, go, 4)
def _aj_lon_lat(self, s, new=True): start = to_time(self.nearby.start) finish = to_time(self.nearby.finish) agroup = s.query(ActivityGroup).filter( ActivityGroup.name == self.nearby.activity_group).one() lat = s.query(StatisticName.id). \ filter(StatisticName.name == LATITUDE, StatisticName.constraint == agroup).scalar() lon = s.query(StatisticName.id). \ filter(StatisticName.name == LONGITUDE, StatisticName.constraint == agroup).scalar() if not lat or not lon: log.warning( f'No {LATITUDE} or {LONGITUDE} in database for {agroup}') return sj_lat = inspect(StatisticJournal).local_table sj_lon = alias(inspect(StatisticJournal).local_table) sjf_lat = inspect(StatisticJournalFloat).local_table sjf_lon = alias(inspect(StatisticJournalFloat).local_table) aj = inspect(ActivityJournal).local_table ns = inspect(ActivitySimilarity).local_table existing_lo = select([ns.c.activity_journal_lo_id]). \ where(ns.c.constraint == self.nearby.constraint) existing_hi = select([ns.c.activity_journal_hi_id]). \ where(ns.c.constraint == self.nearby.constraint) existing = existing_lo.union(existing_hi).cte() stmt = select([sj_lat.c.source_id, sjf_lon.c.value, sjf_lat.c.value]). \ select_from(sj_lat).select_from(sj_lon).select_from(sjf_lat).select_from(sjf_lat).select_from(aj). \ where(and_(sj_lat.c.source_id == sj_lon.c.source_id, # same source sj_lat.c.time == sj_lon.c.time, # same time sj_lat.c.source_id == aj.c.id, # and associated with an activity aj.c.activity_group_id == agroup.id, # of the right group sj_lat.c.id == sjf_lat.c.id, # lat sub-class sj_lon.c.id == sjf_lon.c.id, # lon sub-class sj_lat.c.statistic_name_id == lat, # lat name sj_lon.c.statistic_name_id == lon, # lon name sj_lat.c.time >= start.timestamp(), # time limits sj_lat.c.time < finish.timestamp(), sjf_lat.c.value > self.nearby.latitude - self.nearby.height / 2, sjf_lat.c.value < self.nearby.latitude + self.nearby.height / 2, sjf_lon.c.value > self.nearby.longitude - self.nearby.width / 2, sjf_lon.c.value < self.nearby.longitude + self.nearby.width / 2)) if new: stmt = stmt.where(func.not_(sj_lat.c.source_id.in_(existing))) else: stmt = stmt.where(sj_lat.c.source_id.in_(existing)) stmt = stmt.order_by(sj_lat.c.source_id) # needed for seen logic yield from s.connection().execute(stmt)
def _as_array_query(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) branch_intensities = select([apeak.c.intensity, anode.c.retention_time]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(branch_peaks_join) root_intensities = select([apeak.c.intensity, anode.c.retention_time]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(root_peaks_join) all_intensities_q = root_intensities.union_all(branch_intensities).order_by( anode.c.retention_time) all_intensities = session.execute(all_intensities_q).fetchall() time = [] signal = [] current_signal = all_intensities[0][0] current_time = all_intensities[0][1] for intensity, rt in all_intensities[1:]: if abs(current_time - rt) < 1e-4: current_signal += intensity else: time.append(current_time) signal.append(current_signal) current_time = rt current_signal = intensity time.append(current_time) signal.append(current_signal) return np.array(time), np.array(signal)
def get_providers_with_resource(ctx, rc_id, amount, tree_root_id=None): """Returns a set of tuples of (provider ID, root provider ID) of providers that satisfy the request for a single resource class. :param ctx: Session context to use :param rc_id: Internal ID of resource class to check inventory for :param amount: Amount of resource being requested :param tree_root_id: An optional root provider ID. If provided, the results are limited to the resource providers under the given root resource provider. """ # SELECT rp.id, rp.root_provider_id # FROM resource_providers AS rp # JOIN inventories AS inv # ON rp.id = inv.resource_provider_id # AND inv.resource_class_id = $RC_ID # LEFT JOIN ( # SELECT # alloc.resource_provider_id, # SUM(allocs.used) AS used # FROM allocations AS alloc # WHERE allocs.resource_class_id = $RC_ID # GROUP BY allocs.resource_provider_id # ) AS usage # ON inv.resource_provider_id = usage.resource_provider_id # WHERE # used + $AMOUNT <= ((total - reserved) * inv.allocation_ratio) # AND inv.min_unit <= $AMOUNT # AND inv.max_unit >= $AMOUNT # AND $AMOUNT % inv.step_size == 0 rpt = sa.alias(_RP_TBL, name="rp") inv = sa.alias(_INV_TBL, name="inv") usage = _usage_select([rc_id]) rp_to_inv = sa.join( rpt, inv, sa.and_(rpt.c.id == inv.c.resource_provider_id, inv.c.resource_class_id == rc_id)) inv_to_usage = sa.outerjoin( rp_to_inv, usage, inv.c.resource_provider_id == usage.c.resource_provider_id) sel = sa.select([rpt.c.id, rpt.c.root_provider_id]) sel = sel.select_from(inv_to_usage) where_conds = _capacity_check_clause(amount, usage, inv_tbl=inv) if tree_root_id is not None: where_conds = sa.and_(rpt.c.root_provider_id == tree_root_id, where_conds) sel = sel.where(where_conds) res = ctx.session.execute(sel).fetchall() res = set((r[0], r[1]) for r in res) return res
def _adducts_query(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id ).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) branch_node_info = select([ anode.c.node_type_id, anode.c.retention_time ]).where(ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id).select_from(branch_peaks_join) root_node_info = select([ anode.c.node_type_id, anode.c.retention_time ]).where(ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id).select_from(root_peaks_join) all_node_info_q = root_node_info.union_all(branch_node_info).order_by( anode.c.retention_time) all_node_info = session.execute(all_node_info_q).fetchall() node_type_ids = set() for node_type_id, rt in all_node_info: node_type_ids.add(node_type_id) node_types = [] for ntid in node_type_ids: node_types.append( session.query(CompoundMassShift).get(ntid).convert()) return node_types
def anchors_for_sharing_providers(context, rp_ids, get_id=False): """Given a list of internal IDs of sharing providers, returns a set of tuples of (sharing provider UUID, anchor provider UUID), where each of anchor is the unique root provider of a tree associated with the same aggregate as the sharing provider. (These are the providers that can "anchor" a single AllocationRequest.) The sharing provider may or may not itself be part of a tree; in either case, an entry for this root provider is included in the result. If the sharing provider is not part of any aggregate, the empty list is returned. If get_id is True, it returns a set of tuples of (sharing provider ID, anchor provider ID) instead. """ # SELECT sps.uuid, COALESCE(rps.uuid, shr_with_sps.uuid) # FROM resource_providers AS sps # INNER JOIN resource_provider_aggregates AS shr_aggs # ON sps.id = shr_aggs.resource_provider_id # INNER JOIN resource_provider_aggregates AS shr_with_sps_aggs # ON shr_aggs.aggregate_id = shr_with_sps_aggs.aggregate_id # INNER JOIN resource_providers AS shr_with_sps # ON shr_with_sps_aggs.resource_provider_id = shr_with_sps.id # LEFT JOIN resource_providers AS rps # ON shr_with_sps.root_provider_id = rps.id # WHERE sps.id IN $(RP_IDs) rps = sa.alias(_RP_TBL, name='rps') sps = sa.alias(_RP_TBL, name='sps') shr_aggs = sa.alias(_RP_AGG_TBL, name='shr_aggs') shr_with_sps_aggs = sa.alias(_RP_AGG_TBL, name='shr_with_sps_aggs') shr_with_sps = sa.alias(_RP_TBL, name='shr_with_sps') join_chain = sa.join(sps, shr_aggs, sps.c.id == shr_aggs.c.resource_provider_id) join_chain = sa.join( join_chain, shr_with_sps_aggs, shr_aggs.c.aggregate_id == shr_with_sps_aggs.c.aggregate_id) join_chain = sa.join( join_chain, shr_with_sps, shr_with_sps_aggs.c.resource_provider_id == shr_with_sps.c.id) if get_id: sel = sa.select([sps.c.id, shr_with_sps.c.root_provider_id]) else: join_chain = sa.join(join_chain, rps, shr_with_sps.c.root_provider_id == rps.c.id) sel = sa.select([sps.c.uuid, rps.c.uuid]) sel = sel.select_from(join_chain) sel = sel.where(sps.c.id.in_(rp_ids)) return set([(r[0], r[1]) for r in context.session.execute(sel).fetchall()])
class BoardListFilterQuery(AbstractBaseFilter): user_alias = alias(User) board_alias = alias(Board) user_in_board_alias = alias(UserOnBoard) @classmethod def base_query(cls, user_id: int) -> Select: j = cls.board_alias.outerjoin( cls.user_in_board_alias, cls.board_alias.c.id == cls.user_in_board_alias.c.board_id, ).outerjoin(cls.user_alias, cls.user_in_board_alias.c.user_id == cls.user_alias.c.id) query = (sa.select([ cls.board_alias, cls.user_in_board_alias.c.role, cls.user_alias.c.uuid.label("user_uuid"), ]).select_from(j).where( cls.user_in_board_alias.c.user_id == user_id).distinct( cls.board_alias.c.id)) return query @classmethod def get_my_boards_filter(cls, request: Request, query: Select, value: bool = False) -> Select: if value: query = query.where(cls.board_alias.c.author_id == request.user.id) else: query = query.where(cls.board_alias.c.author_id != request.user.id) return query @classmethod def get_query_with_filters(cls, request: Request, model: Type["BaseModel"], **kwargs) -> Select: """ :param request: :param model: BaseModel model for validating data :param kwargs: params for base query :return: Query with filters """ base_query = cls.base_query(**kwargs) filtered = cls.add_filters_from_request(request, model, base_query) return filtered my_boards = get_my_boards_filter
def generate_insert(self): result = [] # first insert fields for which we have no change record staging_alias = catalog.aliases[self.target_alias['load'] ['staging_alias']] # create a subselect from the transform query and add column # gdw_state_dts_range = all timestamp ranges for which this row is valid select_sql = sqlalchemy.alias(self.select_sql, 'source_transform') select_sql_columns = [ select_sql.corresponding_column(c) for c in self.select_sql.c ] gdw_state_dts_range = func.prioritize_ranges( func.array_agg( func.tsrange(literal_column('gdw_state_start'), literal_column('gdw_state_end'))).over( partition_by=[ literal_column(c) for c in self.object_key_columns ], order_by=self.priority_order)).label( 'gdw_state_dts_range') select_sql_columns.append(gdw_state_dts_range) result.append(staging_alias.sql_table.insert().from_select( self.stage_col_names, select(select_sql_columns))) # select all from existing dimension. look up the object key in stage and remove from # gdw_state_dts_range that already is calculated on stage select_sql = sqlalchemy.alias(self.select_sql, 'source_transform') select_sql_columns = [ select_sql.corresponding_column(c) for c in self.select_sql.c ] gdw_state_dts_range = func.prioritize_ranges( func.array_agg( func.tsrange(literal_column('gdw_state_start'), literal_column('gdw_state_end'))).over( partition_by=[ literal_column(c) for c in self.object_key_columns ], order_by=self.priority_order)).label( 'gdw_state_dts_range') select_sql_columns.append(gdw_state_dts_range) result.append(staging_alias.sql_table.insert().from_select( self.stage_col_names, select(select_sql_columns))) return result
def execute(self, metadata, connection, filter_values): try: table = metadata.tables[self.table_name] except KeyError: raise TableNotFoundException("Unable to query table, table not found: %s" % self.table_name) asha_table = self.get_asha_table(metadata) max_date_query = sqlalchemy.select([ sqlalchemy.func.max(asha_table.c.date).label('date'), asha_table.c.case_id.label('case_id') ]) if self.filters: for filter in self.filters: max_date_query.append_whereclause(filter.build_expression(table)) max_date_query.append_group_by( asha_table.c.case_id ) max_date_subquery = sqlalchemy.alias(max_date_query, 'max_date') checklist_query = sqlalchemy.select() for column in self.columns: checklist_query.append_column(column.build_column(asha_table)) checklist_query = checklist_query.where( asha_table.c.case_id == max_date_subquery.c.case_id ).where( asha_table.c.date == max_date_subquery.c.date ) return connection.execute(checklist_query, **filter_values).fetchall()
def set_root_provider_ids(context, batch_size): """Simply sets the root_provider_id value for a provider identified by rp_id. Used in explicit online data migration via CLI. :param rp_id: Internal ID of the provider to update :param root_id: Value to set root provider to """ # UPDATE resource_providers # SET root_provider_id=resource_providers.id # WHERE resource_providers.id # IN (SELECT subq_1.id # FROM (SELECT resource_providers.id AS id # FROM resource_providers # WHERE resource_providers.root_provider_id IS NULL # LIMIT :param_1) # AS subq_1) subq_1 = context.session.query(_RP_TBL.c.id) subq_1 = subq_1.filter(_RP_TBL.c.root_provider_id.is_(None)) subq_1 = subq_1.limit(batch_size) subq_1 = sa.alias(subq_1.as_scalar(), name="subq_1") subq_2 = sa.select([subq_1.c.id]).select_from(subq_1) upd = _RP_TBL.update().where(_RP_TBL.c.id.in_(subq_2.as_scalar())) upd = upd.values(root_provider_id=_RP_TBL.c.id) res = context.session.execute(upd) return res.rowcount, res.rowcount
def find(self, _limit=None, _offset=0, _step=5000, order_by='id', **_filter): """ Performs a simple search on the table. Simply pass keyword arguments as ``filter``. :: results = table.find(country='France') results = table.find(country='France', year=1980) Using ``_limit``:: # just return the first 10 rows results = table.find(country='France', _limit=10) You can sort the results by single or multiple columns. Append a minus sign to the column name for descending order:: # sort results by a column 'year' results = table.find(country='France', order_by='year') # return all rows sorted by multiple columns (by year in descending order) results = table.find(order_by=['country', '-year']) By default :py:meth:`find() <dataset.Table.find>` will break the query into chunks of ``_step`` rows to prevent huge tables from being loaded into memory at once. For more complex queries, please use :py:meth:`db.query() <dataset.Database.query>` instead.""" self._check_dropped() if not isinstance(order_by, (list, tuple)): order_by = [order_by] order_by = [o for o in order_by if o in self.table.columns] order_by = [self._args_to_order_by(o) for o in order_by] args = self._args_to_clause(_filter) # query total number of rows first count_query = alias(self.table.select(whereclause=args, limit=_limit, offset=_offset), name='count_query_alias').count() rp = self.database.executable.execute(count_query) total_row_count = rp.fetchone()[0] if _step is None or _step is False or _step == 0: _step = total_row_count if total_row_count > _step and not order_by: _step = total_row_count log.warn("query cannot be broken into smaller sections because it is unordered") queries = [] for i in count(): qoffset = _offset + (_step * i) qlimit = _step if _limit is not None: qlimit = min(_limit - (_step * i), _step) if qlimit <= 0: break queries.append(self.table.select(whereclause=args, limit=qlimit, offset=qoffset, order_by=order_by)) return ResultIter((self.database.executable.execute(q) for q in queries))
def compute_distance(session, table, monosaccharide_names, model=GlycopeptideMatch): distance_table = make_distance_table(model) distance_table.create(session.connection()) from_entity = alias(table) to_entity = alias(table) distances = [getattr(from_entity.c, name) - getattr(to_entity.c, name) for name in monosaccharide_names] selected = [from_entity.c.id, to_entity.c.id] + distances q = session.query(*selected).join(to_entity, from_entity.c.id != to_entity.c.id).yield_per(1000) for fields in q: from_id, to_id = fields[:2] distance = sum(fields[2:]) # print from_id, to_id, distance session.execute(distance_table.insert(), [{'from_id': from_id, "to_id": to_id, "distance": distance}]) session.commit() return distance_table
def _get_provider_ids_having_all_traits(ctx, required_traits): """Returns a set of resource provider internal IDs that have ALL of the required traits. NOTE: Don't call this method with no required_traits. :param ctx: Session context to use :param required_traits: A map, keyed by trait string name, of required trait internal IDs that each provider must have associated with it :raise ValueError: If required_traits is empty or None. """ if not required_traits: raise ValueError('required_traits must not be empty') rptt = sa.alias(_RP_TRAIT_TBL, name="rpt") sel = sa.select([rptt.c.resource_provider_id]) sel = sel.where(rptt.c.trait_id.in_(required_traits.values())) sel = sel.group_by(rptt.c.resource_provider_id) # Only get the resource providers that have ALL the required traits, so we # need to GROUP BY the resource provider and ensure that the # COUNT(trait_id) is equal to the number of traits we are requiring num_traits = len(required_traits) cond = sa.func.count(rptt.c.trait_id) == num_traits sel = sel.having(cond) return set(r[0] for r in ctx.session.execute(sel))
def filter_ancestors(self, and_self=False): "The same as :meth:`filter_descendants` but filters ancestor nodes." options = self._tree_options obj = self._get_obj() #self._get_session_and_assert_flushed(obj) # Restrict ourselves to just those nodes within the same tree: tree_id = getattr(obj, self.tree_id_field.name) filter_ = self.tree_id_field == tree_id alias = sqlalchemy.alias(options.table) left_field = self.left_field filter_ &= sqlalchemy.between( getattr(alias.c, self.left_field.name), self.left_field, self.right_field) filter_ &= getattr(alias.c, self.pk_field.name) == \ getattr(obj, self.pk_field.name) if not and_self: filter_ &= self.pk_field != getattr(obj, self.pk_field.name) # WHERE tree_id = <node.tree_id> AND <node.path> LIKE path || '%' #filter_ = (self.tree_id_field == tree_id) \ # & sqlalchemy.sql.expression.literal( # path, sqlalchemy.String # ).like(options.path_field + '%') #if and_self: # filter_ &= self.depth_field <= depth #else: # filter_ &= self.depth_field < depth return filter_
def apply_default_value(self, column): if column.default: execute = self.table.migration.conn.execute val = column.default.arg table = self.table.migration.metadata.tables[self.table.name] table.append_column(column) cname = getattr(table.c, column.name) if column.default.is_callable: table2 = alias(select([table]).limit(1).where(cname.is_(None))) Table = self.table.migration.metadata.tables['system_model'] Column = self.table.migration.metadata.tables['system_column'] j1 = join(Table, Column, Table.c.name == Column.c.model) query = select([func.count()]).select_from(table) nb_row = self.table.migration.conn.execute(query).fetchone()[0] query = select([Column.c.name]).select_from(j1) query = query.where(Column.c.primary_key.is_(True)) query = query.where(Table.c.table == self.table.name) columns = [x[0] for x in execute(query).fetchall()] where = and_(*[getattr(table.c, x) == getattr(table2.c, x) for x in columns]) for offset in range(nb_row): # call for each row because the default value # could be a sequence or depend of other field query = update(table).where(where).values( {cname: val(None)}) execute(query) else: query = update(table).where(cname.is_(None)).values( {cname: val}) execute(query)
def execute(self, metadata, connection, filter_values): asha_table = self.get_asha_table(metadata) max_date_query = sqlalchemy.select([ sqlalchemy.func.max(asha_table.c.date).label('date'), asha_table.c.case_id.label('case_id') ]) if self.filters: for filter in self.filters: max_date_query.append_whereclause(filter.build_expression()) max_date_query.append_group_by( asha_table.c.case_id ) max_date_subquery = sqlalchemy.alias(max_date_query, 'max_date') checklist_query = sqlalchemy.select() for column in self.columns: checklist_query.append_column(column.build_column(asha_table)) checklist_query = checklist_query.where( asha_table.c.case_id == max_date_subquery.c.case_id ).where( asha_table.c.date == max_date_subquery.c.date ) return connection.execute(checklist_query, **filter_values).fetchall()
def execute(self, connection, filter_values): max_date_query = sqlalchemy.select([ sqlalchemy.func.max(sqlalchemy.column('completed_on')).label('completed_on'), sqlalchemy.column('case_id').label('case_id') ]).select_from(sqlalchemy.table(self.table_name)) if self.filters: for filter in self.filters: max_date_query.append_whereclause(filter.build_expression()) max_date_query.append_group_by( sqlalchemy.column('case_id') ) max_date_subquery = sqlalchemy.alias(max_date_query, 'max_date') asha_table = self.get_asha_table_name() checklist_query = sqlalchemy.select() for column in self.columns: checklist_query.append_column(column.build_column()) checklist_query = checklist_query.where( sqlalchemy.literal_column('"{}".case_id'.format(asha_table)) == max_date_subquery.c.case_id ).where( sqlalchemy.literal_column('"{}".completed_on'.format(asha_table)) == max_date_subquery.c.completed_on ).select_from(sqlalchemy.table(asha_table)) return connection.execute(checklist_query, **filter_values).fetchall()
def _provider_ids_from_root_ids(context, root_ids): """Given an iterable of internal root resource provider IDs, returns a dict, keyed by internal provider Id, of sqla objects describing those providers under the given root providers. :param root_ids: iterable of root provider IDs for trees to look up :returns: dict, keyed by internal provider Id, of sqla objects with the following attributes: id: resource provider internal id uuid: resource provider uuid parent_id: internal id of the resource provider's parent provider (None if there is no parent) root_id: internal id of the resource providers's root provider """ # SELECT # rp.id, rp.uuid, rp.parent_provider_id, rp.root_provider.id # FROM resource_providers AS rp # WHERE rp.root_provider_id IN ($root_ids) me = sa.alias(_RP_TBL, name="me") cols = [ me.c.id, me.c.uuid, me.c.parent_provider_id.label('parent_id'), me.c.root_provider_id.label('root_id'), ] sel = sa.select(cols).where( me.c.root_provider_id.in_(sa.bindparam('root_ids', expanding=True))) ret = {} for r in context.session.execute(sel, {'root_ids': list(root_ids)}): ret[r['id']] = r return ret
def test_delete_stmt_with_comma_subquery_alias_join(): parent_ = sa.alias(product) del_stmt = ( sa.delete(items) .where(items.c.order_id == orders.c.id) .where(orders.c.customer_id.in_(sa.select([customers.c.id]).where(customers.c.email.endswith("test.com")))) .where(items.c.product_id == product.c.id) .where(product.c.parent_id == parent_.c.id) .where(parent_.c.id != hammy_spam.c.ham_id) ) expected = """ DELETE FROM items USING orders, products, products AS products_1, "ham, spam" WHERE items.order_id = orders.id AND orders.customer_id IN (SELECT customers.id FROM customers WHERE (customers.email LIKE '%%' || 'test.com')) AND items.product_id = products.id AND products.parent_id = products_1.id AND products_1.id != "ham, spam".ham_id""" assert clean(compile_query(del_stmt)) == clean(expected)
def qmonosaccharide(cls, monosaccharide_name): if monosaccharide_name in cls._qmonosaccharide_cache: return cls._qmonosaccharide_cache[monosaccharide_name] symbol = alias(cls.GlycanCompositionAssociation.__table__.select().where( cls.GlycanCompositionAssociation.__table__.c.base_type == monosaccharide_name), monosaccharide_name) cls._qmonosaccharide_cache[monosaccharide_name] = symbol return symbol
def test_delete_stmt_on_alias(): parent_ = sa.alias(product) del_stmt = sa.delete(product).where(product.c.parent_id == parent_.c.id) expected = """ DELETE FROM products USING products AS products_1 WHERE products.parent_id = products_1.id""" assert clean(compile_query(del_stmt)) == clean(expected)
def _weighted_neutral_mass_query(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) branch_intensities = select([apeak.c.intensity, apeak.c.neutral_mass, anode.c.node_type_id]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(branch_peaks_join) root_intensities = select([apeak.c.intensity, apeak.c.neutral_mass, anode.c.node_type_id]).where( ChromatogramToChromatogramTreeNode.c.chromatogram_id == self.id ).select_from(root_peaks_join) all_intensity_mass_q = root_intensities.union_all(branch_intensities) all_intensity_mass = session.execute(all_intensity_mass_q).fetchall() arr = np.array(all_intensity_mass) mass = arr[:, 1] shift_ids = arr[:, 2].astype(int) distinct_shifts = set(shift_ids) for i in distinct_shifts: shift = session.query(CompoundMassShift).get(i) mass[shift_ids == i] -= shift.convert().mass intensity = arr[:, 0] return mass.dot(intensity) / intensity.sum()
def _build_median_query(self, median_id_table, median_table): """ SELECT tu.user_name, (tu.value + tl.value) / 2.0 as value FROM temp_median_ids LEFT JOIN temp_median tu ON tu.id = temp_median_ids.upper LEFT JOIN temp_median tl ON tl.id = temp_median_ids.lower; """ t_upper = alias(median_table, name="tup") t_lower = alias(median_table, name="tlo") final_query = select(from_obj=median_id_table) for group in self.group_by: final_query.append_column(t_upper.c[group]) final_query.append_column(((t_upper.c[self.VAL_COL] + t_lower.c[self.VAL_COL]) / 2.0).label(self.alias)) final_query.append_whereclause(median_id_table.c["upper"] == t_upper.c[self.ID_COL]) final_query.append_whereclause(median_id_table.c["lower"] == t_lower.c[self.ID_COL]) return final_query
def test_date(session): dates = ( date(2016, 1, 1), date(2016, 1, 2), ) selects = tuple(sa.select((MakeADate(d),)) for d in dates) data = sa.alias(sa.union(*selects, use_labels=True), 'dates') stmt = sa.select((data,)) result = session.execute(stmt).fetchall() assert tuple(chain.from_iterable(result)) == dates
def get_end_tx_column_query( table, end_tx_column_name='end_transaction_id', tx_column_name='transaction_id' ): v1 = sa.alias(table, name='v') v2 = sa.alias(table, name='v2') v3 = sa.alias(table, name='v3') primary_keys = [c.name for c in table.c if c.primary_key] tx_criterion = sa.select( [sa.func.min(getattr(v3.c, tx_column_name))] ).where( sa.and_( getattr(v3.c, tx_column_name) > getattr(v1.c, tx_column_name), *[ getattr(v3.c, pk) == getattr(v1.c, pk) for pk in primary_keys if pk != tx_column_name ] ) ) return sa.select( columns=[ getattr(v1.c, column) for column in primary_keys ] + [ getattr(v2.c, tx_column_name).label(end_tx_column_name) ], from_obj=v1.outerjoin( v2, sa.and_( getattr(v2.c, tx_column_name) == tx_criterion ) ) ).order_by(getattr(v1.c, tx_column_name))
def latest_prices_by_codes(codes=[]): p1 = models.Price p2 = sql.alias(models.Price) with models.session_scope() as s: query = s.query(p1).outerjoin(p2, sql.and_( p1.quandl_code == p2.c.quandl_code, p1.date < p2.c.date, )).filter( p1.quandl_code.in_(codes) if codes else True, p2.c.date.is_(None), ) df = pd.read_sql(query.statement, query.session.bind, index_col="quandl_code") return df
def _get_consumer_by_uuid(ctx, uuid): # The SQL for this looks like the following: # SELECT # c.id, c.uuid, # p.id AS project_id, p.external_id AS project_external_id, # u.id AS user_id, u.external_id AS user_external_id, # c.updated_at, c.created_at # FROM consumers c # INNER JOIN projects p # ON c.project_id = p.id # INNER JOIN users u # ON c.user_id = u.id # WHERE c.uuid = $uuid consumers = sa.alias(CONSUMER_TBL, name="c") projects = sa.alias(project_obj.PROJECT_TBL, name="p") users = sa.alias(user_obj.USER_TBL, name="u") cols = [ consumers.c.id, consumers.c.uuid, projects.c.id.label("project_id"), projects.c.external_id.label("project_external_id"), users.c.id.label("user_id"), users.c.external_id.label("user_external_id"), consumers.c.generation, consumers.c.updated_at, consumers.c.created_at ] c_to_p_join = sa.join( consumers, projects, consumers.c.project_id == projects.c.id) c_to_u_join = sa.join( c_to_p_join, users, consumers.c.user_id == users.c.id) sel = sa.select(cols).select_from(c_to_u_join) sel = sel.where(consumers.c.uuid == uuid) res = ctx.session.execute(sel).fetchone() if not res: raise exception.ConsumerNotFound(uuid=uuid) return dict(res)
def _build_query(self, table, filter_values): key_column = table.c[self.key] sum_query = sqlalchemy.alias( sqlalchemy.select( self.group_by + [sqlalchemy.func.sum(key_column).label('sum_col')] + [table.c.month], group_by=self.group_by + [table.c.month], whereclause=self.filter.build_expression(table), ), name='s') return select( self.group_by + [sqlalchemy.func.avg(sum_query.c.sum_col).label(self.key)], group_by=self.group_by, from_obj=sum_query ).params(filter_values)
def find(self, _limit=None, _offset=0, _step=5000, order_by='id', return_count=False, **_filter): """ Performs a simple search on the table. Simply pass keyword arguments as ``filter``. :: results = table.find(country='France') results = table.find(country='France', year=1980) Using ``_limit``:: # just return the first 10 rows results = table.find(country='France', _limit=10) You can sort the results by single or multiple columns. Append a minus sign to the column name for descending order:: # sort results by a column 'year' results = table.find(country='France', order_by='year') # return all rows sorted by multiple columns (by year in descending order) results = table.find(order_by=['country', '-year']) For more complex queries, please use :py:meth:`db.query() <dataset.Database.query>` instead.""" self._check_dropped() if not isinstance(order_by, (list, tuple)): order_by = [order_by] order_by = [o for o in order_by if (o.startswith('-') and o[1:] or o) in self.table.columns] order_by = [self._args_to_order_by(o) for o in order_by] args = self._args_to_clause(_filter) # query total number of rows first count_query = alias(self.table.select(whereclause=args, limit=_limit, offset=_offset), name='count_query_alias').count() rp = self.database.executable.execute(count_query) total_row_count = rp.fetchone()[0] if return_count: return total_row_count if _limit is None: _limit = total_row_count if _step is None or _step is False or _step == 0: _step = total_row_count query = self.table.select(whereclause=args, limit=_limit, offset=_offset, order_by=order_by) return ResultIter(self.database.executable.execute(query), row_type=self.database.row_type, step=_step)
def _get_project_by_external_id(ctx, external_id): projects = sa.alias(PROJECT_TBL, name="p") cols = [ projects.c.id, projects.c.external_id, projects.c.updated_at, projects.c.created_at ] sel = sa.select(cols) sel = sel.where(projects.c.external_id == external_id) res = ctx.session.execute(sel).fetchone() if not res: raise exception.ProjectNotFound(external_id=external_id) return dict(res)
def _mass_shifts_query_inner(self, session): anode = alias(ChromatogramTreeNode.__table__) bnode = alias(ChromatogramTreeNode.__table__) apeak = alias(DeconvolutedPeak.__table__) peak_join = apeak.join( ChromatogramTreeNodeToDeconvolutedPeak, ChromatogramTreeNodeToDeconvolutedPeak.c.peak_id == apeak.c.id) root_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == anode.c.id) branch_peaks_join = peak_join.join( anode, ChromatogramTreeNodeToDeconvolutedPeak.c.node_id == anode.c.id).join( ChromatogramTreeNodeBranch, ChromatogramTreeNodeBranch.child_id == anode.c.id).join( bnode, ChromatogramTreeNodeBranch.parent_id == bnode.c.id).join( ChromatogramToChromatogramTreeNode, ChromatogramToChromatogramTreeNode.c.node_id == bnode.c.id) return root_peaks_join, branch_peaks_join, anode, bnode, apeak
def _get_user_by_external_id(ctx, external_id): users = sa.alias(USER_TBL, name="u") cols = [ users.c.id, users.c.external_id, users.c.updated_at, users.c.created_at ] sel = sa.select(cols) sel = sel.where(users.c.external_id == external_id) res = ctx.session.execute(sel).fetchone() if not res: raise exception.UserNotFound(external_id=external_id) return dict(res)
def _build_query(self, table, filter_values): key_column = table.c[self.key] subquery = sqlalchemy.alias( sqlalchemy.select( self.group_by + [sqlalchemy.func.count(sqlalchemy.distinct(key_column)).label('count_unique')], group_by=self.group_by + [table.c.month], whereclause=self.filter.build_expression(table), ), name='cq') return sqlalchemy.select( self.group_by + [sqlalchemy.func.sum(subquery.c.count_unique).label(self.key)], group_by=self.group_by, from_obj=subquery ).params(filter_values)
def query_prices_by_codes(codes=[]): p1 = models.Price p2 = sql.alias(models.Price) with models.session_scope(expire_on_commit=False) as s: query = s.query(p1).join(p2, sql.and_( p1.quandl_code == p2.c.quandl_code, p1.date <= p2.c.date, )).filter( p1.quandl_code.in_(codes) if codes else True, ).group_by( p1.quandl_code, p1.date, ).having( sql.func.count("*") <= 2 ) # .order_by(models.Price.quandl_code, models.Price.date) return itertools.groupby(query.all(), key=lambda p: p.quandl_code)
def fill(self): """fills the tree view with data """ logger.debug('start filling tasks_treeView') logger.debug('creating a new model') if not self.project: from sqlalchemy import alias from stalker import Task, Project from stalker.db.session import DBSession # projects = Project.query.order_by(Project.name).all() inner_tasks = alias(Task.__table__) subquery = DBSession.query(inner_tasks.c.id).filter( inner_tasks.c.project_id == Project.id) query = DBSession\ .query( Project.id, Project.name, Project.entity_type, Project.status_id, subquery.exists().label('has_children') ) if not self.show_completed_projects: from stalker import Status status_cmpl = \ Status.query.filter(Status.code == 'CMPL').first() query = query.filter(Project.status != status_cmpl) query = query.order_by(Project.name) projects = query.all() else: self.project.has_children = bool(self.project.tasks) projects = [self.project] logger.debug('projects: %s' % projects) # delete the old model if any if self.model() is not None: self.model().deleteLater() task_tree_model = TaskTreeModel() task_tree_model.populateTree(projects) self.setModel(task_tree_model) self.is_updating = False self.auto_fit_column() logger.debug('finished filling tasks_treeView')
def _filter_ancestors_of_node_helper(node): # Restrict ourselves to just those nodes within the same tree as node: tree_id = getattr(node, self.tree_id_field.name) filter_ = self.tree_id_field == tree_id # Restrict to ancestors, inclusive of node: alias = sqlalchemy.alias(options.table) left_field = self.left_field filter_ &= sqlalchemy.between( getattr(alias.c, self.left_field.name), self.left_field, self.right_field) filter_ &= getattr(alias.c, self.pk_field.name) == \ getattr(node, self.pk_field.name) # Explicitly exclude node, if requested: if not include_self: filter_ &= self.pk_field != getattr(node, self.pk_field.name) # We're done! return filter_