def addAttributes(Attr_Args): try: with con.cursor() as cur: cur.callproc('deletePrivateAttribute', Attr_Args) con.commit() except Exception as ex: print(ex)
def getTableAttributesRepresentNameById(result_1, id_category): try: with con.cursor() as cur: attributes_by_Id = {'Attributes': []} for j, second_layer in enumerate(result_1, 0): attributeObject = { 'Id': second_layer[0], 'Name': second_layer[1], 'Description': second_layer[2], 'AttributesContainer': {}, 'Category': second_layer[5] } attributes_by_Id['Attributes'].append(attributeObject) if second_layer[3] is not None: cur.execute( f'SELECT taa.IdTableAttributes, taa.TableAttributesRepresentName, taa.IdTypeAttributes ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId ' f'LEFT JOIN table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes ' f'WHERE lpa.ListCategoryId={id_category} AND taa.IdTableAttributes={second_layer[3]}' ) result_2 = [list(i) for i in cur.fetchall()] getTypeAttributesById(attributes_by_Id, result_2, id_category, j) except Exception as ex: abort(description=str(ex)) return attributes_by_Id
def addAttributesInfo(): try: Attr_Name = str(request.json['Attr_Name']) Description = str(request.json['Description']).replace('', 'None') \ if str(request.json['Description']) == '' else str(request.json['Description']) Cont_Name = str(request.json['Cont_Name']).replace('', 'None') \ if str(request.json['Cont_Name']) == '' else str(request.json['Cont_Name']) # Type must be only List, SVN, FileServer, OnlineSource Cont_Type = str(request.json['Cont_Type']).replace('', 'None') \ if str(request.json['Cont_Type']) == '' else str(request.json['Cont_Type']) Attr_Args = [Cont_Type, Cont_Name, Attr_Name, Description] with con.cursor() as cur: cur.callproc('addCommonAttribute', Attr_Args) con.commit() new_category = { 'Name': Attr_Name, 'Description': Description, 'AttributesContainer': { 'Name': Cont_Name, 'Type': Cont_Type } } except Exception as ex: print(ex) return new_category
def displayCategoryById(Id): with con.cursor() as cur: cur.execute('SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC') res_Id = [i[0] for i in cur.fetchall()] if Id in filter(lambda t: t == Id, res_Id): return jsonify(getNameById(Id)), 200 else: return abort(404, description='Resource not found')
def getNameById(Id): try: with con.cursor() as cur: cur.execute( f'SELECT lc.IdListCategory, lc.RepresentName FROM list_category AS lc WHERE lc.IdListCategory={Id}') result_1 = [list(i) for i in cur.fetchall()] except Exception as ex: abort(400, description=str(ex)) return getDescriptionById(result_1, Id)
def addFolder(id, object): cur = con.cursor() if id == '0': request = "INSERT INTO folderscomponent SET FolderName = '{folderName}'" request = request.format(folderName=object['Name']) cur.execute(request) else: request = "INSERT INTO folderscomponent SET FolderName = '{folderName}', FolderID = {idParent}" request = request.format(folderName=object['Name'], idParent=id) cur.execute(request)
def displayCurrentPrivateAttribute(id_category, id_attribute): with con.cursor() as cur: cur.execute( 'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC' ) res_id_category = [i[0] for i in cur.fetchall()] if id_category in filter(lambda t: t == id_category, res_id_category): return jsonify(getDescriptionById(id_category, id_attribute)), 200 else: return abort(404, description='Resource not found')
def checkCategoryId(id_category): with con.cursor() as cur: cur.execute( 'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC' ) res_id_category = [i[0] for i in cur.fetchall()] if id_category in filter(lambda t: t == id_category, res_id_category): return getCategoryName(id_category) else: return abort(404, description='Resource not found')
def getName(categories): try: with con.cursor() as cur: cur.execute('SELECT lc.idListCategory, lc.RepresentName ' 'FROM list_category AS lc ORDER BY lc.idListCategory ASC') result_1 = [list(i) for i in cur.fetchall()] getDescription(categories, result_1) except Exception as ex: print(ex) return categories
def getLogsInfo(session_logs): try: with con.cursor() as cur: cur.execute( 'SELECT ls.DateTime, u.Login FROM logs_session AS ls LEFT JOIN users AS u ON ' 'ls.idUser=u.idUser ORDER BY ls.idLogSession ASC ') result_1 = [list(i) for i in cur.fetchall()] collectList(result_1, session_logs) except Exception as ex: print(ex) return session_logs
def checkSpecificCommonAttributeId(id_attribute): with con.cursor() as cur: cur.execute( 'SELECT lca.idListCommonAttribute FROM list_common_attribute AS lca ' 'ORDER BY lca.idListCommonAttribute ASC') res_id_attribute = [i[0] for i in cur.fetchall()] if id_attribute in filter(lambda t: t == id_attribute, res_id_attribute): return getCommonAttributeInfo(id_attribute) else: return abort(404, description='Resource not found')
def getCommonAttributeInfo(id_attribute): try: with con.cursor() as cur: cur.execute( f'SELECT lca.idListCommonAttribute, lca.RepresentName, lca.Description, lca.idTableAttributes ' f'FROM list_common_attribute AS lca ' f'WHERE lca.idListCommonAttribute={id_attribute} ') result_1 = [list(i) for i in cur.fetchall()] except Exception as ex: print(ex) return getTableAttributeRepresentName(result_1, id_attribute)
def getAttributesContainersInfo(containers_of_attributes): try: with con.cursor() as cur: cur.execute('SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, ' 'tya.TypeAttributes FROM table_attributes AS taa LEFT JOIN type_attributes AS tya ON ' 'taa.idTypeAttributes=tya.idTypeAttributes ORDER BY taa.idTableAttributes ASC ') result_1 = [list(i) for i in cur.fetchall()] collectList(result_1, containers_of_attributes) except Exception as ex: print(ex) return containers_of_attributes
def displayContainerOfAttributesById(id_container): with con.cursor() as cur: cur.execute( 'SELECT taa.idTableAttributes FROM table_attributes AS taa ORDER BY taa.idTableAttributes ASC' ) res_id_container = [i[0] for i in cur.fetchall()] if id_container in filter(lambda t: t == id_container, res_id_container): return jsonify(getAttributeContainersInfoById(id_container)), 200 else: return abort(404, description='Resource not found')
def getCategoryName(id_category): try: with con.cursor() as cur: cur.execute( f'SELECT lc.IdListCategory, lc.RepresentName ' f'FROM list_category AS lc WHERE lc.IdListCategory={id_category}' ) Cater_Name = cur.fetchone()[1] except Exception as ex: print(ex) return Cater_Name
def getAttributesInfo(common_attributes): try: with con.cursor() as cur: cur.execute( f'SELECT lca.idListCommonAttribute, lca.RepresentName, lca.Description, lca.idTableAttributes ' f'FROM list_common_attribute AS lca ' f'ORDER BY lca.idListCommonAttribute ASC ') result_1 = [list(i) for i in cur.fetchall()] getTableAttributesRepresentName(result_1, common_attributes) except Exception as ex: print(ex) return common_attributes
def getAttributeContainersInfoById(id_container): try: with con.cursor() as cur: cur.execute( f'SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, ' f'tya.TypeAttributes FROM table_attributes AS taa LEFT JOIN type_attributes AS tya ON ' f'taa.idTypeAttributes=tya.idTypeAttributes WHERE taa.idTableAttributes={id_container}' ) result_1 = [list(i) for i in cur.fetchall()] except Exception as ex: print(ex) return collectDict(result_1)
def addCategory(): try: Cater_Name = str(request.json['Categ_Name']) Cater_Args = [Cater_Name] with con.cursor() as cur: cur.callproc('createCategory', Cater_Args) con.commit() new_category = {'Name': Cater_Name, 'Attributes': []} if 'Attr_Name' in request.json: addAttributes(new_category, Cater_Name) except Exception as ex: print(ex) return new_category
def getDescriptionById(id_category): try: with con.cursor() as cur: cur.execute( f'SELECT lpa.IdListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.IdTableAttributes, ' f'lpa.ListCategoryId, lc.RepresentName ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId ' f'WHERE lpa.ListCategoryId={id_category}') result_1 = [list(i) for i in cur.fetchall()] except Exception as ex: abort(description=str(ex)) return getTableAttributesRepresentNameById(result_1, id_category)
def getDescriptionById(result_1, Id): try: categories_by_Id = {'Id': result_1[0][0], 'Name': result_1[0][1], 'Attributes': []} with con.cursor() as cur: cur.execute( f'SELECT lpa.IdListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.IdTableAttributes, lpa.ListCategoryId ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId ' f'WHERE lpa.ListCategoryId={Id}') result_2 = [list(i) for i in cur.fetchall()] getTableAttributesRepresentNameById(categories_by_Id, result_2, Id) except Exception as ex: abort(description=str(ex)) return categories_by_Id
def getTypeAttributes(categories, result_3, i, j, first_layer): try: with con.cursor() as cur: for third_layer in result_3: cur.execute(f'SELECT tya.TypeAttributes, tya.idTypeAttributes FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID ' f'LEFT JOIN table_attributes AS taa ON lpa.idTableAttributes=taa.idTableAttributes ' f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes ' f'WHERE lpa.ListCategoryID={first_layer} AND tya.idTypeAttributes={third_layer[2]}') result_4 = [list(i) for i in cur.fetchall()] sendTypeAttributes(categories, result_4, i, j, third_layer) except Exception as ex: print(ex) return categories
def getLogsInfo(db_logs): try: with con.cursor() as cur: cur.execute( 'SELECT l.DateTime, tyq.TypeQuery, l.Element, lc.RepresentName, l.Description, u.Login ' 'FROM logs AS l LEFT JOIN type_query AS tyq ON l.idTypeQuery=tyq.idTypeQuery ' 'LEFT JOIN list_category AS lc ON l.idListCategory=lc.idListCategory ' 'LEFT JOIN users AS u ON l.idUser=u.idUser ORDER BY l.idLog ASC ' ) result_1 = [list(i) for i in cur.fetchall()] collectList(result_1, db_logs) except Exception as ex: print(ex) return db_logs
def getData(id_category, id_attribute): try: with con.cursor() as cur: cur.execute( f'SELECT lpa.RepresentName, tya.TypeAttributes, lc.RepresentName FROM list_category AS lc LEFT ' f'JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId LEFT JOIN ' f'table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes LEFT JOIN ' f'type_attributes AS tya ON taa.IdTypeAttributes=tya.IdTypeAttributes WHERE ' f'lc.idListCategory={id_category} AND lpa.idListPrivateAttribute={id_attribute}' ) data_list = [j for i in cur.fetchall() for j in i] except Exception as ex: print(ex) return data_list
def getTypeAttributesById(categories_by_Id, result_3, Id, j): try: with con.cursor() as cur: for third_layer in result_3: cur.execute(f'SELECT tya.TypeAttributes, tya.IdTypeAttributes FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId ' f'LEFT JOIN table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes ' f'LEFT JOIN type_attributes AS tya ON taa.IdTypeAttributes=tya.IdTypeAttributes ' f'WHERE lpa.ListCategoryId={Id} AND tya.IdTypeAttributes={third_layer[2]}') result_4 = [list(i) for i in cur.fetchall()] sendTypeAttributes(categories_by_Id, result_4, j, third_layer) except Exception as ex: abort(description=str(ex)) return categories_by_Id
def getTypeAttribute(common_attributes, result_2, id_attribute): try: with con.cursor() as cur: cur.execute( f'SELECT tya.TypeAttributes FROM list_common_attribute AS lca ' f'LEFT JOIN table_attributes AS taa ON lca.idTableAttributes=taa.idTableAttributes ' f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes ' f'WHERE tya.idTypeAttributes={result_2[0][2]} AND lca.idListCommonAttribute={id_attribute}' ) result_3 = [list(i) for i in cur.fetchone()] sendTypeAttribute(common_attributes, result_3, result_2) except Exception as ex: print(ex) return common_attributes
def getTypeAttributes(common_attributes, result_2, i): try: with con.cursor() as cur: for second_layer in result_2: cur.execute( f'SELECT tya.TypeAttributes FROM list_common_attribute AS lca ' f'LEFT JOIN table_attributes AS taa ON lca.idTableAttributes=taa.idTableAttributes ' f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes ' f'WHERE tya.idTypeAttributes={second_layer[2]}') result_3 = [list(i) for i in cur.fetchall()] sendTypeAttributes(common_attributes, result_3, second_layer, i) except Exception as ex: print(ex) return common_attributes
def getDescription(categories, result_1): try: with con.cursor() as cur: for i, first_layer in enumerate(result_1, 0): categories.append({'Id': first_layer[0], 'Name': first_layer[1], 'Attributes': []}) cur.execute( f'SELECT lpa.idListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.idTableAttributes, lpa.ListCategoryID ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID ' f'WHERE lpa.ListCategoryID={first_layer[0]}') result_2 = [list(i) for i in cur.fetchall()] getTableAttributesRepresentName(categories, result_2, i, first_layer[0]) except Exception as ex: print(ex) return categories
def checkCategoryId(id_category, id_attribute): try: with con.cursor() as cur: cur.execute( 'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC' ) res_id_category = [i[0] for i in cur.fetchall()] if id_category in filter(lambda t: t == id_category, res_id_category): return checkAttributeId(id_category, id_attribute) else: return abort(404, description='Requested category do not found') except Exception as ex: print(ex)
def getTableAttributesRepresentName(categories, result_2, i, first_layer): try: with con.cursor() as cur: for j, second_layer in enumerate(result_2, 0): attributeObject = {'Id': second_layer[0], 'Name': second_layer[1], 'Description': second_layer[2], 'AttributesContainer': {}} categories[i]['Attributes'].append(attributeObject) if second_layer[3] is not None: cur.execute(f'SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, taa.idTypeAttributes ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID ' f'LEFT JOIN table_attributes AS taa ON lpa.idTableAttributes=taa.idTableAttributes ' f'WHERE lpa.ListCategoryID={first_layer} AND taa.idTableAttributes={second_layer[3]}') result_3 = [list(i) for i in cur.fetchall()] getTypeAttributes(categories, result_3, i, j, first_layer) except Exception as ex: print(ex) return categories
def checkAttributeId(id_category, id_attribute): try: with con.cursor() as cur: cur.execute( f'SELECT lpa.IdListPrivateAttribute, lpa.ListCategoryId ' f'FROM list_category AS lc ' f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId ' f'WHERE lpa.ListCategoryId={id_category} AND lpa.idListPrivateAttribute={id_attribute}' ) res_id_attribute = [i[0] for i in cur.fetchall()] if id_attribute in filter(lambda t: t == id_attribute, res_id_attribute): return getData(id_category, id_attribute) else: return abort(404, description='Requested attribute do not found') except Exception as ex: print(ex)