class Student(db.Model): __tablename__ = 'student' id = Column(Integer, primary_key=True) contextual_id = column_property( func.md5( bindparam('context', value='', type_=String) + func.cast(id, String))) name = Column(String(64), index=True, nullable=False) address = Column(String(128), index=False, nullable=True) phone = Column(String(35), nullable=True) home_phone = Column(String(35), nullable=True) email = Column(CIText(64, True), nullable=True) created = Column(DateTime, server_default='now()') guardian_number = MapColumn('home_phone') phone_numbers = MapColumn(['phone', 'home_phone']) contact_info = MapColumn({ 'phone': 'phone', 'home_phone': 'home_phone', 'email': 'email' }) teachers = relationship( "Teacher", secondary='teacher_to_student', primaryjoin="Student.id == teacher_to_student.c.student_id", secondaryjoin="teacher_to_student.c.teacher_id == Teacher.id") first_name = column_property(func.split_part(func.trim(name), " ", 1))
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) return jsonify( profiles_schema.dump( matching_profiles(query).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' '), 1 # How many words in the name )))))
def rollup_test(request): id = request.params['job'] one = func.split_part(Run.test_id, '/', 4).alias('one') two = func.split_part(Run.test_id, '/', 5).alias('two') passed = (Run.result == 'PASS').alias('PASS') failed = (Run.result == 'FAIL').alias('FAIL') abort = (Run.result == 'ABORT').alias('ABORT') timeout = (Run.result == 'TIMEOUT').alias('TIMEOUT') rollup_cols = [one, two] q = DBSession.query(passed, failed, abort, timeout, *rollup_cols) \ .where(Run.job_id == id) q = rollup(DBSession, q, *rollup_cols) columns = [c['name'] for c in q.column_descriptions] return dict(cols=columns, rows=q.all())
def rollup_test(request): id = request.params['job'] one = func.split_part(Run.test_id, '/', 4).alias('one') two = func.split_part(Run.test_id, '/', 5).alias('two') passed = (Run.result == 'PASS').alias('PASS') failed = (Run.result == 'FAIL').alias('FAIL') abort = (Run.result == 'ABORT').alias('ABORT') timeout = (Run.result == 'TIMEOUT').alias('TIMEOUT') rollup_cols = [one, two] q = DBSession.query(passed, failed, abort, timeout, *rollup_cols) \ .where(Run.job_id == id) q = rollup(DBSession, q, *rollup_cols) columns = [c['name'] for c in q.column_descriptions] return dict(cols=columns, rows=q.all())
async def count_routes(db, operator_id: int) -> int: """ Count how many routes an operator has """ # This query uses route_desc, which contains the official route license number # this field is separated by minus, and the first item is the route's # unique license number (after that it's "direction code" and "alternative". # Using PostgreSQL's split_part function, count, and distinct we can use # this to quickly count the routes per operator. query = select([func.count(func.distinct(func.split_part(Route.route_desc, '-', 1)))]) return await db.scalar(query.where(Route.agency_id == str(operator_id)))
def split_vo(dialect, column, return_vo=False): """ Utility script for extracting the name and VO from InternalAccount/Scope entries in the DB. :param dialect: The dialct of the DB. :param column: The column to perform the operation on. :param return_vo: If True, return the 3 characters after the '@' symbol, else return everything before it. """ if dialect == 'postgresql': if return_vo: return func.split_part(column, bindparam('split_character'), bindparam('int_2')) else: return func.split_part(column, bindparam('split_character'), bindparam('int_1')) else: # Dialects other than postgresql haven't been tested i = func.INSTR(column, bindparam('split_character')) if return_vo: return func.SUBSTR(column, i + 1) else: return func.SUBSTR(column, bindparam('int_1'), i - 1)
def upgrade(): # Add new nullable columns op.add_column('users', Column('floor', SmallInteger(), nullable=True)) op.add_column('users', Column('seat', SmallInteger(), nullable=True)) # Migrate data query = users_table.update().values(floor=cast( func.split_part(users_table.c.location, LOCATION_DELIMETER, 1), Integer), seat=cast( func.split_part( users_table.c.location, LOCATION_DELIMETER, 2), Integer)) op.execute(query) # Make new columns non-nullable op.alter_column('users', 'floor', nullable=False) op.alter_column('users', 'seat', nullable=False) # Drop legacy column op.drop_column('users', 'location')
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') degrees = request.args.get('degrees', '') affiliations = request.args.get('affiliations', '') page = int(request.args.get('page', 1)) start, end = pagination(page) verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) queryset = ( matching_profiles(query, degrees, affiliations).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' ', ), 1, # How many words in the name ), ), ).group_by(Profile.id)) return jsonify({ 'profileCount': queryset.count(), 'profiles': profiles_schema.dump(queryset[start:end]), })
def get_ordering(sorting): last_name_sorting = func.split_part( profile_class.name, " ", func.array_length( func.string_to_array(profile_class.name, " "), 1, # Length in the 1st dimension ), ) sort_options = { "starred": [ desc(text("profile_star_count")), desc(profile_class.date_updated), ], "last_name_alphabetical": [asc(last_name_sorting)], "last_name_reverse_alphabetical": [desc(last_name_sorting)], "date_updated": [desc(profile_class.date_updated)], } if sorting not in sort_options: raise InvalidPayloadError({"sorting": ["invalid"]}) return sort_options[sorting]
def username(cls): return func.split_part(cls.email, '@', 1)
def name(cls): return func.reverse(func.split_part(func.reverse(cls.location), '/', 1))
def get_dataset_srid_alchemy_expression(md: MetadataType, default_crs: str = None): doc = md.dataset_fields['metadata_doc'].alchemy_expression if 'grid_spatial' not in md.definition['dataset']: # Non-spatial product return None projection_offset = md.definition['dataset']['grid_spatial'] # Most have a spatial_reference field we can use directly. spatial_reference_offset = projection_offset + ['spatial_reference'] spatial_ref = doc[spatial_reference_offset].astext # When datasets have no CRS, optionally use this as default. default_crs_expression = None if default_crs: if not default_crs.lower().startswith('epsg:'): raise NotImplementedError( "CRS expected in form of 'EPSG:1234'. Got: %r" % default_crs) auth_name, auth_srid = default_crs.split(':') default_crs_expression = select([ SPATIAL_REF_SYS.c.srid ]).where(func.lower(SPATIAL_REF_SYS.c.auth_name) == auth_name.lower( )).where(SPATIAL_REF_SYS.c.auth_srid == int(auth_srid)).as_scalar() expression = func.coalesce( case( [( # If matches shorthand code: eg. "epsg:1234" spatial_ref.op("~")(r"^[A-Za-z0-9]+:[0-9]+$"), select([SPATIAL_REF_SYS.c.srid]).where( func.lower(SPATIAL_REF_SYS.c.auth_name) == func.lower(func.split_part(spatial_ref, ':', 1))).where( SPATIAL_REF_SYS.c.auth_srid == func.split_part( spatial_ref, ':', 2).cast(Integer)).as_scalar())], else_=None), case( [( # Plain WKT that ends in an authority code. # Extract the authority name and code using regexp. Yuck! # Eg: ".... AUTHORITY["EPSG","32756"]]" spatial_ref.op("~") (r'AUTHORITY\["[a-zA-Z0-9]+", *"[0-9]+"\]\]$'), select([SPATIAL_REF_SYS.c.srid]).where( func.lower(SPATIAL_REF_SYS.c.auth_name) == func.lower( func.substring( spatial_ref, r'AUTHORITY\["([a-zA-Z0-9]+)", *"[0-9]+"\]\]$')) ).where(SPATIAL_REF_SYS.c.auth_srid == func.substring( spatial_ref, r'AUTHORITY\["[a-zA-Z0-9]+", *"([0-9]+)"\]\]$' ).cast(Integer)).as_scalar())], else_=None), # Some older datasets have datum/zone fields instead. # The only remaining ones in DEA are 'GDA94'. case([ (doc[(projection_offset + ['datum'])].astext == 'GDA94', select([ SPATIAL_REF_SYS.c.srid ]).where(func.lower(SPATIAL_REF_SYS.c.auth_name) == 'epsg').where( SPATIAL_REF_SYS.c.auth_srid == ('283' + func.abs(doc[ (projection_offset + ['zone'])].astext.cast(Integer)) ).cast(Integer)).as_scalar()) ], else_=None), default_crs_expression, # TODO: Handle arbitrary WKT strings (?) ) # print(as_sql(expression)) return expression