class Post(db.Model, ResourceMixin): __tablename__ = 'post' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(100), nullable=False) content = db.Column(db.String(1024), nullable=False) # ===================== Relationships ================================== account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) account = db.relationship('Account', backref=db.backref('posts', lazy=True)) @classmethod def find(cls, identity): return Post.query.filter((cls.title == identity) | (cls.id == identity)).first() # @classmethod # def get_all_posts(cls): # return Post.query.all() @classmethod def get_all_posts(cls, page=None): if page is None: return Post.query.order_by(Post.create_date.desc()).all() else: return Post.query.order_by(Post.create_date.desc()).paginate( page=page, per_page=5)
class CityWeather(db.Model): __tablename__ = 'cityweather' id = db.Column(db.Integer, primary_key=True) # ID time = db.Column(db.String(32)) city = db.Column(db.String(128)) # 城市名字 AQI = db.Column(db.String(32)) # AQI quality = db.Column(db.String(8)) # 空气质量 pm = db.Column(db.String(32)) # pm2.5
class Book(db.Model): id = db.Column(db.Integer, primary_key = True) title = db.Column(db.String(100), nullable=False) description = db.Column(db.String(200), nullable=False) author_id = db.Column(db.Integer, db.ForeignKey('author.id'), nullable=False) def __init__(self, title, description, author_id): self.title = title self.description = description self.author_id = author_id
class Product(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(10), unique=True) description = db.Column(db.String(200)) price = db.Column(db.Float) qty = db.Column(db.Integer) def __init__(self, name, description, price, qty): self.name = name self.description = description self.price = price self.qty = qty
class Author(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False) book = db.relationship('Book', backref='author', lazy='dynamic') def __init__(self, name): self.name = name
class BookTags(db.Model): __tablename__ = "book_tags" id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=True) goodreads_book_id = db.Column(db.Integer) tag_id = db.Column(db.Integer) tag_name = db.Column(db.String(120)) count = db.Column(db.Integer) @classmethod def get_tags(cls, gid, n=5): """Gets list of tags for a given book. Args: gid ([string]): Goodreads book id provided in the dataset. Encountered as `goodreads_book_id` Returns: [List]: List of tag objects, each of which has a two fields (tag_id, count) """ return [ x.__dict__ for x in (cls.query.filter_by( goodreads_book_id=gid).order_by(cls.count.desc()).limit(n)) ]
class BookTags( db.Model ): # the table book_tags in the form of a class, that was able by SQL Alchemy ORM __tablename__ = "book_tags" #identifying the name of the table id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=True) #the id column goodreads_book_id = db.Column( db.Integer ) #the column containing the id of the books from the goodreads dataset tag_id = db.Column( db.Integer) #The column containing the id of the tags from the dataset tag_name = db.Column( db.String(120) ) #the column containing the name of the tag corresponding to the tag id count = db.Column(db.Integer) @classmethod def get_tags(cls, gid, n=5): """Gets list of tags for a given book. Args: gid ([string]): Goodreads book id provided in the dataset. Encountered as `goodreads_book_id` Returns: [List]: List of tag objects, each of which has a two fields (tag_id, count) """ return [ x.__dict__ for x in (cls.query.filter_by( goodreads_book_id=gid).order_by(cls.count.desc()).limit(n)) ]
class Books(db.Model): #the books table as a class where all the columns are identified __tablename__ = "books" id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=True) book_id = db.Column(db.Integer) goodreads_book_id = db.Column(db.Integer) isbn13 = db.Column(db.Integer) authors = db.Column(db.String(200)) original_publication_year = db.Column(db.Integer) original_title = db.Column(db.String(120)) title = db.Column(db.String(400)) language_code = db.Column(db.String(40)) average_rating = db.Column(db.Float) ratings_count = db.Column(db.Integer) image_url = db.Column(db.String(200)) @classmethod def get_paginated(cls, page): return cls.query.paginate(page, 12, False).items @classmethod def get_list(cls, ids): return cls.query.filter(cls.book_id.in_(ids)) @classmethod def get_list_from_goodreads_ids(cls, ids): return cls.query.filter(cls.goodreads_book_id.in_(ids)) @classmethod def get_book(cls, id): try: res = cls.query.filter_by(book_id=id).first() tags = BookTags.get_tags(res.__dict__.get("goodreads_book_id"), n=10) tag_names = [ x.get("tag_name") for x in tags ] res.__dict__["tags"] = tag_names if res is not None: return res.__dict__ return None except Exception as e: logging.error(e) return None
class User(BaseModel, db.Model): __tablename__ = 'user' id = db.Column(db.BIGINT, primary_key=True) name = db.Column(db.String(32), default="") password = db.Column(db.String(256), default="") ext_property = db.Column(db.JSON, default={}) is_available = db.Column(db.INT, default=1) create_ts = db.Column(db.TIMESTAMP, default=datetime.now()) update_ts = db.Column(db.TIMESTAMP, default=datetime.now()) def is_active(self): return self.id != None def is_authenticated(self): return self.id != None def get_id(self): return self.id def is_anonymous(self): return self.id == None
class Record(db.Model): __tablename__ = 'record' id = db.Column(db.String(30), primary_key=True) score = db.Column(db.Integer, nullable=False, default=0) def save(self): db.session.add(self) db.session.commit() return self @classmethod def find(cls, id): return Record.query.filter(cls.id == id).first()
class Task(db.Model): id = db.Column(db.Integer, primary_key=True) task_name = db.Column(db.String(80), unique=True) date_created = db.Column(db.DateTime, default=datetime.datetime.utcnow) date_modified = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, task_name): self.task_name = task_name def __repr__(self): return '<Task {self.task_name}>' def to_json(self): return { 'id': self.id, 'task_name': self.task_name, 'date_created': self.date_created, 'date_modified': self.date_modified }
class Jobs(db.Model): __tablename__="data" sno=db.Column(db.Integer,primary_key=True) company=db.Column(db.String(100)) location=db.Column(db.String(100)) role=db.Column(db.String(60)) type=db.Column(db.String(30)) sector=db.Column(db.String(50)) link=db.Column(db.String(200)) def __init__(self,sno,company,location,role,type,sector,link): self.sno=sno self.company=company self.location=location self.role=role self.type=type self.sector=sector self.link=link self.list=[]
class Account(db.Model, UserMixin, ResourceMixin): __tablename__ = 'account' id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String(50), nullable=False) last_name = db.Column(db.String(50), nullable=False) username = db.Column(db.String(30), nullable=False, unique=True) email = db.Column(db.String(50), nullable=False, unique=True) password = db.Column(db.String(1024), nullable=False) active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1') profile_pic = db.Column(db.String(300), nullable=False, default='static/images/default.jpg') sign_in_count = db.Column(db.Integer, nullable=False, default=0) current_sign_in_date = db.Column(db.DateTime) current_sign_in_ip = db.Column(db.String(200)) last_sign_in_date = db.Column(db.DateTime) last_sign_in_ip = db.Column(db.String(200)) @classmethod def find(cls, identity): return Account.query.filter((cls.email == identity) | (cls.username == identity)).first() def is_active(self): return self.active def serialize_token(self, expiration=300): private_key = app.config['SECRET_KEY'] serializer = TimedJSONWebSignatureSerializer(private_key, expiration) return serializer.dumps({'email': self.email}).decode('utf-8') @classmethod def deserialize_token(cls, token): private_key = TimedJSONWebSignatureSerializer(app.config['SECRET_KEY']) try: decoded_payload = private_key.loads(token) return Account.find(decoded_payload.get('email')) except Exception: return None def update_activity_tracking(self, ip_address): self.sign_in_count = self.sign_in_count + 1 self.last_sign_in_date = self.current_sign_in_date self.last_sign_in_ip = self.current_sign_in_ip self.current_sign_in_date = datetime.now() self.current_sign_in_ip = ip_address self.save() def get_posts(self, page=None): if page is None: return self.posts else: return Post.query.filter(Post.account_id == self.id)\ .order_by(Post.create_date.desc())\ .paginate(page=page, per_page=5)
class Applicant(db.Model, ResourceMixin): __tablename__ = 'applicant' ATTAINMENT = OrderedDict([ ('none', 'No formal education'), ('primary', 'Primary education'), ('secondary', 'Secondary education/High school'), ('ged', 'General Education Diploma'), ('vocational', 'Vocational qualification'), ('bachelor\'s', 'Bachelor\'s degree'), ('master\'s', 'Master\'s degree'), ('doctoral', 'Doctorate or higher') ]) SHIFT = OrderedDict([ ('any', 'Any'), ('day', 'Day Shift'), ('mid', 'Mid Shift'), ('night', 'Night Shift'), ('shift', 'Shifting') ]) MARITAL_STATUS = OrderedDict([ ('single', 'Single'), ('married', 'Married'), ('widowed', 'Widowed'), ('divorced', 'Divorced'), ('separated', 'Separated') ]) STATUS = OrderedDict([ ('pending', 'Pending'), ('phone_invite', 'Phone Invite'), ('no_reach', 'Can\'t be reached'), ('declined', 'Declined'), ('interview', 'For interview'), ('passed', 'Passed'), ('failed', 'Failed'), ('hired', 'Hired') ]) id = db.Column(db.Integer, primary_key=True) # Personal Information last_name = db.Column(db.String(50), nullable=False) first_name = db.Column(db.String(50), nullable=False) middle_name = db.Column(db.String(50), nullable=True) birthdate = db.Column(db.DateTime, nullable=True, default=datetime.strptime('0001-01-01 12:00 AM', '%Y-%m-%d %I:%M %p')) email = db.Column(db.String(50), nullable=False, unique=True) address = db.Column(db.String(300), nullable=True) mobile1 = db.Column(db.String(11), nullable=False, unique=True) mobile2 = db.Column(db.String(11), nullable=True) landline = db.Column(db.String(11), nullable=True) marital_status = db.Column(db.Enum(*MARITAL_STATUS, name='marital_status', native_enum=False), index=True, nullable=False, server_default='single') # Scholastic Information educational_attainment = db.Column(db.Enum(*ATTAINMENT, name='educational_attainment', native_enum=False), index=True, nullable=False, server_default='bachelor\'s') course = db.Column(db.String(30), nullable=True) graduation_year = db.Column(db.String(4), nullable=True) # Job Preference applied_position = db.Column(db.String(50), nullable=True) expected_salary = db.Column(db.Integer, nullable=False, default=0) preferred_shift = db.Column(db.Enum(*SHIFT, name='preferred_shift', native_enum=False), index=True, nullable=False, server_default='any') preferred_location = db.Column(db.String(50), nullable=True) # Call Information status = db.Column(db.Enum(*STATUS, name='status', native_enum=False), index=True, nullable=False, server_default='pending') remarks = db.Column(db.String(300), nullable=True) # Additional Information acquire_date = db.Column(db.DateTime, nullable=False, default=datetime.now()) source = db.Column(db.String(30), nullable=True) interview_datetime = db.Column(db.DateTime, nullable=True, default=datetime.strptime('0001-01-01 12:00 AM', '%Y-%m-%d %I:%M %p')) # ===================== Relationships ================================== hr_id = db.Column(db.Integer, db.ForeignKey('account.id')) hr = db.relationship('Account', backref=db.backref('applicants', lazy=True)) @classmethod def find_applicant(cls, id): return Applicant.query.filter(cls.id == id).first() @classmethod def count(cls): return Applicant.query.count()
class Account(db.Model, ResourceMixin, UserMixin): __tablename__ = 'account' ROLE = OrderedDict([ ('it', 'IT'), ('admin', 'HR Admin'), ('hr', 'HR') ]) id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String(50), nullable=False) last_name = db.Column(db.String(50), nullable=False) username = db.Column(db.String(30), nullable=False, unique=True) email = db.Column(db.String(50), nullable=False, unique=True) mobile = db.Column(db.String(11), nullable=False) password = db.Column(db.String(50), nullable=False) role = db.Column(db.Enum(*ROLE, name='role', native_enum=False), index=True, nullable=False, server_default='it') active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1') profile_pic = db.Column(db.String(300), nullable=False, default='static/images/default.jpg') # Activity tracking for IT sign_in_count = db.Column(db.Integer, nullable=False, default=0) current_sign_in_date = db.Column(db.DateTime) current_sign_in_ip = db.Column(db.String(200)) last_sign_in_date = db.Column(db.DateTime) last_sign_in_ip = db.Column(db.String(200)) @classmethod def find_account(cls, identity): return Account.query.filter((cls.username == identity) | (cls.email == identity)).first() @classmethod def get_all_accounts(cls): return Account.query.all() @classmethod def get_all_active_hr(cls): return Account.query.filter(cls.role == 'hr' \ and cls.active == True).all() @classmethod def count(cls): return Account.query.count() @classmethod def hr_count(cls): return Account.query.filter(cls.role == 'hr' \ and cls.active == True).count() def is_active(self): return self.active def update_activity_tracking(self, ip_address): self.sign_in_count = self.sign_in_count + 1 self.last_sign_in_date = self.current_sign_in_date self.last_sign_in_ip = self.current_sign_in_ip self.current_sign_in_date = datetime.now() self.current_sign_in_ip = ip_address self.save()
class User(db.Model): __tablename__ = "users" user_id = db.Column(db.String(120), primary_key=True, unique=True) name = db.Column(db.String(120)) email = db.Column(db.String(120), unique=True) pwd = db.Column(db.String(120)) is_admin = db.Column(db.Boolean) def __init__(self, name=None, email=None, password=None, is_admin=False): self.user_id = member_token() self.name = name self.email = email self.pwd = password self.is_admin = is_admin def __repr__(self): return "email: {0},\n is_admin: {1}".format(self.email, self.is_admin) @property def is_active(self): return True @property def is_authenticated(self): return True @property def is_anonymous(self): return False def get_id(self): return str(self.name) def get_name(self): return self.name def get_books(self): book_obj_lst = UserBooks.get_books(user_id=self.user_id) bmap = {b.get("book_id"): b for b in book_obj_lst} books = Books.get_list(ids=[b.get("book_id") for b in book_obj_lst]) res = [] for b in books: b.__dict__["reading_state"] = bmap[ b.__dict__["book_id"]]["reading_state"] res.append(b.__dict__) return res @classmethod def verify_user(cls, email, password): u = cls.query.filter_by(email=email).first() if u is not None: if verify_password(password, u.pwd): return u return None @classmethod def register(cls, name, email, is_admin, password=None): try: existing = cls.query.filter_by(email=email).first() if existing: raise EmailExistsError if password is None or len(password) < 8: raise PasswordTooShortError hashed_password = salt_and_hash(password) u = User(name=name, email=email, password=hashed_password, is_admin=is_admin) db.session.add(u) db.session.commit() return cls.query.filter_by(email=email).first() except Exception as e: raise e @classmethod def update_password(cls, email, password=None): try: if password is None: return Exception u = cls.query.filter_by(email=email).first() hashed_password = salt_and_hash(password) u.pwd = hashed_password db.session.add(u) db.session.commit() return cls.query.filter_by(email=email).first() except Exception as e: print(e) return None @classmethod def update(cls, user_email, **kwargs): u = cls.query.filter_by(email=user_email).first() if not u: raise UserDoesNotExistError if kwargs.get("email"): print("Updating email: ", kwargs.get("email")) u.email = kwargs.get("email") if kwargs.get("name"): print("Updating name: ", kwargs.get("name")) u.name = kwargs.get("name") if kwargs.get("password"): print("Updating password: "******"password")) u.pwd = salt_and_hash(kwargs.get("password")) if kwargs.get("is_admin"): print("Updating is_admin: ", kwargs.get("is_admin")) u.is_admin = kwargs.get("is_admin") db.session.add(u) db.session.commit() return True @classmethod def update_password(cls, email, password): u = cls.query.filter_by(email=email).first() u.pwd = salt_and_hash(password) db.session.add(u) db.session.commit() return True @classmethod def update_email(cls, old_email, email): u = cls.query.filter_by(email=old_email).first() email_is_taken = cls.query.filter_by(email=email).first() if email_is_taken: raise EmailExistsError u.email = email db.session.add(u) db.session.commit() return True @classmethod def update_name(cls, email, name): u = cls.query.filter_by(email=email).first() u.name = name db.session.add(u) db.session.commit() return True @classmethod def delete(cls, email): if email is not None: u = cls.query.filter_by(email=email).first() db.session.delete(u) db.session.commit() return True return False