def get_by_category(category_id): cursor = mysql.get_db().cursor() cursor.execute( 'select id, content_type, text_content, picture_id, video_id from case_content where ' 'id in (select content_id from case_category_content where category_id=%s)', (category_id, )) return cursor.fetchall()
def create_page_info(role_id, page_title, contents): if not RoleplayInfoHelper.get_by_id(role_id): return False, 'invalid role_id' db = mysql.get_db() cursor = db.cursor() # get page size cursor.execute( 'select count(*) from role_page where role_id=%s', (role_id,) ) page_size = cursor.fetchone()[0] print page_size # insert role_page cursor.execute( "insert into role_page (role_id, title, pagination) " "values (%s, %s, %s)", (role_id, page_title, int(page_size) + 1) ) RoleplayInfoHelper.modify(role_id, [('page_size', int(page_size) + 1)]) db.commit() page_id = cursor.lastrowid for content in contents: RoleplayContentHelper.create_content(page_id, content) return True, page_id
def create_worker(worker_name, worker_job_id, worker_depart_ids): if WorkerHelper.get_by_name(worker_name) is not None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("insert into worker (name, job_id) " "values (%s, %s)", (worker_name, worker_job_id)) db.commit() if cursor.rowcount != 1: return False worker = WorkerHelper.get_by_name(worker_name) if not worker: return False success = True for depart_id in worker_depart_ids: if not WorkerHelper.add_worker_depart(worker['worker_id'], depart_id): success = False if success: return True else: abort(400) return False
def get_by_page_id(page_id): cursor = mysql.get_db().cursor() cursor.execute( 'select id, content_type, text_content, picture_id, video_id from role_page_content where page_id=%s', [page_id] ) return cursor.fetchall()
def get_by_case_id(case_id): cursor = mysql.get_db().cursor() cursor.execute( 'select id, name, description from case_category where ' 'id in (select category_id from case_info_category where case_id=%s)', (case_id, )) return cursor.fetchall()
def get_by_name(item_type_name): cursor = mysql.get_db().cursor() cursor.execute( 'select item_type.id, item_type.name, item_type.description' ' from item_type' ' where item_type.name=%s', [item_type_name]) return cursor.fetchone()
def create_roleplay_info(role_name, role_desc, depart_id, picture_id): if RoleplayInfoHelper.get_by_name(role_name) is not None: return False, 'duplicate role play name' #if not PictureHelper.get_by_id(picture_id): # return False, 'invalid picture_id' #if not DepartHelper.get_by_id(depart_id): # return False, 'invalid depart_id' if not depart_id : depart_id = 0 db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into role (name, description, page_size, depart_id, picture) " "values (%s, %s, '0', %s, %s)", (role_name, role_desc, depart_id, picture_id) ) db.commit() # row count为1表示插入成功 if cursor.rowcount != 1: return False, 'insert into db failed' else: return True, cursor.lastrowid
def create(file): video_name = file.filename ext = video_name.rsplit('.', 1)[1].lower() if '.' in video_name and ext in VIDEO_EXTS: gene_name = str(uuid.uuid4()) new_video_name = gene_name + '.' + ext address = os.path.join('video', new_video_name) file.save(os.path.join(app.config['UPLOAD_FOLDER'], address)) screenshot_name = gene_name + '.jpeg' screenshot_address = os.path.join('video', 'screenshot', screenshot_name) screenshot = VideoStream( os.path.join(app.config['UPLOAD_FOLDER'], address)).get_frame_at_sec(2).image() screenshot.save( os.path.join(app.config['UPLOAD_FOLDER'], screenshot_address)) db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into video (name, address, screenshot) " "values (%s, %s, %s)", (video_name, address, screenshot_address)) db.commit() if cursor.rowcount != 1: return False, 'insert into db failed' return True, cursor.lastrowid else: return False, 'invalid extension'
def get_by_manager_id(manager_id): cursor = mysql.get_db().cursor() cursor.execute( 'select auth.id, auth.description from authority auth, manager_auth ' 'where manager_auth.manager_id=%s and auth.id = manager_auth.auth_id', [manager_id]) return cursor.fetchall()
def get_by_roleid(role_id): cursor = mysql.get_db().cursor() cursor.execute( 'select id, title, pagination from role_page where role_id=%s', [role_id] ) return cursor.fetchall()
def create_case_category(case_id, category_name, category_desc): if CaseInfoHelper.get_by_id(case_id) is None: print 'can not find case' return False db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into case_category (name, description) " "values (%s, %s)", (category_name, category_desc)) db.commit() # row count为1表示插入成功 if cursor.rowcount != 1: return False category_id = cursor.lastrowid cursor.execute( "insert into case_info_category (case_id, category_id) " "values (%s, %s)", (case_id, category_id)) db.commit() # row count为1表示插入成功 if cursor.rowcount != 1: return False else: return category_id
def get_by_name(worker_name): cursor = mysql.get_db().cursor() cursor.execute( 'select worker.id, worker.name, job.id, job.name, job.description' ' from worker, job where worker.id=%s and worker.job_id = job.id', [worker_name]) return cursor.fetchone()
def get_by_pagination(role_id, pagination): cursor = mysql.get_db().cursor() cursor.execute( 'select id, title, pagination from role_page where role_id=%s and pagination=%s', [role_id, pagination] ) return cursor.fetchone()
def get_by_name(depart_name): cursor = mysql.get_db().cursor() cursor.execute( 'select id, name, type, description from department where name=%s', [depart_name] ) return cursor.fetchone()
def create_manager(username, password, auth_list=None): if ManagerHelper.get_by_name(username) is not None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into manager_account (username, password) " "values (%s, %s)", (username, password)) db.commit() # row count为1表示插入成功 if cursor.rowcount != 1: return False else: if auth_list: # get created manager manager = ManagerHelper.get_by_name(username) # success, insert authorities is_success = True for auth_id in auth_list: if AuthorityHelper.get_by_id(auth_id) is not None: if not AuthorityHelper.add_authority( manager['manager_id'], auth_id): is_success = False else: pass else: pass return is_success else: return True
def get_by_name(role_name): cursor = mysql.get_db().cursor() cursor.execute( 'select id, name, description, page_size, depart_id, picture from role where name=%s', [role_name] ) return cursor.fetchone()
def create_content(page_id, content): content_type = content['content_type'] sql_content = None # 字段名 arg_content = None # 内容 if str(content_type) == '1': sql_content = 'text_content' arg_content = content['text_content'] elif str(content_type) == '2': sql_content = 'video_id' arg_content = content['video_content'] elif str(content_type) == '3': sql_content = 'picture_id' arg_content = content['picture_content'] db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into role_page_content (page_id, content_type, {}) values (%s, %s, %s)".format(sql_content), (page_id, content_type, arg_content) ) db.commit() # row count为1表示插入成功 if cursor.rowcount != 1: return False return cursor.lastrowid
def remove_authority(manager_id, auth_id): db = mysql.get_db() cursor = db.cursor() cursor.execute( "delete from manager_auth " "where manager_id=%s and auth_id=%s", (manager_id, auth_id)) db.commit() return cursor.rowcount == 1
def add_authority(manager_id, auth_id): db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into manager_auth(manager_id, auth_id) " "values(%s, %s)", (manager_id, auth_id)) db.commit() return cursor.rowcount == 1
def get_by_worker(worker_id): cursor = mysql.get_db().cursor() cursor.execute( 'select department.id, department.name, department.type, department.description ' 'from department, depart_worker ' 'where department.id = depart_worker.depart_id and depart_worker.worker_id=%s', (worker_id,) ) return cursor.fetchall()
def delete_by_id(content_id): if CaseContentHelper.get_by_id(content_id) is None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("delete from case_content where id=%s", (content_id, )) db.commit() return cursor.rowcount == 1
def delete_by_id(item_type_id): if ItemTypeHelper.get_by_id(item_type_id) is None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("delete from item_type where id=%s", (item_type_id, )) db.commit() return cursor.rowcount == 1
def delete_by_id(worker_id): if WorkerHelper.get_by_id(worker_id) is None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("delete from worker where id=%s", (worker_id, )) db.commit() return cursor.rowcount == 1
def add_depart_item(depart_id, item_id): db = mysql.get_db() cursor = db.cursor() cursor.execute( "insert into depart_item (depart_id, item_id) " "values (%s, %s)", (depart_id, item_id)) db.commit() return cursor.rowcount == 1
def get_by_item_id(item_id): cursor = mysql.get_db().cursor() cursor.execute( 'select item.id, item.name, item.description, item.type_id, ' 'item_type.name, item_type.description' ' from item, item_type' ' where item.type_id = item_type.id' ' and item.id=%s', [item_id]) return cursor.fetchone()
def get_by_depart_id(depart_id): cursor = mysql.get_db().cursor() cursor.execute( 'select item.id, item.name, item.description, item.type_id, ' 'item_type.name, item_type.description' ' from item, item_type, depart_item' ' where item.type_id = item_type.id and depart_item.item_id = item.id' ' and depart_item.depart_id=%s', [depart_id]) return cursor.fetchall()
def modify_username(manager_id, manager_name): if ManagerHelper.get_by_id(manager_id) is None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("update manager_account set username=%s where id=%s", (manager_name, manager_id)) db.commit() return cursor.rowcount == 1
def modify_password(user_id, password): if UserHelper.get_by_id(user_id) is None: return False db = mysql.get_db() cursor = db.cursor() cursor.execute("update user_account set password=%s where id=%s", (password, user_id)) db.commit() return cursor.rowcount == 1
def delete_by_id(content_id): db = mysql.get_db() cursor = db.cursor() cursor.execute( "delete from role_page_content where id=%s", (content_id,) ) db.commit() return cursor.rowcount == 1
def delete_by_page_id(page_id): db = mysql.get_db() cursor = db.cursor() cursor.execute( "delete from role_page_content where page_id=%s", (page_id,) ) db.commit() return True