def GetJoinTable(self, searchInfo): ObservationTable = Base.metadata.tables['Observation'] obsValTable = Base.metadata.tables['observationdynpropvaluenow'] joinTable = super().GetJoinTable(searchInfo) joinTable = outerjoin(joinTable, ObservationTable, ObservationTable.c['FK_Station'] == Station.ID) joinTable = outerjoin(joinTable, obsValTable, and_(ObservationTable.c['ID'] == obsValTable.c['FK_Observation'], obsValTable.c['Name'] == 'nom_vernaculaire')) self.selectable.append( func.string_agg( obsValTable.c['ValueString'], aggregate_order_by(literal_column("','"), Station.ID )).label('nom_vernaculaire')) self.selectable.append( func.string_agg( ObservationTable.c['taxon'], aggregate_order_by(literal_column("','"), Station.ID )).label('nom_latin')) return joinTable
def builder(t_from, t_to, completed, invalidated): # aggregation query q = sa.select([ sa.func.time_bucket(t_from.c.time, sa.text( "'%s'" % interval)).label('time'), sa.func.first(pg.aggregate_order_by(t_from.c.open, t_from.c.time)).label('open'), sa.func.max(t_from.c.high).label('high'), sa.func.min(t_from.c.low).label('low'), sa.func.last(pg.aggregate_order_by(t_from.c.close, t_from.c.time)).label('close'), sa.func.sum(t_from.c.volume).label('volume'), sa.func.sum(t_from.c.value).label('value') ], from_obj=t_from).group_by( sa.text('1')).where(t_from.c.time <= invalidated) if completed is not None: q = q.where(t_from.c.time >= completed) # upsert materialized table inst = pg.insert(t_to).from_select(t_to.columns, q) inst = inst.on_conflict_do_update(index_elements=[t_kline_1m.c.time], set_={ 'open': inst.excluded.open, 'high': inst.excluded.high, 'low': inst.excluded.low, 'close': inst.excluded.close, 'volume': inst.excluded.volume, 'value': inst.excluded.value, }) print(inst.compile(dialect=pg.dialect())) return inst
def get_by_player(playerid): q = db.session.query( #RaidDropsModel.id, RaidDropsModel.name.label('playername'), #RaidDropsModel.world.label('playerworld'), db.func.array_agg(aggregate_order_by(RaidDropsModel.time, ItemModel.name)).label('time'), db.func.array_agg(aggregate_order_by(RaidDropsModel.classjob, ItemModel.name)).label('job'), #db.func.array_agg(RaidDropsModel.itemid).label('idd'), #RaidDropsModel.itemquantity, db.func.array_agg(aggregate_order_by(ItemModel.rarity, ItemModel.name)).label('rarity'), db.func.array_agg(aggregate_order_by(ItemModel.name, ItemModel.name)).label('itemnames'), db.func.array_agg(aggregate_order_by(ItemModel.id, ItemModel.name)).label('ids'), db.func.array_agg(aggregate_order_by(RaidDropsModel.itemquantity, ItemModel.name)).label('itemquantities'), db.func.array_agg(aggregate_order_by(ItemModel.itemuicategory, ItemModel.name)).label('itemcategories'), db.func.array_agg(aggregate_order_by(ItemModel.equipslotcategory, ItemModel.name)).label('equipcategories'), db.func.array_agg(aggregate_order_by(ItemModel.icon, ItemModel.name)).label('icons'), #ItemModel.name.label('itemname'), #ItemModel.icon.label('itemicon'), #ItemModel.description.label('itemdescription'), #ItemModel.equipslotcategory.label('itemequipslot'), #ItemModel.itemuicategory.label('itemcategory'), ).join(ItemModel).group_by( RaidDropsModel.name ).filter(RaidDropsModel.playerid == str(playerid), RaidDropsModel.view == True) return q
def db_detail(prodid): db_conn = None db_conn = db_engine.connect() query1 = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_acts.c.actorname, aggregate_order_by(literal_column("'; '"), db_acts.c.actorname)) ], distinct=True).where( and_(db_films.c.movieid == db_prod.c.movieid, db_prod.c.prod_id == prodid, db_actfilm.c.actorid == db_acts.c.actorid, db_actfilm.c.movieid == db_films.c.movieid)).group_by( db_films.c.movietitle, db_prod.c.prod_id) result1 = db_conn.execute(query1) query2 = select( [ func.string_agg( db_genres.c.genrename, aggregate_order_by(literal_column("', '"), db_genres.c.genrename)) ], distinct=True).where( and_(db_prod.c.prod_id == prodid, db_filmgen.c.movieid == db_prod.c.movieid, db_genres.c.genreid == db_filmgen.c.genreid)).group_by( db_prod.c.prod_id) result2 = db_conn.execute(query2) query3 = select( [ func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_(db_prod.c.prod_id == prodid, db_dirfilm.c.movieid == db_prod.c.movieid, db_dirs.c.directorid == db_dirfilm.c.directorid)).group_by( db_prod.c.prod_id) result3 = db_conn.execute(query3) result1 = list(result1) result1 = list(result1[0]) result2 = list(result2) result3 = list(result3) result1.append(result2[0][0]) result1.insert(4, result3[0][0]) db_conn.close() return result1
def _generate_selectable(): cte = select([ EndpointSIP.uuid.label('uuid'), literal(0).label('level'), literal('0', String).label('path'), EndpointSIP.uuid.label('root'), ]).cte(recursive=True) endpoints = cte.union_all( select([ EndpointSIPTemplate.parent_uuid.label('uuid'), (cte.c.level + 1).label('level'), (cte.c.path + cast( func.row_number().over( partition_by='level', order_by=EndpointSIPTemplate.priority, ), String, )).label('path'), (cte.c.root), ]).select_from( join(cte, EndpointSIPTemplate, cte.c.uuid == EndpointSIPTemplate.child_uuid))) return (select([ endpoints.c.root, cast( func.jsonb_object( func.array_agg( aggregate_order_by( EndpointSIPSectionOption.key, endpoints.c.path.desc(), )), func.array_agg( aggregate_order_by( EndpointSIPSectionOption.value, endpoints.c.path.desc(), )), ), JSONB, ).label('options'), ]).select_from( join( endpoints, EndpointSIPSection, EndpointSIPSection.endpoint_sip_uuid == endpoints.c.uuid, ).join( EndpointSIPSectionOption, EndpointSIPSectionOption.endpoint_sip_section_uuid == EndpointSIPSection.uuid, )).group_by('root'))
def ballot_retrieval_list(jurisdiction: Jurisdiction, round: Round) -> str: previous_ballots = set( SampledBallotDraw.query.join(Round).filter( Round.round_num < round.round_num).join(SampledBallot).join( Batch).filter_by(jurisdiction_id=jurisdiction.id).values( Batch.name, SampledBallot.ballot_position)) ballots = (SampledBallotDraw.query.filter_by( round_id=round.id).join(SampledBallot).join(Batch).filter_by( jurisdiction_id=jurisdiction.id).join(AuditBoard).group_by( AuditBoard.id, SampledBallot.id, Batch.id).order_by( AuditBoard.name, Batch.name, SampledBallot.ballot_position).values( Batch.name, SampledBallot.ballot_position, Batch.storage_location, Batch.tabulator, func.string_agg( SampledBallotDraw.ticket_number, aggregate_order_by( literal_column("','"), SampledBallotDraw.ticket_number), ), AuditBoard.name, )) csv_io = io.StringIO() retrieval_list_writer = csv.writer(csv_io) retrieval_list_writer.writerow([ "Batch Name", "Ballot Number", "Storage Location", "Tabulator", "Ticket Numbers", "Already Audited", "Audit Board", ]) for ballot in ballots: ( batch_name, position, storage_location, tabulator, ticket_numbers, audit_board_name, ) = ballot previously_audited = "Y" if (batch_name, position) in previous_ballots else "N" retrieval_list_writer.writerow([ batch_name, position, storage_location, tabulator, ticket_numbers, previously_audited, audit_board_name, ]) return csv_io.getvalue()
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected( self): case_study_query = (db.session.query( CaseStudy.supplier_code.label('supplier_code'), CaseStudy.data['service'].astext.label('domain'), func.count(CaseStudy.id).label('count')).group_by( CaseStudy.supplier_code, CaseStudy.data['service'].astext)) subquery = (case_study_query.intersect( case_study_query.filter( CaseStudy.status == 'rejected')).subquery()) results = (db.session.query( Supplier.id, Supplier.code, Supplier.name, func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label( 'domains')).join(SupplierDomain, Domain).join( subquery, and_(Supplier.code == subquery.columns.supplier_code, Domain.name == subquery.columns.domain)).filter( Supplier.status != 'deleted', Supplier.data['recruiter'].astext.in_( ['no', 'both']), SupplierDomain.status == 'unassessed').group_by( Supplier.id, Supplier.code, Supplier.name).all()) return [r._asdict() for r in results]
def get_ips_ports(self, flt, limit=None, skip=None): req = flt.query(select([self.tables.scan.id])) if skip is not None: req = req.offset(skip) if limit is not None: req = req.limit(limit) base = req.cte("base") return ({ "addr": rec[2], "starttime": rec[1], "ports": [{ "proto": proto, "port": int(port), "state_state": state } for proto, port, state in ( elt.split(',') for elt in ''.join(rec[0])[3:-3].split(')","('))] } for rec in self.db.execute( select([ func.array_agg( postgresql.aggregate_order_by( tuple_(self.tables.port.protocol, self.tables.port. port, self.tables.port.state).label('a'), tuple_(self.tables.port.protocol, self.tables.port.port ).label('a'))).label('ports'), self.tables.scan.time_start, self.tables.scan.addr, ]).select_from(join(self.tables.port, self.tables.scan)).group_by( self.tables.scan.addr, self.tables.scan.time_start).where( and_(self.tables.port.port >= 0, self.tables.scan.id.in_(base)))))
def get_group_team_stats(self, replay_ids, session=None): query = session.query( PlayerGame.game, func.array_agg( aggregate_order_by( PlayerGame.player, PlayerGame.player)).label("team")).filter( PlayerGame.game.in_(replay_ids)).group_by( PlayerGame.game).group_by( PlayerGame.is_orange).subquery() teams = session.query(query.c.team, func.array_agg( query.c.game)).group_by(query.c.team).all() return { "teamStats": [{ "team": team[0], "games": team[1], "names": [ name for (name, ) in session.query(func.min( PlayerGame.name)).filter(PlayerGame.game.in_(team[1])). filter(PlayerGame.player.in_(team[0])).order_by( PlayerGame.player).group_by(PlayerGame.player).all() ], **self._create_group_stats(session, player_filter=team[0], replay_ids=team[1]) } for team in teams] }
def get_skillEntity(conn: Connection, skill_ids: Iterable[int]) -> list[SkillEntityNoReverse]: mstSkillLvJson = (select( mstSkillLv.c.skillId, func.jsonb_agg( aggregate_order_by(mstSkillLv.table_valued(), mstSkillLv.c.lv)).label(mstSkillLv.name), ).where(mstSkillLv.c.skillId.in_(skill_ids)).group_by( mstSkillLv.c.skillId).cte()) JOINED_SKILL_TABLES = (mstSkill.outerjoin( mstSkillDetail, mstSkillDetail.c.id == mstSkill.c.id).outerjoin( mstSvtSkill, mstSvtSkill.c.skillId == mstSkill.c.id).outerjoin( mstSkillLvJson, mstSkillLvJson.c.skillId == mstSkill.c.id)) SELECT_SKILL_ENTITY = [ func.to_jsonb(mstSkill.table_valued()).label(mstSkill.name), sql_jsonb_agg(mstSkillDetail), sql_jsonb_agg(mstSvtSkill), mstSkillLvJson.c.mstSkillLv, ] stmt = (select( *SELECT_SKILL_ENTITY).select_from(JOINED_SKILL_TABLES).where( mstSkill.c.id.in_(skill_ids)).group_by( mstSkill.c.id, mstSkillLvJson.c.mstSkillLv)) skill_entities = (SkillEntityNoReverse.from_orm(skill) for skill in conn.execute(stmt).fetchall()) order = {skill_id: i for i, skill_id in enumerate(skill_ids)} return sorted(skill_entities, key=lambda skill: order[skill.mstSkill.id])
def get_rates_by_dates_for_currency_in_period(self, currency, start_date, end_date): """ :type currency: str :type start_date: datetime.date :type end_date: datetime.date :rtype: dict[datetime.date, list[decimal.Decimal]] """ result = self.db_session\ .query( ExchangeRate.date, func.array_agg(aggregate_order_by(ExchangeRate.rate, ExchangeRate.provider_id.asc())) )\ .filter( and_( ExchangeRate.date >= start_date, ExchangeRate.date <= end_date, ExchangeRate.currency == currency, ExchangeRate.rate.isnot(None) ) )\ .group_by(ExchangeRate.date)\ .order_by(ExchangeRate.date)\ .all() return {r[0]: list(r[1]) for r in result}
def _group_lines_stops(list_stops): """ Groups lines and stops such that each distinct line and direction has a group of stops associated with it. """ stops = [s.atco_code for s in list_stops] separator = db.literal_column("' / '") destinations = db.func.string_agg( db.distinct(models.JourneyPattern.destination), pg.aggregate_order_by(separator, models.JourneyPattern.destination) ) array_stops = pg.array_agg(db.distinct(models.JourneyLink.stop_point_ref)) groups = ( db.session.query( models.Service.code.label("code"), models.JourneyPattern.direction.label("direction"), models.Service.line.label("line"), destinations.label("destination"), array_stops.label("stops") ) .select_from(models.Service) .join(models.Service.patterns) .join(models.JourneyPattern.links) .filter(models.JourneyLink.stop_point_ref.in_(stops)) .group_by(models.Service.code, models.Service.line, models.JourneyPattern.direction) .order_by(models.Service.line, models.JourneyPattern.direction) .all() ) return [g._asdict() for g in groups]
def fill_description(): delimiter = sa.bindparam("delimiter", " – ") separator = sa.bindparam("separator", " / ") origin = sa.func.string_agg( sa.distinct(pattern.c.origin), sa_pg.aggregate_order_by(separator, pattern.c.origin)) destination = sa.func.string_agg( sa.distinct(pattern.c.origin), sa_pg.aggregate_order_by(separator, pattern.c.origin)) query = (sa.select([ service.c.id.label("id"), (origin + delimiter + destination).label("description") ]).where((service.c.description == "") & (service.c.id == pattern.c.service_ref) & ~pattern.c.direction).group_by(service.c.id).alias("fill_dest")) return query
def build_column(self): table_column = column(self.column_name) if self.order_by_col: order_by_column = column(self.order_by_col) return func.array_agg( aggregate_order_by(table_column, order_by_column.asc())).label(self.label) return func.array_agg(table_column).label(self.label)
class OptimizationProcedureORM(ProcedureMixin, BaseResultORM): """ An Optimization procedure """ __tablename__ = 'optimization_procedure' id = Column(Integer, ForeignKey('base_result.id', ondelete='cascade'), primary_key=True) def __init__(self, **kwargs): kwargs.setdefault("version", 1) self.procedure = "optimization" super().__init__(**kwargs) schema_version = Column(Integer, default=1) initial_molecule = Column(Integer, ForeignKey('molecule.id')) initial_molecule_obj = relationship(MoleculeORM, lazy='select', foreign_keys=initial_molecule) # # Results energies = Column(JSON) #Column(ARRAY(Float)) final_molecule = Column(Integer, ForeignKey('molecule.id')) final_molecule_obj = relationship(MoleculeORM, lazy='select', foreign_keys=final_molecule) # ids, calculated not stored in this table # NOTE: this won't work in SQLite since it returns ARRAYS, aggregate_order_by trajectory = column_property( select([func.array_agg(aggregate_order_by(Trajectory.result_id, Trajectory.position))]).where(Trajectory.opt_id == id)) # array of objects (results) - Lazy - raise error of accessed trajectory_obj = relationship(Trajectory, cascade="all, delete-orphan", # backref="optimization_procedure", order_by=Trajectory.position, collection_class=ordering_list('position')) __mapper_args__ = { 'polymorphic_identity': 'optimization_procedure', # to have separate select when querying BaseResultsORM 'polymorphic_load': 'selectin', } __table_args__ = ( Index('ix_optimization_program', 'program'), # todo: needed for procedures? ) def update_relations(self, trajectory=None, **kwarg): # update optimization_results relations # self._update_many_to_many(opt_result_association, 'opt_id', 'result_id', # self.id, trajectory, self.trajectory) self.trajectory_obj = [] trajectory = [] if not trajectory else trajectory for result_id in trajectory: traj = Trajectory(opt_id=int(self.id), result_id=int(result_id)) self.trajectory_obj.append(traj)
def test_aggregate_order_by_two(self): m = MetaData() table = Table('table1', m, Column('a', Integer), Column('b', Integer)) expr = func.string_agg( table.c.a, aggregate_order_by(literal_column("','"), table.c.a)) stmt = select([expr]) self.assert_compile( stmt, "SELECT string_agg(table1.a, ',' ORDER BY table1.a) " "AS string_agg_1 FROM table1")
def test_aggregate_order_by_one(self): m = MetaData() table = Table('table1', m, Column('a', Integer), Column('b', Integer)) expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) stmt = select([expr]) # note this tests that the object exports FROM objects # correctly self.assert_compile( stmt, "SELECT array_agg(table1.a ORDER BY table1.b DESC) " "AS array_agg_1 FROM table1")
def test_aggregate_order_by_adapt(self): m = MetaData() table = Table('table1', m, Column('a', Integer), Column('b', Integer)) expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) stmt = select([expr]) a1 = table.alias('foo') stmt2 = sql_util.ClauseAdapter(a1).traverse(stmt) self.assert_compile( stmt2, "SELECT array_agg(foo.a ORDER BY foo.b DESC) AS array_agg_1 FROM table1 AS foo" )
def _array_agg_and_order( to_select: DbColumn[Y], order_by: DbColumn[Z], *, remove_nulls: bool = False ) -> DbColumn[t.Sequence[Y]]: res = sqlalchemy.func.array_agg( aggregate_order_by(to_select, order_by).asc() ) if remove_nulls: res = sqlalchemy.func.array_remove(res, sqlalchemy.sql.null()) return res
def get_vocabualary(only_positive=False): session = db_session() query = session.query( func.string_agg( SourceData.filtered_words, aggregate_order_by(literal_column("' '"), SourceData.id))) if only_positive: query = query.filter(SourceData.is_negative == False) query = query.first() session.close() return u"%s" % str(query)
def test_aggregate_order_by_one(self): m = MetaData() table = Table('table1', m, Column('a', Integer), Column('b', Integer)) expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) stmt = select([expr]) # note this tests that the object exports FROM objects # correctly self.assert_compile( stmt, "SELECT array_agg(table1.a ORDER BY table1.b DESC) " "AS array_agg_1 FROM table1" )
def budget_deptx(client, dept_id): user = current_user if client is None: client = user.client[0].abbreviation else: client = Client.query.filter(Client.abbreviation.ilike(client)).first() if not current_user.has_role(client.abbreviation.lower()): abort(403) from mahercpa.modules.models_views import get_budget_actual, BudgetSpendJson, TransactionDetail, func, and_ td = TransactionDetail dt_filt = (datetime(2017, 4, 1), datetime(2018, 3, 30)) txn = (db.session.query( td.budget, td.vendor, td.period, td.amount).filter(td.client_id == client.id).filter( td.period.between(dt_filt[0], dt_filt[1]), td.path_id.any(dept_id)).subquery()) q1 = (db.session.query( txn.c.budget, txn.c.vendor, func.json_object_agg(txn.c.period, txn.c.amount).label('months')).group_by( txn.c.budget, txn.c.vendor).subquery()) q2 = (db.session.query( td.budget, td.vendor, func.json_agg( aggregate_order_by( func.json_build_object('date', td.date, 'memo', td.memo, 'id', td.id, 'txn_num', td.txn_number, 'ref_num', td.ref_num, 'amount', td.amount), order_by=td.date)).label('transactions')).filter( td.client_id == client.id).filter( td.period.between(dt_filt[0], dt_filt[1]), td.path_id.any(dept_id)).group_by( td.budget, td.vendor)).subquery() spend_obj = (db.session.query( q1.c.budget, q2.c.vendor, func.json_build_object('budget', q1.c.budget, 'vendor', q1.c.vendor, 'totals', q1.c.months, 'transactions', q2.c.transactions).label('json')).join( q2, and_(q1.c.budget == q2.c.budget, q1.c.vendor == q2.c.vendor)).all()) return jsonify([r.json for r in spend_obj])
def test_aggregate_order_by_two(self): m = MetaData() table = Table('table1', m, Column('a', Integer), Column('b', Integer)) expr = func.string_agg( table.c.a, aggregate_order_by(literal_column("','"), table.c.a) ) stmt = select([expr]) self.assert_compile( stmt, "SELECT string_agg(table1.a, ',' ORDER BY table1.a) " "AS string_agg_1 FROM table1" )
def create(self): user_id = context.form.get('userId') destination = DBSession.query(Member) \ .filter(Member.id == user_id).one_or_none() if destination is None: raise HTTPStatus('611 Member Not Found') current_user = DBSession.query(Member) \ .filter(Member.reference_id == context.identity.reference_id) \ .one() is_blocked = DBSession.query(member_block) \ .filter(or_( and_( member_block.c.member_id == user_id, member_block.c.blocked_member_id == current_user.id ), and_( member_block.c.member_id == current_user.id, member_block.c.blocked_member_id == user_id ) )) \ .count() if is_blocked: raise HTTPStatus( '613 Not Allowed To Create Direct With This Member') source = Member.current() cte = select([ TargetMember.target_id.label('direct_id'), func.array_agg(aggregate_order_by(TargetMember.member_id, TargetMember.member_id), type_=ARRAY(Integer)).label('members') ]).group_by(TargetMember.target_id).cte() direct = DBSession.query(Direct) \ .join(cte, cte.c.direct_id == Direct.id) \ .filter(cte.c.members == sorted([source.id, user_id])) \ .one_or_none() if direct: return direct direct = Direct(members=[source, destination]) return direct
def get_tdEntity(conn: Connection, td_ids: Iterable[int]) -> list[TdEntityNoReverse]: mstTreasureDeviceLvJson = (select( mstTreasureDeviceLv.c.treaureDeviceId, func.jsonb_agg( aggregate_order_by(mstTreasureDeviceLv.table_valued(), mstTreasureDeviceLv.c.lv)).label( mstTreasureDeviceLv.name), ).where(mstTreasureDeviceLv.c.treaureDeviceId.in_(td_ids)).group_by( mstTreasureDeviceLv.c.treaureDeviceId).cte()) JOINED_TD_TABLES = (mstTreasureDevice.outerjoin( mstTreasureDeviceDetail, mstTreasureDeviceDetail.c.id == mstTreasureDevice.c.id, ).outerjoin( mstSvtTreasureDevice, mstSvtTreasureDevice.c.treasureDeviceId == mstTreasureDevice.c.id, ).outerjoin( mstTreasureDeviceLvJson, mstTreasureDeviceLvJson.c.treaureDeviceId == mstTreasureDevice.c.id, )) SELECT_TD_ENTITY = [ mstTreasureDevice.c.id, func.to_jsonb(mstTreasureDevice.table_valued()).label( mstTreasureDevice.name), sql_jsonb_agg(mstTreasureDeviceDetail), sql_jsonb_agg(mstSvtTreasureDevice), mstTreasureDeviceLvJson.c.mstTreasureDeviceLv, ] stmt = (select(*SELECT_TD_ENTITY).select_from(JOINED_TD_TABLES).where( mstTreasureDevice.c.id.in_(td_ids)).group_by( mstTreasureDevice.c.id, mstTreasureDeviceLvJson.c.mstTreasureDeviceLv)) skill_entities = [ TdEntityNoReverse.from_orm(skill) for skill in conn.execute(stmt).fetchall() ] order = {skill_id: i for i, skill_id in enumerate(td_ids)} return sorted(skill_entities, key=lambda td: order[td.mstTreasureDevice.id])
def get_negative(): session = db_session() query = session.query(Statement.id.label('statement_id'), func.string_agg(TelegramTextMessage.message, aggregate_order_by(literal_column("'. '"), TelegramTextMessage.created)).label('agg_message')).\ filter(Statement.reviewed==True).\ filter(Statement.is_question==Statement.false_assumption).\ filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\ filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\ group_by(Statement.id).\ subquery() query_results = session.query(query.c.statement_id, query.c.agg_message, func.length(query.c.agg_message).label('len'), TelegramChannel.tags.label('tags')).\ outerjoin(Statement, Statement.id==query.c.statement_id).\ outerjoin(TelegramChannel, TelegramChannel.channel_id==Statement.channel_id).distinct().all() session.close() return query_results
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(self): case_study_query = ( db.session.query( CaseStudy.supplier_code.label('supplier_code'), CaseStudy.data['service'].astext.label('domain'), func.count(CaseStudy.id).label('count') ) .group_by(CaseStudy.supplier_code, CaseStudy.data['service'].astext) ) subquery = ( case_study_query .intersect( case_study_query .filter(CaseStudy.status == 'rejected') ) .subquery() ) results = ( db .session .query( Supplier.id, Supplier.code, Supplier.name, func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label('domains') ) .join(SupplierDomain, Domain) .join(subquery, and_( Supplier.code == subquery.columns.supplier_code, Domain.name == subquery.columns.domain )) .filter( Supplier.status != 'deleted', Supplier.data['recruiter'].astext.in_(['no', 'both']), SupplierDomain.status == 'unassessed' ) .group_by(Supplier.id, Supplier.code, Supplier.name) .all() ) return [r._asdict() for r in results]
def do_auto_review(): session = db_session() subquery = session.query(Statement.id.label('statement_id'), func.length(func.string_agg(TelegramTextMessage.message, aggregate_order_by(literal_column("'. '"), TelegramTextMessage.created))).label('agg_message')).\ filter(Statement.reviewed==False).\ filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\ filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\ group_by(Statement.id).\ subquery() query = session.query(subquery.c.statement_id).filter( subquery.c.agg_message < MINIMIM_QUESTION_LENGHT).subquery() stmt = update(Statement).where(Statement.id.in_(query)).values( reviewed=True, is_question=False, false_assumption=False) session.execute(stmt) session.commit() session.close()
async def get_sessions_with_cached_data( session: BacktestSessionInputSchema) -> List[BacktestSessionSchema]: response: List[BacktestSessionSchema] = [] sources_ids = session.sources_ids async with db.transaction(): query = (db.select(BacktestSessionModel).select_from( BacktestSessionModel.join(BacktestSessionSourceModel)).where( and_( BacktestSessionModel.cached_session_id == None, BacktestSessionModel.finished_datetime.isnot(None), BacktestSessionModel.start_datetime <= session.start_datetime, BacktestSessionModel.end_datetime >= session.end_datetime, )).group_by(BacktestSessionModel.id).having( db.func.array_agg( aggregate_order_by( BacktestSessionSourceModel.source_id, BacktestSessionSourceModel.source_id.asc())) == sources_ids)) async for s in query.gino.iterate(): response.append(BacktestSessionSchema.from_orm(s)) return response
def __init__(self, tables, *, column=None, order=None, label=None, join_on=None): self._tables = list(always_iterable(tables)) self._label = label self._from = reduce(lambda x, y: x.join(y), self._tables) if self._label is not None: self._from = self._from.alias(self._label) if column is not None: self.column = column else: if len(self._tables) > 1: raise ValueError('For multi-table-joined relations `column` should be specified') self.column = self._tables[0].c.id if order is not None: self._order = order else: self._order = self.column.asc().nullslast() self._join_on = join_on self.aggregated = array_agg(aggregate_order_by(self.column, self._order))
def get_teams_overview(self, user_id, agency_id): teams_led_by_user = ( db .session .query(TeamMember.team_id) .join(Team) .filter( Team.status == 'completed', TeamMember.user_id == user_id, TeamMember.is_team_lead.is_(True) ) .subquery('teams_led_by_user') ) team_leads = ( db .session .query( TeamMember.team_id, func.array_agg(aggregate_order_by(User.name, User.name)).label('leads') ) .join(User) .filter( TeamMember.is_team_lead.is_(True), User.agency_id == agency_id, User.role == 'buyer' ) .group_by(TeamMember.team_id) .subquery('team_leads') ) team_members = ( db .session .query( TeamMember.team_id, func.array_agg(aggregate_order_by(User.name, User.name)).label('members') ) .join(User) .filter( TeamMember.is_team_lead.is_(False), User.agency_id == agency_id, User.role == 'buyer' ) .group_by(TeamMember.team_id) .subquery('team_members') ) result = ( db .session .query( Team.id, Team.name, team_leads.columns.leads, team_members.columns.members, case( [(teams_led_by_user.columns.team_id.isnot(None), True)], else_=False ).label('isTeamLead') ) .join(team_leads, team_leads.columns.team_id == Team.id) .join(team_members, team_members.columns.team_id == Team.id, isouter=True) .join(teams_led_by_user, teams_led_by_user.columns.team_id == Team.id, isouter=True) .filter(Team.status == 'completed') .order_by(Team.name) .all() ) return [r._asdict() for r in result]
def get_taxa_list(id_area): """ :param type: :return: """ try: reproduction_id = ( ( DB.session.query(TNomenclatures.id_nomenclature) .join( BibNomenclaturesTypes, TNomenclatures.id_type == BibNomenclaturesTypes.id_type, ) .filter( and_( BibNomenclaturesTypes.mnemonique.like("STATUT_BIO"), TNomenclatures.cd_nomenclature.like("3"), ) ) ) .first() .id_nomenclature ) print("reproduction_id", reproduction_id) query_territory = ( DB.session.query( Taxref.cd_ref.label("id"), LAreas.id_area, LAreas.area_code, Taxref.cd_ref, func.split_part(Taxref.nom_vern, ",", 1).label("nom_vern"), Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, func.count(distinct(Synthese.id_synthese)).label("count_occtax"), func.count(distinct(Synthese.observers)).label("count_observer"), func.count(distinct(Synthese.date_min)).label("count_date"), func.count(distinct(Synthese.id_dataset)).label("count_dataset"), func.max(distinct(func.extract("year", Synthese.date_min))).label( "last_year" ), func.array_agg( aggregate_order_by( distinct(func.extract("year", Synthese.date_min)), func.extract("year", Synthese.date_min).desc(), ) ).label("list_years"), func.array_agg( aggregate_order_by( distinct(func.extract("month", Synthese.date_min)), func.extract("month", Synthese.date_min).asc(), ) ).label("list_months"), func.bool_or( Synthese.id_nomenclature_bio_status == reproduction_id ).label("reproduction"), func.max(distinct(func.extract("year", Synthese.date_min))) .filter(Synthese.id_nomenclature_bio_status == reproduction_id) .label("last_year_reproduction"), func.array_agg(distinct(Synthese.id_nomenclature_bio_status)).label( "bio_status_id" ), case( [(func.count(TaxrefProtectionEspeces.cd_nom) > 0, True)], else_=False, ).label("protection"), ) .select_from(CorAreaSynthese) .join(Synthese, Synthese.id_synthese == CorAreaSynthese.id_synthese) .join(Taxref, Synthese.cd_nom == Taxref.cd_nom) .join(LAreas, LAreas.id_area == CorAreaSynthese.id_area) .outerjoin(TaxrefLR, TaxrefLR.cd_nom == Taxref.cd_ref) .outerjoin( TaxrefProtectionEspeces, TaxrefProtectionEspeces.cd_nom == Taxref.cd_nom ) .filter(LAreas.id_area == id_area) .group_by( LAreas.id_area, LAreas.area_code, Taxref.cd_ref, Taxref.nom_vern, Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, ) .order_by( func.count(distinct(Synthese.id_synthese)).desc(), Taxref.group1_inpn, Taxref.group2_inpn, Taxref.nom_valide, ) ) print("query_territory", query_territory) result = query_territory.all() count = len(result) data = [] for r in result: dict = r._asdict() bio_status = [] for s in r.bio_status_id: bio_status.append(get_nomenclature(s)) dict["bio_status"] = bio_status redlist = get_redlist_status(r.cd_ref) dict["redlist"] = redlist data.append(dict) redlistless_data = list(filter(redlist_list_is_null, data)) print("redlistless_data", len(redlistless_data)) redlist_data = list(filter(redlist_is_not_null, data)) print("redlist_data", len(redlist_data)) redlist_sorted_data = sorted( redlist_data, key=lambda k: ( k["redlist"][0]["priority_order"], k["redlist"][0]["threatened"], ), ) sorted_data = redlist_sorted_data + list(redlistless_data) return jsonify({"count": count, "data": sorted_data}), 200 except Exception as e: error = "<get_taxa_list> ERROR: {}".format(e) current_app.logger.error(error) return {"Error": error}, 400
def generate_view_selectable(): cte = ( sa.select( [ sa.literal_column('endpoint_sip.uuid').label('uuid'), sa.literal(0).label('level'), sa.literal('0', sa.String).label('path'), sa.literal_column('endpoint_sip.uuid').label('root'), ] ) .select_from(sa.table('endpoint_sip')) .cte(recursive=True) ) endpoints = cte.union_all( sa.select( [ sa.literal_column('endpoint_sip_template.parent_uuid').label('uuid'), (cte.c.level + 1).label('level'), ( cte.c.path + sa.cast( func.row_number().over( partition_by='level', order_by=sa.literal_column( 'endpoint_sip_template.priority' ), ), sa.String, ) ).label('path'), (cte.c.root), ] ).select_from( sa.join( cte, sa.table('endpoint_sip_template'), cte.c.uuid == sa.literal_column('endpoint_sip_template.child_uuid'), ) ) ) return ( sa.select( [ endpoints.c.root, sa.cast( sa.func.jsonb_object( array_agg( aggregate_order_by( sa.literal_column('endpoint_sip_section_option.key'), endpoints.c.path.desc(), ) ), array_agg( aggregate_order_by( sa.literal_column('endpoint_sip_section_option.value'), endpoints.c.path.desc(), ) ), ), JSONB, ).label('options'), ] ) .select_from( sa.join( endpoints, sa.table('endpoint_sip_section'), sa.literal_column('endpoint_sip_section.endpoint_sip_uuid') == endpoints.c.uuid, ).join( sa.table('endpoint_sip_section_option'), sa.literal_column( 'endpoint_sip_section_option.endpoint_sip_section_uuid' ) == sa.literal_column('endpoint_sip_section.uuid'), ) ) .group_by(endpoints.c.root) )
def ballot_retrieval_list(jurisdiction: Jurisdiction, round: Round) -> str: previous_ballots = set( SampledBallotDraw.query.join(Round) .filter(Round.round_num < round.round_num) .join(SampledBallot) .join(Batch) .filter_by(jurisdiction_id=jurisdiction.id) .values(Batch.tabulator, Batch.name, SampledBallot.ballot_position) ) ballots = list( SampledBallotDraw.query.filter_by(round_id=round.id) .join(SampledBallot) .join(Batch) .filter_by(jurisdiction_id=jurisdiction.id) .outerjoin( CvrBallot, and_( CvrBallot.batch_id == SampledBallot.batch_id, CvrBallot.ballot_position == SampledBallot.ballot_position, ), ) .join(SampledBallot.audit_board) .group_by(AuditBoard.id, SampledBallot.id, Batch.id, CvrBallot.imprinted_id) .order_by( AuditBoard.name, Batch.container, Batch.tabulator, Batch.name, SampledBallot.ballot_position, ) .values( Batch.container, Batch.tabulator, Batch.name, SampledBallot.ballot_position, CvrBallot.imprinted_id, func.string_agg( SampledBallotDraw.ticket_number, aggregate_order_by( literal_column("','"), SampledBallotDraw.ticket_number ), ), AuditBoard.name, ) ) show_imprinted_id = jurisdiction.election.audit_type == AuditType.BALLOT_COMPARISON show_container = len(ballots) > 0 and ballots[0][0] is not None show_tabulator = len(ballots) > 0 and ballots[0][1] is not None csv_io = io.StringIO() retrieval_list_writer = csv.writer(csv_io) columns_to_show = [ ("Container", show_container), ("Tabulator", show_tabulator), ("Batch Name", True), ("Ballot Number", True), ("Imprinted ID", show_imprinted_id), ("Ticket Numbers", True), ("Already Audited", True), ("Audit Board", True), ] retrieval_list_writer.writerow( [header for header, should_show in columns_to_show if should_show] ) for ballot in ballots: ( container, tabulator, batch_name, position, imprinted_id, ticket_numbers, audit_board_name, ) = ballot previously_audited = ( "Y" if (tabulator, batch_name, position) in previous_ballots else "N" ) values_to_show = [ (container, show_container), (tabulator, show_tabulator), (batch_name, True), (position, True), (imprinted_id, show_imprinted_id), (ticket_numbers, True), (previously_audited, True), (audit_board_name, True), ] retrieval_list_writer.writerow( [value for value, should_show in values_to_show if should_show] ) return csv_io.getvalue()