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))