def GetJoinTable(self, searchInfo): ObservationTable = Base.metadata.tables['Observation'] obsValTable = Base.metadata.tables['observationdynpropvaluenow'] joinObsValNow = join( ObservationTable,obsValTable ,and_( ObservationTable.c['ID'] == obsValTable.c['FK_Observation'] , obsValTable.c['Name'] == 'nom_vernaculaire') ) joinTable = super().GetJoinTable(searchInfo) joinTable = outerjoin(joinTable, joinObsValNow, joinObsValNow.c['Observation_FK_Station'] == Station.ID) 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 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 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 match(self, level, limit=10): ''' Returns a list of UserSkillMatch objects, in descending order of number of skills matched for each user. ''' skills_to_learn = [ s.name for s in self.skills if s.level == LEVELS['LEVEL_I_WANT_TO_LEARN']['score'] ] if skills_to_learn: matched_users = User.query_in_deployment().\ add_column(func.string_agg(UserSkill.name, ',')).\ add_column(func.count(UserSkill.id)).\ filter(UserSkill.name.in_(skills_to_learn)).\ filter(User.id == UserSkill.user_id).\ filter(UserSkill.level == level).\ filter(UserSkill.user_id != self.id).\ group_by(User).\ order_by(func.count().desc()).\ limit(limit) else: matched_users = [] for user, question_ids_by_comma, count in matched_users: yield UserSkillMatch(user, question_ids_by_comma.split(','))
def get_region_data(value: str, data: dt.datetime, session: Optional[Session] = None) -> pd.DataFrame: value = "deceduti" if value == "variazione_deceduti" else value session = session or db.db.session sub_query = (session.query( db.ItalyProvince.codice_regione, (func.string_agg(concat, "<br>").label("tot_by_prov") if session.bind.dialect.name == "postgresql" else func.GROUP_CONCAT( concat, "<br>").label("tot_by_prov")), ).filter(db.ItalyProvince.codice_provincia == db.ItalyProvinceCase.codice_provincia).filter( db.ItalyProvinceCase.data == data).filter( db.ItalyProvince.codice_provincia < 200).group_by( db.ItalyProvince.codice_regione)).subquery() columns = ["totale_positivi", "dimessi_guariti", "deceduti"] select_columns = set(columns + [value]) query = (session.query( db.ItalyRegion.codice_regione, db.ItalyRegion.denominazione_regione, *[db.ItalyRegionCase.__table__.columns[col] for col in select_columns], sub_query.c.tot_by_prov, ).filter( db.ItalyRegion.codice_regione == sub_query.c.codice_regione ).filter(db.ItalyRegionCase.data == data).filter( db.ItalyRegionCase.codice_regione == db.ItalyRegion.codice_regione)) return pd.DataFrame(query)
def user_list(): """ 用户列表 :return: """ id = request.args.get("id") if id: return get_user(id) q = db.session.query(User.id, User.department_key, func.max(User.name).label("name"), func.max(User.loginid).label("loginid"), func.max(User.telephone) .label("telephone"), func.max(User.address).label("address"), func.string_agg(func.cast(Role.id, Text), ',').label("roles")).outerjoin(UserRole, UserRole.user_id == User.id).outerjoin( Role, Role.id == UserRole.role_id).group_by(User.id) name = request.args.get("name") if name is not None: q = q.filter(User.name.like("%" + name.split(".")[-1] + "%")) # q = q.order_by(User.name.desc()) offset = int(request.args.get('offset')) limit = int(request.args.get('limit')) sort = request.args.get('sort') if sort == None: sort = "-id" res, total = sql_tool.model_page(q, limit, offset, sort) return JsonResult.res_page(res, total)
def get_teaching_learning_courses(self): """ Returns course information for any course that is within the Teaching & Learning menu Including the grade info stored in course_ssis_metadata Grade info is a string, if there are more than two then is in 11,12 format TODO: Sometimes it formats as 12/11, make it sort (but if you sort you have to put in group_by) Workaround: the model just sorts it for us """ with DBSession() as session: sub = session.query( Course.id.label('course_id'), func.string_agg(CourseSsisMetadatum.value, ',').label('grade')).\ select_from(Course).\ join(CourseCategory, CourseCategory.id == Course.category).\ filter(and_( not_(Course.idnumber == ''), #CourseCategory.path == '/{}'.format(self.TEACHING_LEARNING_CATEGORY_ID) CourseCategory.path.like('/{}/%'.format(self.TEACHING_LEARNING_CATEGORY_ID)) )).\ group_by(Course.id).\ subquery() statement = session.query(Course.idnumber, Course.fullname, sub.c.grade, Course.id.label('database_id')).\ join(sub, Course.id == sub.c.course_id).\ order_by(Course.id) for item in statement.all(): yield item
def get_teaching_learning_courses_with_grades_subquery(self): """ Returns course information for any course that is within the Teaching & Learning menu Including the grade info stored in course_ssis_metadata Grade info is a string, if there are more than two then is in 11,12 format TODO: Sometimes it formats as 12/11, make it sort (but if you sort you have to put in group_by) Workaround: the model just sorts it for us Left in for posperity, because it illustrates a concrete example of a subquery """ with self.db_session() as session: sub = session.query( Course.id.label('course_id'), func.string_agg(CourseSsisMetadatum.value, ',').label('grade')).\ select_from(Course).\ join(CourseCategory, CourseCategory.id == Course.category).\ filter(and_( not_(Course.idnumber == ''), #CourseCategory.path == '/{}'.format(self.TEACHING_LEARNING_CATEGORY_ID) CourseCategory.path.like('/{}/%'.format(self.TEACHING_LEARNING_CATEGORY_ID)) )).\ group_by(Course.id).\ subquery() statement = session.query(Course.idnumber, Course.fullname, sub.c.grade, Course.id.label('database_id')).\ join(sub, Course.id == sub.c.course_id).\ order_by(Course.id) for item in statement.all(): yield item
def itinerary_ids_query(self, left_request_id: int, right_request_id: int): flight_fingerprint = select( [Flight.trip_id, func.concat(Flight.carrier_id, '_', Flight.flight_number, '_').label('flight_fingerprint')]) \ .order_by(Flight.order) \ .alias("flight_fingerprint") itinerary_fingerprint = db.query(Itinerary.id.label('id'), func.string_agg(flight_fingerprint.c.flight_fingerprint, '|').label( 'fingerprint')) \ .select_from(Itinerary) \ .group_by(Itinerary.id) \ .join(flight_fingerprint, flight_fingerprint.c.trip_id == Itinerary.onward_trip_id) request_1 = itinerary_fingerprint.filter( Itinerary.request_id == left_request_id).selectable.alias('left') request_2 = itinerary_fingerprint.filter( Itinerary.request_id == right_request_id).selectable.alias('right') result = db.query(request_1.c.id.label('left_id'), request_2.c.id.label('right_id'), coalesce(request_1.c.fingerprint, request_2.c.fingerprint).label('fingerprint') ) \ .select_from(request_1) \ .join(request_2, request_2.c.fingerprint == request_1.c.fingerprint, full=True) return result
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 _list_filepaths( session, print_queries=False, count=False, ensemble=None, multi_variable=None, multi_year_mean=None, mym_concatenated=None, list_ensembles = None, ): df_query = data_file_query( session, print_queries=print_queries, ensemble=ensemble, multi_variable=multi_variable, multi_year_mean=multi_year_mean, mym_concatenated=mym_concatenated, ) if list_ensembles: if count: info_query = ( session.query( Ensemble.name.label('ensemble_name'), func.count(DataFile.id).label('number') ) .filter(DataFile.id.in_(df_query)) .join(DataFile.data_file_variables) .join(DataFileVariable.ensembles) .group_by(Ensemble.name) ) else: info_query = ( session.query( DataFile.filename, func.string_agg(Ensemble.name, ',').label('ensemble_names') ) .filter(DataFile.id.in_(df_query)) .join(DataFile.data_file_variables) .join(DataFileVariable.ensembles) .group_by(DataFile.id) ) else: info_query = df_query.add_columns(DataFile.filename) if print_queries: print_query('Info query', info_query) if list_ensembles: template = '{row.filename}\t{row.ensemble_names}' else: template = '{row.filename}' list_information(info_query, template, count=count)
def get_columns(self): _columns = list() _columns.append(Orders.id) _columns.append(Orders.order_name) _columns.append(Orders.customer_id) _columns.append(func.string_agg(OrderItems.product, ',').label('product_names')) _columns.append(func.to_char(Orders.created_at, 'Mon DDth, YYYY HH24:MI AM').label('order_date')) # Placeholder for missing columns for now _columns.append(Orders.id.label('customer_name')) _columns.append(Orders.id.label('customer_company')) return _columns
def get_locations(self): """Return SQL query of locations table for given date range.""" log.debug('get_locations') subquery = SESSION.query( Location.document_id, func.bool_and(Location.location_publish).label('location_publish'), func.string_agg( cast(Location.street_number, Text) + ' ' + cast(Location.address, Text), '; ' ).label('address'), func.string_agg( 'Unit: ' + cast(Location.unit, Text) + ', ' + 'Condo: ' + cast(Location.condo, Text) + ', ' + 'Weeks: ' + cast(Location.weeks, Text) + ', ' + 'Subdivision: ' + cast(Location.subdivision, Text) + ', ' + 'District: ' + cast(Location.district, Text) + ', ' + 'Square: ' + cast(Location.square, Text) + ', ' + 'Lot: ' + cast(Location.lot, Text), '; ' ).label('location_info') # todo: Once SQLAlchemy supports this, add these fields this way. # 'mode() WITHIN GROUP (ORDER BY locations.zip_code) AS zip_code', # 'mode() WITHIN GROUP (ORDER BY locations.latitude) AS latitude', # 'mode() WITHIN GROUP (ORDER BY locations.longitude) ' + # ' AS longitude', # 'mode() WITHIN GROUP (ORDER BY locations.neighborhood) ' + # 'AS neighborhood' ).group_by( Location.document_id ).subquery() # log.debug(subquery) SESSION.close() return subquery
def query_enumerate_attribute_values(dbsession, layerinfos, fieldname): attrinfos = layerinfos["attributes"][fieldname] table = attrinfos["table"] layertable = get_table(table, session=dbsession) column = attrinfos.get("column_name", fieldname) attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) return dbsession.query(distinct(attribute)).order_by(attribute).all()
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 get_vendees(self): """Return SQL query of vendees table for given date range.""" log.debug('get_vendees') subquery = SESSION.query( Vendee.document_id, func.string_agg( cast(Vendee.vendee_firstname, Text) + " " + cast(Vendee.vendee_lastname, Text), ', ').label('buyers')).group_by(Vendee.document_id).subquery() # log.debug(subquery) SESSION.close() return subquery
def get_all_proposals(self, year): sponsors_cte = lambda chamber_slug: ( db.session.query( Sponsorship.proposal_id.label('id'), func.string_agg( Person.first_name + " " + Person.last_name, ', ' ).label('namelist'), ) .join(Sponsorship.mandate) .join(Mandate.person) .join(Mandate.chamber) .filter(Chamber.slug == chamber_slug) .group_by(Sponsorship.proposal_id) .cte() ) cdep_cte = sponsors_cte('cdep') senate_cte = sponsors_cte('senat') query = ( db.session.query( Proposal, cdep_cte.c.namelist, senate_cte.c.namelist, ) .outerjoin(cdep_cte, cdep_cte.c.id == Proposal.id) .outerjoin(senate_cte, senate_cte.c.id == Proposal.id) .filter(Proposal.date >= date(year, 1, 1)) .filter(Proposal.date <= date(year, 12, 31)) .order_by(Proposal.date, Proposal.id) ) for (proposal, namelist_cdep, namelist_senate) in query: yield { 'title': proposal.title, 'url_cdep': proposal.url_cdep, 'url_senate': proposal.url_senate, 'number_cdep': proposal.number_cdep, 'number_senate': proposal.number_senate, 'number_bpi': proposal.number_bpi, 'date': proposal.date, 'modification_date': proposal.modification_date, 'status': proposal.status, 'namelist_cdep': namelist_cdep or "", 'namelist_senate': namelist_senate or "", }
def query_enumerate_attribute_values( dbsession: sqlalchemy.orm.Session, layerinfos: Dict[str, Any], fieldname: str) -> Set[Tuple[str, ...]]: attrinfos = layerinfos["attributes"][fieldname] table = attrinfos["table"] layertable = get_table(table, session=dbsession) column = attrinfos.get("column_name", fieldname) attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest( func.string_to_array(func.string_agg(attribute, separator), separator)) return set( cast(List[Tuple[str, ...]], dbsession.query(attribute).order_by(attribute).all()))
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest('Unknown layer: %s' % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos['attributes']: # pragma: no cover raise HTTPBadRequest('Unknown attribute: %s' % fieldname) dbsession = DBSessions.get( layerinfos.get('dbsession', general_dbsession_name), None) if dbsession is None: # pragma: no cover raise HTTPInternalServerError('No dbsession found for layer "%s"' % layername) layer_table = layerinfos.get('table', None) attrinfos = layerinfos['attributes'][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get('table', layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( 'No config table found for layer "%s"' % layername) layertable = get_table(table, DBSession=dbsession) column = attrinfos['column_name'] \ if 'column_name' in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a ',' we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if 'separator' in attrinfos: separator = attrinfos['separator'] attribute = func.unnest( func.string_to_array(func.string_agg(attribute, separator), separator)) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { 'items': [{ 'label': value[0], 'value': value[0] } for value in values] } return enum
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest("Unknown layer: %s" % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos["attributes"]: # pragma: no cover raise HTTPBadRequest("Unknown attribute: %s" % fieldname) dbsession = DBSessions.get( layerinfos.get("dbsession", general_dbsession_name), None ) if dbsession is None: # pragma: no cover raise HTTPInternalServerError( "No dbsession found for layer '%s'" % layername ) layer_table = layerinfos.get("table", None) attrinfos = layerinfos["attributes"][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get("table", layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( "No config table found for layer '%s'" % layername ) layertable = get_table(table, session=dbsession) column = attrinfos["column_name"] \ if "column_name" in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { "items": [{"label": value[0], "value": value[0]} for value in values] } return enum
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest('Unknown layer: %s' % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos['attributes']: # pragma: no cover raise HTTPBadRequest('Unknown attribute: %s' % fieldname) dbsession = DBSessions.get( layerinfos.get('dbsession', general_dbsession_name), None ) if dbsession is None: # pragma: no cover raise HTTPInternalServerError( 'No dbsession found for layer "%s"' % layername ) layer_table = layerinfos.get('table', None) attrinfos = layerinfos['attributes'][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get('table', layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( 'No config table found for layer "%s"' % layername ) layertable = get_table(table, session=dbsession) column = attrinfos['column_name'] \ if 'column_name' in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a ',' we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if 'separator' in attrinfos: separator = attrinfos['separator'] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { 'items': [{'label': value[0], 'value': value[0]} for value in values] } return enum
def update_video_feed_item_stars(date_from, date_to): # Find all star actions in this interval for which a friend of the star'ing user # has the video in their feed and update the stars list with these new stars at # the top. subscription_friend = Channel.query.\ join(Subscription, Subscription.channel == Channel.id).\ with_entities(Channel.owner.label('friendid'), Subscription.user.label('userid')) email_friend = ExternalFriend.query.\ join(User, (ExternalFriend.email == User.email) & (ExternalFriend.external_system == 'email')).\ with_entities(User.id.label('friendid'), ExternalFriend.user.label('userid')) external_friend = ExternalToken.query.\ join(ExternalFriend, (ExternalFriend.external_system == ExternalToken.external_system) & (ExternalFriend.external_uid == ExternalToken.external_uid)).\ with_entities(ExternalToken.user.label('friendid'), ExternalFriend.user.label('userid')) unioned = subscription_friend.union_all(email_friend, external_friend).subquery() feed_items = UserContentFeed.query.\ join(UserActivity, (UserActivity.action == 'star') & (UserActivity.date_actioned.between(date_from, date_to))).\ join(unioned, unioned.c.userid == UserContentFeed.user).\ filter((UserActivity.user == unioned.c.friendid) & (UserContentFeed.user != UserActivity.user) & (UserContentFeed.video_instance == UserActivity.object_id)).\ with_entities(UserContentFeed, func.string_agg(UserActivity.user, ' ')).\ group_by(UserContentFeed.id) star_limit = app.config.get('FEED_STARS_LIMIT', 3) for feed_item, new_stars in feed_items: old_stars = json.loads(feed_item.stars) if feed_item.stars else [] new_stars = [l for l in set(new_stars.split()) if l not in old_stars] stars = (new_stars + old_stars)[:star_limit] feed_item.stars = json.dumps(stars)
def get_vendors(self): """Return SQL query of vendors table for given date range.""" log.debug('get_vendors') subquery = SESSION.query( Vendor.document_id, func.string_agg( cast(Vendor.vendor_firstname, Text) + " " + cast(Vendor.vendor_lastname, Text), ', ' ).label('sellers') ).group_by( Vendor.document_id ).subquery() # log.debug(subquery) SESSION.close() return subquery
def get_all_proposals(self, year): sponsors_cte = lambda chamber_slug: ( db.session.query( Sponsorship.proposal_id.label("id"), func.string_agg(Person.first_name + " " + Person.last_name, ", ").label("namelist"), ) .join(Sponsorship.mandate) .join(Mandate.person) .join(Mandate.chamber) .filter(Chamber.slug == chamber_slug) .group_by(Sponsorship.proposal_id) .cte() ) cdep_cte = sponsors_cte("cdep") senate_cte = sponsors_cte("senat") query = ( db.session.query(Proposal, cdep_cte.c.namelist, senate_cte.c.namelist) .outerjoin(cdep_cte, cdep_cte.c.id == Proposal.id) .outerjoin(senate_cte, senate_cte.c.id == Proposal.id) .filter(Proposal.date >= date(year, 1, 1)) .filter(Proposal.date <= date(year, 12, 31)) .order_by(Proposal.date, Proposal.id) ) for (proposal, namelist_cdep, namelist_senate) in query: yield { "title": proposal.title, "url_cdep": proposal.url_cdep, "url_senate": proposal.url_senate, "number_cdep": proposal.number_cdep, "number_senate": proposal.number_senate, "number_bpi": proposal.number_bpi, "date": proposal.date, "modification_date": proposal.modification_date, "status": proposal.status, "namelist_cdep": namelist_cdep or "", "namelist_senate": namelist_senate or "", }
def intervenciones(con_etiquetas): """ devuelve un diccionario con los datos de las intervenciones incluyendo una lista de etiquetas y provincias """ if con_etiquetas is None: categoria = [Intervencion.etiquetas == None] else: categoria = [] query = self.request.dbsession.query( Intervencion, func.string_agg(Etiqueta.nombre, ';')).outerjoin( Intervencion.etiquetas).filter(*categoria).group_by( Intervencion.id).order_by( Intervencion.fecha.desc()).all() # build_cadena = lambda s: '' if s is None else str(s) def build_cadena(cadena): if cadena is None: return '' else: return str(cadena) intervenciones = [ dict( id=intervencion.id, fecha=intervencion.fecha.strftime('%d/%m/%Y'), nombre=intervencion.nombre, lista_etiquetas=set(build_cadena(etiquetas).split(";")) - set(provincias), lista_provincias=set( build_cadena(etiquetas).split(";")).intersection( set(provincias)), enlace="%s%s#page=%s" % (link, intervencion.referencia, intervencion.pagina), ) for intervencion, etiquetas in query ] return intervenciones
ch = Column(Integer) seq = Column(Integer) data = Column(LargeBinary) def __repr__(self): return f"<{type(self).__name__}({self._id},d:{self.data},ch:{self.ch},seq:{self.seq})>" Base.metadata.drop_all(engine) Base.metadata.create_all(engine) db = Session(engine) db.add(Blob(data=b'....aa....', ch=0, seq=0)) db.add(Blob(data=b'....bb....', ch=1, seq=1)) db.add(Blob(data=b'....cc....', ch=1, seq=0)) db.commit() list(map(print, db.query(Blob))) channel = 1 filter_ = and_(Blob.ch == channel) db.add( Blob(data=db.query( func.string_agg(Blob.data, aggregate_order_by( None, Blob.seq.asc()))).filter(filter_))) db.query(Blob).filter(filter_).delete(synchronize_session='fetch') db.commit() print('============= AFTER') list(map(print, db.query(Blob)))
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()
def get_chasse_bilan(params): columns = GenericTable('v_pre_bilan_pretty', 'oeasc_chasse', DB.engine).tableDef.columns localisation = ('zone_indicative' if params['id_zone_indicative'] else 'zone_cynegetique' if params['id_zone_cynegetique'] else 'secteur' if params['id_secteur'] else "") localisation_id_key = 'id_{}'.format(localisation) localisation_name_key = 'nom_{}'.format(localisation or 'espece') localisation_keys = (params['id_zone_indicative'] or params['id_zone_cynegetique'] or params['id_secteur'] or []) suffix = ('_zi' if params['id_zone_indicative'] else '_zc' if params['id_zone_cynegetique'] else '_secteur' if params['id_secteur'] else '_espece') res_keys = [ 'nb_realisation{}'.format(suffix), 'nb_realisation_avant_11{}'.format(suffix), 'nb_attribution_min{}'.format(suffix), 'nb_attribution_max{}'.format(suffix), ] name_keys = [ 'nom_espece', 'nom_saison', ] localisation_name_keys = ([localisation_name_key] if localisation_name_key else []) scope = (list( map(lambda k: (columns[k]), res_keys + name_keys + localisation_name_keys))) res = (DB.session.query(*scope).filter( columns['id_espece'] == params['id_espece'])) if localisation: res = res.filter(columns[localisation_id_key].in_(localisation_keys)) res = res.order_by(columns['nom_saison']) res = res.group_by(*(map(lambda k: columns[k], res_keys + name_keys + localisation_name_keys))) res = res.subquery() scope2 = (list(map(lambda k: func.sum(res.columns[k]), res_keys)) + list(map(lambda k: res.columns[k], name_keys))) if localisation_name_key: scope2.append(func.string_agg(res.columns[localisation_name_key], ', ')) res2 = (DB.session.query(*scope2).group_by( *(map(lambda k: res.columns[k], name_keys))).order_by( res.columns['nom_saison'])) res2 = res2.all() res = res2 if not res: return None out = {} query_keys = res_keys + name_keys for index, key in enumerate(res_keys): out[key.replace(suffix, '')] = [[ r[query_keys.index('nom_saison')], (int(r[index]) if r[index] is not None else 0) ] for r in res] out['taux_realisation'] = [[ out['nb_realisation'][i][0], out['nb_realisation'][i][1] / out['nb_attribution_max'][i][1] ] for i in range(len(out['nb_realisation']))] for key in name_keys: try: out[key] = res[0][query_keys.index(key)] if query_keys.index( key) else None except ValueError: pass if params['id_zone_indicative']: out['nom_zone_indicative'] = res[0][-1] elif params['id_zone_cynegetique']: out['nom_zone_cynegetique'] = res[0][-1] elif params['id_secteur']: out['nom_secteur'] = res[0][-1] return out
def db_results(searchparam=None, actit=None, genre=None): db_conn = None db_conn = db_engine.connect() # Si no se pone ninguna restricción if searchparam is None and genre is None: db_res = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_( db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid)).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) db_list = db_conn.execute(db_res) db_conn.close() return list(db_list) # Si hay restricciones que no son el género if searchparam is not None and genre is None: # Si hay que filtrar solo por Actor/Director if actit == 'actdir': query = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_( db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid, or_( db_acts.c.actorname.like( "%{0}%".format(searchparam)), db_dirs.c.directorname.like( "%{0}%".format(searchparam))))).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) result = db_conn.execute(query) db_conn.close() return list(result) # Si hay que filtrar solo por Titulo else: query = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_( db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid, db_films.c.movietitle.like( "%{0}%".format(searchparam)))).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) result = db_conn.execute(query) db_conn.close() return list(result) # Si solo tenemos que filtrar por género if searchparam is None and genre is not None: query = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_(db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid, db_films.c.movieid == db_filmgen.c.movieid, db_filmgen.c.genreid == db_genres.c.genreid, db_genres.c.genrename.like( "%{0}%".format(genre)))).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) result = db_conn.execute(query) db_conn.close() return list(result) else: # Si tenemos que buscar por género y filtrar por Actor/Director if actit == 'actdir': query = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_( db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid, db_films.c.movieid == db_filmgen.c.movieid, db_filmgen.c.genreid == db_genres.c.genreid, db_genres.c.genrename.like("%{0}%".format(genre)), or_( db_acts.c.actorname.like( "%{0}%".format(searchparam)), db_dirs.c.directorname.like( "%{0}%".format(searchparam))))).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) result = db_conn.execute(query) db_conn.close() return list(result) # Si tenemos que buscar por género y por Título else: query = select( [ db_films.c.movietitle, db_prod.c.prod_id, db_prod.c.price, db_prod.c.description, func.string_agg( db_dirs.c.directorname, aggregate_order_by(literal_column("'; '"), db_dirs.c.directorname)) ], distinct=True).where( and_( db_prod.c.movieid == db_films.c.movieid, db_films.c.movieid == db_actfilm.c.movieid, db_actfilm.c.actorid == db_acts.c.actorid, db_films.c.movieid == db_dirfilm.c.movieid, db_dirfilm.c.directorid == db_dirs.c.directorid, db_films.c.movietitle.like( "%{0}%".format(searchparam)), db_films.c.movieid == db_filmgen.c.movieid, db_filmgen.c.genreid == db_genres.c.genreid, db_genres.c.genrename.like( "%{0}%".format(genre)))).group_by( db_prod.c.prod_id, db_films.c.movietitle, db_acts.c.actorname) result = db_conn.execute(query) db_conn.close() return list(result)
class TRealisations(DB.Model): ''' Realisation of a circuit ''' __tablename__ = 't_realisations' __table_args__ = {'schema': 'oeasc_in', 'extend_existing': True} id_realisation = DB.Column(DB.Integer, primary_key=True) id_circuit = DB.Column(DB.Integer, DB.ForeignKey('oeasc_in.t_circuits.id_circuit')) serie = DB.Column(DB.Integer) groupes = DB.Column(DB.Integer) vent = DB.Column(DB.Unicode) temps = DB.Column(DB.Unicode) temperature = DB.Column(DB.Unicode) date_realisation = DB.Column(DB.Date) secteur = DB.relationship( TSecteurs, secondary="oeasc_in.t_circuits", primaryjoin="TRealisations.id_circuit == TCircuits.id_circuit", secondaryjoin="TCircuits.id_secteur == TSecteurs.id_secteur", uselist=False) circuit = DB.relationship(TCircuits, lazy='joined') observations = DB.relationship( TObservations, cascade="save-update, merge, delete, delete-orphan", lazy='joined') observers = DB.relationship( TObservers, secondary=cor_realisation_observer, lazy='joined', ) tags = DB.relationship(CorRealisationTag, cascade="save-update, merge, delete, delete-orphan", lazy='joined') observers_table = column_property( select([func.string_agg(TObservers.nom_observer, ', ')]).where( and_(TObservers.id_observer == CorRealisationObserver.id_observer, id_realisation == CorRealisationObserver.id_observer))) tags_table = column_property( select([ func.string_agg( func.concat(TTags.nom_tag, ' : ', case([(CorRealisationTag.valid, 'o')], else_='x')), ', ') ]).where( and_(CorRealisationTag.id_realisation == id_realisation, CorRealisationTag.id_tag == TTags.id_tag))) cerfs = column_property( select([TObservations.nb]).where( and_(TObservations.id_realisation == id_realisation, TObservations.id_espece == TEspeces.id_espece, TEspeces.nom_espece == 'Cerf'))) lievres = column_property( select([TObservations.nb]).where( and_(TObservations.id_realisation == id_realisation, TObservations.id_espece == TEspeces.id_espece, TEspeces.nom_espece == 'Lièvre'))) chevreuils = column_property( select([TObservations.nb]).where( and_(TObservations.id_realisation == id_realisation, TObservations.id_espece == TEspeces.id_espece, TEspeces.nom_espece == 'Chevreuil'))) renards = column_property( select([TObservations.nb]).where( and_(TObservations.id_realisation == id_realisation, TObservations.id_espece == TEspeces.id_espece, TEspeces.nom_espece == 'Renard')))