def venues(): # substitute NULLs in state and city with N/A - this will be a group city_venues = db.session.query(func.coalesce(Venue.city, 'N/A'), func.coalesce(Venue.state, 'N/A'), func.array_agg(Venue.id), func.array_agg(Venue.name)).group_by( Venue.city, Venue.state).all() data = [] for venue_data_tuple in city_venues: city_venue_dict = {} city_venue_dict["city"] = venue_data_tuple[0] city_venue_dict["state"] = venue_data_tuple[1] city_venue_dict["venues"] = [] for i in range(len(venue_data_tuple[2])): venue_dict = {} venue_dict["id"] = venue_data_tuple[2][i] venue_dict["name"] = venue_data_tuple[3][i] # count the number of shows linked to Venue that have start_time after now venue_dict["num_upcoming_shows"] = sum([ x.start_time > datetime.now(pytz.utc) for x in Venue.query.get(venue_data_tuple[2][i]).venue_shows ]) city_venue_dict["venues"].append(venue_dict) data.append(city_venue_dict) return render_template('pages/venues.html', areas=data)
def query(self): pq = qualstat_getstatdata(column("eval_type") == "f") base = alias(pq) query = (select([ func.array_agg(column("queryid")).label("queryids"), "qualid", cast(column("quals"), JSONB).label('quals'), "occurences", "execution_count", func.array_agg(column("query")).label("queries"), "avg_filter", "filter_ratio" ]).select_from( join(base, powa_databases, onclause=( powa_databases.c.oid == literal_column("dbid")))) .where(powa_databases.c.datname == bindparam("database")) .where(column("avg_filter") > 1000) .where(column("filter_ratio") > 0.3) .group_by(column("qualid"), column("execution_count"), column("occurences"), cast(column("quals"), JSONB), column("avg_filter"), column("filter_ratio")) .order_by(column("occurences").desc()) .limit(200)) return query
def _build_subquery(self): if not self.kwargs.get("tad_set_uuid"): return None set_pk = TadSet.objects.get(sodar_uuid=self.kwargs["tad_set_uuid"]).pk if self.kwargs["database_select"] == "refseq": term_join_gene_id = GeneInterval.sa.gene_id == Hgnc.sa.entrez_id else: # kwargs["database_select"] == "ensembl" term_join_gene_id = GeneInterval.sa.gene_id == Hgnc.sa.ensembl_gene_id query = (select([ func.array_agg( GeneInterval.sa.gene_id).label("itv_shared_gene_ids"), func.array_agg(Hgnc.sa.symbol).label("itv_shared_gene_symbols"), ]).select_from( GeneInterval.sa.table.outerjoin( Hgnc.sa.table, term_join_gene_id).outerjoin( TadInterval.sa.table, and_( TadInterval.sa.tad_set_id == set_pk, overlaps(TadInterval, StructuralVariant), ), )).where( and_( GeneInterval.sa.database == self.kwargs["database_select"], overlaps(TadInterval, GeneInterval), )).alias("subquery_genes_intervals_inner") ).lateral("subquery_genes_intervals_outer") return query
def attribute_pictures(language_code='en'): """ Retrieve attribute pictures in a given language. If no language code prameter supplied, the default language is English. :param language_code: language code, in lower case two letter format. Example: 'fr' for French :type language_code: string **Example requests**: .. sourcecode:: html # get attribute pictures in default language (English) GET {0}/api/v0.2/attributes/pictures # get attribute pictures in French GET {0}/api/v0.2/attributes/pictures/fr """ result = tree() picpath = conf.ATTRIBUTES_PICTURES_SITE_PATH query = db.session.query(Attribute.name, Category.name.label("category_name"), func.array_agg(picpath + '/' + language_code + '/' + AttributePicture.file_name).label("file_names"))\ .join(AttributePicture, Attribute.id==AttributePicture.attribute_id)\ .join(Category, Category.id==Attribute.category_id)\ .filter(AttributePicture.language_code==language_code)\ .group_by(Category.name, Attribute.name) for a in query: result[a.category_name][a.name] = a.file_names return jsonify(result)
def sql_jsonb_agg(table: Table) -> Any: """Equivalent to `func.JSONB_AGG` but removes empty elements from the output""" return func.to_jsonb( func.array_remove( func.array_agg(table.table_valued().distinct()), None, )).label(table.name)
def query_parts(): """Query Product objects.""" data = request.get_json().get("query") try: return_data = QuerySchema(["search"]).load(data) # Return silent errors if len(return_data.errors) > 0: raise ValidationError(next(iter(return_data.errors.values()))[0]) data = return_data.data except ValidationError as e: return e.messages[0], 400 search = data["search"] sort_by = data["sort_by"] limit = data["limit"] offset = data["offset"] query = db.session.query( Product.id, Product.name, Product.supplier_id, Supplier.name.label("supplier_name"), Product.units_in_stock, Product.unit_price, Product.part_number, Product.image_name, Product.image, func.array_agg(ProductCategory.category_id).label("product_category_list") ).join( Supplier, Supplier.id == Product.supplier_id, isouter=True ).join( ProductCategory, ProductCategory.product_id == Product.id, isouter=True ).filter( or_( Product.name.ilike(search + "%"), Product.name.ilike("% " + search + "%") ) ).group_by( Product.id, Supplier.name ) if sort_by == "id": query = query.order_by(Product.id) elif sort_by == "alpha": query = query.order_by(Product.name) query = query.offset(offset * limit).limit(limit + 1) query = query.all() query = process_result(query, ["name"]) return jsonify(result=populate_return(query, limit, offset))
def test_cloned_alias(self): entity = table( "entity", column("id"), column("employer_id"), column("name") ) tag = table("tag", column("tag"), column("entity_id")) tags = ( select([tag.c.entity_id, func.array_agg(tag.c.tag).label("tags")]) .group_by(tag.c.entity_id) .cte("unaliased_tags") ) entity_tags = tags.alias(name="entity_tags") employer_tags = tags.alias(name="employer_tags") q = ( select([entity.c.name]) .select_from( entity.outerjoin( entity_tags, tags.c.entity_id == entity.c.id ).outerjoin( employer_tags, tags.c.entity_id == entity.c.employer_id ) ) .where(entity_tags.c.tags.op("@>")(bindparam("tags"))) .where(employer_tags.c.tags.op("@>")(bindparam("tags"))) ) self.assert_compile( q, "WITH unaliased_tags AS " "(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags " "FROM tag GROUP BY tag.entity_id)" " SELECT entity.name " "FROM entity " "LEFT OUTER JOIN unaliased_tags AS entity_tags ON " "unaliased_tags.entity_id = entity.id " "LEFT OUTER JOIN unaliased_tags AS employer_tags ON " "unaliased_tags.entity_id = entity.employer_id " "WHERE (entity_tags.tags @> :tags) AND " "(employer_tags.tags @> :tags)", ) cloned = q.params(tags=["tag1", "tag2"]) self.assert_compile( cloned, "WITH unaliased_tags AS " "(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags " "FROM tag GROUP BY tag.entity_id)" " SELECT entity.name " "FROM entity " "LEFT OUTER JOIN unaliased_tags AS entity_tags ON " "unaliased_tags.entity_id = entity.id " "LEFT OUTER JOIN unaliased_tags AS employer_tags ON " "unaliased_tags.entity_id = entity.employer_id " "WHERE (entity_tags.tags @> :tags) AND " "(employer_tags.tags @> :tags)", )
def test_cloned_alias(self): entity = table( 'entity', column('id'), column('employer_id'), column('name')) tag = table('tag', column('tag'), column('entity_id')) tags = select([ tag.c.entity_id, func.array_agg(tag.c.tag).label('tags'), ]).group_by(tag.c.entity_id).cte('unaliased_tags') entity_tags = tags.alias(name='entity_tags') employer_tags = tags.alias(name='employer_tags') q = ( select([entity.c.name]) .select_from( entity .outerjoin(entity_tags, tags.c.entity_id == entity.c.id) .outerjoin(employer_tags, tags.c.entity_id == entity.c.employer_id) ) .where(entity_tags.c.tags.op('@>')(bindparam('tags'))) .where(employer_tags.c.tags.op('@>')(bindparam('tags'))) ) self.assert_compile( q, 'WITH unaliased_tags AS ' '(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags ' 'FROM tag GROUP BY tag.entity_id)' ' SELECT entity.name ' 'FROM entity ' 'LEFT OUTER JOIN unaliased_tags AS entity_tags ON ' 'unaliased_tags.entity_id = entity.id ' 'LEFT OUTER JOIN unaliased_tags AS employer_tags ON ' 'unaliased_tags.entity_id = entity.employer_id ' 'WHERE (entity_tags.tags @> :tags) AND ' '(employer_tags.tags @> :tags)' ) cloned = q.params(tags=['tag1', 'tag2']) self.assert_compile( cloned, 'WITH unaliased_tags AS ' '(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags ' 'FROM tag GROUP BY tag.entity_id)' ' SELECT entity.name ' 'FROM entity ' 'LEFT OUTER JOIN unaliased_tags AS entity_tags ON ' 'unaliased_tags.entity_id = entity.id ' 'LEFT OUTER JOIN unaliased_tags AS employer_tags ON ' 'unaliased_tags.entity_id = entity.employer_id ' 'WHERE (entity_tags.tags @> :tags) AND ' '(employer_tags.tags @> :tags)')
def available_translations(): #""" #Retrieve language codes of available translations #""" result = tree() subquery = db.session.query(Translation.language_code).group_by(Translation.language_code).subquery() query = db.session.query(func.array_agg(subquery.c.language_code)).first() #for language in available_languages result["languages"]= query[0] return jsonify_utf8(result)
def on_get(self, req, resp, phrase_id): columns = [] columns.extend(phrase.c) columns.append( f.coalesce( select([f.array_agg(gs_phrase.c.section_id)]). where(gs_phrase.c.phrase_id == phrase_id). as_scalar(), [] ). label("grammar_sections") ) columns.append( f.coalesce( select([f.array_agg(theme_phrase.c.theme_id)]). where(theme_phrase.c.phrase_id == phrase_id). as_scalar(), [] ). label("themes") ) sel = select(columns).where(phrase.c.id == phrase_id) resp.body = phrase_editor_view(self.db.execute(sel).fetchone())
def available_translations(): """ Retrieve language codes of available translations """ result = tree() subquery = db.session.query(Translation.language_code).group_by( Translation.language_code).subquery() available_languages = db.session.query( func.array_agg(subquery.c.language_code)).first() #for language in available_languages result["languages"] = available_languages[0] return Response(json.dumps(result, indent=3), mimetype='application/json;charset=utf-8')
def query(self): pq = qualstat_getstatdata(bindparam("server"), column("eval_type") == "f") base = alias(pq) query = ( select([ # queryid in pg11+ is int64, so the value can exceed javascript's # Number.MAX_SAFE_INTEGER, which mean that the value can get # truncated by the browser, leading to looking for unexisting # queryid when processing this data. To avoid that, simply cast # the value to text. func.array_agg(cast(column("queryid"), TEXT)).label("queryids"), column("qualid"), cast(column("quals"), JSONB).label('quals'), column("occurences"), column("execution_count"), func.array_agg(column("query")).label("queries"), column("avg_filter"), column("filter_ratio") ]).select_from( join(base, powa_databases, onclause=(powa_databases.c.oid == literal_column("dbid") and powa_databases.c.srvid == literal_column("srvid")))). where(powa_databases.c.datname == bindparam("database")).where( powa_databases.c.srvid == bindparam("server")).where( column("avg_filter") > 1000).where( column("filter_ratio") > 0.3).group_by( column("qualid"), column("execution_count"), column("occurences"), cast(column("quals"), JSONB), column("avg_filter"), column("filter_ratio")).order_by( column("occurences").desc()).limit(200)) return query
def test_cloned_alias(self): entity = table('entity', column('id'), column('employer_id'), column('name')) tag = table('tag', column('tag'), column('entity_id')) tags = select([ tag.c.entity_id, func.array_agg(tag.c.tag).label('tags'), ]).group_by(tag.c.entity_id).cte('unaliased_tags') entity_tags = tags.alias(name='entity_tags') employer_tags = tags.alias(name='employer_tags') q = (select([entity.c.name]).select_from( entity.outerjoin( entity_tags, tags.c.entity_id == entity.c.id).outerjoin( employer_tags, tags.c.entity_id == entity.c.employer_id)).where( entity_tags.c.tags.op('@>')(bindparam('tags'))).where( employer_tags.c.tags.op('@>')(bindparam('tags')))) self.assert_compile( q, 'WITH unaliased_tags AS ' '(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags ' 'FROM tag GROUP BY tag.entity_id)' ' SELECT entity.name ' 'FROM entity ' 'LEFT OUTER JOIN unaliased_tags AS entity_tags ON ' 'unaliased_tags.entity_id = entity.id ' 'LEFT OUTER JOIN unaliased_tags AS employer_tags ON ' 'unaliased_tags.entity_id = entity.employer_id ' 'WHERE (entity_tags.tags @> :tags) AND ' '(employer_tags.tags @> :tags)') cloned = q.params(tags=['tag1', 'tag2']) self.assert_compile( cloned, 'WITH unaliased_tags AS ' '(SELECT tag.entity_id AS entity_id, array_agg(tag.tag) AS tags ' 'FROM tag GROUP BY tag.entity_id)' ' SELECT entity.name ' 'FROM entity ' 'LEFT OUTER JOIN unaliased_tags AS entity_tags ON ' 'unaliased_tags.entity_id = entity.id ' 'LEFT OUTER JOIN unaliased_tags AS employer_tags ON ' 'unaliased_tags.entity_id = entity.employer_id ' 'WHERE (entity_tags.tags @> :tags) AND ' '(employer_tags.tags @> :tags)')
def unified_package_view(template, query_fn=None, **template_args): untracked = request.args.get('untracked') == '1' order_name = request.args.get('order_by', 'running,failing,name') # pylint: disable=E1101 subq = db.query(Package.name, func.array_agg(func.row(Package.collection_id, Package.tracked, Package.resolved, Package.last_complete_build_state)) .label("states"), func.bool_or(Package.resolved).label("resolved"), func.bool_or(Package.last_complete_build_state == Build.FAILED) .label("failing"), func.bool_or(Package.last_complete_build_id != Package.last_build_id) .label("has_running_build"))\ .filter(Package.blocked == False)\ .group_by(Package.name) if not untracked: subq = subq.filter(Package.tracked == True) if query_fn: subq = query_fn(subq) subq = subq.subquery() order_map = { 'name': [subq.c.name], 'failing': [subq.c.resolved, Reversed(subq.c.failing)], 'running': [NullsLastOrder(subq.c.has_running_build)], } order_names, order = get_order(order_map, order_name) package_query = db.query(subq.c.name, subq.c.states, subq.c.has_running_build) page = package_query.order_by(*order).paginate(packages_per_page) page.items = map(UnifiedPackage, page.items) populate_package_groups(page.items) return render_template(template, packages=page.items, page=page, order=order_names, collection=None, **template_args)
def get_tile_ids_by_group(group_by, filters=None): from splice.environment import Environment env = Environment.instance() group_by_field = { 'category': AdgroupCategory.category, 'account_id': Account.id, 'campaign_id': Campaign.id, 'adgroup_id': Adgroup.id }.get(group_by) rows = (env.db.session.query( group_by_field.label(group_by), func.array_agg(Tile.id).label('tile_ids')).select_from(Tile).group_by( group_by_field)) rows = add_joins_for_group_by(query=rows, group_by=group_by) rows = add_filters(rows, filters, group_by) rows = rows.all() return [tuple_to_dict(r) for r in rows] if rows else None
def attribute_pictures(language_code='en'): """ Retrieve attribute pictures :param language_code: language code :type language_code: string """ result = tree() picpath = conf.ATTRIBUTES_PICTURES_SITE_PATH query = db.session.query(Attribute.name, Category.name.label("category_name"), func.array_agg(picpath + '/' + language_code + '/' + AttributePicture.file_name).label("file_names"))\ .join(AttributePicture, Attribute.id==AttributePicture.attribute_id)\ .join(Category, Category.id==Attribute.category_id)\ .filter(AttributePicture.language_code==language_code)\ .group_by(Category.name, Attribute.name) for a in query: result[a.category_name][a.name] = a.file_names return jsonify(result)
def counthistory(page): ''' Count history for all shelters for the past 14 days. Used for chart showing counts over time. Supports pagination with page in path ''' tz = Prefs['timezone'] pagesize = 14 # days daysback = int(page) * pagesize + pagesize - 1 today = pendulum.today(tz).subtract(days=(int(page) * pagesize)) backthen = pendulum.today(tz).subtract(days=daysback) date_list = func.generate_series(cast(backthen.to_date_string(), Date), cast(today.to_date_string(), Date), '1 day').alias('gen_day') time_series = db.session.query(Shelter.name.label('label'), func.array_agg(Count.personcount).label('data'))\ .join(date_list, true())\ .outerjoin(Count, (Count.day == column('gen_day')) & (Count.shelter_id == Shelter.id)) if 'user' not in g or set(['admin', 'visitor']).isdisjoint( set([role.name for role in g.user.roles])): time_series = time_series.filter(Shelter.visible == True, Shelter.public) else: time_series = time_series.filter(Shelter.visible == True) time_series = time_series\ .group_by(Shelter.name)\ .order_by(Shelter.name) results = { "dates": [d.to_date_string() for d in (today - backthen)], "shelters": [row._asdict() for row in time_series] } return jsonify(results)
def build_sqlalchemy_columns( self, fields, base_query_tables=None, custom_columns=None): ''' Returns an ordered dict of sqlalchemy.sql.schema.Column objects, associated with the sqlalchemy.sql.schema.Table definitions, which are bound to the sqlalchemy.engine.Engine: "Connects a Pool and Dialect together to provide a source of database connectivity and behavior." @param fields - field definitions, from the resource schema @param bridge - a reports.utils.sqlalchemy_bridge.Bridge @param base_query_tables - if specified, the fields for these tables will be available as part of the base query, so the column definitions become simpler, and do not need to be joined in. @param manual_includes - columns to include even if the field visibility is not set ''' DEBUG_BUILD_COLUMNS = False or logger.isEnabledFor(logging.DEBUG) base_query_tables = base_query_tables or [] custom_columns = custom_columns or [] try: columns = OrderedDict() for field in fields: key = field['key'] if key in custom_columns: if DEBUG_BUILD_COLUMNS: logger.info( 'custom field: %r, %r', key,custom_columns[key]) columns[key] = custom_columns[key].label(key) continue if DEBUG_BUILD_COLUMNS: logger.info('build column: %r, %r', field['key'], field) field_name = field.get('field', None) if not field_name: field_name = field['key'] field_table = field.get('table', None) if not field_table and DEBUG_BUILD_COLUMNS: logger.info( 'field: %r, val: %r, skipping field because there is no ' '"field_table" value set',key,field) continue if DEBUG_BUILD_COLUMNS: logger.info( 'field: %r, field_table: %r', field['key'], field_table ) if field_table in base_query_tables: # simple case: table.fields already selected in the base query: # just need to specify them if field_name in get_tables()[field_table].c: col = get_tables()[field_table].c[field_name] else: raise Exception( 'field: %r, not found in table: %r' % (field_name, field_table)) col = col.label(key) columns[key] = col elif field.get('linked_field_value_field', None): link_table = field['table'] link_table_def = get_tables()[link_table] linked_field_parent = field['linked_field_parent'] link_field = linked_field_parent + '_id' join_args = { 'link_table': link_table, 'link_field': link_field, 'parent_table': linked_field_parent } if field['linked_field_type'] != 'fields.ListField': join_stmt = select([link_table_def.c[field_name]]).\ where(text('{link_table}.{link_field}=' '{parent_table}.{link_field}'.format(**join_args))) if field.get('linked_field_meta_field', None): # TODO: test - the linked meta field is the "datacolumn type" linked_field_meta_field = field['linked_field_meta_field'] meta_field_obj = MetaHash.objects.get( key=field['key'], scope=field['scope']) meta_table_def = get_tables()['metahash'] join_stmt.join(meta_table_def, link_table_def.c[linked_field_meta_field]== getattr(meta_field_obj,'pk') ) join_stmt = join_stmt.label(key) columns[key] = join_stmt elif field['linked_field_type'] == 'fields.ListField': join_stmt = select([link_table_def.c[field_name]]).\ where(text('{link_table}.{link_field}=' '{parent_table}.{link_field}'.format(**join_args))) if field.get('linked_field_meta_field', None): # TODO: test - the linked meta field is the "datacolumn type" linked_field_meta_field = field['linked_field_meta_field'] meta_field_obj = MetaHash.objects.get( key=field['key'], scope=field['scope']) meta_table_def = get_tables()['metahash'] join_stmt.join(meta_table_def, link_table_def.c[linked_field_meta_field]== getattr(meta_field_obj,'pk') ) ordinal_field = field.get('ordinal_field', None) if ordinal_field: join_stmt = join_stmt.order_by(link_table_def.c[ordinal_field]) join_stmt = join_stmt.alias('a') stmt2 = select([func.array_to_string( func.array_agg(column(field_name)), LIST_DELIMITER_SQL_ARRAY)]) stmt2 = stmt2.select_from(join_stmt).label(key) columns[key] = stmt2 else: if DEBUG_BUILD_COLUMNS: logger.info( 'field is not in the base tables %r, nor in a linked field, ' 'and is not custom: %s', base_query_tables, key) if DEBUG_BUILD_COLUMNS: logger.info('columns: %r', columns.keys()) return columns except Exception, e: logger.exception('on build sqlalchemy columns') raise e
class CoreFixtures(object): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=Integer)), text("select a, b, c from table where foo=:foo").bindparams( bindparam("foo", type_=Integer)), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=String)), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), column("z") + column("x"), column("z") - column("x"), column("x") - column("z"), column("z") > column("x"), column("x").in_([5, 7]), column("x").in_([10, 7, 8]), # note these two are mathematically equivalent but for now they # are considered to be different column("z") >= column("x"), column("x") <= column("z"), column("q").between(5, 6), column("q").between(5, 6, symmetric=True), column("q").like("somstr"), column("q").like("somstr", escape="\\"), column("q").like("somstr", escape="X"), ), lambda: ( table_a.c.a, table_a.c.a._annotate({"orm": True}), table_a.c.a._annotate({ "orm": True })._annotate({"bar": False}), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("a", table_a) }), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("b", table_a) }), table_a.c.a._annotate( { "orm": True, "parententity": MyEntity("b", select([table_a])) }), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("b", select([table_a]).where(table_a.c.a == 5)), }), ), lambda: ( table_a, table_a._annotate({"orm": True}), table_a._annotate({ "orm": True })._annotate({"bar": False}), table_a._annotate({ "orm": True, "parententity": MyEntity("a", table_a) }), table_a._annotate({ "orm": True, "parententity": MyEntity("b", table_a) }), table_a._annotate({ "orm": True, "parententity": MyEntity("b", select([table_a])) }), ), lambda: ( table("a", column("x"), column("y")), table("a", column("x"), column("y"))._annotate({"orm": True}), table("b", column("x"), column("y"))._annotate({"orm": True}), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(), ), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_(1, 2), tuple_(3, 4)), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b, column("q")), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over(order_by=table_a.c.a, partition_by=table_a.c.b), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case(whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ]), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({ "wendy": "W", "jack": "J" }, value=table_a.c.a, else_="E"), case({ "wendy": "W", "jack": "J" }, value=table_a.c.b, else_="E"), case({ "wendy_w": "W", "jack": "J" }, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.b, table_a.c.a]).apply_labels(), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update( nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]).where(table_a.c.b == 5).correlate_except( table_b), ), lambda: ( future_select(table_a.c.a), future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.a ), future_select(table_a.c.a).join_from(table_a, table_b, table_a.c.a == table_b.c.a), future_select(table_a.c.a).join_from(table_a, table_b), future_select(table_a.c.a).join_from(table_c, table_b), future_select(table_a.c.a).join( table_b, table_a.c.a == table_b.c.a).join( table_c, table_b.c.b == table_c.c.x), future_select(table_a.c.a).join(table_b), future_select(table_a.c.a).join(table_c), future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.b ), future_select(table_a.c.a).join(table_c, table_a.c.a == table_c.c.x ), ), lambda: ( select([table_a.c.a]).cte(), select([table_a.c.a]).cte(recursive=True), select([table_a.c.a]).cte(name="some_cte", recursive=True), select([table_a.c.a]).cte(name="some_cte"), select([table_a.c.a]).cte(name="some_cte").alias("other_cte"), select([table_a.c.a]).cte(name="some_cte").union_all( select([table_a.c.a])), select([table_a.c.a]).cte(name="some_cte").union_all( select([table_a.c.b])), select([table_a.c.a]).lateral(), select([table_a.c.a]).lateral(name="bar"), table_a.tablesample(func.bernoulli(1)), table_a.tablesample(func.bernoulli(1), seed=func.random()), table_a.tablesample(func.bernoulli(1), seed=func.other_random()), table_a.tablesample(func.hoho(1)), table_a.tablesample(func.bernoulli(1), name="bar"), table_a.tablesample( func.bernoulli(1), name="bar", seed=func.random()), ), lambda: ( table_a.insert(), table_a.insert().values({})._annotate({"nocache": True}), table_b.insert(), table_b.insert().with_dialect_options(sqlite_foo="some value"), table_b.insert().from_select(["a", "b"], select([table_a])), table_b.insert().from_select(["a", "b"], select([table_a]).where(table_a.c.a > 5)), table_b.insert().from_select(["a", "b"], select([table_b])), table_b.insert().from_select(["c", "d"], select([table_a])), table_b.insert().returning(table_b.c.a), table_b.insert().returning(table_b.c.a, table_b.c.b), table_b.insert().inline(), table_b.insert().prefix_with("foo"), table_b.insert().with_hint("RUNFAST"), table_b.insert().values(a=5, b=10), table_b.insert().values(a=5), table_b.insert().values({ table_b.c.a: 5, "b": 10 })._annotate({"nocache": True}), table_b.insert().values(a=7, b=10), table_b.insert().values(a=5, b=10).inline(), table_b.insert().values([{ "a": 5, "b": 10 }, { "a": 8, "b": 12 }])._annotate({"nocache": True}), table_b.insert().values([{ "a": 9, "b": 10 }, { "a": 8, "b": 7 }])._annotate({"nocache": True}), table_b.insert().values([(5, 10), (8, 12)])._annotate({"nocache": True}), table_b.insert().values([(5, 9), (5, 12)])._annotate({"nocache": True}), ), lambda: ( table_b.update(), table_b.update().where(table_b.c.a == 5), table_b.update().where(table_b.c.b == 5), table_b.update().where(table_b.c.b == 5).with_dialect_options( mysql_limit=10), table_b.update().where(table_b.c.b == 5).with_dialect_options( mysql_limit=10, sqlite_foo="some value"), table_b.update().where(table_b.c.a == 5).values(a=5, b=10), table_b.update().where(table_b.c.a == 5).values(a=5, b=10, c=12), table_b.update().where(table_b.c.b == 5).values(a=5, b=10). _annotate({"nocache": True}), table_b.update().values(a=5, b=10), table_b.update().values({ "a": 5, table_b.c.b: 10 })._annotate({"nocache": True}), table_b.update().values(a=7, b=10), table_b.update().ordered_values(("a", 5), ("b", 10)), table_b.update().ordered_values(("b", 10), ("a", 5)), table_b.update().ordered_values((table_b.c.a, 5), ("b", 10)), ), lambda: ( table_b.delete(), table_b.delete().with_dialect_options(sqlite_foo="some value"), table_b.delete().where(table_b.c.a == 5), table_b.delete().where(table_b.c.b == 5), ), lambda: ( values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myothervalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 89), (2, "textG", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mynottext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), # TODO: difference in type # values( # [ # column("mykey", Integer), # column("mytext", Text), # column("myint", Integer), # ], # (1, "textA", 99), # (2, "textB", 88), # alias_name="myvalues", # ), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).prefix_with("foo"), select([table_a.c.a]).prefix_with("foo", dialect="mysql"), select([table_a.c.a]).prefix_with("foo", dialect="postgresql"), select([table_a.c.a]).prefix_with("bar"), select([table_a.c.a]).suffix_with("bar"), ), lambda: ( select([table_a_2.c.a]), select([table_a_2_fs.c.a]), select([table_a_2_bs.c.a]), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).with_hint(None, "some hint"), select([table_a.c.a]).with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some hint"), select([table_a.c.a]).with_hint(table_a, "some hint").with_hint( None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some other hint"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="mysql"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="postgresql"), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join(table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( SelectStatementGrouping(select([table_a])), SelectStatementGrouping(select([table_b])), ), lambda: ( select([table_a.c.a]).scalar_subquery(), select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: (table_a, table_b), ] dont_compare_values_fixtures = [ lambda: ( # note the in_(...) all have different column names becuase # otherwise all IN expressions would compare as equivalent column("x").in_(random_choices(range(10), k=3)), column("y").in_( bindparam( "q", random_choices(range(10), k=random.randint(0, 7)), expanding=True, )), column("z").in_(random_choices(range(10), k=random.randint(0, 7))), column("x") == random.randint(1, 10), ) ] def _complex_fixtures(): def one(): a1 = table_a.alias() a2 = table_b_like_a.alias() stmt = (select([table_a.c.a, a1.c.b, a2.c.b]).where(table_a.c.b == a1.c.b).where( a1.c.b == a2.c.b).where(a1.c.a == 5)) return stmt def one_diff(): a1 = table_b_like_a.alias() a2 = table_a.alias() stmt = (select([table_a.c.a, a1.c.b, a2.c.b]).where(table_a.c.b == a1.c.b).where( a1.c.b == a2.c.b).where(a1.c.a == 5)) return stmt def two(): inner = one().subquery() stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from( table_b.join(inner, table_b.c.b == inner.c.b)) return stmt def three(): a1 = table_a.alias() a2 = table_a.alias() ex = exists().where(table_b.c.b == a1.c.a) stmt = (select([a1.c.a, a2.c.a]).select_from( a1.join(a2, a1.c.b == a2.c.b)).where(ex)) return stmt return [one(), one_diff(), two(), three()] fixtures.append(_complex_fixtures) def _statements_w_context_options_fixtures(): return [ select([table_a])._add_context_option(opt1, True), select([table_a])._add_context_option(opt1, 5), select([table_a])._add_context_option(opt1, True)._add_context_option( opt2, True), select([table_a ])._add_context_option(opt1, True)._add_context_option(opt2, 5), select([table_a])._add_context_option(opt3, True), ] fixtures.append(_statements_w_context_options_fixtures) def _statements_w_anonymous_col_names(): def one(): c = column("q") l = c.label(None) # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d subq = select([l]).subquery() # this creates a ColumnClause as a proxy to the Label() that has # an anoymous name, so the column has one too. anon_col = subq.c[0] # then when BindParameter is created, it checks the label # and doesn't double up on the anonymous name which is uncachable return anon_col > 5 def two(): c = column("p") l = c.label(None) # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d subq = select([l]).subquery() # this creates a ColumnClause as a proxy to the Label() that has # an anoymous name, so the column has one too. anon_col = subq.c[0] # then when BindParameter is created, it checks the label # and doesn't double up on the anonymous name which is uncachable return anon_col > 5 def three(): l1, l2 = table_a.c.a.label(None), table_a.c.b.label(None) stmt = select([table_a.c.a, table_a.c.b, l1, l2]) subq = stmt.subquery() return select([subq]).where(subq.c[2] == 10) return ( one(), two(), three(), ) fixtures.append(_statements_w_anonymous_col_names)
if __name__ == "__main__": engine = create_engine(PSQL_CONNECTION_STRING, echo=False) Session = sessionmaker(bind=engine) session = Session() # query get average score of ratings for each year averageScores = session.query(func.avg( Review.score), Review.pub_year).group_by(Review.pub_year).all() # query get best new music scores are scores for somgs that weren't best new music bestNewMusicScores = session.query( Review.score).filter(Review.best_new_music == 1).all() bestNewMusicOrNotScores = (session.query( Review.best_new_music, func.array_agg(Review.score)).group_by(Review.best_new_music).all()) bestNewMusicByYear = (session.query( Review.pub_year, func.array_agg(Review.score)).group_by( Review.pub_year).filter(Review.best_new_music == 1).all()) # Join query for Genre and Review to see reviews by genre averageReviewsScoreByGenre = (session.query( Genre.genre, func.avg(Review.score)).filter( Genre.genre != None, Review.best_new_music == 1).join( Review, Genre.reviewid == Review.reviewid).group_by(Genre.genre).all()) # Lists of scores by genre allScoresByGenre = (session.query(Genre.genre, func.array_agg( Review.score)).filter(Genre.genre != None).join( Review, Genre.reviewid == Review.reviewid).group_by(Genre.genre).all())
class CoreFixtures(object): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer ), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=Integer) ), text("select a, b, c from table where foo=:foo").bindparams( bindparam("foo", type_=Integer) ), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=String) ), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), column("z") + column("x"), column("z") - column("x"), column("x") - column("z"), column("z") > column("x"), column("x").in_([5, 7]), column("x").in_([10, 7, 8]), # note these two are mathematically equivalent but for now they # are considered to be different column("z") >= column("x"), column("x") <= column("z"), column("q").between(5, 6), column("q").between(5, 6, symmetric=True), column("q").like("somstr"), column("q").like("somstr", escape="\\"), column("q").like("somstr", escape="X"), ), lambda: ( table_a.c.a, table_a.c.a._annotate({"orm": True}), table_a.c.a._annotate({"orm": True})._annotate({"bar": False}), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("a", table_a)} ), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("b", table_a)} ), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("b", select([table_a]))} ), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(),), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_(1, 2), tuple_(3, 4)), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group( table_a.c.a, table_a.c.b, column("q") ), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over( order_by=table_a.c.a, partition_by=table_a.c.b ), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case( whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ] ), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({"wendy": "W", "jack": "J"}, value=table_a.c.a, else_="E"), case({"wendy": "W", "jack": "J"}, value=table_a.c.b, else_="E"), case({"wendy_w": "W", "jack": "J"}, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]) .where(table_a.c.b == 5) .where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]) .where(table_a.c.b == 5) .with_for_update(nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]) .where(table_a.c.b == 5) .correlate_except(table_b), ), lambda: ( select([table_a.c.a]).cte(), select([table_a.c.a]).cte(recursive=True), select([table_a.c.a]).cte(name="some_cte", recursive=True), select([table_a.c.a]).cte(name="some_cte"), select([table_a.c.a]).cte(name="some_cte").alias("other_cte"), select([table_a.c.a]) .cte(name="some_cte") .union_all(select([table_a.c.a])), select([table_a.c.a]) .cte(name="some_cte") .union_all(select([table_a.c.b])), select([table_a.c.a]).lateral(), select([table_a.c.a]).lateral(name="bar"), table_a.tablesample(func.bernoulli(1)), table_a.tablesample(func.bernoulli(1), seed=func.random()), table_a.tablesample(func.bernoulli(1), seed=func.other_random()), table_a.tablesample(func.hoho(1)), table_a.tablesample(func.bernoulli(1), name="bar"), table_a.tablesample( func.bernoulli(1), name="bar", seed=func.random() ), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).prefix_with("foo"), select([table_a.c.a]).prefix_with("foo", dialect="mysql"), select([table_a.c.a]).prefix_with("foo", dialect="postgresql"), select([table_a.c.a]).prefix_with("bar"), select([table_a.c.a]).suffix_with("bar"), ), lambda: ( select([table_a_2.c.a]), select([table_a_2_fs.c.a]), select([table_a_2_bs.c.a]), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).with_hint(None, "some hint"), select([table_a.c.a]).with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some hint"), select([table_a.c.a]) .with_hint(table_a, "some hint") .with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some other hint"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="mysql" ), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="postgresql" ), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join( table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1) ), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( SelectStatementGrouping(select([table_a])), SelectStatementGrouping(select([table_b])), ), lambda: ( select([table_a.c.a]).scalar_subquery(), select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: (table_a, table_b), ] dont_compare_values_fixtures = [ lambda: ( # note the in_(...) all have different column names becuase # otherwise all IN expressions would compare as equivalent column("x").in_(random_choices(range(10), k=3)), column("y").in_( bindparam( "q", random_choices(range(10), k=random.randint(0, 7)), expanding=True, ) ), column("z").in_(random_choices(range(10), k=random.randint(0, 7))), column("x") == random.randint(1, 10), ) ] def _complex_fixtures(): def one(): a1 = table_a.alias() a2 = table_b_like_a.alias() stmt = ( select([table_a.c.a, a1.c.b, a2.c.b]) .where(table_a.c.b == a1.c.b) .where(a1.c.b == a2.c.b) .where(a1.c.a == 5) ) return stmt def one_diff(): a1 = table_b_like_a.alias() a2 = table_a.alias() stmt = ( select([table_a.c.a, a1.c.b, a2.c.b]) .where(table_a.c.b == a1.c.b) .where(a1.c.b == a2.c.b) .where(a1.c.a == 5) ) return stmt def two(): inner = one().subquery() stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from( table_b.join(inner, table_b.c.b == inner.c.b) ) return stmt def three(): a1 = table_a.alias() a2 = table_a.alias() ex = exists().where(table_b.c.b == a1.c.a) stmt = ( select([a1.c.a, a2.c.a]) .select_from(a1.join(a2, a1.c.b == a2.c.b)) .where(ex) ) return stmt return [one(), one_diff(), two(), three()] fixtures.append(_complex_fixtures)
class CompareAndCopyTest(fixtures.TestBase): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(), ), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_([1, 2]), tuple_([3, 4])), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b, column("q")), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over(order_by=table_a.c.a, partition_by=table_a.c.b), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case(whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ]), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({ "wendy": "W", "jack": "J" }, value=table_a.c.a, else_="E"), case({ "wendy": "W", "jack": "J" }, value=table_a.c.b, else_="E"), case({ "wendy_w": "W", "jack": "J" }, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update( nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]).where(table_a.c.b == 5).correlate_except( table_b), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join(table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( select([table_a.c.a]).as_scalar(), select([table_a.c.a]).where(table_a.c.b == 5).as_scalar(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: ( Table("a", MetaData(), Column("q", Integer), Column("b", String)), Table("b", MetaData(), Column("q", Integer), Column("b", String)), ), ] @classmethod def setup_class(cls): # TODO: we need to get dialects here somehow, perhaps in test_suite? [ importlib.import_module("sqlalchemy.dialects.%s" % d) for d in dialects.__all__ if not d.startswith("_") ] def test_all_present(self): need = set( cls for cls in class_hierarchy(ClauseElement) if issubclass(cls, (ColumnElement, Selectable)) and "__init__" in cls.__dict__ and not issubclass(cls, (Annotated)) and "orm" not in cls.__module__ and "crud" not in cls.__module__ and "dialects" not in cls.__module__ # TODO: dialects? ).difference({ColumnElement, UnaryExpression}) for fixture in self.fixtures: case_a = fixture() for elem in case_a: for mro in type(elem).__mro__: need.discard(mro) is_false(bool(need), "%d Remaining classes: %r" % (len(need), need)) def test_compare(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): if a == b: is_true( case_a[a].compare(case_b[b], arbitrary_expression=True), "%r != %r" % (case_a[a], case_b[b]), ) else: is_false( case_a[a].compare(case_b[b], arbitrary_expression=True), "%r == %r" % (case_a[a], case_b[b]), ) def test_cache_key(self): def assert_params_append(assert_params): def append(param): if param._value_required_for_cache: assert_params.append(param) else: is_(param.value, None) return append for fixture in self.fixtures: case_a = fixture() case_b = fixture() for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): assert_a_params = [] assert_b_params = [] visitors.traverse_depthfirst( case_a[a], {}, {"bindparam": assert_params_append(assert_a_params)}, ) visitors.traverse_depthfirst( case_b[b], {}, {"bindparam": assert_params_append(assert_b_params)}, ) if assert_a_params: assert_raises_message( NotImplementedError, "bindparams collection argument required ", case_a[a]._cache_key, ) if assert_b_params: assert_raises_message( NotImplementedError, "bindparams collection argument required ", case_b[b]._cache_key, ) if not assert_a_params and not assert_b_params: if a == b: eq_(case_a[a]._cache_key(), case_b[b]._cache_key()) else: ne_(case_a[a]._cache_key(), case_b[b]._cache_key()) def test_cache_key_gather_bindparams(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() # in the "bindparams" case, the cache keys for bound parameters # with only different values will be the same, but the params # themselves are gathered into a collection. for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): a_params = {"bindparams": []} b_params = {"bindparams": []} if a == b: a_key = case_a[a]._cache_key(**a_params) b_key = case_b[b]._cache_key(**b_params) eq_(a_key, b_key) if a_params["bindparams"]: for a_param, b_param in zip(a_params["bindparams"], b_params["bindparams"]): assert a_param.compare(b_param) else: a_key = case_a[a]._cache_key(**a_params) b_key = case_b[b]._cache_key(**b_params) if a_key == b_key: for a_param, b_param in zip(a_params["bindparams"], b_params["bindparams"]): if not a_param.compare(b_param): break else: assert False, "Bound parameters are all the same" else: ne_(a_key, b_key) assert_a_params = [] assert_b_params = [] visitors.traverse_depthfirst( case_a[a], {}, {"bindparam": assert_a_params.append}) visitors.traverse_depthfirst( case_b[b], {}, {"bindparam": assert_b_params.append}) # note we're asserting the order of the params as well as # if there are dupes or not. ordering has to be deterministic # and matches what a traversal would provide. eq_(a_params["bindparams"], assert_a_params) eq_(b_params["bindparams"], assert_b_params) def test_compare_col_identity(self): stmt1 = (select([table_a.c.a, table_b.c.b ]).where(table_a.c.a == table_b.c.b).alias()) stmt1_c = (select([table_a.c.a, table_b.c.b ]).where(table_a.c.a == table_b.c.b).alias()) stmt2 = union(select([table_a]), select([table_b])) stmt3 = select([table_b]) equivalents = {table_a.c.a: [table_b.c.a]} is_false( stmt1.compare(stmt2, use_proxies=True, equivalents=equivalents)) is_true( stmt1.compare(stmt1_c, use_proxies=True, equivalents=equivalents)) is_true((table_a.c.a == table_b.c.b).compare( stmt1.c.a == stmt1.c.b, use_proxies=True, equivalents=equivalents, )) def test_copy_internals(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() assert case_a[0].compare(case_b[0]) clone = case_a[0]._clone() clone._copy_internals() assert clone.compare(case_b[0]) stack = [clone] seen = {clone} found_elements = False while stack: obj = stack.pop(0) items = [ subelem for key, elem in clone.__dict__.items() if key != "_is_clone_of" and elem is not None for subelem in util.to_list(elem) if (isinstance(subelem, (ColumnElement, ClauseList)) and subelem not in seen and not isinstance( subelem, Immutable) and subelem is not case_a[0]) ] stack.extend(items) seen.update(items) if obj is not clone: found_elements = True # ensure the element will not compare as true obj.compare = lambda other, **kw: False obj.__visit_name__ = "dont_match" if found_elements: assert not clone.compare(case_b[0]) assert case_a[0].compare(case_b[0])
def query_work_day_stats( company_id, start_date=None, end_date=None, first=None, after=None, tzname="Europe/Paris", ): tz = gettz(tzname) if after: max_time, user_id_ = parse_datetime_plus_id_cursor(after) max_date = max_time.date() end_date = min(max_date, end_date) if end_date else max_date query = (Activity.query.join(Mission).join( Expenditure, and_( Activity.user_id == Expenditure.user_id, Activity.mission_id == Expenditure.mission_id, ), isouter=True, ).with_entities( Activity.id, Activity.user_id, Activity.mission_id, Mission.name, Activity.start_time, Activity.end_time, Activity.type, Expenditure.id.label("expenditure_id"), Expenditure.type.label("expenditure_type"), func.generate_series( func.date_trunc( "day", func.timezone( tzname, func.timezone("UTC", Activity.start_time), ), ), func.timezone( tzname, func.coalesce( func.timezone("UTC", Activity.end_time), func.now(), ), ), "1 day", ).label("day"), ).filter( Mission.company_id == company_id, ~Activity.is_dismissed, Activity.start_time != Activity.end_time, )) query = _apply_time_range_filters( query, to_datetime(start_date, tz_for_date=tz), to_datetime(end_date, tz_for_date=tz, convert_dates_to_end_of_day_times=True), ) has_next_page = False if first: activity_first = max(first * 5, 200) query = query.order_by(desc("day"), desc( Activity.user_id)).limit(activity_first + 1) has_next_page = query.count() > activity_first query = query.subquery() query = (db.session.query(query).group_by( query.c.user_id, query.c.day, query.c.mission_id, query.c.name).with_entities( query.c.user_id.label("user_id"), query.c.day, func.timezone("UTC", func.timezone(tzname, query.c.day)).label("utc_day_start"), query.c.mission_id.label("mission_id"), query.c.name.label("mission_name"), func.min( func.greatest( query.c.start_time, func.timezone("UTC", func.timezone(tzname, query.c.day)), )).label("start_time"), func.max( func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval)), ), func.coalesce(query.c.end_time, func.now()), )).label("end_time"), func.bool_or( and_( query.c.end_time.is_(None), query.c.day == func.current_date(), )).label("is_running"), *[ func.sum( case( [( query.c.type == a_type.value, extract( "epoch", func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval), ), ), func.coalesce(query.c.end_time, func.now()), ) - func.greatest( query.c.start_time, func.timezone( "UTC", func.timezone(tzname, query.c.day), ), ), ), )], else_=0, )).label(f"{a_type.value}_duration") for a_type in ActivityType ], func.greatest(func.count(distinct(query.c.expenditure_id)), 1).label("n_exp_dups"), func.count(distinct(query.c.id)).label("n_act_dups"), *[ func.sum( case( [(query.c.expenditure_type == e_type.value, 1)], else_=0, )).label(f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).subquery()) query = (db.session.query(query).group_by( query.c.user_id, query.c.day).with_entities( query.c.user_id.label("user_id"), query.c.day, func.array_agg(distinct( query.c.mission_name)).label("mission_names"), func.min(query.c.start_time).label("start_time"), func.max(query.c.end_time).label("end_time"), func.bool_or(query.c.is_running).label("is_running"), *[ func.sum( getattr(query.c, f"{a_type.value}_duration") / query.c.n_exp_dups).cast(Integer).label( f"{a_type.value}_duration") for a_type in ActivityType ], *[ func.sum( getattr(query.c, f"n_{e_type.value}_expenditures") / query.c.n_act_dups).cast(Integer).label( f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).order_by(desc("day"), desc("user_id")).subquery()) query = db.session.query(query).with_entities( *query.c, extract("epoch", query.c.end_time - query.c.start_time).label("service_duration"), reduce( lambda a, b: a + b, [ getattr(query.c, f"{a_type.value}_duration") for a_type in ActivityType ], ).label("total_work_duration"), ) results = query.all() if after: results = [ r for r in results if r.day.date() < max_date or ( r.day.date() == max_date and r.user_id < user_id_) ] if first: if has_next_page: # The last work day may be incomplete because we didn't fetch all the activities => remove it results = results[:-1] if len(results) > first: results = results[:first] has_next_page = True return results, has_next_page
def analytics_query(self, start, end, locations=None, library=None): """Build a database query that fetches rows of analytics data. This method uses low-level SQLAlchemy code to do all calculations and data conversations in the database. It's modeled after Work.to_search_documents, which generates a large JSON document entirely in the database. :return: An iterator of results, each of which can be written directly to a CSV file. """ clauses = [ CirculationEvent.start >= start, CirculationEvent.start < end, ] if locations: event_types = [ CirculationEvent.CM_CHECKOUT, CirculationEvent.CM_FULFILL, CirculationEvent.OPEN_BOOK, ] locations = locations.strip().split(",") clauses += [ CirculationEvent.type.in_(event_types), CirculationEvent.location.in_(locations), ] if library: clauses += [CirculationEvent.library == library] # Build the primary query. This is a query against the # CirculationEvent table and a few other tables joined against # it. This makes up the bulk of the data. events_alias = ( select( [ func.to_char(CirculationEvent.start, "YYYY-MM-DD HH24:MI:SS").label( "start" ), CirculationEvent.type.label("event_type"), Identifier.identifier, Identifier.type.label("identifier_type"), Edition.sort_title, Edition.sort_author, case( [(Work.fiction == True, literal_column("'fiction'"))], else_=literal_column("'nonfiction'"), ).label("fiction"), Work.id.label("work_id"), Work.audience, Edition.publisher, Edition.imprint, Edition.language, CirculationEvent.location, ], ) .select_from( join( CirculationEvent, LicensePool, CirculationEvent.license_pool_id == LicensePool.id, ) .join(Identifier, LicensePool.identifier_id == Identifier.id) .join(Work, Work.id == LicensePool.work_id) .join(Edition, Work.presentation_edition_id == Edition.id) ) .where(and_(*clauses)) .order_by(CirculationEvent.start.asc()) .alias("events_alias") ) # A subquery can hook into the main query by referencing its # 'work_id' field in its WHERE clause. work_id_column = literal_column( events_alias.name + "." + events_alias.c.work_id.name ) # This subquery gets the names of a Work's genres as a single # comma-separated string. # # This Alias selects some number of rows, each containing one # string column (Genre.name). Genres with higher affinities with # this work go first. genres_alias = ( select([Genre.name.label("genre_name")]) .select_from(join(WorkGenre, Genre, WorkGenre.genre_id == Genre.id)) .where(WorkGenre.work_id == work_id_column) .order_by(WorkGenre.affinity.desc(), Genre.name) .alias("genres_subquery") ) # Use array_agg() to consolidate the rows into one row -- this # gives us a single value, an array of strings, for each # Work. Then use array_to_string to convert the array into a # single comma-separated string. genres = select( [func.array_to_string(func.array_agg(genres_alias.c.genre_name), ",")] ).select_from(genres_alias) # This subquery gets the a Work's target age as a single string. # # This Alias selects two fields: the lower and upper bounds of # the Work's target age. This reuses code originally written # for Work.to_search_documents(). target_age = Work.target_age_query(work_id_column).alias("target_age_subquery") # Concatenate the lower and upper bounds with a dash in the # middle. If both lower and upper bound are empty, just give # the empty string. This simulates the behavior of # Work.target_age_string. target_age_string = select( [ case( [ ( or_(target_age.c.lower != None, target_age.c.upper != None), func.concat(target_age.c.lower, "-", target_age.c.upper), ) ], else_=literal_column("''"), ) ] ).select_from(target_age) # Build the main query out of the subqueries. events = events_alias.c query = select( [ events.start, events.event_type, events.identifier, events.identifier_type, events.sort_title, events.sort_author, events.fiction, events.audience, events.publisher, events.imprint, events.language, target_age_string.label("target_age"), genres.label("genres"), events.location, ] ).select_from(events_alias) return query
def prediction(): data = json.loads(request.data.decode("utf-8")) users = data["userIds"] # Getting previous attendance rates for each attendee at the upcoming event attendanceHistoryForUsersAtUpcomingEvent = ( Attendance.query .filter( Attendance.meetup_user_id.in_(users) ) .with_entities( Attendance.meetup_user_id, func.count(case([((Attendance.did_attend == True), 1)], else_=literal_column("NULL"))).label('count_did_attend'), func.count(Attendance.did_rsvp).label('count_did_rsvp')) .group_by(Attendance.meetup_user_id) .all() ) # For those who this is the first recorded attendance, (they may have attended before, just not up to the point where we started recording) # let's just use the average attendance rate for those who've attended once attendanceForThoseWhoAttdendedOneMeetup = ( Attendance.query .with_entities( Attendance.meetup_user_id, func.count(case([(Attendance.did_attend == True, Attendance.did_attend)], else_=literal_column("NULL"))).label('count_did_attend'), func.count(Attendance.did_rsvp).label('count_did_rsvp') ) .filter( # Filter out those attendees that attended, but did not RSVP (there's no way for us to predict them) Attendance.meetup_user_id != None ) .group_by(Attendance.meetup_user_id).having(func.count(Attendance.did_rsvp) == 1).all() ) singleAttendanceCountAndRSVPs = getAttendanceRateForThoseAttendingSingleMeetup( attendanceForThoseWhoAttdendedOneMeetup) singleAttendanceRate = (singleAttendanceCountAndRSVPs["attended"] / singleAttendanceCountAndRSVPs["rsvped"]) # Linear Regression - Get important data for each event events = ( Attendance.query .with_entities( Attendance.event_id, Events.event_name, Events.event_date, func.count(case([(Attendance.did_attend, 1)], else_=literal_column("NULL"))).label('count_did_attend'), func.count(case([(Attendance.did_rsvp, 1)], else_=literal_column("NULL"))).label('count_did_rsvp'), # Right now this includes those without user_ids such as (NONE) func.array_agg(Attendance.meetup_user_id).label('meetup_user_ids') ) .join(Events) .filter( # Filter out those attendees that attended, but did not RSVP (there's no way for us to predict them) Attendance.meetup_user_id != None ) .group_by(Attendance.event_id, Events.event_name, Events.event_date) # Order for sake of pandas and being able to use training data from middle .order_by(Events.event_date.asc()) .all() ) # Loop through events to get the previous attendance rate prior to each event events_with_attendees = [] for event in events: (event_id, event_name, event_date, attendees_who_rsvped_count, rsvp_count, attendeeIds) = event # Getting previous attendance rates for each attendee at the event attendanceHistoryForUsersAtEvent = ( Attendance.query .filter( Attendance.meetup_user_id.in_(attendeeIds), # We only care about events that were before the date of the event (not the overall history of the attendee up to today) Events.event_date < event_date ) .with_entities( Attendance.meetup_user_id, func.count(case([((Attendance.did_attend == True), 1)], else_=literal_column("NULL"))).label('count_did_attend'), func.count(Attendance.did_rsvp).label('count_did_rsvp')) .group_by(Attendance.meetup_user_id) .join(Events) .all() ) count_previous_attendees = len(attendanceHistoryForUsersAtEvent) events_with_attendees.append({ "event_id": event_id, "event_name": event_name, "event_date": event_date, "attendees_who_rsvped_count": attendees_who_rsvped_count, "rsvp_count": rsvp_count, "previous_attendance_rates_sum": getPredictedAttendeesOfMembers(attendanceHistoryForUsersAtEvent), "count_previous_attendees": count_previous_attendees, "old_attendees": count_previous_attendees, "new_attendees": (rsvp_count - count_previous_attendees) * singleAttendanceRate }) rsvps = len(users) oldAttendees = len(attendanceHistoryForUsersAtEvent) new_event = { "rsvp_count": rsvps, "old_attendees": oldAttendees, "previous_attendance_rates_sum": getPredictedAttendeesOfMembers(attendanceHistoryForUsersAtEvent), "new_attendees": (rsvps - oldAttendees) * singleAttendanceRate } regressionPrediction = getLinearRegressionPrediction( regressionInput=events_with_attendees, newEvent=[new_event]) print(regressionPrediction) # format data we have for front end consumption attendanceHistory = [] for attendee in attendanceHistoryForUsersAtUpcomingEvent: (meetupUserId, eventsAttendedCount, eventsRSVPedCount) = attendee attendanceHistory.append({ "meetupUserId": meetupUserId, "attended": eventsAttendedCount, "rsvped": eventsRSVPedCount }) return jsonify(data={ "memberAttendanceHistory": attendanceHistory, "singleAttendanceCountAndRSVPs": singleAttendanceCountAndRSVPs, "regressionPrediction": regressionPrediction })
def array_agg_row(*arg): return func.array_agg(func.row(*arg), type_=ArrayOfRecord(arg))
def get_query_column(self, entity): return func.array_agg(self.resolve_entity_column(entity))
def _process_events(klass, events, page, query=None, cities=None, user=None, flags=None, selected_tags=None, selected_categories=None, future_only=None): if future_only: events_with_counts = events_with_counts.filter( or_(Event.start_time >= datetime.datetime.now(), Event.end_time >= datetime.datetime.now())) events = events.filter(Event.status != Event.STATUS_CLOSED_PERM) events, selected_tags = klass._filter_events( events, query=query, categories=selected_categories, tags=selected_tags, flags=flags) event_cities = klass._cities_for_events(events) if cities: events = events.filter(Event.city.in_(cities)) for city in event_cities: city['selected'] = city['chip_name'] in cities tags, categories = klass._tags_for_events( events=events, selected_categories=selected_categories, selected_tags=selected_tags) event_user_ids = None if user: event_ids = {e[0].event_id for e in events if e[1]} following_user_ids = alias( db_session.query(func.distinct(Follow.follow_id)).filter( and_(UserEvent.event_id.in_(event_ids), UserEvent.user_id == Follow.follow_id, Follow.user_id == user.user_id, Follow.follow_id != user.user_id, Follow.active == True)), "following_user_ids") event_users = { str(u.user_id): { 'user_id': u.user_id, 'username': u.username, 'image_url': u.image_url } for u in User.query.filter(User.user_id.in_( following_user_ids)) } events_with_following_counts = db_session.query( UserEvent.event_id, func.array_agg(func.distinct( User.user_id)).label('user_ids')).filter( and_(Follow.user_id == user.user_id, UserEvent.user_id == Follow.follow_id, UserEvent.event_id.in_(event_ids), Follow.follow_id != user.user_id)).group_by( UserEvent.event_id) event_user_ids = { row[0]: set(str(follower_id) for follower_id in row[1]) for row in events_with_following_counts } events = klass._order_events(events) events = events.limit(klass.PAGE_SIZE).offset( (page - 1) * klass.PAGE_SIZE) results = [] for event, user_count in events: event.card_user_count = user_count if event_user_ids and event.event_id in event_user_ids: event.card_event_users = [ event_users[x] for x in event_user_ids[event.event_id] if x in event_users ] results.append(event) return results, categories, tags, event_cities, events
# query get average score of ratings for each year averageBestNewMusicScores = (session.query( func.avg(Review.score), Review.pub_year).filter(Review.best_new_music == 1).group_by( Review.pub_year).all()) # Join query for Genre and Review to see reviews by genre averageReviewsScoreByGenre = (session.query( Genre.genre, func.avg(Review.score)).filter( Genre.genre != None, Review.best_new_music == 1).join( Review, Genre.reviewid == Review.reviewid).group_by(Genre.genre).all()) # Lists of scores by genre allScoresByGenre = (session.query(Genre.genre, func.array_agg( Review.score)).filter(Genre.genre != None).join( Review, Genre.reviewid == Review.reviewid).group_by(Genre.genre).all()) # Graphing score averages fig1, (ax1, ax2) = plt.subplots(1, 2) fig2, (ax3, ax4) = plt.subplots(1, 2) averageScores = sorted(averageScores, key=lambda x: (-x[1], x[0])) scores = [i[0] for i in averageScores] years = [i[1] for i in averageScores] ax1.plot(years, scores) ax1.set_xticks(years) ax1.set_title("Music Score Average by Year") ax1.set_ylabel("Average Score") ax1.set_xlabel("Year")
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