class User(UserMixin,Base): userid=Column(Integer,primary_key=True,autoincrement=True)#主键 username=Column(String(20)) userPhone=Column(String(20),index=True) password_hash=Column(String(128)) wxopenId=Column(String(100),index=True,nullable=True) roleId=Column(Integer,db.ForeignKey('roles.id')) role=db.relationship('Roles',back_populates='users') pubCount=Column(Integer,default=0) signInCount=Column(Integer,default=0) pubSignIns=db.relationship('SignInOrder',back_populates='pubPerson') myPubSuggestions=db.relationship('Suggestions',back_populates='pubPerson') myReplySuggestions=db.relationship('Reply',back_populates='replyPerson') posts=db.relationship('Post',back_populates='pubPerson') admin=Column(Boolean,default=False) canPub = db.Column(db.Boolean, default=False, nullable=False)#可以发布签到 canEdit = db.Column(db.Boolean, default=False, nullable=False)#可以发布文章 @property def password(self): return self.password_hash # 装饰器装饰后,可以像调用属性一样调用password @password.setter def password(self, raw): self.password_hash = generate_password_hash(raw) # 密码加密 # 装饰器装饰后,可以像设置属性一样调用password def validate_password(self,password): return check_password_hash(self.password,password) def increase_pubCount(self): self.pubCount+=1 def increase_signInCount(self): self.signInCount+=1 def get_id(self): #覆盖父类的id try: return text_type(self.userid) except AttributeError: raise NotImplementedError('`get_id`error') def can(self,permission_name): #判断是否有权限 permission=Permission.query.filter_by(name=permission_name).first() return permission is not None and self.role is not None and \ permission in self.role.permissions
class Reply(Base): id = Column(Integer, autoincrement=True, primary_key=True) content = Column(String(300), nullable=False) suggestionId = Column(Integer, db.ForeignKey('suggestions.id')) suggestion = db.relationship('Suggestions') replyPersonId = Column(Integer, db.ForeignKey('user.userid')) replyPerson = db.relationship('User', back_populates='myReplySuggestions')
class Permission(Base): id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(30), unique=True) roles = db.relationship('Roles', secondary=roles_permissions, back_populates='permissions')
class Suggestions(Base): id = Column(Integer, autoincrement=True, primary_key=True) title = Column(String(50), nullable=False) content = Column(String(300), nullable=False) sugPubPersonId = Column(Integer, db.ForeignKey('user.userid')) pubPerson = db.relationship('User', back_populates='myPubSuggestions') sugStatus = Column(SmallInteger, default=1) reply = db.relationship('Reply', uselist=False) IGNORE = 0 NOREPLY = 1 HAVEREPLY = 2
class medication_deid(Mixin, db.Model): """medication_deid table """ __tablename__ = "medication_deid" medication_id = db.Column(db.INT, unique=True, primary_key=True) pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id")) generic_name = db.Column(db.VARCHAR) therapeutic_class = db.Column(db.VARCHAR) order_placed_dt = db.Column(db.DateTime) order_end_dt = db.Column(db.DateTime) usage_directions = db.Column(db.VARCHAR) order_class = db.Column(db.VARCHAR) strength = db.Column(db.VARCHAR) form = db.Column(db.VARCHAR) number_of_doses = db.Column(db.INT) dose_unit = db.Column(db.VARCHAR) frequency = db.Column(db.VARCHAR) medication_administration_deid = db.relationship( "medication_administration_deid", backref="medication_deid", lazy="dynamic") def __repr__(self): return "<medication_deid {!r}, pt_id {!r}".format( self.medication_id, self.pt_id) @staticmethod def get_pt_id_by_generic_name(mgn: list) -> list: """Get pt_id by medication_generic_name :param mgn <list<str>> list of medication_generic_name :returns <list<int>> list of pt_id """ # Initialise query qry = medication_deid.query.with_entities( medication_deid.pt_id).distinct() # do query qry = qry.filter(medication_deid.generic_name.in_(mgn)) return [v.pt_id for v in qry.all()] @staticmethod def get_pt_id_by_therapeutic_class(mtc: list) -> list: """Get pt_id by medication_generic_name :param mgn <list<str>> list of medication_generic_name :returns <list<int>> list of pt_id """ # Initialise query qry = medication_deid.query.with_entities( medication_deid.pt_id).distinct() # do query qry = qry.filter(medication_deid.therapeutic_class.in_(mtc)) return [v.pt_id for v in qry.all()]
class Post(Base): id = Column(Integer, primary_key=True, autoincrement=True) title = Column(String(60)) body = Column(Text) time = Column(DateTime, default=datetime.now) pubPersonId = Column(Integer, db.ForeignKey('user.userid')) pubPerson = db.relationship('User', back_populates='posts') status = Column(SmallInteger, default=1)
class Order(BaseModel): __tablename__ = "orders" id = db.Column(db.Unicode(), primary_key=True) user_id = db.Column( db.Unicode(), db.ForeignKey("users.id", ondelete="CASCADE"), ) stock_id = db.Column( db.Unicode(), db.ForeignKey("stocks.id", ondelete="CASCADE"), ) quantity = db.Column(db.Integer(), nullable=False) is_buy = db.Column(db.Boolean, default=False) stock = db.relationship("Stock", backref="orders") user = db.relationship("User", backref="orders") def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) @classmethod def create_buy_order(cls, **kwargs): user_stock_quantity = UserStockQuantity.get_or_create( user_id=kwargs.get('user_id'), stock_id=kwargs.get('stock_id')) user_stock_quantity.quantity += kwargs.get("quantity") instance = cls.create(**{**kwargs, "is_buy": True}) Stock.add_stock_quantity(kwargs.get('stock_id'), -kwargs.get("quantity")) user_stock_quantity.save() return instance @classmethod def create_sell_order(cls, **kwargs): user_stock_quantity = UserStockQuantity.get_or_create( user_id=kwargs.get('user_id'), stock_id=kwargs.get('stock_id')) user_stock_quantity.quantity -= kwargs.get("quantity") instance = cls.create(**{**kwargs, "is_buy": False}) Stock.add_stock_quantity(kwargs.get('stock_id'), kwargs.get("quantity")) user_stock_quantity.save() return instance
class Roles(Base): id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(30), unique=True) permissions = db.relationship('Permission', secondary=roles_permissions, back_populates='roles') #在对方那叫做roles users = db.relationship('User', back_populates='role') Administrator = 3 Moderator = 2 User = 1 @staticmethod def init_role(): roles_permissions_map = { 'User': ['LOOK', 'PUBSIGNIN'], 'Moderator': ['LOOK', 'PUBSIGNIN', 'UPLOAD', 'DELSELF'], 'Administrator': ['LOOK', 'PUBSIGNIN', 'UPLOAD', 'CREATEOTHER', 'DELOTHER'] } for role_name in roles_permissions_map: # print(role_name) # print(str(roles_permissions_map[role_name])) role = Roles.query.filter_by(name=role_name).first() # role = None if role is None: role = Roles() role.name = role_name db.session.add(role) role.permissions = [] for permission_name in roles_permissions_map[role_name]: permission = Permission.query.filter_by( name=permission_name).first() if permission is None: permission = Permission() permission.name = permission_name db.session.add(permission) # print(permission) role.permissions.append(permission) db.session.commit()
class SignInOrder(Base): id = Column(Integer, autoincrement=True, primary_key=True) pubPersonId = Column(Integer, db.ForeignKey('user.userid')) pubPerson = db.relationship('User', back_populates='pubSignIns') pubTime = Column(DateTime) endTime = Column(DateTime) # DateTime datetime.datetime 日期和时间 needtoSignIn = Column(Integer) haveSignIn = Column(Integer, default=0) signInStatus = Column(SmallInteger, default=1) qrcodeUrl = Column(String(100)) #本地的相对路径 signInPerson = db.relationship('SignInPeople', back_populates='signInOrder') #一对多的一侧 def generate_pubTime(self): return datetime.now() # 可能有误类型错误,这里的应该有闭包不影响
class SignInPeople(Base): id = Column(Integer, autoincrement=True, primary_key=True) signInOrderId = Column(Integer, db.ForeignKey('sign_in_order.id')) signInOrder = db.relationship('SignInOrder', back_populates='signInPerson') userId = Column(Integer, index=True, nullable=False) username = Column(String(20)) userPhone = Column(String(20), index=True) signInTime = Column(Time) # Time datetime.time 时间 def generate_signInTime(self): return datetime.time(datetime.now()) #17:51:30.854656修改
class exam_deid(Mixin, db.Model): """exam_deid table """ __bind_key__ = "image_exams_db" __tablename__ = "exam_deid" exam_id = db.Column(db.INT, unique=True, primary_key=True) pt_id = db.Column(db.INT, db.ForeignKey("pt_deid.pt_id")) exam_date = db.Column(db.DateTime) image_deid = db.relationship("image_deid", backref="exam_deid", lazy="dynamic") def __repr__(self): return "<exam_deid {!r}, pt_id {!r}>".format(self.exam_id, self.pt_id) @staticmethod def get_distinct_pt_ids(): """Get all pt_ids available Using this table to make sure all the patients returned indeed have relevant data entries """ qry = exam_deid.query.with_entities(exam_deid.pt_id).distinct() return [v.pt_id for v in qry.all()]
class pt_deid(Mixin, db.Model): """pt_deid table """ __tablename__ = "pt_deid" pt_id = db.Column(db.INT, unique=True, primary_key=True) dob = db.Column(db.DateTime, nullable=False) over_90 = db.Column(db.SMALLINT) race_1 = db.Column(db.VARCHAR) diagnosis_deid = db.relationship( "diagnosis_deid", backref="pt_deid", lazy="dynamic" ) exam_deid = db.relationship("exam_deid", backref="pt_deid", lazy="dynamic") image_deid = db.relationship("image_deid", backref="pt_deid", lazy="dynamic") lab_value_deid = db.relationship( "lab_value_deid", backref="pt_deid", lazy="dynamic" ) medication_administration_deid = db.relationship( "medication_administration_deid", backref="pt_deid", lazy="dynamic" ) medication_deid = db.relationship( "medication_deid", backref="pt_deid", lazy="dynamic" ) smart_data_deid = db.relationship( "smart_data_deid", backref="pt_deid", lazy="dynamic" ) visit_movement_deid = db.relationship( "visit_movement_deid", backref="pt_deid", lazy="dynamic" ) def __repr__(self): return "<pt_deid {!r}>".format(self.pt_id) @staticmethod def get_all_pt_ids(): """Get all pt_id available """ qry = pt_deid.query.with_entities(pt_deid.pt_id).distinct() return [v.pt_id for v in qry.all()] @staticmethod def get_pt_id_by_age_or_race_1( race_1: list = None, younger_than: datetime = None, older_than: datetime = None, ) -> list: """Filter pt_id by age and/or race_1 :param race_1 <list<str>> :param younger_than <DateTime> earliest DoB :param older_than <DateTime> latest DoB :returns <list<int>> pt_id """ qry = pt_deid.query.with_entities(pt_deid.pt_id).distinct() if younger_than != None: qry = qry.filter(pt_deid.dob > younger_than) if older_than != None: qry = qry.filter(pt_deid.dob < older_than) if race_1: qry = qry.filter(pt_deid.race_1.in_(race_1)) return [v.pt_id for v in qry.all()]