def initUser(datafn, tablename, cur, clear, logger):
"""
读取data文件并初始化数据库中的对应表
变量定义见模块注释
"""
logger.info("Start init user.")
lines = _readlines(datafn, logger)
if lines is None or len(lines) == 0:
logger.error("End init user.")
return
sqlstr1 = "insert into `user`(`username`,`passwdmd5`,email,role)" \
" values('{0}','{1}','{2}','{3}');"
chksql = "select id from `user` where `username`='{0}';"
warnstr = "User account '{0}' defined in user.txt exists!"
for line in lines:
if len(line) != 4:
continue
# 判断管理员账号是否已存在，选定账号
sqlstr = ''
musern = funcs.b64encode(line[0])
chksqlstr = chksql.format(musern)
# print(chksqlstr)
cur.execute(chksqlstr.encode("utf-8", "ignore"))
munids = cur.fetchall()
if len(munids) > 0:
logger.warning(warnstr.format(line[0]))
continue
else:
sqlstr = sqlstr1
# get md5 of passwd
tpwmd5 = funcs.get_md5(line[1])
# insert
sqlstr3 = sqlstr.format(
funcs.b64encode(line[0]),
tpwmd5,
funcs.b64encode(line[2]),
funcs.formatSQL(line[3]),
)
# print(sqlstr2)
cur.execute(sqlstr3.encode("utf-8", "ignore"))
# get id
cur.execute(
('select id from `user` where `username`=\'' +
funcs.b64encode(line[0]) + '\';').encode("utf-8", "ignore"))
uid = cur.fetchall()[0][0]
# insert info
cur.execute(('insert into ' + funcs.formatSQL(line[3]) +
'(id,`name`) values(' + str(uid) + ',\'' +
funcs.b64encode('name' + str(uid)) + '\');').encode(
"utf-8", "ignore"))
logger.info("End init user.")
def initAdmin(datafn, tablename, cur, clear, logger):
"""
读取data文件并初始化数据库中的对应表
变量定义见模块注释
"""
logger.info("Start init admin.")
lines = _readlines(datafn, logger)
if lines is None or len(lines) == 0:
logger.error("End init admin.")
return
sqlstr1 = "insert into `user`(id,`username`,`passwdmd5`,email,role)" \
" values({4},'{0}','{1}','{2}','{3}');"
sqlstr2 = "update `user` set " \
" `username`='{0}'," \
" passwdmd5='{1}'," \
" email='{2}'," \
" role='{3}'" \
" where `username`='{0}' and role='admin';"
chksql = "select id from `user` where `username`='{0}';"
warnstr = "Admin account '{0}' defined in admin.txt exists!"
for line in lines:
if len(line) != 3:
continue
# 判断管理员账号是否已存在，选定账号
sqlstr = ''
musern = funcs.b64encode(line[0])
chksqlstr = chksql.format(musern)
# print(chksqlstr)
cur.execute(chksqlstr.encode("utf-8", "ignore"))
munids = cur.fetchall()
if len(munids) > 0:
logger.warning(warnstr.format(line[0]))
sqlstr = sqlstr2
else:
sqlstr = sqlstr1
# get md5 of passwd
tpwmd5 = funcs.get_md5(line[1])
# insert
sqlstr3 = sqlstr.format(
funcs.b64encode(line[0]),
tpwmd5,
funcs.b64encode(line[2]),
'admin',
funcs.get_random_int64()
)
# print(sqlstr2)
cur.execute(sqlstr3.encode("utf-8", "ignore"))
logger.info("End init admin.")
def get_id_by_username(un):
"""
通过用户名得到id和role
:param un: username
:return : unity formated dict,data:{'id':id,'role':role,'email':email}
"""
try:
sqlstr = "select id,email,`role` from user where `username`='{un}';".format(
un=funcs.b64encode(un))
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
cur.execute(sqlstr.encode("utf-8", "ignore"))
pwres = cur.fetchall()
cur.close()
conn.close()
if pwres is None or len(pwres) == 0:
return ee.NORMAL()
resu = ee.NORMAL()
resu['count'] = len(pwres)
resu['data'].append({
'id': pwres[0][0],
'email': funcs.b64decode(pwres[0][1]),
'role': pwres[0][2]
})
return resu
except:
gv.logger.error(traceback.format_exc())
res = ee.DBERR()
return res
def _initStatus(datafn, tablename, cur, clear, logger, f1, f2):
"""
读取data文件并初始化数据库中的对应表
变量定义见模块注释
:param f1: 字段1名稱
:param f2: 字段2名稱
"""
logger.info("Start init " + tablename + ".")
lines = _readlines(datafn, logger)
if lines is None or len(lines) == 0:
logger.error("End init " + tablename + ".")
return
# clear
if clear:
sqlstr = "delete from `{tbn}`;".format(tbn=tablename)
cur.execute(sqlstr.encode("utf-8", "ignore"))
sqlstr1 = "insert into `{tbn}`(`id`,`{f1}`,`{f2}`)" \
" values({0},'{1}','{2}');"
for line in lines:
if len(line) != 3:
continue
sqlstr = sqlstr1.format(line[0],
funcs.formatSQL(line[1]),
funcs.b64encode(line[2]),
tbn=tablename,
f1=f1,
f2=f2)
cur.execute(sqlstr.encode("utf-8", "ignore"))
def delete_user(un):
'''
delete user in db
:param un: username
'''
try:
sqlstr = "delete from `user` where `username`='{0}';".format(
funcs.b64encode(un))
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
cur.execute(sqlstr.encode("utf-8", "ignore"))
conn.commit()
cur.close()
conn.close()
return None
except:
gv.logger.error(traceback.format_exc())
return None
def check_username(un):
"""
检查用户名是否存在
:param un: username
:return : Ture=exists or False=not
"""
try:
sqlstr = "select id from user where `username`='{un}';".format(
un=funcs.b64encode(un))
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
cur.execute(sqlstr.encode("utf-8", "ignore"))
pwres = cur.fetchall()
cur.close()
conn.close()
if pwres is None or len(pwres) == 0:
return False
return True
except:
gv.logger.error(traceback.format_exc())
res = ee.DBERR()
return res
def get_cid_by_alias(alias):
'''
get cid from db
'''
try:
sqlstr = "select id from child where `alias`='{un}';".format(
un=funcs.b64encode(alias))
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
cur.execute(sqlstr.encode("utf-8", "ignore"))
pwres = cur.fetchall()
cur.close()
conn.close()
if pwres is None or len(pwres) == 0:
return ee.NORMAL()
resu = ee.NORMAL()
resu['count'] = len(pwres)
resu['data'].append({'id': str(pwres[0][0])})
return resu
except:
gv.logger.error(traceback.format_exc())
res = ee.DBERR()
return res
def insert_staff():
# field define list
uid = funcs.get_random_int64()
fs_p = [{
'fn': 'id',
'type': 'int',
'val': uid
}, {
'fn': 'name',
'type': 'b64str',
'val': request.form.get('name')
}, {
'fn': 'name_chs',
'type': 'b64str',
'val': request.form.get('name_chs')
}, {
'fn': 'gender',
'type': 'str',
'val': request.form.get('gender')
}, {
'fn': 'group_id',
'type': 'int',
'val': request.form.get('group_id')
}]
fs_u = [{
'fn': 'id',
'type': 'int',
'val': uid
}, {
'fn': 'username',
'type': 'b64str',
'val': request.form.get('username')
}, {
'fn': 'passwdmd5',
'type': 'str',
'val': request.form.get('passwd_md5')
}, {
'fn': 'email',
'type': 'b64str',
'val': request.form.get('email')
}, {
'fn': 'role',
'type': 'str',
'val': 'staff'
}]
# check params
if fs_u[1]['val'] is None or len(fs_u[1]['val']) < 1:
return {
'code': 40,
'msg': 'username is needed',
'count': 0,
'data': []
}
if fs_u[2]['val'] is None or len(fs_u[2]['val']) < 1:
return {
'code': 40,
'msg': 'passwd_md5 is needed',
'count': 0,
'data': []
}
if check_username(fs_u[1]['val']):
return {
'code': 50,
'msg': 'username already exists',
'count': 0,
'data': []
}
# build sql string
kstr_u = funcs.build_field_str(fs_u)
vstr_u = funcs.build_values_str(fs_u)
if vstr_u is None:
return {'code': 40, 'msg': 'parameters error', 'count': 0, 'data': []}
kstr_p = funcs.build_field_str(fs_p)
vstr_p = funcs.build_values_str(fs_p)
if vstr_p is None:
return {'code': 40, 'msg': 'parameters error', 'count': 0, 'data': []}
tbn_p = 'staff'
tbn_u = 'user'
sqlstr = "insert into `{tbn}`({0}) select {1} from dual where not exists (select 1 from `{tbn}` where `username`='{2}');"
sqlstr_p = "insert into `{tbn}`({0}) select {1} from dual where not exists (select 1 from `{tbn}` where `id`='{2}');"
try:
conn = gv.dbpool.connection()
cur = conn.cursor()
cur.execute("SET NAMES UTF8mb4;")
sqlstr1 = sqlstr.format(kstr_u,
vstr_u,
funcs.b64encode(fs_u[1]['val']),
tbn=tbn_u)
cur.execute(sqlstr1.encode("utf-8", "ignore"))
sqlstr1 = sqlstr_p.format(kstr_p, vstr_p, str(uid), tbn=tbn_p)
cur.execute(sqlstr1.encode("utf-8", "ignore"))
conn.commit()
cur.close()
conn.close()
except:
gv.logger.error(traceback.format_exc())
res = {'code': 10, 'msg': 'database error', 'count': 0, 'data': []}
return res
return {'code': 0, 'msg': '', 'count': 0, 'data': []}