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