class Topic(db.Model): __tablename__ = "topics" id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True) subject = db.Column(db.String(128), nullable=True) description = db.Column(db.Text, nullable=True) created_by = db.Column( UUID(as_uuid=True), db.ForeignKey("users.id"), ) updated_by = db.Column( UUID(as_uuid=True), db.ForeignKey("users.id"), ) created_at = db.Column(ISO8601DateTime, nullable=False, default=datetime.datetime.now) updated_at = db.Column(ISO8601DateTime, nullable=False, default=datetime.datetime.now, onupdate=datetime.datetime.now) deleted_at = db.Column(ISO8601DateTime, nullable=True) messages = db.relationship("Message", lazy="dynamic") creator = db.relationship(User, primaryjoin=(created_by == User.id)) updator = db.relationship(User, primaryjoin=(updated_by == User.id)) def __init__(self, subject, description, created_by, updated_by): self.subject = subject self.description = description self.created_by = created_by self.updated_by = updated_by def json(self) -> Dict: return { "id": str(self.id), "subject": self.subject, "description": self.description, "created_by": self.creator.json(), "updated_by": self.updator.json(), "created_at": self.created_at, "updated_at": self.updated_at, "deleted_at": self.deleted_at, "messages_count": len(self.messages.all()), "messages": self.paginated_messages } @property def paginated_messages(self) -> List[Dict]: """Paginated messages representation.""" from app.api.rest.messages.models import Message _ordered_msgs = self.messages.order_by(Message.created_at.desc()) _paginated_msgs = _ordered_msgs.paginate( page=1, per_page=current_app.config.get("COMMENTS_PER_PAGE"), error_out=False).items return [message.json() for message in _paginated_msgs] @classmethod def find(cls, **kwargs) -> "Topic": """Find a database entry that matches given keyword argument.""" keys = list(kwargs.keys()) if (len(keys) == 1 and keys[0] in cls.__table__.columns): return cls.query.filter_by(**kwargs).first() @classmethod def find_all(cls, page: int) -> List[Dict]: """Find all topics in the database that are not deleted yet.""" if not page: raise ValueError topics = (cls.query.filter_by(deleted_at=None).order_by( func.lower(cls.subject)).paginate( page=page, per_page=current_app.config.get("TOPICS_PER_PAGE"), error_out=False)) pagination_data = (topics.has_next, topics.next_num, [topic.json() for topic in topics.items]) return pagination_data def insert(self) -> None: """Insert into the database.""" db.session.add(self) db.session.commit() def delete(self) -> None: """Mark a topic as deleted in the database.""" self.deleted_at = datetime.datetime.now() db.session.commit()
class MotosModel(db.Model): __tablename__ = 'motos' id = db.Column(db.Integer, primary_key=True) model = db.Column(db.String(30),nullable=False) active = db.Column(db.Boolean,nullable=False) charge = db.Column(db.Integer,nullable=False) position = db.Column(db.String(50),nullable=False) latitude = db.Column(db.Float,nullable=False) longitude = db.Column(db.Float,nullable=False) plate = db.Column(db.String(8), unique=True, nullable=False) def __init__(self, model, active, charge, latitude, longitude, plate, position="Passeig de Gracia, 55, Barcelona"): self.model = model self.active = active self.charge = charge self.latitude = latitude self.longitude = longitude self.position = position self.plate = plate def json(self): return { 'id': self.id, 'model': self.model, 'active': self.active, 'charge': self.charge, 'latitude': self.latitude, 'longitude': self.longitude, 'plate':self.plate } def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() @classmethod def find_by_id(cls, id): return MotosModel.query.filter_by(id=id).first() @classmethod def is_active(cls, id): moto = cls.query.filter_by(id=id).first() return moto.active @classmethod def change_status(cls, id): moto = cls.query.filter_by(id=id).first() if moto.active is True: moto.active = False elif moto.active is False: moto.active = True db.session.add(moto) db.session.commit() @classmethod def distMotoUser(cls, user, moto): # Ej: coordUser = [41.386422, 2.16407] # UB #0.45 km coordMoto = [41.387872, 2.170001] # ECI - Pz Cat. # coordMoto = [moto.latitude,moto.longitude] # coordUser = [user.latitude, user.longitude] slat = radians(coordUser[0]) slon = radians(coordUser[1]) elat = radians(coordMoto[0]) elon = radians(coordMoto[1]) # Distance in kilometer return 6371.01 * acos(sin(slat) * sin(elat) + cos(slat) * cos(elat) * cos(slon - elon)) @classmethod def modify_bike(cls, id, modified_bike): bike = cls.query.filter_by(id=id).first() if bike.model != modified_bike.model: bike.model = modified_bike.model if bike.active != modified_bike.active: bike.active = modified_bike.active if bike.charge != modified_bike.charge: bike.charge = modified_bike.charge if bike.latitude != modified_bike.latitude: bike.latitude = modified_bike.latitude if bike.longitude != modified_bike.longitude: bike.longitude = modified_bike.longitude if bike.plate != modified_bike.plate: bike.plate = modified_bike.plate db.session.add(bike) db.session.commit()
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2 )) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name ,price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return {'name': self.name, 'price': self.price} @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() # SELECT * FROM items WHERE name=name LIMIT=1 # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "SELECT * FROM items WHERE name=?" # result = cursor.execute(query,(name,)) # row = result.fetchone() # connection.close() # # if row: # #return {'item':{name: row[0], 'price': row[1]}} # #return cls(row[0], row[1]) # 'cls' calls the __init__() and row[0]=name, row[1]=price # return cls(*row) # bir üstteki return ile aynı işlevde # After Implementing SQLAlchemy # def insert(self): # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "INSERT INTO items VALUES (?, ?)" # cursor.execute(query, (self.name, self.price)) # # connection.commit() # connection.close() # INSERT METHOD IN SQLAlchemy def save_to_db(self): db.session.add(self) db.session.commit() # def update(self): # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "UPDATE items SET price=? WHERE name=?" # cursor.execute(query, (self.price, self.name)) # # connection.commit() # connection.close() def delete_from_db(self): db.session.delete(self) db.session.commit()
class ClientModel(db.Model): __tablename__ = 'clients' client_id = db.Column(db.Integer, primary_key=True) # Personal nombre = db.Column(db.String(50), nullable=False) # Bancario iban = db.Column(db.String(34), nullable=False) # Únicos dni_nie = db.Column(db.String(9), nullable=False, unique=True) email = db.Column(db.String(50), nullable=False, unique=True) # Provisional password = db.Column(db.String(20), nullable=False) # Para la referencia many_to_many con la tabla de motos. motos = db.relationship('MotoModel', secondary=relations_table, backref=db.backref('clients', lazy='dynamic')) rr = db.relationship('ReservedRunningModel', backref='client', uselist=False) def __init__(self, nombre, iban, dni_nie, email, password): self.nombre = nombre self.email = email self.iban = iban self.password = password self.dni_nie = dni_nie def json(self): return { "client_id": self.client_id, "nombre": self.nombre, "email": self.email, "iban": self.iban, "dni_nie": self.dni_nie } def json_profile(self): data = { 'nombre': self.nombre, 'iban': self.iban, 'dni_nie': self.dni_nie, 'email': self.email } return data def set_name(self, name): self.nombre = name db.session.commit() def set_iban(self, iban): self.iban = iban db.session.commit() def set_dni_nie(self, dni_nie): self.dni_nie = dni_nie db.session.commit() def set_email(self, email): self.email = email db.session.commit() def save_to_db(self): """Saves instance to DB """ db.session.add(self) db.session.commit() def delete_from_db(self): """Deletes instance from DB """ db.session.delete(self) db.session.commit() # Metodo que encuentra al cliente segun su email y lo retorna @classmethod def find_by_email(cls, email): return ClientModel.query.filter_by(email=email).first() # Metodo que encuentra al cliente segun su dni y lo retorna @classmethod def find_by_dni(cls, dni_nie): return ClientModel.query.filter_by(dni_nie=dni_nie).first() # Metodo que comprueba si la contraseña es correcta def verify_password(self, password): return self.password.__eq__(password)
class JournalModel(db.Model): __tablename__ = 'journals' username = db.Column(db.Integer, db.ForeignKey('users.username'), primary_key=True) date = db.Column(db.String(10), primary_key=True) content = db.Column(db.Text) private = db.Column(db.Boolean) timestamp = db.Column(db.DateTime) def __init__(self, username, date, content, private=False): self.username = username self.date = str(date) self.content = content self.private = private def save_to_db(self): self.timestamp = datetime.datetime.utcnow() db.session.add(self) db.session.commit() def is_editable(self): # TODO: Impose restritions on edit-allowed time limit and validate for well-formed date string return True def to_dict(self): return { "username": self.username, "date": self.date, "content": self.content, "private": self.private } def __lt__(self, other): return other.date < self.date @classmethod def retrieve_by_username(cls, username, count, daterange): if username: user_query = cls.query.filter_by(username=username) date_descending = cls.date.desc() if count == 0: return user_query.order_by(date_descending).all() elif count: return user_query.order_by(date_descending).limit(count).all() else: start_date = str(datetime.date.today() - datetime.timedelta(daterange)) return user_query.filter( cls.date >= start_date).order_by(date_descending).all() @classmethod def retrieve_exact_post(cls, username, date): if username and date: return cls.query.filter_by(username=username, date=date).first() @classmethod def convert_date(cls, date_string): if type(date_string) is datetime.date: return date_string datelist = date_string.split('-') if len(datelist) == 3: return datetime.date(*[int(dateunit) for dateunit in datelist]) raise ValueError('Invalid date string provided') @classmethod def is_valid_payload(cls, payload): required = ['username', 'date', 'content'] return all(key in payload for key in required)
class ATSCandidate(db.Model): """ A candidate from an ATS. May or may not be linked to a GT candidate. """ __tablename__ = 'ats_candidate' id = db.Column(db.Integer, primary_key=True) # ID in ATS table ats_account_id = db.Column(db.Integer) # Candidate ID in the remote ATS ats_remote_id = db.Column(db.String(100)) # getTalent candidate ID, if linked gt_candidate_id = db.Column(db.Integer) # ID into the ATSCandidateProfile table profile_id = db.Column(db.Integer) # ID into an ATS-specific table ats_table_id = db.Column(db.Integer) added_at = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow) updated_at = db.Column(db.TIMESTAMP, default=datetime.datetime.utcnow) def to_dict(self): """ Return a dict representation of this object. :rtype: dict """ return { 'id': self.id, 'ats_account_id': self.ats_account_id, 'ats_remote_id': self.ats_remote_id, 'gt_candidate_id': self.gt_candidate_id } @classmethod def get_all(cls, account_id): """ Return all ATS candidates associated with this account. :param int account_id: primary key of the account. :rtype list|None: list of candidates. """ candidate_list = cls.query.filter_by(ats_account_id=account_id).all() if not candidate_list: return return_list = [] for candidate in candidate_list: item = candidate.to_dict() profile = ATSCandidateProfile.get(candidate.profile_id) item.update(profile.to_dict()) return_list.append({candidate.id: item}) return return_list @classmethod def get_all_as_json(cls, account_id): """ Return all ATS candidates associated with this account as JSON. :param int account_id: primary key of the account. :rtype str|None: JSON list of candidates. """ candidates = ATSCandidate.get_all(account_id) if not candidates: return return json.dumps(candidates) @classmethod def get_by_ats_id(cls, account_id, ats_id): """ Retrive a candidate by ATS account and remote ATS id. :param int account_id: primary key of the account. :param int ats_id: Id of the candidate in the remote ATS. :rtype list: A candidate. """ # TODO Test this. return cls.query.filter(cls.ats_account_id == account_id, cls.ats_remote_id == ats_id).first() def __repr__(self): return "<ATS Candidate (id = %r)>" % self.ats_remote_id
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return {'name': self.name, 'price': self.price} @classmethod def find_by_name(cls, name): # without sqlalchemy: ''' connection = sqlite3.connect('data.db') cursor = connection.cursor() query = "SELECT * FROM items WHERE name=?" result = cursor.execute(query, (name,)) row = result.fetchone() connection.close() if row: return cls(*row) # row[0], row[1] ''' # with sqlalchemy: # returns a ItemModel # ItemModel.query.filter_by(name=name, id=1) equals to ItemModel.query.filter_by(name=name).filter_by(id=1)... # equals to: return cls.query.filter_by( name=name).first() # = SELECT * FROM items WHERE name=name LIMIT 1 ''' def insert(self): connection = sqlite3.connect('data.db') cursor = connection.cursor() query = "INSERT INTO items VALUES (?, ?)" cursor.execute(query, (self.name, self.price)) connection.commit() connection.close() def update(self): connection = sqlite3.connect('data.db') cursor = connection.cursor() query = "UPDATE items SET price=? WHERE name=?" cursor.execute(query, (self.price, self.name)) connection.commit() connection.close() ''' def save_to_db(self): # = insert + update db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class RecipeModel(db.Model): """ This recipe model class represents the recipe model """ __tablename__ = 'recipes' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) description = db.Column(db.Text) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp()) category_id = db.Column(db.Integer, db.ForeignKey('categories.id')) created_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) def json(self): """ This method jsonifies the recipe model """ return {'name': self.name, 'description': self.description} @property def get_url(self): return url_for(request.endpoint, _external=True) @classmethod def find_by_name(cls, name): """ This class method returns the recipe by name """ return cls.query.filter_by(name=name).first() @classmethod def find_by_category(cls, category_id): """ This class method returns the recipe by category id """ return cls.query.filter_by(category_id=category_id).first() @classmethod def find_by_id(cls, id): """ This class method returns the recipe by id """ return cls.query.filter_by(id=id).first() @classmethod def row_count(cls): """ This class method returns the number of rows """ return cls.query.count() def save_to_db(self): """This method saves recipe to the database""" db.session.add(self) db.session.commit() def delete_from_db(self): """ This method deletes recipe from the database """ db.session.delete(self) db.session.commit()
class User(db.Model): ''' auth_level 1 - basic user auth_level 2 - expert user auth_level 3 - admin ''' __tablename__ = 'fossil_finder_users' id = db.Column(db.Integer, primary_key=True) auth_level = db.Column(db.Integer, nullable=False) group_code = db.Column(db.String(255), index=True, unique=True) group_name = db.Column(db.String(255)) email = db.Column(db.String(255), index=True, unique=True) password_hash = db.Column(db.String(255), index=True) verified = db.Column(db.Boolean, nullable=False, default=False) active = db.Column(db.Boolean, nullable=False, default=True) def generate_token(self, exp=36000): s = Serializer(app.config['SERIALIZER_KEY'], expires_in=exp) return s.dumps({'id': self.id}) def generate_password_hash(self, password): try: password_hash = bcrypt.hashpw(password, bcrypt.gensalt()) self.password_hash = password_hash db.session.commit() except: db.session.rollback() def verify_password(self, password): return bcrypt.hashpw( password, self.password_hash) == self.password_hash and self.verified def login(self): session['token'] = self.generate_token() g.current_user = self def verify(self): try: self.verified = True db.session.add(self) db.session.commit() except: db.session.rollback() @classmethod def from_group_code(cls, group_code): return cls.query.filter(cls.group_code == group_code).first() @classmethod def from_token(cls, token): s = Serializer(app.config['SERIALIZER_KEY']) try: data = s.loads(token) except SignatureExpired: return None except BadSignature: return None user = cls.query.get(data['id']) return user @classmethod def from_email(cls, email): return cls.query.filter(cls.email == email).first() @classmethod def create(cls, email=None, password=None, auth_level=1): if not email or not password: return try: user = cls(email=email, auth_level=1) db.session.add(user) db.session.commit() user.generate_password_hash(password) return user except: db.session.rollback() return None
class SubscriberModel(db.Model): __tablename__ = 'subscriber' id = db.Column(db.Integer, primary_key=True) subscriber_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE')) notification_id = db.Column( db.Integer, db.ForeignKey('notification.id', ondelete='CASCADE')) subscribername = db.Column(db.String(80)) email = db.Column(db.String(80)) def __init__(self, notification_id, subscribername=None): print(subscribername) user = UserModel.find_by_username(str(subscribername)) #self.subscriber_id = subscriber_id self.notification_id = notification_id if subscribername == None: self.subscribername = user.username else: self.subscribername = subscribername self.email = user.email def json(self): return { 'id': self.id, 'subscriber_id': self.subscriber_id, 'notification_id': self.notification_id, 'subscribername': self.subscribername, 'email': self.email } @classmethod def update(self, id, param): group_subscriber = cls.find({"id": id}) if group_subscriber: db.update(group_subscriber).values(**param) ''' @classmethod def find_by_subscriber_id(cls, subscriber_id): return cls.query.filter_by(subscriber_id=subscriber_id).first() ''' @classmethod def find_by_notification_id(cls, id): return cls.query.filter_by(notification_id=id).first() @classmethod def findById(cls, group_subscriber_id): return cls.query.filter_by(id=group_subscriber_id).first() @classmethod def find(cls, **queryArguments): return list(cls.query.filter_by(**queryArguments)) @classmethod def findOne(cls, **queryArguments): return cls.query.filter_by(**queryArguments).first() def save_to_db(self): db.session.add(self) self.commit() @staticmethod def commit(): db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class Project(db.Model, Serializrable): __tablename__ = 'project' id = db.Column(db.Integer, db.Sequence('id_seq'), primary_key=True, autoincrement=True) pro_name = db.Column(db.String(80), nullable=False, unique=True) res_name = db.Column(db.String(80), nullable=False) create_name = db.Column(db.String(80), nullable=False) finish_time = db.Column(db.String(8), nullable=False) create_time = db.Column(db.DateTime, default=datetime.datetime.now) company = db.Column(db.String(80)) category = db.Column(db.String(80)) postcode = db.Column(db.String(80)) contact = db.Column(db.String(80)) tele_phone = db.Column(db.String(80)) u_email = db.Column(db.String(80)) address = db.Column(db.String(256)) #检查单 #check_id = db.Column(db.Integer) program = db.relationship('Program', backref='pro', lazy=True) def __repr__(self): return '<Project %r>' % self.pro_name
class PasswordRecoveryModel(db.Model): __tablename__ = 'password_recovery' __table_args__ = (db.UniqueConstraint('key'), ) SIZE = 32 VALID_UNTIL = timedelta(hours=1) user_id = db.Column(db.Integer(), db.ForeignKey('users.id'), primary_key=True) key = db.Column(db.String(SIZE), nullable=False) time = db.Column(db.DateTime(), nullable=False) def __init__(self, user_id, key=None): self.user_id = user_id self.time = datetime.now() self.key = self.generate_key() if key is None else key def json(self): return { 'user_id': self.user_id, 'valid_until': (self.time + self.VALID_UNTIL).strftime('%H:%M:%S') } def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() def update_from_db(self, data): """ Updates through a dictionary with paris of string of name of attribute and it's value. Following same structure as json(). In case of wanting to modify an attribute of an enum use the string name of one of the values. Will raise Exception in case of invalid enum value if it isn't contained inside the possible values of the enum. """ for attr, newValue in data.items(): if newValue is not None: cls = getattr(self, attr) # Checks if value is of the attribute that's trying to be modified is an Enum if isinstance(cls, Enum): # Checks if the enum doesn't contain the newValue if newValue not in type(cls).__members__: raise Exception( f"Enum {type(cls).__name__} doesn't have value: {newValue}" ) # Gets the object of the enum with same name as newValue setattr(self, attr, type(cls)[newValue]) else: setattr(self, attr, newValue) db.session.commit() def send_email(self, email, url_root): message = f"Has sol·licitat una recuperació de contrasenya. Accedeix a {url_root}reset?key={self.key} "\ f"per canviar de contrasenya. \n L'enllaç deixarà de ser vàlid en {self.VALID_UNTIL} o si es torna " \ f"a solicitar un canvi en la mateixa compte." send_email(email, 'Password recovery', message) def has_time_expired(self): return self.time + self.VALID_UNTIL < datetime.now() @classmethod def find_by_id(cls, user_id): cls.clean_expired_keys() return cls.query.filter_by(user_id=user_id).first() @classmethod def find_by_key(cls, key): cls.clean_expired_keys() return cls.query.filter_by(key=key).first() @classmethod def clean_expired_keys(cls): """ Cleans all entries that their time has expired. Will be called every time a query to the model is made. Expiration time is decided through constant class variable VALID_UNTIL. """ time = datetime.now() - cls.VALID_UNTIL cls.query.filter(cls.time <= time).delete() @classmethod def generate_key(cls): """ Generates a random key avoiding duplicating keys using the most secure random generator of the OS. The key will be made by a combination of uppercase and lowercase letters and numbers. """ new_key = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(cls.SIZE)) while cls.query.filter_by(key=new_key).count() != 0: new_key = ''.join( random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(cls.SIZE)) return new_key
class StoreModel(db.Model ): # tells SQLAlchemy it's something to save/add to db # tell ALchemy which table items will be stored in __tablename__ = "stores" # tell ALchemy which columns it will contain # creates an index & makes it easier to search id = db.Column(db.Integer, primary_key=True) # not used in prior code name = db.Column(db.String(80)) # can limit size of username # do a back reference to the ItemModel # allows a store to see which items are in the items DB # knows it is a many-to-1 relationship (list of items) # items = db.relationship('ItemModel') items = db.relationship( 'ItemModel', lazy='dynamic') #don't create obj for each item in ItemModel yet # self.items is no longer a list of items def __init__(self, name): """ Upon creation of StoreModel, will have attribuate "name" """ # these items must match the columns above # if they're not created above, they won't be stored to the DB self.name = name def json(self): """ Return JSON representation of the model. """ # return {'name': self.name, 'items': self.items} # return {'name': self.name, 'items': [item.json for item in self.items]} # with lazy='dynamic' self.items is a query builder in items table # so until calling JSON method we're not looking into the table return { 'name': self.name, 'items': [item.json() for item in self.items.all()] } @classmethod def find_by_name(cls, name): """ This function acts like the GET method - will return information from the database. """ return cls.query.filter_by(name=name).first() # returns ItemModel obj def save_to_db(self): # changed from insert once SQLAlchemy got involved """ This function takes in an ItemModel object and saves it to the DB. """ # this is helpful for both update and insert db.session.add(self) # session = coll of objs to add to DB db.session.commit() def del_from_db(self): """ This function deletes an ItemModel object from the DB. """ db.session.delete(self) db.session.commit()
class EnderecoModel(db.Model): __tablename__ = 'enderecos' id = db.Column(db.Integer, primary_key=True) cep = db.Column(db.String(9)) rua = db.Column(db.String(80)) bairro = db.Column(db.String(80)) cidade = db.Column(db.String(80)) estado = db.Column(db.String(50)) complemento = db.Column(db.String(40)) numero = db.Column(db.String(20)) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship('UserModel') def __init__(self, cep, rua, bairro, numero, cidade, estado, complemento, user_id): self.cep = cep self.rua = rua self.bairro = bairro self.cidade = cidade self.numero = numero self.estado = estado self.complemento = complemento self.user_id = user_id def json(self): return { 'id': self.id, 'cep': self.cep, 'rua': self.rua, 'bairro': self.bairro, 'cidade': self.cidade, 'estado': self.estado, 'numero': self.numero, 'complemento': self.complemento, 'user_id': self.user_id } @classmethod def find_by_name(cls, cep): return cls.query.filter_by(cep=cep).first() @classmethod def find_by_id_unique(cls, id): return cls.query.filter_by(id=id).first() @classmethod def find_by_id(cls, _id): return cls.query.filter_by(user_id=_id).all() @classmethod def find_all(cls): return cls.query.all() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class User(db.Model): ''' Represents a User ''' required_attributes = ["email", "password"] _id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50)) token = db.Column(db.String, default=key_generator, nullable=False, unique=True) email = db.Column(db.String(50), nullable=False, unique=True) auth_email = db.Column(db.String(100), db.ForeignKey('user_auth.email')) auth = db.relationship('UserAuth', foreign_keys=[auth_email]) admin = db.Column(db.Boolean, default=False, nullable=False) owned_problems = db.relationship('Problem') owned_courses = db.relationship('Course') course_participations = db.relationship('CourseParticipation') solutions = db.relationship('Solution') @property def solution_qnt(self): return len(self.solutions) @property def courses(self): return [ participation.course for participation in self.course_participations ] problem_service = ProblemService() api_fields = { "id": fields.Integer(attribute='_id'), "token": fields.String, "email": fields.String, "ownedProblems": fields.Nested(Problem.api_fields, attribute='owned_problems'), "name": fields.String } def add_problem(self, name, description, tip, publish, tests, tags=None): problem = self.problem_service.create_problem(name, description, tip, tags) self.owned_problems.append(problem) db.session.add(problem) problem.add_tests(tests) if publish: self.problem_service.create_publish_request(problem) db.session.commit() return problem def try_solution(self, problem_key, code, tests): solution = self.problem_service.create_solution( self, problem_key, code, tests) for participation in self.course_participations: participation.course.add_solution(solution) db.session.commit() return solution def create_course(self, name, description, language=None, problems=None): if problems: course = Course(name=name, description=description, language=language, _problems=problems) else: course = Course(name=name, description=description, language=language) self.owned_courses.append(course) db.session.add(course) db.session.commit() course.add_member(self) return course
class Author_Relationships(db.Model): __tablename__ = 'author_relationships' # AuthorRelationship_id = db.Column(db.Integer, primary_key=True) AuthorRelationship_id = db.Column(db.String(100), primary_key=True) authorServer1_id = db.Column(db.Integer) author1_id = db.Column(db.String(100)) author1_name = db.Column(db.String(60)) authorServer2_id = db.Column(db.Integer) author2_id = db.Column(db.String(100)) author2_name = db.Column(db.String(60)) relationship_type = db.Column( db.Integer ) # if 1, author1 is following author 2, if 2 then author2 is following author1, if 3 then both are friends # db.PrimaryKeyConstraint(authorServer1_id, author1_id) # db.PrimaryKeyConstraint(authorServer2_id, author2_id) def __new__(cls, datum=None): """ Input: For datum, see comments in __init__ Description: Checks whether the keys are inside datum dictionary. If not found, then it returns None """ # When the DB will query and retrieve objects, __new__ will have to called to create the objects and datum wont be provided if datum == None: return super(Author_Relationships, cls).__new__(cls) if ('AuthorRelationship_id' and 'authorServer1_id' and 'author1_id' and 'authorServer2_id' and 'author2_id' and 'relationship_type') not in datum.keys(): return None else: return super(Author_Relationships, cls).__new__(cls) def __init__(self, datum=None): """ Input: datum is a dictionary with keys as column names and values as their corresponding values. eg, datum['author1_id']=3, etc Description: This constructor sets the values of fields based on datum dictionary. If any field is missing from datum, its default value will be inserted. TODO: """ if datum == None: self.AuthorRelationship_id = uuid.uuid4().hex return self.AuthorRelationship_id = datum['AuthorRelationship_id'] self.author1_id = datum['author1_id'] self.author2_id = datum['author2_id'] self.authorServer1_id = datum['authorServer1_id'] self.authorServer2_id = datum['authorServer2_id'] self.relationship_type = datum['relationship_type'] if "author1_name" in datum.keys(): self.author1_name = datum["author1_name"] if "author2_name" in datum.keys(): self.author2_name = datum["author2_name"] def insert(self): """ Call this method for inserting an Author_Relationships row into the DB. Before inserting, it makes sure that the authors, servers are all present in their respective DB. """ willInsert = True if Authors.query.filter( Authors.author_id == self.author1_id).all() == []: willInsert = False if Authors.query.filter( Authors.author_id == self.author2_id).all() == []: willInsert = False if Servers.query.filter( Servers.server_index == self.authorServer1_id).all() == []: willInsert = False if Servers.query.filter( Servers.server_index == self.authorServer2_id).all() == []: willInsert = False if willInsert is True: db.session.add(self) db.session.commit() return True #Returns true as it succesfully inserted the row. return False #In case insertion failed def updateRow(self): """ Call this method to update any changes made to any rows in the DB, such as changing the relationship type. """ self.insert() @staticmethod def deleteRowsByQuery(query_param): """ Read query method's description for query_param. This method uses static method query for first retrieving a set of rows that matches the query given in query_param and then deletes """ rows = Author_Relationships.query(query_param) if rows == []: return for row in rows: db.session.delete(row) db.session.commit() @staticmethod def query(query_param): """ query param is a dictionary containing query information. Types of queries: 1) query_param['server_author_1']=[server1_obj, author1_obj] query_param['server_author_2']=[server2_obj, author2_obj] 2) query_param['server_author_1']=[server1_obj, author1_obj] 3) query_param['server_author_1']=[server1_obj, author1_obj] query_param['relationship_type']=relationship_type value 4) query_param['server_author_2']=[server2_obj, author2_obj] 5) query_param['server_author_2']=[server2_obj, author2_obj] query_param['relationship_type']=relationship_type value 6) query_param['relationship_type']=relationship_type value 7) query_param={} // This gives back all the rows 8) query_param["author_ids"]=[author1_id, author2_id] #Add test later 9) query_param["server_author_id1"]=[server1_index, author1_id, type] 10) query_param["server_author_id2"]=[server2_index, author2_id, type] 11) query_param["server_author_id1"]=[server1_index, author1_id] query_param["server_author_id2"]=[server2_index, author2_id] """ if query_param == {}: return db.session.query(Author_Relationships).all() if "areFollowers" in query_param.keys(): author1_id, author2_id = query_param["areFollowers"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1_id, Author_Relationships.author2_id == author2_id, Author_Relationships.relationship_type == 1).all() results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1_id, Author_Relationships.author2_id == author2_id, Author_Relationships.relationship_type == 2).all() return results if "areFriends" in query_param.keys(): author1_id, author2_id = query_param["areFriends"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1_id, Author_Relationships.author2_id == author2_id, Author_Relationships.relationship_type == 3).all() return results if ("server_author_id1" in query_param.keys()) and ("server_author_id2" in query_param.keys()): server1_id, author1_id = query_param["server_author_id1"] server2_id, author2_id = query_param["server_author_id2"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1_id, Author_Relationships.author2_id == author2_id, Author_Relationships.authorServer1_id == server1_id, Author_Relationships.authorServer2_id == server2_id, ).all() print "printing all!" print db.session.query(Author_Relationships).all() return results if "server_author_id1" in query_param.keys(): server1_id, author1_id, relationship_type = query_param[ "server_author_id1"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1_id, Author_Relationships.authorServer1_id == server1_id, Author_Relationships.relationship_type == relationship_type).all() return results if "server_author_id2" in query_param.keys(): server2_id, author2_id, relationship_type = query_param[ "server_author_id2"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author2_id == author2_id, Author_Relationships.authorServer2_id == server2_id, Author_Relationships.relationship_type == relationship_type).all() return results if ("server_author_1" in query_param.keys()) and ("server_author_2" in query_param.keys()): server1, author1 = query_param["server_author_1"] server2, author2 = query_param["server_author_2"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1, Author_Relationships.author2_id == author2, Author_Relationships.authorServer1_id == server1, Author_Relationships.authorServer2_id == server2).all() return results ###### For querying with author1 if "server_author_1" in query_param.keys(): server1, author1 = query_param["server_author_1"] if "relationship_type" in query_param.keys(): relationship_type = query_param["relationship_type"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1, Author_Relationships.authorServer1_id == server1, Author_Relationships.relationship_type == relationship_type).all() else: results = db.session.query(Author_Relationships).filter( Author_Relationships.author1_id == author1, Author_Relationships.authorServer1_id == server1, ).all() return results ###### For querying with author2 if "server_author_2" in query_param.keys(): server2, author2 = query_param["server_author_2"] if "relationship_type" in query_param.keys(): relationship_type = query_param["relationship_type"] results = db.session.query(Author_Relationships).filter( Author_Relationships.author2_id == author2, Author_Relationships.authorServer2_id == server2, Author_Relationships.relationship_type == relationship_type).all() else: results = db.session.query(Author_Relationships).filter( Author_Relationships.author2_id == author2, Author_Relationships.authorServer2_id == server2, ).all() return results ###### For a given relationship_type get all the rows. if "relationship_type" in query_param.keys(): relationship_type = query_param["relationship_type"] results = db.session.query(Author_Relationships).filter( Author_Relationships.relationship_type == relationship_type).all() return results print "returning None" return None
class UserAuth(db.Model): email = db.Column(db.String(100), primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('user._id')) user = db.relationship('User', foreign_keys=[user_id])
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column( db.Integer, db.ForeignKey('stores.id')) # set foreign_key of id(stores) table store = db.relationship('StoreModel') # works as a JOIN in SQLAlchemy def __init__(self, name, price, store_id): # there is_no "id" parameter, it won't be used self.name = name self.price = price self.store_id = store_id def json(self ): # to return a_basic_json_representation(ItemModel)..i.e.item return { 'name': self.name, 'price': self.price } # that_is a_dictionary representing_an_item @classmethod # should_be_a_classMethod BCZ it_returns an_object_of_type_"ItemModel" def find_by_name(cls, name): # as supposed to a DICTIONARY # returns the "ItemModel_object" # return ItemModel.query.filter_by(name=name).first() # SELEST * FROM items WHERE name=name LIMIT_1 #/.filetr_by(id=1) return cls.query.filter_by(name=name).first() # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # query = "SELECT * FROM items WHERE name=?" # result = cursor.execute(query, (name,)) # row = result.fetchone() # connection.close() # if row: # # return {'item': {'name': row[0], 'price': row[1]}} # returns a Dictionary # return cls(*row) # cls(row[0], row[1]) # return an_object_of_type_"ItemModel" instead_of_dictionary With_arg_unpacking # def insert(self): # pass "item" as_it_self as_an_argument_of_function_of_ItemModel # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # query = "INSERT INTO items VALUES (?, ?)" # # cursor.execute(query, (item['name'], item['price'])) # cursor.execute(query, (self.name, self.price)) # connection.commit() # connection.close() def save_to_db(self): # contains_for_both_INSERT_and_UPDATE db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class StoreModel(db.Model): __tablename__ = 'stores' # having an id for an entity is useful id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) #back reference does the opposite # allows a store to see which items are in the items database with a store id # equal to its own id # this is saying we have a relationship with ItemModel, and then SQLAlchemy says # okay there is a relationship there. whats the relationship? # it goes into the item model and finds the store id there and says # "aha there is a store id in the item, which means that 1 item is related to a store. # and thus there could be more than 1 item related to the same store" # hence it knwos the store variable in item.oy is a single store (only one store an item is related to) # but the items variable in the store.py (below) can be many items so therefore items is a list of # item models. (many to 1). can be many items with the same store id # Whenever we create a StoreModel, this relationship is also created # meaning, if we have many stores and we have many items, whenever we create a StoreModel # we are going to go and create an object for each item in the db that matches that store id # now if we have a few items that is fine, but alot can be expensive # so to avoid that, that we can tell SQLAlchemy to not to that i.e. do not go into the items # table and create an object for each item YET. # we do that by lazy = 'dynamic'. items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name): self.name = name # returns JSON representation of a model, basically a dictionary # Because we used lazy = 'dynamic', we have to do self.items.all() # why? when we use lazy = 'dynamic', self.items is no longer a list of items # instead it is a query builder that has the ability to look into the items table # this means that until we call the json method, we are not looking into the table # which means creating stores is very simple # However it also that everytime we call the json method, we have to go in to the # table so then it will be slower that if we create a store, load up all the items and then call # the json method manytimes for free essentially. # so if we use lazy = 'dynamic', everytime we call the json method, we have to go into the table # so then that is slower. so there is a trade off there between the speed of creation of the store # and speed of calling the json method, thats upto you which to call # we stick with this in this case, cuz our StoreModel will get created when we want to access the data # (thats how we are doing it in the Store Resource) def json(self): return { 'name': self.name, 'items': [item.json() for item in self.items.all()] } @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class ItemModel(db.Model): __tablename__ = 'geometry_columns' id = db.Column(db.Integer, primary_key=True) idm = db.Column(db.String(80)) date = db.Column(db.String(80)) name = db.Column(db.String(80)) telephone = db.Column(db.String(80)) email = db.Column(db.String) categorie = db.Column(db.String) toelichting = db.Column(db.String) XCoordinaat = db.Column(db.Float) YCoordinaat = db.Column(db.Float) image = db.Column(db.String) status = db.Column(db.String) nearestaddress = db.Column(db.String) nearestpostal = db.Column(db.String) nearestplace = db.Column(db.String) def __init__(self, idm, date, name, telephone, email, categorie, toelichting, XCoordinaat, YCoordinaat, image, status, nearestaddress, nearestpostal, nearestplace): self.idm = idm self.date = date self.name = name self.telephone = telephone self.email = email self.categorie = categorie self.toelichting = toelichting self.XCoordinaat = XCoordinaat self.YCoordinaat = YCoordinaat self.image = image self.status = status self.nearestaddress = nearestaddress self.nearestpostal = nearestpostal self.nearestplace = nearestplace def json(self): #return{'id':self.idm,'date':self.date,'name':self.name, 'telephone':self.telephone,'email':self.email,'categorie':self.categorie,'toelichting':self.toelichting,'XCoordinaat':self.XCoordinaat,'YCoordinaat':self.YCoordinaat,'image':self.image,'status':self.status,'nearestaddress':self.nearestaddress,'nearestpostal':self.nearestpostal,'nearestplace':self.nearestplace} #FeatureCollection(data['features']) #return (data1) return { 'type': 'Feature', 'id': self.idm, "properties": { 'date': self.date, "name": self.name, 'telephone': self.telephone, 'email': self.email, 'categorie': self.categorie, 'toelichting': self.toelichting, 'image': self.image, 'status': self.status, 'nearestaddress': self.nearestaddress, 'nearestpostal': self.nearestpostal, 'nearestplace': self.nearestplace }, 'geometry': { 'type': 'Point', 'coordinates': [self.XCoordinaat, self.YCoordinaat] } } @classmethod def find_by_name(cls, name): return cls.query.filter_by( name=name).first() #SELECT * FROM items WHERE name=name LIMIT 1 @classmethod def find_by_categorie(cls, categorie): return cls.query.filter_by( categorie=categorie ) #SELECT * FROM items WHERE categorie=categorie LIMIT 1 def save_to_db(self): db.session.add(self) db.session.commit() #@classmethod def delete_from_db(self): db.session.delete(self) db.session.commit()
class TransactionModel(db.Model): __tablename__ = 'transaction' id = db.Column('transaction_id', db.Integer, primary_key=True) categoryId = db.Column('category_id', db.Integer, db.ForeignKey('category.category_id')) transactionDate = db.Column(DATETIME, nullable=False, default=func.now()) description = db.Column('description', db.String(32)) amount = db.Column('amount', db.Numeric, nullable=False) def __init__(self, param_dict): self.categoryId = param_dict['categoryId'] self.transactionDate = param_dict['transactionDate'] self.description = param_dict['description'] self.amount = param_dict['amount'] @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_params(cls, queryParams): query = cls.query if queryParams['startDate']: query = query.filter( func.date(TransactionModel.transactionDate) >= func.date( queryParams['startDate'])) if queryParams['endDate']: query = query.filter( func.date(TransactionModel.transactionDate) <= func.date( queryParams['endDate'])) if queryParams['categoryName']: cat = CategoryModel.find_by_name(queryParams['categoryName']) if cat: query = query.filter(TransactionModel.categoryId == cat.id) else: return [] return query @classmethod def find_all(cls): return cls.query.all() def update(self): db.session.query(TransactionModel).filter(TransactionModel.id == self.id) \ .update( { 'categoryId': self.categoryId, 'transactionDate': self.transactionDate, 'description': self.description, 'amount': self.amount } ) db.session.commit() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() def json(self): resp = { 'id': self.id, 'categoryId': self.categoryId, 'transactionDate': self.transactionDate.replace(microsecond=0).isoformat() if self.transactionDate else datetime.now().replace( microsecond=0).isoformat(), 'description': self.description, 'amount': "{0:.2f}".format(float(self.amount)) } if self.categoryId: cat = CategoryModel.find_by_id(self.categoryId) if cat: resp['categoryName'] = cat.name return resp
class ItemModel(db.Model): # specify table name and columns __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) # 80 character maximum to limit the size price = db.Column( db.Float(precision=2)) # 2 numbers after the decimal point store_id = db.Column( db.Integer, db.ForeignKey('stores.id') ) # add a store id to link the items and its belonging store store = db.relationship('StoreModel') # how SQLAlchemy does join def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id # return a JSON representation of model, basically a dictionary def json(self): return {'name': self.name, 'price': self.price} # move following methods from item.py in resource to here, models, since they don't belong to a resource # keep this as class method since it will return a dictionary other than a model object @classmethod def find_by_name(cls, name): ### use SQLAlchemy # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "SELECT * FROM items WHERE name = ?" # result = cursor.execute(query, (name,)) # row = result.fetchone() # connection.close() # # if row: # return cls(*row) # parse all elements in row to a item model ### # SQLAlchemy will transit a row to ItemModel if it can, .query is using a query builder # return ItemModel.query.filter_by(name = name).first() return cls.query.filter_by( name=name).first() # SELECT * FROM items WHERE name = name LIMIT 1 # return a item model object # modify following 2 methods as not class method since they can use item model object directly def save_to_db(self): ### use SQLAlchemy # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "INSERT INTO items VALUES (?, ?)" # cursor.execute(query, (self.name, self.price)) # # connection.commit() # connection.close() ### # save the model into database, SQLAlchemy can automatically translate model to row in a database, so we just tell it the object - self db.session.add(self) db.session.commit() # it can update so we change this method to do insertion and updating, then we don't need another separate update method but we create another delete_from_db method for better use ### don't need # def update(self): # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "UPDATE items SET price = ? WHERE name = ?" # cursor.execute(query, (self.price, self.name)) # match the values in-order # # connection.commit() # connection.close() ### def delete_from_db(self): db.session.delete(self) db.session.commit()
class RuntimeModel(db.Model): __tablename__ = 'runtimes' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) type = db.Column(db.String(80)) user_name = db.Column(db.String(30)) user_password = db.Column(db.String(30)) user_hostname = db.Column(db.String(30)) dockerfile = db.Column(db.String(5000)) image_name = db.Column(db.String(1000)) error = db.Column(db.String(1000)) #endpoint_id = db.Column(db.Integer, db.ForeignKey('endpoints.id')) #endpoint = db.relationship('EndpointModel') # now every RuntimeModel has a property endpoint which matches with a endpoint_id def __init__(self, name, type, user_name=None, user_password=None, user_hostname=None, dockerfile=None, error=None): self.name = name self.type = type self.user_name = user_name self.user_password = user_password self.user_hostname = user_hostname self.dockerfile = dockerfile self.error = error def json(self): if (self.error): return { 'id': self.id, 'type': self.type, 'name': self.name, 'error': self.error } return {'id': self.id, 'type': self.type, 'name': self.name} @classmethod def find_by_name(cls, name): return RuntimeModel.query.filter_by(name=name).first() @classmethod def find_by_id(cls, id): return RuntimeModel.query.filter_by(id=id).first() def insert_or_update(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit()
class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True) email = db.Column(db.String(120), unique=True)
class AppointmentModel(db.Model, BaseMethods): __tablename__ = 'appointments' id = db.Column(db.Integer, primary_key=True) # Relationships doctorId = db.Column(db.Integer, db.ForeignKey('doctors.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False) doctor = db.relationship('DoctorModel') patientId = db.Column(db.Integer, db.ForeignKey('patients.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False) patient = db.relationship('PatientModel') appointmentDate = db.Column(db.DateTime, nullable=False) reason = db.Column(db.String(200)) createdAt = db.Column(db.DateTime, nullable=False) canceledAt = db.Column(db.DateTime) updatedOn = db.Column(db.DateTime) status = db.Column(db.String(3), server_default='ACT') def __init__(self, doctor_id, patient_id, appointment_date, reason, created_at, canceled_at, updated_on, status): super(AppointmentModel, self).__init__() self.doctorId = doctor_id self.patientId = patient_id self.appointmentDate = (datetime.now() + timedelta(hours=2)) if ( appointment_date is None) else appointment_date self.reason = reason self.createdAt = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if ( created_at is None) else created_at self.canceledAt = canceled_at self.updatedOn = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if ( updated_on is None) else updated_on self.status = status def __repr__(self): return 'Appointment: %r' % self.reason def json(self, role_id): if role_id == 1: return { 'id': self.id, 'patient': self.patient.json(), 'appointmentDate': self.appointmentDate, 'reason': self.reason, 'canceledAt': self.canceledAt, 'status': self.status } else: return { 'id': self.id, 'doctor': self.doctor.json(), 'appointmentDate': self.appointmentDate, 'reason': self.reason, 'canceledAt': self.canceledAt, 'status': self.status } @classmethod def find_by_patient_id(cls, patient_id): return cls.query.filter_by(patientId=patient_id).all() @classmethod def find_by_doctor_id(cls, doctor_id): return cls.query.filter_by(doctorId=doctor_id).all() @classmethod def find_by_appointment_date(cls, appointment_date): return cls.query.filter_by(appointmentDate=appointment_date).first()
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.String(80)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def __repr__(self): return f"ItemModel({self.name},{self.price})" def json(self): return { 'name': self.name, 'price': self.price, 'store_id': self.store_id } @classmethod def get_by_name(cls, name): """connecton = sqlite3.connect('data.db') cursor = connecton.cursor() query = "SELECT * from items where Name =?" result = cursor.execute(query, (name,)) row = result.fetchone() if row: return cls(*row)""" return cls.query.filter_by(name=name).first() def save_to_db(self): """connecton = sqlite3.connect('data.db') cursor = connecton.cursor() query = "INSERT into items values (?,?) " cursor.execute(query, (self.name, self.price)) connecton.commit() connecton.close() """ db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class UserModel(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True, nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) passwd_hash = db.Column(db.String(120), nullable=False) registered_on = db.Column(db.DateTime, nullable=False) confirmed = db.Column(db.Boolean, nullable=False, default=False) confirmed_on = db.Column(db.DateTime, nullable=True) trips = db.relationship("TripsModel", passive_deletes="all", backref="parent") #images = db.relationship("ImagesModel", passive_deletes="all", backref="parent") def __init__(self, email, confirmed=False, registered_on=None, confirmed_on=None): # self.id = id self.email = email self.username = email.split('@')[0] self.passwd_hash = None if registered_on is None: self.registered_on = datetime.datetime.now() else: self.registered_on = registered_on self.confirmed = confirmed self.confirmed_on = confirmed_on # set password hash def set_password(self, password): self.passwd_hash = generate_password_hash(password) # set username def set_username(self): username_taken = UserModel.check_user(username=self.username) if username_taken: self.username += str(random.randint(0, 1000)) self.set_username() else: return 'Username set' @classmethod def is_password(cls, hashed_passwd, password): return check_password_hash(hashed_passwd, password) @classmethod def check_user(cls, username=None, email=None): if username: return cls.query.filter_by(username=username).first() if email: return cls.query.filter_by(email=email).first() @classmethod def delete_all(cls): try: num_rows_deleted = db.session.query(cls).delete() db.session.commit() return {'message': '{} row(s) deleted'.format(num_rows_deleted)} except: return {'message': 'Something went wrong'} @classmethod def find_user_id(cls, username): user_record = cls.query.filter_by(username=username).first() return user_record.id def save_to_db(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit()
from flask_wtf import FlaskForm from wtforms import StringField, TextAreaField from wtforms.validators import DataRequired from db import db table = db.Table( "sign_ins", db.Column("first_name", db.String(length=255)), db.Column("last_name", db.String(length=255)), db.Column("street_and_house_number", db.Text), db.Column("plz_and_city", db.Text), db.Column("phone_number", db.String(length=255)), db.Column("signed_in_at", db.DateTime), ) class Form(FlaskForm): first_name = StringField( "Vorname", validators=[DataRequired(message="Bitte trag deinen Vornamen ein")], ) last_name = StringField( "Nachname", validators=[DataRequired(message="Bitte trag deinen Nachnamen ein")], ) street_and_house_number = StringField( "Straße und Hausnummer", validators=[ DataRequired(message="Bitte trag deine Straße und Hausnummer ein") ],
class UserModel(db.Model): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True, autoincrement=False) #username = db.Column(db.String(80), nullable=True, unique=True) password = db.Column(db.String(200), nullable=True) temporary_password = db.Column(db.String(200), nullable=True) email = db.Column(db.String(80), nullable=False, unique=True) phone = db.Column(db.String(15), unique=True, nullable=True) name = db.Column(db.String(80)) birth_date = db.Column(db.Date, nullable=True) gender = db.Column(db.String(1)) confirmation = db.relationship("ConfirmationModel", lazy="dynamic", cascade="all, delete-orphan") @property def most_recent_confirmation(self) -> "ConfirmationModel": # ordered by expiration time (in descending order) return self.confirmation.order_by(db.desc( ConfirmationModel.expire_at)).first() @classmethod def find_by_username(cls, username: str) -> "UserModel": return cls.query.filter_by(username=username).first() @classmethod def find_by_email(cls, email: str) -> "UserModel": return cls.query.filter_by(email=email).first() @classmethod def find_by_phone(cls, phone: str) -> "UserModel": return cls.query.filter_by(phone=phone).first() @classmethod def find_by_id(cls, _id: int) -> "UserModel": return cls.query.filter_by(id=_id).first() def send_confirmation_email(self) -> Response: # configure e-mail contents subject = "Registration Confirmation" link = request.url_root[:-1] + url_for( "confirmation", confirmation_id=self.most_recent_confirmation.id) # string[:-1] means copying from start (inclusive) to the last index (exclusive), a more detailed link below: # from `http://127.0.0.1:5000/` to `http://127.0.0.1:5000`, since the url_for() would also contain a `/` # https://stackoverflow.com/questions/509211/understanding-pythons-slice-notation text = f"Please click the link to confirm your registration: {link}" html = f"<html>Please click the link to confirm your registration: <a href={link}>link</a></html>" # send e-mail with MailGun return Mailgun.send_email([self.email], subject, text, html) def send_sms(self) -> MessageInstance: text = gettext("user_sms_text_code").format( str(self.most_recent_confirmation.code)) return Twilio.send_sms(number="+351" + self.phone, body=text) def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit()
class PersonModel(db.Model): __tablename__ = 'Persons' person_id = db.Column(db.Integer, primary_key=True) id = db.Column(UUID(as_uuid=True), default=uuid.uuid4, nullable=False) first_name = db.Column(db.String(100), nullable=False) middle_name = db.Column(db.String(100)) last_name = db.Column(db.String(100), nullable=False) email = db.Column(db.String(1000), nullable=False) age = db.Column(db.Integer, nullable=False) version = db.Column(db.Integer, nullable=False) latest = db.Column(db.Boolean, nullable=False) def __init__(self, id, first_name, middle_name, last_name, email, age, version, latest): self.id = id self.first_name = first_name self.middle_name = middle_name self.last_name = last_name self.email = email self.age = age self.version = version self.latest = latest def __repr__(self): return "PersonModel(person_id=%s, id=%s, first_name=%s, middle_name=%s, last_name=%s, email=%s, age=%s, version=%s, latest=%s)" \ % (self.person_id, self.id, self.first_name, self.middle_name, self.last_name, self.email, self.age, self.version, self.latest) def json(self): return { "first_name": self.first_name, "last_name": self.last_name, "middle_name": self.middle_name, "age": self.age, "email": self.email, "id": self.id, "version": self.version } @classmethod def find_by_id(cls, id) -> "PersonModel": return cls.query.filter_by(id=id, latest=True).first() @classmethod def find_by_id_and_version(cls, id, version) -> "PersonModel": return cls.query.filter_by(id=id, version=version).first() @classmethod def is_unique(cls, first_name, middle_name, last_name) -> "PersonModel": result = cls.query.filter_by(first_name=first_name, middle_name=middle_name, last_name=last_name).first() return not result @classmethod def find_all(cls) -> List["PersonModel"]: return cls.query.filter_by(latest=True).all() def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit()