def create_fixtures(self): db = database.LibDatabase() con = db._connect_db() # Open Cursor cur = con.cursor() param_vals = ['test_create_id-0ac6cb428b23', '2016-12-31 23:59:59', 'test_update_id-0ac6cb428b23', '2016-12-31 23:59:59', 0, '10.50.60.50', '{"status": 1, "node_id": "node_id-11111111111", ' +\ '"tenant_name": "tenant_name001"}'] cur.execute( "INSERT INTO NAL_GLOBAL_IP_MNG(create_id, " + "create_date, update_id, update_date, delete_flg, " + "global_ip, extension_info) VALUES " + "(%s, %s, %s, %s, %s, %s, %s)", param_vals) cur.execute('SELECT last_insert_id() FROM NAL_GLOBAL_IP_MNG') global ID1 ID1 = cur.fetchall()[0][0] con.commit() # Close Cursor cur.close() # Close Database con.close()
def execute(self, request_params, table_name): # ------------------------------------------------------ # Initialaize # ------------------------------------------------------ table_conf = getattr(tables_conf, table_name, {}) table_pkey = table_conf.get('primaryKey') request_params_id = request_params.get('id') # Create Instance(LibDatabase) db = database.LibDatabase() # ------------------------------------------------------ # Get Current Record(extension_info) # ------------------------------------------------------ sel_sql = '' sel_param_vals = [] sel_sql_where = '' sel_sql = 'SELECT * ' sel_sql += ' FROM ' + table_name if len(table_pkey) > 0 and len(request_params_id) == len(table_pkey): sel_sql_where = ' WHERE ' sel_sql_where += ' = %s AND '.join(table_pkey) + ' = %s ' sel_param_vals = request_params_id else: raise exception.InvalidRequestParam sel_sql += sel_sql_where res = db.execute_sql(sel_sql, sel_param_vals) if len(res) == 0: raise exception.NotFound # ------------------------------------------------------ # Delete Record # ------------------------------------------------------ sql = 'DELETE FROM ' + table_name sql_where = '' param_vals = [] sql_where = ' WHERE ' sql_where += ' = %s AND '.join(table_pkey) + ' = %s ' param_vals += request_params_id sql += sql_where # Execute SQL return db.execute_sql(sql, param_vals)
def destroy_fixtures(self): db = database.LibDatabase() con = db._connect_db() # Open Cursor cur = con.cursor() # Execute SQL param_vals = ['test_create_id-0ac6cb428b23'] cur.execute("DELETE FROM NAL_GLOBAL_IP_MNG WHERE " + "create_id = %s", param_vals) # Commit Transaction con.commit() # Close Cursor cur.close() # Close Database con.close()
def execute(self, request_params, table_name): # ------------------------------------------------------ # Initialaize # ------------------------------------------------------ table_conf = getattr(tables_conf, table_name, {}) table_columns = table_conf.get('columns') table_extension_columns = table_conf.get('extension_columns') request_params_body = request_params.get('body') now_date = datetime.datetime.today().strftime("%Y-%m-%d %H:%M:%S") # Create Instance(LibDatabase) db = database.LibDatabase() # ------------------------------------------------------ # Insert Record # ------------------------------------------------------ sql_columns = '' sql_values = '' param_vals = [] for column in table_columns: if column != 'extension_info' \ and column in request_params_body: val = request_params_body.get(column) else: if column == 'create_date' or column == 'update_date': val = now_date else: continue if len(sql_columns) > 0: sql_columns += ',' sql_values += ',' sql_columns += column sql_values += '%s' param_vals.append(val) extension_info = {} for extension_column in table_extension_columns: if extension_column in request_params_body: extension_val = request_params_body.get(extension_column) else: extension_val = '' extension_info[extension_column] = extension_val sql_columns += ',extension_info' sql_values += ',%s' param_vals.append(json.dumps(extension_info)) sql = 'INSERT INTO ' + table_name sql += '(' + sql_columns + ') VALUES (' + sql_values + ')' # Execute SQL res = db.execute_sql(sql, param_vals) # Return Create Primary Key return {'ID': res[0]['last_insert_id()']}
def execute(self, request_params, table_name): # ------------------------------------------------------ # Initialaize # ------------------------------------------------------ request_id = request_params.get('request_id', '') log = logger.LibLogger(request_id) # Get Table Definition table_conf = getattr(tables_conf, table_name, {}) # Get ConfigValue table_columns = table_conf.get('columns') table_extension_columns = table_conf.get('extension_columns') table_pkey = table_conf.get('primaryKey') request_params_id = request_params.get('id', []) request_params_query = request_params.get('query', {}) # Create Instance(LibDatabase) db = database.LibDatabase() # ------------------------------------------------------ # Select Record # ------------------------------------------------------ # Get ColumnName select_columns = [] for key in table_columns: select_columns.append(self._edit_select_column(key, \ table_columns.get(key).get('type'))) # Edit Query sql = 'SELECT ' + ','.join(select_columns) sql += ' FROM ' + table_name param_vals = [] sql_where = '' # Map Parameters if len(request_params_id) > 0: if len(request_params_id) == len(table_pkey): sql_where += ' = %s AND '.join(table_pkey) + ' = %s ' param_vals = request_params_id else: raise exception.InvalidRequestParam if len(request_params_query) > 0: for column in table_columns: if column in request_params_query: if len(sql_where) > 0: sql_where += ' AND' sql_where += ' ' + column + ' = %s' param_vals.append(request_params_query[column]) if len(sql_where) > 0: sql_where = ' WHERE ' + sql_where # Add Where Phrase sql += sql_where # Add Where Phrase sql += ' ORDER BY ID ASC' # Execute SQL res = db.execute_sql(sql, param_vals) # Edit ResultData to Array result = [] extension_info = [] for res_row in res: row = {} qurey_unmatch_flg = False # Set Columns Info for column_key, column_val in res_row.items(): if column_key == 'extension_info': if column_val is None: extension_info = {} else: extension_info = json.loads(column_val) else: if column_val is None: row[column_key] = '' else: row[column_key] = column_val # Set Ext Columns Info for ext_column in table_extension_columns: if ext_column in extension_info: row[ext_column] = extension_info[ext_column] else: row[ext_column] = '' # Check query matching if ext_column in request_params_query and \ str(request_params_query[ext_column]) \ != str(extension_info[ext_column]): qurey_unmatch_flg = True break # Set Records if qurey_unmatch_flg == False: result.append(row) # Return ResultData return result
def execute(self, request_params, table_name): # ------------------------------------------------------ # Initialaize # ------------------------------------------------------ # Get ConfigValue table_conf = getattr(tables_conf, table_name, {}) table_pkey = table_conf.get('primaryKey') table_columns = table_conf.get('columns') table_extension_columns = table_conf.get('extension_columns') request_params_id = request_params.get('id') request_params_body = request_params.get('body') # Get Date now_date = datetime.datetime.today().strftime("%Y-%m-%d %H:%M:%S") # Create Instance(LibDatabase) db = database.LibDatabase() # ------------------------------------------------------ # Get Current Record(extension_info) # ------------------------------------------------------ sel_sql = '' sel_param_vals = [] sel_sql_where = '' sel_sql = 'SELECT extension_info' sel_sql += ' FROM ' + table_name if len(table_pkey) > 0 and len(request_params_id) == len(table_pkey): sel_sql_where = ' WHERE ' sel_sql_where += ' = %s AND '.join(table_pkey) + ' = %s ' sel_param_vals = request_params_id else: raise exception.InvalidRequestParam sel_sql += sel_sql_where res = db.execute_sql(sel_sql, sel_param_vals) if len(res) == 0: raise exception.NotFound # ------------------------------------------------------ # Update Record # ------------------------------------------------------ sql_columns = [] param_vals = [] for column in table_columns: if column in request_params_body: val = request_params_body.get(column) else: if column == 'update_date': val = now_date else: continue sql_columns.append(column) param_vals.append(val) # Set Extension Clumns extension_info = json.loads(res[0]['extension_info']) extension_info_update = {} for extension_column in table_extension_columns: if extension_column in request_params_body: extension_val = request_params_body.get(extension_column) else: extension_val = extension_info.get(extension_column) extension_info_update[extension_column] = extension_val sql_columns.append('extension_info') param_vals.append(json.dumps(extension_info_update)) sql = 'UPDATE ' + table_name + ' SET ' if len(sql_columns) > 0: sql += ' = %s, '.join(sql_columns) + ' = %s' sql_where = '' sql_where = ' WHERE ' sql_where += ' = %s AND '.join(table_pkey) + ' = %s ' param_vals += request_params_id sql += sql_where # Execute SQL return db.execute_sql(sql, param_vals)