def unlock(uuid):
engine, cursor = db.engine.get_engine()
sql = "update user set status='active' where uuid='%s'" % uuid
cursor.execute(sql)
engine.commit()
engine.close()
return
def drop_code(uuid):
engine, cursor = db.engine.get_engine()
sql = "delete from verify_code where uuid='%s'" % uuid
cursor.execute(sql)
engine.commit()
engine.close()
return
def drop_subway_line(uuid):
engine, cursor = db.engine.get_engine()
sql = "delete from subway_line where uuid=\"%s\"" % uuid
cursor.execute(sql)
engine.commit()
engine.close()
return
def update(uuid, name):
engine, cursor = db.engine.get_engine()
sql = "update station set name='%s' where uuid='%s'" % (name, uuid)
cursor.execute(sql)
engine.commit()
engine.close()
return
def is_user_exist(email):
engine, cursor = db.engine.get_engine()
sql = "select count(1) from user where email='%s'" % email
cursor.execute(sql)
data = cursor.fetchone()
engine.close()
return data[0]
def line_exist(uuid):
engine, cursor = db.engine.get_engine()
sql = "select count(1) from subway_line where uuid='%s'" % uuid
cursor.execute(sql)
data = cursor.fetchone()
engine.close()
return data[0]
def add_code(uuid, code, operate, modify_time):
engine, cursor = db.engine.get_engine()
sql = "insert into verify_code values(%s, %s, %s, %s)"
val = (uuid, code, operate, modify_time)
cursor.execute(sql, val)
engine.commit()
engine.close()
return
def update_token(email, token):
engine, cursor = db.engine.get_engine()
sql = "update user set token='%s' where email='%s'" % \
(token, email)
cursor.execute(sql)
engine.commit()
engine.close()
return
def add_station(**kwargs):
engine, cursor = db.engine.get_engine()
sql = "insert into station(uuid, name) values(%s, %s)"
val = (kwargs["uuid"], kwargs["name"])
cursor.execute(sql, val)
engine.commit()
engine.close()
return
def update_subway_line(uuid, name):
engine, cursor = db.engine.get_engine()
sql = "update subway_line set name=\"%s\" where uuid=\"%s\"" % \
(name, uuid)
cursor.execute(sql)
engine.commit()
engine.close()
return
def add_subway_line(uuid, name):
engine, cursor = db.engine.get_engine()
sql = "insert into subway_line(uuid, name) values(%s, %s)"
val = (uuid, name)
cursor.execute(sql, val)
engine.commit()
engine.close()
return
def update_photo(uuid, url):
engine, cursor = db.engine.get_engine()
sql = "update user set image='%s' where uuid='%s'" % \
(url, uuid)
cursor.execute(sql)
engine.commit()
engine.close()
return
def get_code_detail(uuid):
engine, cursor = db.engine.get_engine()
sql = "select * from verify_code where uuid='%s'" % uuid
cursor.execute(sql)
data = cursor.fetchall()
if not data:
return data
engine.close()
return data[0]
def add_relation(**kwargs):
engine, cursor = db.engine.get_engine()
sql = "insert into entity_relation(uuid, parent, child, relation) " \
"values(%s, %s, %s, %s)"
val = (kwargs["uuid"], kwargs["parent"], kwargs["child"],
kwargs["relation"])
cursor.execute(sql, val)
engine.commit()
engine.close()
return
def get_user_detail(email):
engine, cursor = db.engine.get_engine()
sql = "select * from user where email='%s'" % email
cursor.execute(sql)
data = cursor.fetchall()
if not data:
logger.error("can not find user %s" % email)
raise DBError("can not find user %s" % email, 404)
user_details = data[0]
engine.close()
return user_details
def get_user_detail_by_uuid(uuid):
engine, cursor = db.engine.get_engine()
sql = "select * from user where uuid='%s'" % uuid
cursor.execute(sql)
data = cursor.fetchall()
if not data:
logger.error("can not find user %s" % uuid)
raise DBError("can not find user %s" % uuid, 404)
detail = data[0]
engine.close()
return detail
def update(**params):
engine, cursor = db.engine.get_engine()
sql = "update user set email='%s', username='******', password='******' " \
"where uuid='%s'" % (params["email"],
params["username"],
params["password"],
params["uuid"]
)
cursor.execute(sql)
engine.commit()
engine.close()
return
def detail(uuid):
engine, cursor = db.engine.get_engine()
sql = "select * from station where uuid='%s'" % uuid
cursor.execute(sql)
data = cursor.fetchall()[0]
station_detail = {
"uuid": data[0],
"name": data[1],
"next_stop": data[2],
"belong": data[3]
}
engine.close()
return station_detail
def subway_list():
engine, cursor = db.engine.get_engine()
sql = "select * from subway_line"
cursor.execute(sql)
db_data = cursor.fetchall()
data = []
for pre_db_data in db_data:
pre_data = {}
pre_data["uuid"] = pre_db_data[0]
pre_data["name"] = pre_db_data[1]
data.append(pre_data)
engine.close()
return data
def init_line():
engine, cursor = db.engine.get_engine()
sql = """
create table if not exists subway_line(
uuid char(27) not null,
name varchar(10) not null,
primary key(name)
) charset utf8
"""
cursor.execute(sql)
engine.close()
logger.info("setup subway line finished.")
return
def exist(value, types="name"):
engine, cursor = db.engine.get_engine()
sql = "select count(1) from station where %s='%s'" % (types, value)
if types == "name":
cursor.execute(sql)
data = cursor.fetchone()
elif types == "uuid":
cursor.execute(sql)
data = cursor.fetchone()
else:
raise TypeError("Unknown types %s." % types)
engine.close()
return data[0]
def init_station():
engine, cursor = db.engine.get_engine()
sql = """
create table if not exists station(
uuid char(27) not null,
name varchar(30),
next_stop text,
primary key(name)
) charset utf8
"""
cursor.execute(sql)
engine.close()
logger.info("Setup station finished.")
return
def init_verify_code():
engine, cursor = db.engine.get_engine()
sql = """
create table if not exists verify_code(
uuid char(27),
code varchar(10) not null,
operate varchar(20) not null,
modify datetime not null,
primary key(uuid)
) charset utf8
"""
cursor.execute(sql)
engine.close()
logger.info("setup verify code finished.")
return
def init_relation():
engine, cursor = db.engine.get_engine()
sql = """
create table if not exists entity_relation(
id bigint auto_increment primary key,
uuid char(27) not null,
parent char(27),
child char(27),
relation varchar(30) not null
) charset utf8
"""
cursor.execute(sql)
engine.close()
logger.info("Set up relation finished.")
return
def search_relation(uuid):
engine, cursor = db.engine.get_engine()
sql = "select * from entity_relation where uuid='%s'" % uuid
cursor.execute(sql)
data = cursor.fetchall()
relation_list = []
for relation_data in data:
each_relation = {}
each_relation["id"] = relation_data[0]
each_relation["uuid"] = relation_data[1]
each_relation["parent"] = relation_data[2]
each_relation["child"] = relation_data[3]
each_relation["relation"] = relation_data[4]
relation_list.append(each_relation)
engine.close()
return relation_list
def get_list():
engine, cursor = db.engine.get_engine()
sql = "select * from station"
cursor.execute(sql)
data = cursor.fetchall()
station_list = []
for station in data:
each_detail = {}
each_detail["uuid"] = station[0]
each_detail["name"] = station[1]
if station[3] is None:
each_detail["belong"] = []
else:
each_detail["belong"] = station[3].split(",")
station_list.append(each_detail)
engine.close()
return station_list
def add_user(**kwargs):
engine, cursor = db.engine.get_engine()
username = kwargs["username"]
password = kwargs["password"]
create_time = kwargs["register_time"]
token = kwargs["token"]
uuid = kwargs["uuid"]
email = kwargs["email"]
user_type = kwargs["user_type"]
image = "/root/image/localhost/default.jpeg"
sql = "insert into user values(%s, %s, %s, %s, %s, %s, %s, default, %s)"
val = (uuid, email, username, password, token, user_type, create_time,
image)
cursor.execute(sql, val)
engine.commit()
engine.close()
return
def init_user():
engine, cursor = db.engine.get_engine()
sql = """
create table if not exists user(
uuid char(27) not null,
email varchar(30),
username varchar(24) default "subway user",
password varchar(18) not null,
token char(10) not null,
user_type enum("admin", "user") not null default "user",
create_time datetime not null,
status enum("active", "down", "lock") not null default "active",
image varchar(100),
primary key(email)
) charset utf8
"""
cursor.execute(sql)
logger.info("Setup user finished.")
# add admin user
conf_path = util.get_root_path() + "/conf/platform.conf"
deploy_conf = ConfigParser()
deploy_conf.read([conf_path])
uuid = util.generate_uuid()
admin_user = deploy_conf.get("deploy", "admin_user")
admin_pwd = deploy_conf.get("deploy", "admin_pwd")
email = deploy_conf.get("deploy", "admin_email")
image = "/root/image/localhost/default.jpeg"
try:
get_user_detail(email)
except DBError:
now = util.get_time_string_format()
token = util.general_token()
user_type = "admin"
sql = "insert into user " \
"values(%s, %s, %s, %s, %s, %s, %s, default, %s)"
val = (uuid, email, admin_user, admin_pwd, token, user_type, now,
image)
cursor.execute(sql, val)
logger.info("Init default admin user success.")
engine.commit()
engine.close()
return
def get_all_user_detail():
engine, cursor = db.engine.get_engine()
sql = "select * from user"
cursor.execute(sql)
db_data = cursor.fetchall()
data = []
for pre_db_data in db_data:
pre_data = {}
pre_data["uuid"] = pre_db_data[0]
pre_data["email"] = pre_db_data[1]
pre_data["username"] = pre_db_data[2]
pre_data["password"] = pre_db_data[3]
pre_data["token"] = pre_db_data[4]
pre_data["type"] = pre_db_data[5]
pre_data["create"] = \
util.get_time_string_format(time_data=pre_db_data[6])
pre_data["status"] = pre_db_data[7]
data.append(pre_data)
engine.close()
return data
def delete(uuid):
engine, cursor = db.engine.get_engine()
sql = "delete from station where uuid='%s'" % uuid
cursor.execute(sql)
engine.close()
return
