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 create_village_venues(): for village in Village.query.all(): venue = Venue.query.filter_by(village_id=village.id).first() if venue: if venue.name in EMF_VENUES: app.logger.info(f"Not updating EMF venue {venue.name}") elif venue.name != village.name: app.logger.info( f"Updating village venue name from {venue.name} to {village.name}" ) venue.name = village.name db.session.commit() continue if Venue.query.filter( func.lower(Venue.name) == func.trim(func.lower( village.name))).count(): app.logger.warning( f"Not creating village venue with colliding name {village.name}" ) continue venue = Venue(name=village.name, village_id=village.id, scheduled_content_only=False) db.session.add(venue) db.session.commit()
def update_ego_grid_ehv_substation(**kwargs): postgres_hook = PostgresHook(postgres_conn_id='postgres_oedb') engine = postgres_hook.get_sqlalchemy_engine() conn = engine.connect() #alembic ctx = MigrationContext.configure(conn) op = Operations(ctx) #add column op.add_column('ego_grid_ehv_substation', Column('otg_id', BigInteger), schema='model_draft') # load the tables meta = MetaData() conn.execute("SET search_path TO model_draft, grid, public") ego_grid_hvmv_substation = Table('ego_grid_hvmv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) ego_grid_ehv_substation = Table('ego_grid_ehv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) otg_ehvhv_bus_data = Table('otg_ehvhv_bus_data', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) #operations with "ego_grid_ehv_substation" table #update ehv_substation_update = ego_grid_ehv_substation.update().values( otg_id=otg_ehvhv_bus_data.c.bus_i).where( and_( otg_ehvhv_bus_data.c.base_kv > 110, otg_ehvhv_bus_data.c.osm_substation_id == cast( func.trim(ego_grid_ehv_substation.c.osm_id, 'nwr'), BigInteger))) #delete ehv_substation_delete = ego_grid_ehv_substation.delete().where( ego_grid_ehv_substation.c.otg_id.is_(None)) #execution conn.execute(ehv_substation_update) conn.execute(ehv_substation_delete)
def update_desa_from_code(): desa_alias = aliased(SdAllDesa) kecamatan_alias = aliased(SdAllDesa) kabupaten_alias = aliased(SdAllDesa) propinsi_alias = aliased(SdAllDesa) query = update(SdDesa).values(desa=desa_alias.region_name, kecamatan=kecamatan_alias.region_name, kabupaten=kabupaten_alias.region_name, propinsi=propinsi_alias.region_name) query = query.where(SdDesa.kode == desa_alias.region_code) query = query.where(desa_alias.parent_code == kecamatan_alias.region_code) query = query.where( kecamatan_alias.parent_code == kabupaten_alias.region_code) query = query.where( kabupaten_alias.parent_code == propinsi_alias.region_code) query = query.where(func.trim(func.coalesce(SdDesa.kode, '')) != '') db.session.execute(query) db.session.commit() return jsonify({'success': True})
def update_ego_grid_ehv_substation(): global execution_time start_time = time.monotonic() # create_engine engine = create_engine('postgresql://*****:*****@localhost:5432/oedb') conn = engine.connect() #alembic ctx = MigrationContext.configure(conn) op = Operations(ctx) #add column op.add_column('ego_grid_ehv_substation', Column('otg_id', BigInteger), schema = 'model_draft' ) # load the tables meta = MetaData() conn.execute("SET search_path TO model_draft, grid, public") ego_grid_hvmv_substation = Table('ego_grid_hvmv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) ego_grid_ehv_substation = Table('ego_grid_ehv_substation', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) otg_ehvhv_bus_data = Table('otg_ehvhv_bus_data', meta, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True) #operations with "ego_grid_hvmv_substation" table #update ehv_substation_update = ego_grid_ehv_substation.update().values(otg_id=otg_ehvhv_bus_data.c.bus_i).where( and_( otg_ehvhv_bus_data.c.base_kv>110, otg_ehvhv_bus_data.c.osm_substation_id==cast(func.trim(ego_grid_ehv_substation.c.osm_id, 'nwr'), BigInteger) ) ) #delete ehv_substation_delete = ego_grid_ehv_substation.delete().where( ego_grid_ehv_substation.c.otg_id.is_(None) ) #execution conn.execute(ehv_substation_update) conn.execute(ehv_substation_delete) execution_time = time.monotonic() - start_time
def expnum(cls): return func.trim(cls._expnum)
class PromoCodeWord(MagModel): """ Words used to generate promo codes. Attributes: word (str): The text of this promo code word. normalized_word (str): A normalized version of `word`, suitable for database queries. part_of_speech (int): The part of speech that `word` is. Valid values are: * 0 `_ADJECTIVE`: `word` is an adjective * 1 `_NOUN`: `word` is a noun * 2 `_VERB`: `word` is a verb * 3 `_ADVERB`: `word` is an adverb part_of_speech_str (str): A human readable description of `part_of_speech`. """ _ADJECTIVE = 0 _NOUN = 1 _VERB = 2 _ADVERB = 3 _PART_OF_SPEECH_OPTS = [(_ADJECTIVE, 'adjective'), (_NOUN, 'noun'), (_VERB, 'verb'), (_ADVERB, 'adverb')] _PARTS_OF_SPEECH = dict(_PART_OF_SPEECH_OPTS) word = Column(UnicodeText) part_of_speech = Column(Choice(_PART_OF_SPEECH_OPTS), default=_ADJECTIVE) __table_args__ = (Index( 'uq_promo_code_word_normalized_word_part_of_speech', func.lower(func.trim(word)), part_of_speech, unique=True), CheckConstraint( func.trim(word) != '', name='ck_promo_code_word_non_empty_word')) _repr_attr_names = ('word', ) @hybrid_property def normalized_word(self): return self.normalize_word(self.word) @normalized_word.expression def normalized_word(cls): return func.lower(func.trim(cls.word)) @property def part_of_speech_str(self): return self._PARTS_OF_SPEECH[self.part_of_speech].title() @presave_adjustment def _attribute_adjustments(self): # Replace multiple whitespace characters with a single space self.word = re.sub(r'\s+', ' ', self.word.strip()) @classmethod def group_by_parts_of_speech(cls, words): """ Groups a list of words by their part_of_speech. Arguments: words (list): List of `PromoCodeWord`. Returns: OrderedDict: A dictionary of words mapped to their part of speech, like this:: OrderedDict([ (0, ['adjective1', 'adjective2']), (1, ['noun1', 'noun2']), (2, ['verb1', 'verb2']), (3, ['adverb1', 'adverb2']) ]) """ parts_of_speech = OrderedDict([ (i, []) for (i, _) in PromoCodeWord._PART_OF_SPEECH_OPTS ]) for word in words: parts_of_speech[word.part_of_speech].append(word.word) return parts_of_speech @classmethod def normalize_word(cls, word): """ Normalizes a word. Arguments: word (str): A word as typed by an admin. Returns: str: A copy of `word` converted to all lowercase, and multiple whitespace characters replaced by a single space. """ return re.sub(r'\s+', ' ', word.strip().lower())
def updateRow(self, sampleRow): for i in range(len(sampleRow)): #sampleRow[i]=sampleRow[i].lower() if sampleRow[i].strip()=='': sampleRow[i]=None plecval =sampleRow[4] try: pslval=str(sampleRow[22]).strip() except: pslval = sampleRow[22] pimval=sampleRow[7].encode('windows-1250') dimval= sampleRow[8] nzwval=sampleRow[5].encode('windows-1250') if sampleRow[9]: oimval=sampleRow[9].encode('windows-1250') else: oimval=sampleRow[9] if sampleRow[10]: mimval=sampleRow[10].encode('windows-1250') else: mimval=sampleRow[10] try: kodval = sampleRow[12].replace(' ', '') except: kodval=sampleRow[12] if sampleRow[13] <> None: nazval = sampleRow[13].upper().encode('windows-1250') nazval = '%'+nazval+'%' else: nazval = '' try: nraval = re.sub('"', '', sampleRow[15]) nraval = str(nraval) except: nraval=sampleRow[15] if nraval is not None and re.search('0,', nraval): nraval= None ############################ # test czy pesel juz jest w bazie!!!! ############################ if self.session.query(Osoby).filter(Osoby.psl == pslval.strip()).count()>=1: return 'jest_w_bazie' rawquery = self.session.query(Osoby.uid).join(Adresy).\ filter(Osoby.plec==plecval,\ Osoby.pim==pimval,\ Osoby.dim==dimval,\ Osoby.nzw==nzwval,\ Osoby.oim==oimval,\ Osoby.mim ==mimval,\ func.upper(Adresy.naz).like(nazval),\ Adresy.kod==kodval,\ func.trim(Adresy.nra)==nraval,\ ) q=rawquery.subquery() rowcunt = 0 rowcunt = rawquery.count() if rowcunt == 0: return 'nie_znalazl'# elif rowcunt == 1: self.session.query(Osoby).filter(Osoby.uid.in_(q)).\ update({Osoby.psl: pslval}, synchronize_session='fetch') self.session.commit() return rowcunt elif rowcunt >1: return 'dubel'
def db_compare(col, text): text_ = text.lower().strip() col_ = func.trim(func.lower(col)) return col_ == text_
def query_id(cls, id): return cls.query().filter(func.trim(cls.subjek_pajak_id) == id)
def available_usernames(self): return self.usercollection.query()\ .with_entities(User.username, User.realname)\ .filter(func.trim(func.coalesce(User.realname, "")) != "")\ .filter(User.active == True)\ .order_by(func.unaccent(func.lower(User.realname)))
def updateRow(self, sampleRow): for i in range(len(sampleRow)): #sampleRow[i]=sampleRow[i].lower() if sampleRow[i] == '': sampleRow[i] = None plecval = sampleRow[4] try: pslval = str(sampleRow[22]).strip('\r') except: pslval = sampleRow[22] pimval = sampleRow[7].encode('windows-1250') dimval = sampleRow[8] nzwval = sampleRow[5].encode('windows-1250') if sampleRow[9]: oimval = sampleRow[9].encode('windows-1250') else: oimval = sampleRow[9] if sampleRow[10]: mimval = sampleRow[10].encode('windows-1250') else: mimval = sampleRow[10] try: kodval = sampleRow[12].replace(' ', '') except: kodval = sampleRow[12] if sampleRow[13] <> None: nazval = sampleRow[13].upper().encode('windows-1250') nazval = '%' + nazval + '%' else: nazval = '' try: nraval = str(sampleRow[15]) except: nraval = sampleRow[15] if re.search('0,', nraval): nraval = None rawquery = self.session.query(Osoby.uid).join(Adresy).\ filter(Osoby.plec==plecval,\ Osoby.pim==pimval,\ Osoby.dim==dimval,\ Osoby.nzw==nzwval,\ Osoby.oim==oimval,\ Osoby.mim ==mimval,\ func.upper(Adresy.naz).like(nazval),\ Adresy.kod==kodval,\ func.trim(Adresy.nra)==nraval,\ ) q = rawquery.subquery() rowcunt = 0 rowcunt = rawquery.count() if rowcunt == 0: return rowcunt elif rowcunt <> 0: self.session.query(Osoby).filter(Osoby.uid.in_(q)).\ update({Osoby.psl: pslval}, synchronize_session='fetch') self.session.commit() return rowcunt
class User(db.Model): __tablename__ = 'users' __updateable_fields__ = { 'firstname': str, 'lastname': str, 'password': bytes, 'is_admin': bool, 'rank_id': int, 'imagename': dict } from .rank import Rank from .rank_update import RankUpdate from .user_verification import UserVerification from .purchase import Purchase from .deposit import Deposit from .replenishment import ReplenishmentCollection id = db.Column(db.Integer, primary_key=True) creation_date = db.Column(db.DateTime, default=func.now(), nullable=False) firstname = db.Column(db.String(32), unique=False, nullable=True) lastname = db.Column(db.String(32), unique=False, nullable=False) password = db.Column(db.String(256), unique=False, nullable=True) is_verified = db.Column(db.Boolean, nullable=False, default=False) image_upload_id = db.Column(db.Integer, db.ForeignKey('uploads.id'), nullable=True) # Column property for the full name fullname = column_property( func.trim(func.coalesce(firstname, "") + " " + lastname)) # Column property for the active state active = column_property( select([Rank.active]).where( and_(RankUpdate.user_id == id, Rank.id == RankUpdate.rank_id)).order_by( RankUpdate.id.desc()).limit(1).as_scalar()) # Column property for the is system user property is_system_user = column_property( select([Rank.is_system_user]).where( and_(RankUpdate.user_id == id, Rank.id == RankUpdate.rank_id)).order_by( RankUpdate.id.desc()).limit(1).as_scalar()) # Column property for the verification_date verification_date = column_property( select([UserVerification.timestamp ]).where(UserVerification.user_id == id).limit(1).as_scalar()) # Column property for the rank_id rank_id = column_property( select([Rank.id]).where( and_(RankUpdate.user_id == id, Rank.id == RankUpdate.rank_id)).order_by( RankUpdate.id.desc()).limit(1).as_scalar()) # Select statement for the sum of all non revoked purchases referring this user. # NOTE: func.coalesce(a, b) returns the first non-null value of (a, b). If there aren't any purchases # (or deposits, ...) yet, the purchase (deposit, ...) sum is NULL. In this case, 0 gets returned. _purchase_sum = column_property( select([func.coalesce(func.sum(Purchase.price), 0)]).where(Purchase.user_id == id).where( Purchase.revoked.is_(False)).as_scalar()) # Select statement for the sum of all non revoked deposits referring this user. _deposit_sum = column_property( select([func.coalesce(func.sum(Deposit.amount), 0)]).where(Deposit.user_id == id).where( Deposit.revoked.is_(False)).as_scalar()) # Select statement for the sum of all non revoked refunds referring this user. _replenishmentcollection_sum = column_property( select([func.coalesce(func.sum(ReplenishmentCollection.price), 0) ]).where(ReplenishmentCollection.seller_id == id).where( ReplenishmentCollection.revoked.is_(False)).as_scalar()) # A users credit is the sum of all amounts that increase his credit (Deposits, ReplenishmentCollections) # and all amounts that decrease it (Purchases) credit = column_property(_replenishmentcollection_sum.expression + _deposit_sum.expression - _purchase_sum.expression) # Link to all purchases of a user. purchases = db.relationship('Purchase', lazy='dynamic', foreign_keys='Purchase.user_id') # Link to all deposits of a user. deposits = db.relationship('Deposit', lazy='dynamic', foreign_keys='Deposit.user_id') # Link to all deposits of a user. replenishmentcollections = db.relationship( 'ReplenishmentCollection', lazy='dynamic', foreign_keys='ReplenishmentCollection.seller_id') def __repr__(self): return f'<User {self.id}: {self.lastname}, {self.firstname}>' @hybrid_property def imagename(self): from .upload import Upload upload = Upload.query.filter_by(id=self.image_upload_id).first() if upload: return upload.filename return None @hybrid_method def set_imagename(self, image, admin_id): filename = insert_image(image) # Create an upload try: from .upload import Upload u = Upload(filename=filename, admin_id=admin_id) db.session.add(u) db.session.flush() self.image_upload_id = u.id except IntegrityError: raise CouldNotCreateEntry() @hybrid_property def is_admin(self): from .admin_update import AdminUpdate au = (AdminUpdate.query.filter_by(user_id=self.id).order_by( AdminUpdate.id.desc()).first()) if au is None: return False return au.is_admin @hybrid_method def set_admin(self, is_admin, admin_id): from .admin_update import AdminUpdate if is_admin and self.password is None: raise UserNeedsPassword() if self.is_admin == is_admin: raise NothingHasChanged() au = AdminUpdate(is_admin=is_admin, admin_id=admin_id, user_id=self.id) db.session.add(au) @hybrid_method def verify(self, admin_id, rank_id): from .user_verification import UserVerification if self.is_verified: raise UserAlreadyVerified() self.is_verified = True uv = UserVerification(user_id=self.id, admin_id=admin_id) self.set_rank_id(rank_id, admin_id) db.session.add(uv) @hybrid_method def set_rank_id(self, rank_id, admin_id): from .rank_update import RankUpdate if self.is_verified: ru = RankUpdate(rank_id=rank_id, admin_id=admin_id, user_id=self.id) db.session.add(ru) else: self.verify(admin_id=admin_id, rank_id=rank_id) @hybrid_method def set_is_admin(self, is_admin, admin_id): self.set_admin(is_admin=is_admin, admin_id=admin_id) if not self.is_admin: users = User.query.all() admins = list(filter(lambda x: x.is_admin, users)) if not admins: raise NoRemainingAdmin() @hybrid_property def rank(self): from .rank import Rank if self.rank_id: rank = Rank.query.filter(Rank.id == self.rank_id).first() if rank: return rank return None @hybrid_property def favorites(self): """ Returns the product ids of the user's favorite products in descending order of number. Inactive products those who are not for sale are ignored. Args: self: self Returns: ids: A list of the favorite product ids in descending order. """ from .tag import Tag from .product_tag_assignment import product_tag_assignments from .purchase import Purchase from .product import Product # Get a list of all invalid tag ids (as SQL subquery) invalid_tag_ids = db.session.query(Tag.id).filter( Tag.is_for_sale.is_(False)).subquery() # Get a list of all products to which this tag is assigned invalid_product_ids = (db.session.query( product_tag_assignments.c.product_id).filter( product_tag_assignments.c.tag_id.in_( invalid_tag_ids)).subquery()) # Get a list of all inactive product ids inactive_product_ids = db.session.query(Product.id).filter( Product.active.is_(False)).subquery() result = ( db.session.query(Purchase.product_id).filter( Purchase.user_id == self.id) # Get only user purchases .group_by(Purchase.product_id) # Group by products .filter(Purchase.product_id.notin_( invalid_product_ids)) # Get only products which are for sale .filter(Purchase.product_id.notin_( inactive_product_ids)) # Get only products which are active .filter( Purchase.revoked.is_(False)) # Get only non revoked purchases .order_by(func.sum( Purchase.amount).desc()) # Order by the sum of purchase amount .all()) return [item.product_id for item in result]
def updateRow(self, sampleRow): for i in range(len(sampleRow)): # sampleRow[i]=sampleRow[i].lower() if sampleRow[i] == "": sampleRow[i] = None plecval = sampleRow[4] try: pslval = str(sampleRow[22]).strip("\r") except: pslval = sampleRow[22] pimval = sampleRow[7].encode("windows-1250") dimval = sampleRow[8] nzwval = sampleRow[5].encode("windows-1250") if sampleRow[9]: oimval = sampleRow[9].encode("windows-1250") else: oimval = sampleRow[9] if sampleRow[10]: mimval = sampleRow[10].encode("windows-1250") else: mimval = sampleRow[10] try: kodval = sampleRow[12].replace(" ", "") except: kodval = sampleRow[12] if sampleRow[13] <> None: nazval = sampleRow[13].upper().encode("windows-1250") nazval = "%" + nazval + "%" else: nazval = "" try: nraval = str(sampleRow[15]) except: nraval = sampleRow[15] if re.search("0,", nraval): nraval = None rawquery = ( self.session.query(Osoby.uid) .join(Adresy) .filter( Osoby.plec == plecval, Osoby.pim == pimval, Osoby.dim == dimval, Osoby.nzw == nzwval, Osoby.oim == oimval, Osoby.mim == mimval, func.upper(Adresy.naz).like(nazval), Adresy.kod == kodval, func.trim(Adresy.nra) == nraval, ) ) q = rawquery.subquery() rowcunt = 0 rowcunt = rawquery.count() if rowcunt == 0: return rowcunt elif rowcunt <> 0: self.session.query(Osoby).filter(Osoby.uid.in_(q)).update({Osoby.psl: pslval}, synchronize_session="fetch") self.session.commit() return rowcunt
def expnum(self): return func.trim(self._expnum)
def db_norm(col): return func.trim(func.lower(col))
def insert_organizations(db: Session): ''' This method will insert all osm objects into organization if the osm_id is not yet inserted ''' # sub-request for select osm_id,name,name_normalized,importance # name_normalized is for removing trailling space and replace white-space by dash query_normalized_name = db.query( OSMName.osm_id.label('osm_id'), OSMName.name.label('name'), func.replace(func.trim(OSMName.name, ' '), ' ', '-').label("name_normalized"), OSMName.importance.label('importance')) query_normalized_name = query_normalized_name.subquery('normalized_name') # sub-request for select osm_id, name, name_normalized, row_number # row_number will be used for generate the slug in the next request # the row number is partition_by name_normalized # and order by "importance" DESC # src: https://osmnames.readthedocs.io/en/latest/introduction.html # "importance" :Importance of the feature, ranging [0.0-1.0], 1.0 being the most important. query_unique_slug = db.query( query_normalized_name.c.osm_id.label('osm_id'), query_normalized_name.c.name.label('name'), query_normalized_name.c.name_normalized.label('name_normalized'), func.row_number().over( partition_by=query_normalized_name.c.name_normalized, order_by=desc( query_normalized_name.c.importance)).label("row_number")) query_unique_slug = query_unique_slug.subquery('unique_slug') # request for filtering what we insert where_query = db.query(Organization.osm_id.label('osm_id')) # final request for build the slug # example # we could have many name_normalized # name | name_normalized | osm_id | row_number # new york | new-york | 1254 | 1 # new-york | new-york | 215486 | 2 # will generate # name | osm_id | slug # new york | 1254 | new-york # new-york | 215486 | new-york-215486 query = db.query( query_unique_slug.c.name.label('name'), query_unique_slug.c.osm_id.label('osm_id'), case([(query_unique_slug.c.row_number == 1, query_unique_slug.c.name_normalized)], else_=func.concat(query_unique_slug.c.name_normalized, '-', query_unique_slug.c.osm_id)).label('slug')) query = query.filter(query_unique_slug.c.osm_id.notin_(where_query)) insert_query = insert(Organization) insert_query = insert_query.from_select( (Organization.name, Organization.osm_id, Organization.slug), query) try: db.execute(insert_query) db.commit() db.close() finally: pass
def _select_training_set_data_from_database(label_columns, filter_args=None, filter_func=None, limit=None, **kwargs): label_columns = list(label_columns) label_names = [column.key for column in label_columns] L = len(label_names) if filter_func is None: filter_func = lambda *_, **__: True # Get the label names. log.info(f"Querying for label names {label_names} from {label_columns}") # Figure out what other columns we will need to identify the input file. for column in label_columns: try: primary_parent = column.class_ except AttributeError: continue else: break else: raise ValueError( "Can't get primary parent. are you labelling every column?") log.debug(f"Identified primary parent table as {primary_parent}") if primary_parent == catalogdb.SDSSApogeeAllStarMergeR13: log.debug( f"Adding columns and setting data_model_func for {primary_parent}") additional_columns = [ catalogdb.SDSSDR16ApogeeStar.apstar_version.label("apstar"), catalogdb.SDSSDR16ApogeeStar.field, catalogdb.SDSSDR16ApogeeStar.apogee_id.label("obj"), catalogdb.SDSSDR16ApogeeStar.file, catalogdb.SDSSDR16ApogeeStar.telescope, # Things that we might want for filtering on. catalogdb.SDSSDR16ApogeeStar.snr ] columns = label_columns + additional_columns q = session.query(*columns).join( catalogdb.SDSSApogeeAllStarMergeR13, func.trim(catalogdb.SDSSApogeeAllStarMergeR13.apstar_ids) == catalogdb.SDSSDR16ApogeeStar.apstar_id) data_model_func = lambda apstar, field, obj, filename, telescope, *_, : { "release": "DR16", "filetype": "apStar", "apstar": apstar, "field": field, "obj": obj, "prefix": filename[:2], "telescope": telescope, "apred": filename.split("-")[1] } else: raise NotImplementedError( f"Cannot intelligently figure out what data model keywords will be necessary." ) if filter_args is not None: q = q.filter(*filter_args) if limit is not None: q = q.limit(limit) log.debug(f"Querying {q}") data_model_identifiers = [] labels = {label_name: [] for label_name in label_names} for i, row in enumerate(tqdm(q.yield_per(1), total=q.count())): if not filter_func(*row): continue for label_name, value in zip(label_names, row[:L]): if not np.isfinite(value) or value is None: log.warning( f"Label {label_name} in {i} row is not finite: {value}!") labels[label_name].append(value) data_model_identifiers.append(data_model_func(*row[L:])) return (labels, data_model_identifiers)
class PromoCode(MagModel): """ Promo codes used by attendees to purchase badges at discounted prices. Attributes: code (str): The actual textual representation of the promo code. This is what the attendee would have to type in during registration to receive a discount. `code` may not be an empty string or a string consisting entirely of whitespace. discount (int): The discount amount that should be applied to the purchase price of a badge. The interpretation of this value depends on the value of `discount_type`. In any case, a value of 0 equates to a full discount, i.e. a free badge. discount_str (str): A human readable description of the discount. discount_type (int): The type of discount this promo code will apply. Valid values are: * 0 `_FIXED_DISCOUNT`: `discount` is interpreted as a fixed dollar amount by which the badge price should be reduced. If `discount` is 49 and the badge price is normally $100, then the discounted badge price would be $51. * 1 `_FIXED_PRICE`: `discount` is interpreted as the actual badge price. If `discount` is 49, then the discounted badge price would be $49. * 2 `_PERCENT_DISCOUNT`: `discount` is interpreted as a percentage by which the badge price should be reduced. If `discount` is 20 and the badge price is normally $50, then the discounted badge price would $40 ($50 reduced by 20%). If `discount` is 100, then the price would be 100% off, i.e. a free badge. group (relationship): An optional relationship to a PromoCodeGroup object, which groups sets of promo codes to make attendee-facing "groups" cost (int): The cost of this promo code if and when it was bought as part of a PromoCodeGroup. expiration_date (datetime): The date & time upon which this promo code expires. An expired promo code may no longer be used to receive discounted badges. is_free (bool): True if this promo code will always cause a badge to be free. False if this promo code may not cause a badge to be free. Note: It's possible for this value to be False for a promo code that still reduces a badge's price to zero. If there are some other discounts that also reduce a badge price (like an age discount) then the price may be pushed down to zero. is_expired (bool): True if this promo code is expired, False otherwise. is_unlimited (bool): True if this promo code may be used an unlimited number of times, False otherwise. is_valid (bool): True if this promo code is still valid and may be used again, False otherwise. normalized_code (str): A normalized version of `code` suitable for database queries. Normalization converts `code` to all lowercase and removes dashes ("-"). used_by (list): List of attendees that have used this promo code. Note: This property is declared as a backref in the Attendee class. uses_allowed (int): The total number of times this promo code may be used. A value of None means this promo code may be used an unlimited number of times. uses_allowed_str (str): A human readable description of uses_allowed. uses_count (int): The number of times this promo code has already been used. uses_count_str (str): A human readable description of uses_count. uses_remaining (int): Remaining number of times this promo code may be used. uses_remaining_str (str): A human readable description of uses_remaining. """ _FIXED_DISCOUNT = 0 _FIXED_PRICE = 1 _PERCENT_DISCOUNT = 2 _DISCOUNT_TYPE_OPTS = [(_FIXED_DISCOUNT, 'Fixed Discount'), (_FIXED_PRICE, 'Fixed Price'), (_PERCENT_DISCOUNT, 'Percent Discount')] _AMBIGUOUS_CHARS = { '0': 'OQD', '1': 'IL', '2': 'Z', '5': 'S', '6': 'G', '8': 'B' } _UNAMBIGUOUS_CHARS = string.digits + string.ascii_uppercase for _, s in _AMBIGUOUS_CHARS.items(): _UNAMBIGUOUS_CHARS = re.sub('[{}]'.format(s), '', _UNAMBIGUOUS_CHARS) code = Column(UnicodeText) discount = Column(Integer, nullable=True, default=None) discount_type = Column(Choice(_DISCOUNT_TYPE_OPTS), default=_FIXED_DISCOUNT) expiration_date = Column(UTCDateTime, default=c.ESCHATON) uses_allowed = Column(Integer, nullable=True, default=None) cost = Column(Integer, nullable=True, default=None) group_id = Column(UUID, ForeignKey('promo_code_group.id', ondelete='SET NULL'), nullable=True) group = relationship(PromoCodeGroup, backref='promo_codes', foreign_keys=group_id, cascade='save-update,merge,refresh-expire,expunge') __table_args__ = (Index('uq_promo_code_normalized_code', func.replace( func.replace(func.lower(code), '-', ''), ' ', ''), unique=True), CheckConstraint(func.trim(code) != '', name='ck_promo_code_non_empty_code')) _repr_attr_names = ('code', ) @classmethod def normalize_expiration_date(cls, dt): """ Converts the given datetime to 11:59pm local in the event timezone. """ if isinstance(dt, six.string_types): if dt.strip(): dt = dateparser.parse(dt) else: dt = c.ESCHATON if dt.tzinfo: dt = dt.astimezone(c.EVENT_TIMEZONE) return c.EVENT_TIMEZONE.localize( dt.replace(hour=23, minute=59, second=59, tzinfo=None)) @property def discount_str(self): if self.discount_type == self._FIXED_DISCOUNT and self.discount == 0: # This is done to account for Art Show Agent codes, which use the PromoCode class return 'No discount' elif not self.discount: return 'Free badge' if self.discount_type == self._FIXED_DISCOUNT: return '${} discount'.format(self.discount) elif self.discount_type == self._FIXED_PRICE: return '${} badge'.format(self.discount) else: return '%{} discount'.format(self.discount) @hybrid_property def is_expired(self): return self.expiration_date < localized_now() @is_expired.expression def is_expired(cls): return cls.expiration_date < localized_now() @property def is_free(self): return not self.discount or ( self.discount_type == self._PERCENT_DISCOUNT and self.discount >= 100) or (self.discount_type == self._FIXED_DISCOUNT and self.discount >= c.BADGE_PRICE) @hybrid_property def is_unlimited(self): return not self.uses_allowed @is_unlimited.expression def is_unlimited(cls): return cls.uses_allowed == None # noqa: E711 @hybrid_property def is_valid(self): return not self.is_expired and (self.is_unlimited or self.uses_remaining > 0) @is_valid.expression def is_valid(cls): return (cls.expiration_date >= localized_now()) \ & ((cls.uses_allowed == None) | (cls.uses_remaining > 0)) # noqa: E711 @hybrid_property def normalized_code(self): return self.normalize_code(self.code) @normalized_code.expression def normalized_code(cls): return func.replace(func.replace(func.lower(cls.code), '-', ''), ' ', '') @property def uses_allowed_str(self): uses = self.uses_allowed return 'Unlimited uses' if uses is None else '{} use{} allowed'.format( uses, '' if uses == 1 else 's') @hybrid_property def uses_count(self): return len(self.used_by) @uses_count.expression def uses_count(cls): from uber.models.attendee import Attendee return select([ func.count(Attendee.id) ]).where(Attendee.promo_code_id == cls.id).label('uses_count') @property def uses_count_str(self): uses = self.uses_count return 'Used by {} attendee{}'.format(uses, '' if uses == 1 else 's') @hybrid_property def uses_remaining(self): return None if self.is_unlimited else self.uses_allowed - self.uses_count @uses_remaining.expression def uses_remaining(cls): return cls.uses_allowed - cls.uses_count @property def uses_remaining_str(self): uses = self.uses_remaining return 'Unlimited uses' if uses is None else '{} use{} remaining'.format( uses, '' if uses == 1 else 's') @presave_adjustment def _attribute_adjustments(self): # If 'uses_allowed' is empty, then this is an unlimited use code if not self.uses_allowed: self.uses_allowed = None # If 'discount' is empty, then this is a full discount, free badge if self.discount == '': self.discount = None self.code = self.code.strip() if self.code else '' if not self.code: # If 'code' is empty, then generate a random code self.code = self.generate_random_code() else: # Replace multiple whitespace characters with a single space self.code = re.sub(r'\s+', ' ', self.code) # Always make expiration_date 11:59pm of the given date self.expiration_date = self.normalize_expiration_date( self.expiration_date) def calculate_discounted_price(self, price): """ Returns the discounted price based on the promo code's `discount_type`. Args: price (int): The badge price in whole dollars. Returns: int: The discounted price. The returned number will never be less than zero or greater than `price`. If `price` is None or a negative number, then the return value will always be 0. """ if not self.discount or not price or price < 0: return 0 discounted_price = price if self.discount_type == self._FIXED_DISCOUNT: discounted_price = price - self.discount elif self.discount_type == self._FIXED_PRICE: discounted_price = self.discount elif self.discount_type == self._PERCENT_DISCOUNT: discounted_price = int(price * ((100.0 - self.discount) / 100.0)) return min(max(discounted_price, 0), price) @classmethod def _generate_code(cls, generator, count=None): """ Helper method to limit collisions for the other generate() methods. Arguments: generator (callable): Function that returns a newly generated code. count (int): The number of codes to generate. If `count` is `None`, then a single code will be generated. Defaults to `None`. Returns: If an `int` value was passed for `count`, then a `list` of newly generated codes is returned. If `count` is `None`, then a single `str` is returned. """ from uber.models import Session with Session() as session: # Kind of inefficient, but doing one big query for all the existing # codes will be faster than a separate query for each new code. old_codes = set(s for (s, ) in session.query(cls.code).all()) # Set an upper limit on the number of collisions we'll allow, # otherwise this loop could potentially run forever. max_collisions = 100 collisions = 0 codes = set() while len(codes) < (1 if count is None else count): code = generator().strip() if not code: break if code in codes or code in old_codes: collisions += 1 if collisions >= max_collisions: break else: codes.add(code) return (codes.pop() if codes else None) if count is None else codes @classmethod def generate_random_code(cls, count=None, length=9, segment_length=3): """ Generates a random promo code. With `length` = 12 and `segment_length` = 3:: XXX-XXX-XXX-XXX With `length` = 6 and `segment_length` = 2:: XX-XX-XX Arguments: count (int): The number of codes to generate. If `count` is `None`, then a single code will be generated. Defaults to `None`. length (int): The number of characters to use for the code. segment_length (int): The length of each segment within the code. Returns: If an `int` value was passed for `count`, then a `list` of newly generated codes is returned. If `count` is `None`, then a single `str` is returned. """ # The actual generator function, called repeatedly by `_generate_code` def _generate_random_code(): letters = ''.join( random.choice(cls._UNAMBIGUOUS_CHARS) for _ in range(length)) return '-'.join(textwrap.wrap(letters, segment_length)) return cls._generate_code(_generate_random_code, count=count) @classmethod def generate_word_code(cls, count=None): """ Generates a promo code consisting of words from `PromoCodeWord`. Arguments: count (int): The number of codes to generate. If `count` is `None`, then a single code will be generated. Defaults to `None`. Returns: If an `int` value was passed for `count`, then a `list` of newly generated codes is returned. If `count` is `None`, then a single `str` is returned. """ from uber.models import Session with Session() as session: words = PromoCodeWord.group_by_parts_of_speech( session.query(PromoCodeWord).order_by( PromoCodeWord.normalized_word).all()) # The actual generator function, called repeatedly by `_generate_code` def _generate_word_code(): code_words = [] for part_of_speech, _ in PromoCodeWord._PART_OF_SPEECH_OPTS: if words[part_of_speech]: code_words.append(random.choice(words[part_of_speech])) return ' '.join(code_words) return cls._generate_code(_generate_word_code, count=count) @classmethod def disambiguate_code(cls, code): """ Removes ambiguous characters in a promo code supplied by an attendee. Arguments: code (str): A promo code as typed by an attendee. Returns: str: A copy of `code` with all ambiguous characters replaced by their unambiguous equivalent. """ code = cls.normalize_code(code) if not code: return '' for unambiguous, ambiguous in cls._AMBIGUOUS_CHARS.items(): ambiguous_pattern = '[{}]'.format(ambiguous.lower()) code = re.sub(ambiguous_pattern, unambiguous.lower(), code) return code @classmethod def normalize_code(cls, code): """ Normalizes a promo code supplied by an attendee. Arguments: code (str): A promo code as typed by an attendee. Returns: str: A copy of `code` converted to all lowercase, with dashes ("-") and whitespace characters removed. """ if not code: return '' return re.sub(r'[\s\-]+', '', code.lower())
def normalized_word(cls): return func.lower(func.trim(cls.word))