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 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 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 _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')))
