class FilesModel(BaseModel, Base): """附件表""" __tablename__ = 'files' file_md5 = Column(CHAR(32), primary_key=True, autoincrement=False, doc='文件md5') form_code = Column(CHAR(32), nullable=False, index=True, doc='工单唯一编码') user_id = Column(Integer, nullable=False, doc='用户id') file_type = Column(VARCHAR(10), doc='文件类型(后缀)') file_name = Column(VARCHAR(255), doc='原始文件名') file_path = Column(VARCHAR(255), nullable=False, doc='文件储存路径') upload_time = Column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'), doc='储存时间') @classmethod def insert(cls, file_md5: str, form_code: str, user_id: int, file_type: str, file_name: str, file_path: str): '''插入文件储信息 :param cls: :param file_md5: 文件md5 :param form_code: 工单唯一编码 :param user_id: 用户id :param file_type: 文件类型(后缀) :param file_name: 原始文件名 :param file_path: 文件储存路径 ''' return cls._insert( cls(file_md5=file_md5, form_code=form_code, user_id=user_id, file_type=file_type, file_name=file_name, file_path=file_path))
class Litery(BaseModel): __tablename__ = 'litery' litera_id = Column(BigInteger, primary_key=True) wyraz_id = Column(BigInteger, ForeignKey('wyrazy.wyraz_id')) numer_litery = Column(BigInteger) sciezka = Column(VARCHAR(255)) predykcja = Column(CHAR(1)) predykcja_slownik = Column(CHAR(1)) litery = relationship('Wyrazy', foreign_keys=wyraz_id)
class FormsModel(BaseModel, Base): """工单表""" __tablename__ = 'forms' form_id = Column(BigInteger, primary_key=True, autoincrement=True, doc='索引id') form_number = Column(VARCHAR(255), doc='介绍信编号') form_code = Column(CHAR(32), doc='工单唯一编码ID') user_id = Column(Integer, doc='用户id') group_id = Column(Integer, doc='区县ID') form_urgent = Column(Integer, doc='紧急1或普通0') form_state = Column(CHAR(3), doc='工单状态(审批中)') form_detail = Column(TEXT, doc='工单说明') form_phones = Column(TEXT, doc='查询的手机号码') form_result = Column(TEXT, doc='处理结果') form_time = Column(DATETIME, doc='工单时间')
class Customer(Base): __tablename__ = 'customers' CustomerID = Column(CHAR(6), primary_key=True) CompanyName = Column(String(40), nullable=False) ContactName = Column(String(30)) ContactTitle = Column(String(30)) Address = Column(String(60)) City = Column(String(15)) Region = Column(String(15)) PostalCode = Column(String(10)) Country = Column(String(15)) Phone = Column(String(24)) Fax = Column(String(24))
class User(Base): __tablename__ = 'user' id = Column('id', SmallInteger, primary_key=True, autoincrement=True) login_name = Column(VARCHAR(16), unique=True, nullable=False) first_name = Column(String(20), nullable=True) last_name = Column(VARCHAR(20)) gender = Column(Enum(GenderEnum), nullable=False, default='M') age = Column(INTEGER) phone = Column(CHAR(11)) password = Column(VARCHAR(512)) def __repr__(self) -> str: # return super().__repr__() return "<{}: id={} login_name={} first_name={} last_name={} gender={} age={} phone={} password={}>".format( self.__class__.name, self.id, self.login_name, self.first_name, self.last_name, self.gender, self.age, self.phone, self.password)
class User(Base): __tablename__ = 'user' __table_args__ = { 'mysql_charset': 'utf8' } id = Column(BIGINT, primary_key=True) name = Column(VARCHAR(80), unique=True) pwd = Column(VARCHAR(100)) sex = Column(VARCHAR(2)) age = Column(INTEGER) photo_url = Column(VARCHAR(300)) profession = Column(VARCHAR(100)) residence = Column(VARCHAR(100)) education = Column(VARCHAR(100)) email = Column(VARCHAR(100), unique=True) signature = Column(VARCHAR(128)) reg_state = Column(CHAR(1), default='0') login_state = Column(CHAR(1)) acti_code = Column(VARCHAR(50), nullable=False) # active code focus_count = Column(INTEGER, default=0) dev_id = Column(VARCHAR(200)) rp_weight = Column(INTEGER, default=100) reg_time = Column(DATETIME, default=func.now()) comment = Column(VARCHAR(200), default='') reserved = Column(VARCHAR(200)) topics = relationship('Topic', backref='creator', lazy='dynamic') collectins = relationship('Collection', backref='collector', lazy='dynamic') userfoucs = relationship('Userfocus', backref='follower', lazy='dynamic') favoriteboxs = relationship('FavoriteBox', backref='creator', lazy='dynamic') favorites = relationship('Favorite', backref='creator', lazy='dynamic') useraction = relationship('UserAction', backref='creator', lazy='dynamic') def hash_password(self, password): self.pwd = pwd_context.encrypt(password) def verify_password(self, password): return pwd_context.verify(password, self.pwd) def generate_auth_token(self, expiration=600): s = Serializer(app.secret_key, expires_in=expiration) result = s.dumps({'id': self.id}) return result @staticmethod def verify_auth_token(token): s = Serializer(app.secret_key) try: data = s.loads(token) except SignatureExpired: return None except BadSignature: return None db_session = Session() user = db_session.query(User).filter(User.id == data['id']) db_session.close() return user
def load_dialect_impl(self, dialect): if dialect.name == "postgresql": # pragma: no cover return dialect.type_descriptor(UUID()) else: return dialect.type_descriptor(CHAR(32))
@pytest.mark.parametrize( "type_str, sql_type", datatype._type_map.items(), ids=datatype._type_map.keys(), ) def test_parse_simple_type(type_str: str, sql_type: TypeEngine, assert_sqltype): actual_type = datatype.parse_sqltype(type_str) if not isinstance(actual_type, type): actual_type = type(actual_type) assert_sqltype(actual_type, sql_type) parse_cases_testcases = { "char(10)": CHAR(10), "Char(10)": CHAR(10), "char": CHAR(), "cHaR": CHAR(), "VARCHAR(10)": VARCHAR(10), "varCHAR(10)": VARCHAR(10), "VARchar(10)": VARCHAR(10), "VARCHAR": VARCHAR(), "VaRchAr": VARCHAR(), } @pytest.mark.parametrize( "type_str, sql_type", parse_cases_testcases.items(), ids=parse_cases_testcases.keys(),
class UsersModel(BaseModel, Base): """用户表""" __tablename__ = 'users' user_id = Column(BigInteger, primary_key=True, autoincrement=True, doc='索引id') group_id = Column(Integer, nullable=False, doc='区县ID') user_state = Column(Integer, server_default=text('1'), doc='帐号是否可用(1可用,0加锁)') user_mark = Column(TEXT, doc='备注') user_role = Column(VARCHAR(15), server_default=text('"普通用户"'), doc='用户角色') user_name = Column(VARCHAR(255), unique=True, doc='用户名') user_nick = Column(VARCHAR(255), doc='昵称') user_upwd = Column(CHAR(32), doc='3次md5加密后的密码') user_dpwd = Column(CHAR(32), doc='3次md5加密后的初始密码') user_ips = Column(VARCHAR(15), doc='用户运行的IP地址头比如192.168.1.') valid_time = Column(DATETIME, nullable=False, server_default=text('CURRENT_TIMESTAMP'), doc='密码有效初始时间') @classmethod def login(cls, user_name, user_upwd, user_ip): """ #用户登录 :param cls: :param user_name: 帐号 :param user_upwd: 密码 :param user_ip: ip """ if not SESSION: gen_log.error('session is null') return None err_msg = '' session = SESSION() try: user = session.query(cls).filter(user_name=user_name).first() session.close() if not user: return 0, '登录失败:用户不存在', None if user.user_dpwd == user_upwd: return 0, '不能使用默认密码登录,请先修改密码', None if user.user_upwd != user_upwd: return 0, '密码不正确', None if not user.user_state: return 0, '该帐号已被加锁:{0}'.format(user.user_mark), None if (datetime.now() - user.valid_time).days > 30: return 0, '密码已到期,请修改密码', None # 校验用户ip是否在运行的ip段内 ip_valid = False for ip in user.user_ips.split(';'): if user_ip.startswith(ip): ip_valid = True break if not ip_valid: return 0, '该帐号不允许在该设备上登录,请联系管理员', None return 200, '登录成功', user.to_dict() except Exception as e: err_msg = str(e) gen_log.error(str(e)) session.close() return 0, '登录失败: {0}'.format(err_msg), None @classmethod def register(cls, group_id, user_role, user_name, user_nick, user_upwd, user_dpwd, user_ips): """ #新增用户 :param cls: :param group_id: 分组ID :param user_role: 角色 :param user_name: 帐号 :param user_nick: 昵称 :param user_upwd: 密码 :param user_dpwd: 默认密码 :param user_ips: 允许IP范围 """ return cls._insert( cls(group_id=group_id, user_role=user_role, user_name=user_name, user_nick=user_nick, user_upwd=user_upwd, user_dpwd=user_dpwd, user_ips=user_ips)) @classmethod def password(cls, user_name, user_upwd, user_npwd, reset=0): """ #修改密码 :param user_name: 帐号 :param user_upwd: 原密码 :param user_npwd: 新密码 :param reset: 是否重置密码 """ if not SESSION: return 0, '数据库连接错误,请联系后台管理员', None err_msg = '' session = SESSION() try: if reset: # 重置密码 ret = session.query(cls).filter(user_name=user_name).update( { cls.user_upwd: user_npwd, cls.user_dpwd: user_npwd }, synchronize_session=False) else: # 修改密码 ret = session.query(cls).filter( and_( cls.user_name == user_name, or_(cls.user_upwd == user_upwd, cls.user_dpwd == user_upwd))).update( { cls.user_upwd: user_npwd, cls.user_dpwd: user_npwd }, synchronize_session=False) session.commit() session.close() gen_log.debug('ret: {0}'.format(ret)) return 200, '密码修改成功,请牢记,如果忘记请联系系统管理员', ret except Exception as e: err_msg = str(e) gen_log.error(str(e)) session.close() return 0, '密码修改失败: {0}'.format(err_msg), None @classmethod def lock(cls, user_name, user_state): """ #加解锁 :param cls: :param user_name: 帐号 :param user_state: 1-解锁,0-加锁 """ if not SESSION: return '数据库连接错误,请联系后台管理员', None err_msg = '' session = SESSION() try: # 更新状态 ret = session.query(cls).filter(user_name=user_name).update( {cls.user_state: user_state}, synchronize_session=False) session.commit() session.close() gen_log.debug('ret: {0}'.format(ret)) return '加解锁成功', ret except Exception as e: err_msg = str(e) gen_log.error(str(e)) session.close() return '加解锁失败: {0}'.format(err_msg), None
def load_dialect_impl(self, dialect): if dialect.name == 'postgresql': return dialect.type_descriptor(UUID()) else: return dialect.type_descriptor(CHAR(32))