def _mysql_select_to_dict(query,table_name): ''' query Mysql and return json ''' default_mysql_conn = mysql_connection() cur = default_mysql_conn.cursor() #dictionary=True print('Executing: \n \n' + query) cur.execute(query) columns_with_type = [(col[0], col[1]) for col in cur.description] print(columns_with_type) fields = [] for colname, coltype in columns_with_type: # replace all non-alphanumeric characters with name = re.sub("[^0-9a-zA-Z]+", "_", colname.lower()) coltype = mysql_mapping(FieldType.get_info(coltype)) fields.append({"name": name, **_avro_switch_type(coltype)}) counts = 0 if cur.description: rows = cur.fetchall() for i in rows: counts +=1 print('rows affected: ' + str(counts)) else: rows = None cur.close() return fields, table_name, cur, rows, counts
def SelectAll(self, table): if table == None or len(table) == 0: raise ErrorClass( 1, "Błąd komunikacji w programie", "Błąd!!", "Nieprzekazano nazwy tabeli, z której mamy wyciągnąć dane") try: sql = "SELECT * FROM " sql += table cursor = self.connection.cursor() cursor.execute(sql) result = cursor.fetchall() colnames = cursor.column_names fieldTypes = [] #print(result) #print(colnames) #typy kolumn for desc in cursor.description: fieldTypes.append(FieldType.get_info(desc[1])) cursor.close() return (colnames, result, fieldTypes) except mysql.connector.Error as err: raise ErrorClass(0, "Błąd zapytania", str(err)) return False
def createVariableList(self, column_names, column_types): return_str = '{<br/>' for name, type in zip(column_names, column_types): return_str += ' "' + name + '": ' + FieldType.get_info( type[1]) + ",<br/>" return_str = return_str[:-6] return return_str + '<br/>}'
def get_entities(mycursor, sql): mycursor.execute(sql) entities = mycursor.fetchall() attributes = [desc[0] for desc in mycursor.description] fieldType = [FieldType.get_info(desc[1]) for desc in mycursor.description] # Debug code result = [] for entity in entities: result.append(dict(zip(attributes, entity))) return result
def _execute_query(self, query = ""): column_names = [] cur = self.mysql_connector_client.cursor() cur.execute(query) rows = cur.fetchall() desc = cur.description columns =[] for row in desc: columns.append({"column_name":row[0], "type":FieldType.get_info(row[1]).lower()}) return columns, rows
def parse_result(cursor): data = cursor.fetchall() # for each col in row if your type is in _DATE_TYPES - parse to string for row in data: for col in cursor.description: if FieldType.get_info(col[1]) in _DATE_TYPES: # TODO: change format row[col[0]] = str(row[col[0]]) return data
def _execute_query(self, query = ""): column_names = [] cur = self.mysql_connector_client.cursor() cur.execute(query) rows = cur.fetchall() desc = cur.description columns =[] for row in desc: columns.append({"column_name":row[0], "type":FieldType.get_info(row[1]).lower()}) return columns, rows
def imprime_tipos_cursor(self, cursor): # En una consulta Mysql imprime los tipos de datos de las columnas # a partir d e for i in range(len(cursor.description)): print("Column {}:". format(i + 1)) desc = cursor.description[i] print(" column_name = {}".format(desc[0])) print(" type = {} ({})".format(desc[1], FieldType.get_info(desc[1]))) print(" null_ok = {}".format(desc[6])) print(" column_flags = {} ({})".format(desc[7], FieldFlag.get_info(desc[7])))
def describe_table(dbobj, src): cur = src.cursor() # cur.execute("SHOW columns FROM %s" % (table.table)) cur.execute("select * FROM %s where 1 = 2" % (dbobj.table)) cur.fetchall() cols = ([(k[0], FieldType.get_info(k[1])) for k in cur.description]) for col in cols: if (col[0]) == dbobj.key_column: return [col[1], len(cols)] raise ValueError('No column found to match user defined %s' % dbobj.key_column)
def getDatabaseSchema(cursor): dbSchema = {'elements': [], 'constraints': []} for i in range(len(cursor.description)): #print("Column {}:".format(i+1)) desc = cursor.description[i] if desc[0] != 'ID': dbSchema['elements'].append(desc[0]) #print(" column_name = {}".format(desc[0])) dbSchema['constraints'].append(FieldType.get_info(desc[1])) #print(" type = {} ({})".format(desc[1], FieldType.get_info(desc[1]))) return dbSchema
def description_to_fields(mysql_cur_description): """Convert a MySQL cursor description to a list of fields for inclusion in the graph data.""" fields = [] for f_data in mysql_cur_description: column_name = f_data[0] dtype_num = f_data[1] dtype_name = FieldType.get_info(f_data[1]) null_ok = f_data[6] column_flags = f_data[7] fields.append((column_name, dtype_name)) return fields
def get_function_info(connection, table): # get the table structure cursor = connection.cursor() query = "select * from " + table + " limit %(count)s" cursor.execute(query, {"count": 1}) cursor.fetchall() # get the column info; see here for field type list: # http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.constants.FIELD_TYPE-module.html column_info = [] for desc in cursor.description: func_type = 'string' db_type = FieldType.get_info(desc[1]) if db_type in ['BIT', 'INT24', 'SHORT', 'LONG']: func_type = 'integer' if db_type in ['DOUBLE', 'FLOAT']: func_type = 'number' info = {} info['name'] = desc[0] info['type'] = func_type info['description'] = 'The ' + info['name'] + ' for the item' column_info.append(info) cursor.close() # return the function info clean_name = table.lower() clean_name = 'mysql-' + re.sub('[^0-9a-zA-Z]+', '-', clean_name) info = {} info['name'] = clean_name info['title'] = '' info['description'] = '' info['task'] = { 'op': 'sequence', 'items': [{ 'op': 'execute', 'lang': 'python', 'code': get_function_extract_task(table) }] } info['returns'] = column_info info['run_mode'] = 'P' info['deploy_mode'] = 'R' info['deploy_api'] = 'A' return info
def sqlToJson(column_names, dataIn, colTypes): types = [] for desc in colTypes: if(args.sqlType == 'mssql'): coltype = pyodbc_mysql_datatype_map[desc[1]] if coltype == FieldType.FLOAT: coltype = specifyFloat(desc[3]) types.append(FieldType.get_info(coltype)) else: coltype = desc[1] types.append(FieldType.get_info(coltype)) data = [] for row in dataIn: i = 0 dataRow = collections.OrderedDict() for field in row: dataRow[column_names[i]] = field i = i + 1 data.append(dataRow) response = {} response['data'] = data response['types'] = types return response
def _mysql_select_to_avro_parameters(query): ''' query Mysql and return json ''' default_mysql_conn = mysql_connection() #default_mysql_conn.set_charset_collation('utf8', 'utf8_general_ci') cur = default_mysql_conn.cursor() #dictionary=True #cur.execute('SET NAMES utf8;') #cur.execute('SET CHARACTER SET utf8;') #cur.execute('SET character_set_connection=utf8;') print('Executing: \n \n' + query) cur.execute(query) columns_with_type = [(col[0], col[1]) for col in cur.description] print(columns_with_type) fields = [] for colname, coltype in columns_with_type: # replace all non-alphanumeric characters with name = re.sub("[^0-9a-zA-Z]+", "_", colname.lower()) print('coltype: ',coltype) coltype = mysql_mapping(FieldType.get_info(coltype)) fields.append({"name": name, **_avro_switch_type(coltype)}) counts = 0 if cur.description: rows = cur.fetchall() for i in rows: counts +=1 print('rows affected: ' + str(counts)) else: rows = None column_names = [col[0] for col in cur.description] cur.close() #convert bytes to str str_rows = [] for row in rows: newrow = [bytes_to_str(val) for val in row] str_rows.append(newrow) return fields, column_names, str_rows, counts #fields, column_names, rows, counts
def genFORM(tabla, cols, conn): """ Columnas seleccionadas por la stored procedure """ out = header(tabla) out += '<form name="%s" action="http://192.168.1.102:5000/cargar/%s" method="post">\n' % ( tabla.title(), tabla) for col in cols: tipo = FieldType.get_info(col[1]) flg = FieldFlag.get_bit_info(col[7]) if 'AUTO_INCREMENT' in flg: continue if 'NOT_NULL' in flg: required = 'required' else: required = '' if 'MULTIPLE_KEY' in flg: out += '<div class="form-group">\n' nom = col[0].replace('id', '', 1).title() out += '\t<label for="%s">%s</label>\n' % (nom, nom) out += '\t<select name="%s">\n' % (nom) for opt in getCombo(conn, col[0].replace('id', '', 1)): out += '\t\t' + opt + '\n' out += '\t</select>\n' out += '</div>\n' else: out += '<div class="form-group">\n' nom = col[0].title() out += '\t<label for="%s">%s:</label>\n' % (nom, nom) out += '\t<input type="%s" class="form-control" id="%s" name="%s" placeholder="%s" %s/>\n' % ( tipo, nom, nom, nom, required) out += '</div>\n' out += '<hr>\n' out += '<button type="submit" formmethod="post">Confirmar</button>\n' out += '</form>\n' out += footer() conn.close() return out
def get_cursor_description(tabla): try: data = [] sql = "select * from " + tabla + " limit 1" conn = get_connection() cursor = conn.cursor() cursor.execute(sql) row = cursor.fetchone() for col in cursor.description: nombre = col[0] tipo = FieldType.get_info(col[1]) flags = FieldFlag.get_bit_info(col[7]) data.append({"nombre": nombre, "tipo": tipo, "flags": flags}) cursor.close() conn.close() return data except Exception as e: msg = "Excepcion procesando %s" % (tabla, str(e)) conn.close() logger.error(msg)
def get_mysql_entities(username, databaseName, entitySetName): """ View all the entites in the entitySetName """ password = get_password(username) try: cnx = connectSQLServerDB(username, password, username + "_" + databaseName) mycursor = cnx.cursor() sql = "SELECT * FROM " + entitySetName + ";" mycursor.execute(sql) entities = mycursor.fetchall() attributes = [desc[0] for desc in mycursor.description] fieldType = [ FieldType.get_info(desc[1]) for desc in mycursor.description ] # Debug code results = [] for entity in entities: results.append(dict(zip(attributes, entity))) cnx.close() # results = json.dumps(results, use_decimal=True) return jsonify(success=1, message=results) except mysql.connector.Error as err: return jsonify(success=0, error_code=err.errno, message=err.msg)
from include.conexao import ConexaoDB """ Podemos obter o tipo real da coluna usando a FieldType classe A FieldType classe fornece todos os tipos de dados suportados do MySQL. Para converter códigos de tipo inteiro em sua representação de cadeia, usamos o get_info() método da FieldType classe, da seguinte maneira: """ try: conn = ConexaoDB().conexao() cursor = conn.cursor() try: cursor.execute("SELECT * FROM clientes") cursor.fetchall() # leia o resultado sem imprimir nada print("\n\n") for desc in cursor.description: print("Coluna '{}' é do tipo......: {}".format( desc[0], FieldType.get_info(desc[1]))) except: raise Exception("Problema para selecionar") except Exception as err: print("Erro:", err) else: cursor.close() conn.close()
def get_type_as_string(self, type_code): from mysql.connector import FieldType return FieldType.get_info(type_code)
def type(self): return enumerate( [FieldType.get_info(item[1]) for item in self.cursor.description])
# Create connection to MySQL db = mysql.connector.connect(option_files="my.ini") cursor = db.cursor() # Create a test table cursor.execute("""CREATE TEMPORARY TABLE world.t1 ( id int unsigned NOT NULL PRIMARY KEY, val1 tinyint, val2 bigint, val3 decimal(10,3), val4 text, val5 varchar(10), val6 char(10) )""") # Select all columns (no rows returned) cursor.execute("SELECT * FROM world.t1") # Print the field type for each column print("{0:6s} {1}".format("Column", "Field Type")) print("=" * 25) for column in cursor.description: print("{0:6s} {1:3d} - {2}".format(column[0], column[1], FieldType.get_info(column[1]))) # Consume the (non-existing) rows cursor.fetchall() cursor.close db.close()
def import_data_into_table(row_idex, filename, valid_dbname, table_name, current_row, header_row, sql_connect, conn, CBC_submission_name, CBC_submission_time="None_Provided"): if row_idex == 1: print("## writting " + filename + " into the `" + valid_dbname + "`.`" + table_name + "` table") query_str = ("show index from `" + valid_dbname + "`.`" + table_name + "` where Key_name = 'PRIMARY';") sql_connect.execute(query_str) rows = sql_connect.fetchall() query_res = sql_connect.rowcount dup_counts = 0 if query_res > 0: string_2 = "where " if query_res == 1: #table has 1 primary key if table_name == "Submission_MetaData": #primary key does not exist in the file Submission_ID = 1 else: curr_prim_key = current_row[header_row.index(rows[0][4])] string_2 = string_2 + " `" + rows[0][ 4] + "` = '" + curr_prim_key + "'" elif query_res == 2: #table has 2 primary keys curr_prim_key_1 = current_row[header_row.index(rows[0][4])] curr_prim_key_2 = current_row[header_row.index(rows[1][4])] string_2 = string_2 + " `" + rows[0][ 4] + "` = '" + curr_prim_key_1 + "'" string_2 = string_2 + "and `" + rows[1][ 4] + "` = '" + curr_prim_key_2 + "'" elif query_res == 3: #table has 3 primary keys curr_prim_key_1 = current_row[header_row.index(rows[0][4])] curr_prim_key_2 = current_row[header_row.index(rows[1][4])] curr_prim_key_3 = current_row[header_row.index(rows[2][4])] string_2 = string_2 + " `" + rows[0][ 4] + "` = '" + curr_prim_key_1 + "'" string_2 = string_2 + "and `" + rows[1][ 4] + "` = '" + curr_prim_key_2 + "'" string_2 = string_2 + "and `" + rows[2][ 4] + "` = '" + curr_prim_key_3 + "'" if table_name != "Submission_MetaData": #primary key does not exist in the file query_str = ("select * from `" + valid_dbname + "`.`" + table_name + "` " + string_2) sql_connect.execute(query_str) a = sql_connect.fetchall() query_res = sql_connect.rowcount if query_res > 0: dup_counts = 1 return dup_counts query_str = "select * from `" + valid_dbname + "`.`" + table_name + "`" sql_connect.execute(query_str) desc = sql_connect.description a = sql_connect.fetchall() query_res = sql_connect.rowcount column_names_list = [] column_type_list = [] for col_name in desc: column_names_list.append(col_name[0]) column_type_list.append(FieldType.get_info(col_name[1])) res_cols = [] res_head = [] for val in enumerate(column_names_list): if val[1] in header_row: match_idx = header_row.index(val[1]) res_cols.append(match_idx) for val in enumerate(header_row): if val[1] in column_names_list: match_idx = column_names_list.index(val[1]) res_head.append(match_idx) string_1 = "INSERT INTO `" + valid_dbname + "`.`" + table_name + "`(" string_2 = "VALUE (" for i in res_cols: if header_row[i] == "Submission_ID": print(string_1) string_1 = string_1 + " " else: string_1 = string_1 + header_row[i] + "," string_1 = string_1[:-1] + ")" res_head.sort() for i in enumerate( res_cols): #still need to check for boolen and date flags column_name = column_names_list[res_head[i[0]]] column_type = column_type_list[res_head[i[0]]] column_value = current_row[res_cols[i[0]]] # print("## column name :: " + column_name + " column type :: " + column_type) if column_type.upper() == 'DATE': column_value = column_value.replace('/', ',') string_2 = string_2 + "STR_TO_DATE('" + column_value + "','%m,%d,%Y')," elif column_type.upper() == 'TIME': string_2 = string_2 + "TIME_FORMAT('" + column_value + "','%H:%i')," elif column_type.upper() == 'TINY': if column_value == 'T': string_2 = string_2 + "1," elif column_value == 'F': string_2 = string_2 + "0," else: string_2 = string_2 + "'" + column_value + "'," string_2 = string_2[:-1] + ")" if 'Submission_CBC' in column_names_list: string_1 = string_1[:-1] + ",Submission_CBC)" string_2 = string_2[:-1] + ",'" + CBC_submission_name + "')" if 'Submission_time' in column_names_list: string_1 = string_1[:-1] + ",Submission_time)" CBC_submission_time = CBC_submission_time.replace('-', ',') CBC_submission_time = CBC_submission_time.replace('_', ',') string_2 = string_2[:-1] + ",STR_TO_DATE('" + CBC_submission_time + "','%H,%i,%S,%m,%d,%Y'))" query_auto = string_1 + string_2 sql_connect.execute(query_auto) processing_table = sql_connect.rowcount if processing_table == 0: print("## error in submission string") else: conn.commit() return dup_counts
def get_type_as_string(self, type_code): from mysql.connector import FieldType return FieldType.get_info(type_code)
try: #@ _DISPLAY_METADATA_ print("Statement: %s" % stmt) # buffer cursor so that rowcount has usable value cursor = conn.cursor(buffered=True) cursor.execute(stmt) # metadata information becomes available at this point ... print("Number of rows: %d" % cursor.rowcount) if cursor.with_rows: ncols = len(cursor.description) else: ncols = 0 # no result set print("Number of columns: %d" % ncols) if ncols == 0: print("Note: statement has no result set") else: for i, col_info in enumerate(cursor.description): # print name, then other information name, type, _, _, _, _, nullable, flags = col_info print("--- Column %d (%s) ---" % (i, name)) print("Type: %d (%s)" % (type, FieldType.get_info(type))) print("Nullable: %d" % (nullable)) print("Flags: %d" % (flags)) cursor.close() #@ _DISPLAY_METADATA_ except mysql.connector.Error as e: print("Error code: %s" % e.errno) print("Error message: %s" % e.msg) conn.close()
def execute(cnx, sql, showSlaveStatus=False): """ execute: Given a connection and a SQL, this function executes the SQL in the database associated with the connection and returns either a result set or a notification of successful run. """ try: cursor = cnx.cursor(buffered=True, dictionary=True) cursor.execute("SHOW SLAVE STATUS;") if cursor.rowcount > 0 and (sql.find("CREATE") == 0 or sql.find("DROP") == 0 or sql.find("ALTER") == 0 or sql.find("GRANT ") == 0 or sql.find("REVOKE") == 0 or sql.find("INSERT") == 0 or sql.find("DELETE") == 0 or sql.find("UPDATE") == 0 or sql.find("IMPORT") == 0 or sql.find("RENAME") == 0): log('error', 'THIS SERVER IS A SLAVE, NO DDL/DML WILL BE RUN HERE!!! ONLY SELECTS ALLOWED!') else: if args.verbosity > 3: log('debug', 'Got a cursor from the conection') cursor.execute(sql) if args.verbosity > 3: log('debug', 'Executed statement') if cursor.with_rows: if args.verbosity > 3: log('debug', 'SELECT statement detected!!') rows = cursor.fetchall() # Calculate Max Length if args.verbosity > 3: log('debug', 'Calculating Max Length for each column') maxLength = {} for column in cursor.column_names: maxLength[column] = len(column) if args.verbosity > 2: log('log', 'MaxLengths') log('log', maxLength) for row in rows: for column in cursor.column_names: if len(str(row[column])) > maxLength[column]: maxLength[column] = len(str(row[column])) if args.verbosity > 2: log('log', 'MaxLengths') log('log', maxLength) if not showSlaveStatus: # Print headers if args.verbosity > 3: log('debug', 'Printing Headers') for column in cursor.column_names: print(column.ljust(maxLength[column] + 2), end="") print("") # Print separator if args.verbosity > 3: log('debug', 'Printing Separator') for column in cursor.column_names: for j in range(maxLength[column] + 2): print("-", end="") print("") # Print rows for row in rows: for column in cursor.column_names: desc = cursor.description[cursor.column_names.index( column)] if FieldType.get_info(desc[1]) in ['BIGINT', 'DECIMAL', 'DOUBLE', 'FLOAT', 'INT', 'LONGLONG', 'NEWDECIMAL']: if args.verbosity > 3: log('debug', 'Current field is ' + FieldType.get_info(desc[1])) print(str(row[column]).rjust( maxLength[column] + 2), end="") else: if args.verbosity > 3: log('debug', 'Current field is ' + FieldType.get_info(desc[1])) print(str(row[column]).ljust( maxLength[column] + 2), end="") print("") else: # showSlaveStatus for row in rows: for column in ['Slave_IO_State', 'Master_Host', 'Master_User', 'Master_Port', 'Master_Log_File', 'Read_Master_Log_Pos', 'Relay_Log_File', 'Relay_Log_Pos', 'Relay_Master_Log_File', 'Slave_IO_Running', 'Slave_SQL_Running', 'Last_Errno', 'Skip_Counter', 'Exec_Master_Log_Pos', 'Relay_Log_Space', 'Until_Condition', 'Until_Log_Pos', 'Seconds_Behind_Master', 'Last_IO_Errno', 'Last_SQL_Errno']: print(column.rjust(max(maxLength.values())) + ": " + str(row[column])) else: if args.verbosity > 3: log('debug', 'Not a select statement') log('info', f'Statement executed. {cursor.rowcount} rows affected!') except mysql.connector.Error as err: if args.verbosity > 3: log('debug', 'Catched exception while executing') log('critical', err.errno) log('critical', err.sqlstate) log('critical', err.msg) finally: if args.verbosity > 3: log('debug', 'Closing the cursor') cursor.close()
def processSql_con(con, sql, parameters={}, maxRows=2000, name='no_name'): pLog = ProcessLog.Current() pLog.system('sql before conversion:', sql) sqlLogger.debug('start sql **************************************') sqlLogger.debug(M(name, sql)) qap = QueryAndParams(sql, parameters) qap.convertQuery() sql = qap.qm_query names = qap.param_list parameters = qap.req_params # # PREPARE SERVICE_STMT # sql_params = [] for n in names: if n in parameters: v = parameters[n] sql_params.append(v) sqlLogger.debug(M('sql-parameter:', n, ':', v)) else: sqlLogger.info( M(name, 'no value provided for parameter:', n, 'will use empty string')) sql_params.append('') # # FINALIZE SERVICE_STMT # result = Result() result.name = name result.rowsAffected = -1 try: cursor = con.cursor(dictionary=True) cursor.execute(sql, sql_params) if (cursor.with_rows): rows = cursor.fetchmany(size=(maxRows + 1)) sqlLogger.debug(M(name, 'sql-rows-found:', len(rows))) result.header = [] result.types = [] _header = [] for desc in cursor.description: _header.append(desc[0]) result.header.append(to_camel_case(desc[0])) result.types.append(FieldType.get_info(desc[1])) result.table = [] for row in rows: r = [] for head in _header: v = row[head] r.append('' if v is None else str(v)) result.table.append(r) if len(result.table) >= maxRows: break result.totalCount = len(rows) else: result.rowsAffected = cursor.rowcount sqlLogger.debug(M('sql-rows-affected:', result.rowsAffected)) except DatabaseError as e: msg = M(name, 'parameters:', parameters, '->', e) pLog.warn(msg) sqlLogger.warning(msg) except Exception as e: msg = M(name, 'parameters:', parameters, '->', e) pLog.error(msg) logger.error(msg) finally: con.commit() closeQuietly(cursor) return result
for name, ddl in DROPTABLES.iteritems(): # Iterate through the items in the DropTables list try: print("Dropping Table {}: ".format(name)) cursor.execute(ddl) # Execute the command except mysql.connector.Error as err: # Trap for errors print "Error occurred: %s " % err # Display the errors cursor.execute('SET foreign_key_checks = 1;') # Re-enable the foreign key constraint # Get Table column details from mysql.connector import FieldType cursor.execute("SELECT * FROM _POC_py") # Change the cursor for i in range(len(cursor.description)): # Iterate through the cursor print("Column {}:".format(i+1)) # Set the Column Header desc = cursor.description[i] # Get the Cursor Description print(" column_name = {}".format(desc[0])) print(" type = {} ({})".format(desc[1], FieldType.get_info(desc[1]))) print(" null_ok = {}".format(desc[6])) print(" column_flags = {}".format(desc[7])) # Write the column names into a list colNames = [] # Set the list object for i in range(len(cursor.description)): # Iterate through the cursor desc = cursor.description[i] # Get the Cursor Description colNames.append(desc[0]) # Append the name to the list print ','.join(colNames) # Add row to table import time sql = "INSERT INTO _POC_py VALUES (null, 'test item 1', 'test item 2', '%s')" % time.strftime('%Y-%m-%d %H:%M:%S') try: cursor.execute(sql)
def lambda_handler(event, context): s3_client = boto3.client("s3") s3_resource = boto3.resource("s3") ssm = boto3.client("ssm") # outputput_bucket = ssm.get_parameter(Name="Unzipped_dest_bucket", WithDecryption=True).get("Parameter").get("Value") host_client = ssm.get_parameter( Name="db_host", WithDecryption=True).get("Parameter").get("Value") user_name = ssm.get_parameter( Name="lambda_db_username", WithDecryption=True).get("Parameter").get("Value") user_password = ssm.get_parameter( Name="lambda_db_password", WithDecryption=True).get("Parameter").get("Value") file_dbname = ssm.get_parameter( Name="jobs_db_name", WithDecryption=True).get("Parameter").get("Value") pre_valid_db = ssm.get_parameter( Name="Prevalidated_DB", WithDecryption=True).get("Parameter").get("Value") try: #conn = mysql.connect(host = host_client, user=user_name, password=user_password, db=file_dbname, connect_timeout=5) conn = mysql.connector.connect(user=user_name, host=host_client, password=user_password, database=file_dbname) print( "SUCCESS: Connection to RDS mysql instance succeeded for file remover tables" ) except mysql.connector.Error as err: print(err) return {} sql_connect = conn.cursor() table_sql_str = ( "SELECT * FROM `" + file_dbname + "`.table_file_remover Where file_status = 'COPY_SUCCESSFUL'") sql_connect.execute(table_sql_str) #returns number of rows in the database rows = sql_connect.fetchall() #list of all the data processing_table = sql_connect.rowcount if processing_table == 0: print('## There are no new files to add ##') print('## All Files have been checked, Closing the connections ##') sql_connect.close() conn.close() return {} elif processing_table > 0: print('SQL command was executed sucessfully') desc = sql_connect.description #tuple list of column names column_names_list = [] column_type_list = [] for col_name in desc: column_names_list.append( col_name[0]) #converts tubple names in list of names column_type_list.append(FieldType.get_info( col_name[1])) #type of variable file_id_index = column_names_list.index('file_id') file_name_index = column_names_list.index('file_name') file_location_index = column_names_list.index('file_location') for row_data in rows: current_row = list( row_data) #Current row function is interating on full_bucket_name = current_row[file_location_index] zip_file_name = current_row[file_name_index] org_file_id = current_row[file_id_index] name_parts_list = full_bucket_name.split("/") if len(name_parts_list) == 4: folder_name = name_parts_list[0] CBC_submission_name = name_parts_list[1] CBC_submission_date = name_parts_list[2] CBD_ZIP_filename = name_parts_list[3] elif len(name_parts_list) == 2: folder_name = name_parts_list[0] CBC_submission_name = "Test_CBC_Name" eastern = dateutil.tz.gettz('US/Eastern') CBC_submission_date = datetime.datetime.now( tz=eastern).strftime("%H-%M-%S-%m-%d-%Y") CBD_ZIP_filename = name_parts_list[1] first_folder_cut = full_bucket_name.find( '/') #seperates the bucket and key names from the file path if first_folder_cut > -1: key_name = full_bucket_name[(first_folder_cut + 1):] bucket_name = full_bucket_name[:(first_folder_cut)] if (str(zip_file_name).endswith('.zip') ): #checks to see if file name is a zip print("## UnZipping folder name :: " + bucket_name + " key name :: " + key_name) size = [2] * 9 zip_obj = s3_resource.Object(bucket_name=bucket_name, key=key_name) buffer = BytesIO(zip_obj.get()["Body"].read()) z = zipfile.ZipFile( buffer) #unzips the file into a temporary space full_name_list = z.namelist() foreign_key_level = [0] * len(full_name_list) for filename in enumerate(z.namelist()): if filename[1] in [ 'Demographic_Data.csv', 'Assay_Metadata.csv' ]: foreign_key_level[filename[0]] = 0 elif filename[1] in [ 'Assay_Target.csv', 'Biospecimen_Metadata.csv', 'Prior_Test_Results.csv' ]: foreign_key_level[filename[0]] = 1 elif filename[1] in [ 'Equipment_Metadata.csv', 'Reagent_Metadata.csv', 'Aliquot_Metadata.csv', 'Confirmatory_Test_Results.csv' ]: foreign_key_level[filename[0]] = 2 sort_idx = sorted(range(len(foreign_key_level)), key=lambda k: foreign_key_level[k]) sort_idx = [int(l) for l in sort_idx] full_name_list = [full_name_list[l] for l in sort_idx] validation_status_list = [] validation_file_location_list = [] for filename in full_name_list: #once zip is open, loops through file contents file_info = z.getinfo(filename) empty_count = 0 dub_counts = 0 if (str(filename).endswith('.zip')): print( '## zip file does not need to be coppied over, not moving' ) else: try: #writes the unziped contents into a new bucket for storage validation_status = 'FILE_VALIDATION_SUCCESS' new_key = CBC_submission_name + '/' + CBC_submission_date + '/' + filename #print("##unziped file location :: " + folder_name + "/" + new_key) response = s3_resource.meta.client.upload_fileobj( z.open(filename), Bucket=folder_name, Key=new_key) bucket = s3_resource.Bucket(folder_name) obj = bucket.Object(key=new_key) response = obj.get() try: lines = response['Body'].read().split( b'\r\n' ) #split on return, newline, and space character except: lines = response['Body'].read().split( b'\.r\.n') ############################################################################################################################ for row in range(len(lines)): if row == 0: header_row = lines[row].decode('utf-8') header_row = list(header_row.split(",")) continue elif row > 0: #row = 0 is the column header row current_row = lines[row].decode('utf-8') current_row = list(current_row.split(",")) if len( current_row ) <= 1: #if list is empty then do not use empty_count = empty_count + 1 continue if (len(set(current_row)) == 1): empty_count = empty_count + 1 continue if filename.lower() == "demographic_data.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Demographic_Data", current_row, header_row, sql_connect, conn, CBC_submission_name) import_data_into_table( row, filename, pre_valid_db, "Comorbidity", current_row, header_row, sql_connect, conn, CBC_submission_name) import_data_into_table( row, filename, pre_valid_db, "Prior_Covid_Outcome", current_row, header_row, sql_connect, conn, CBC_submission_name) import_data_into_table( row, filename, pre_valid_db, "Submission_MetaData", current_row, header_row, sql_connect, conn, CBC_submission_name, CBC_submission_date) elif filename.lower() == "assay_metadata.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Assay_Metadata", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower() == "assay_target.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Assay_Target", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "biospecimen_metadata.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Biospecimen", current_row, header_row, sql_connect, conn, CBC_submission_name) import_data_into_table( row, filename, pre_valid_db, "Collection_Tube", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "prior_test_results.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Prior_Test_Result", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "aliquot_metadata.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Aliquot", current_row, header_row, sql_connect, conn, CBC_submission_name) import_data_into_table( row, filename, pre_valid_db, "Aliquot_Tube", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "equipment_metadata.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Equipment", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "confirmatory_test_results.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Confirmatory_Test_Result", current_row, header_row, sql_connect, conn, CBC_submission_name) elif filename.lower( ) == "reagent_metadata.csv": dub_counts = dub_counts + import_data_into_table( row, filename, pre_valid_db, "Reagent", current_row, header_row, sql_connect, conn, CBC_submission_name) else: print( filename + " IS NOT an expected file and will not be written to database" ) validation_status = 'FILE_VALIDATION_Failure' if (row - empty_count) == 0: print(file_name + " is empty and not a valid file") validation_status = 'FILE_VALIDATION_Failure' print("## there were " + str(row - empty_count) + " rows found with " + str(dub_counts) + " dupliactes found :: " + str(row - empty_count - dub_counts) + " records written to the table") ############################################################################################################################ except Exception as error_msg: validation_status = 'FILE_VALIDATION_Failure' print(error_msg) if filename.lower() == "biospecimen_metadata.csv": query_auto = ( "update `" + pre_valid_db + "`.`Biospecimen`" "set Storage_Time_at_2_8 = TIME_TO_SEC(TIMEDIFF(Storage_End_Time_at_2_8 , Storage_Start_Time_at_2_8))/3600;" ) sql_connect.execute(query_auto) processing_table = sql_connect.rowcount ############################################################################################################################33 ## after contents have been written to mysql, write name of file to file-processing table to show it was done validation_file_location = 's3://' + folder_name + '/' + new_key validation_result_location = 'validation_result_location' validation_notification_arn = 'validation_notification_arn' query_auto = ( "INSERT INTO `" + file_dbname + "`.`table_file_validator` " " (orig_file_id, validation_file_location, validation_status, validation_notification_arn, validation_result_location, validation_date)" "VALUE ('" + str(org_file_id) + "','" + validation_file_location + "','" + validation_status + "','" + validation_notification_arn + "','" + validation_result_location + "'," + "CONVERT_TZ(CURRENT_TIMESTAMP(), '+00:00', '-05:00'))") sql_connect.execute( query_auto ) #mysql command that will update the file-processor table processing_table = sql_connect.rowcount validation_status_list.append( validation_status) # record each validation status validation_file_location_list.append( validation_file_location ) # record each validation file location if processing_table > 0: print("### " + filename + " has been processed, there were " + str(empty_count) + " blank lines found in the file") else: print(zip_file_name + 'is not a zip file.') ############################################################################################################################33 ## after all files have been processed, update file remover to indicate it has been done table_sql_str = ( "UPDATE `" + file_dbname + "`.table_file_remover " "Set file_status = 'FILE_Processed'" "Where file_status = 'COPY_SUCCESSFUL' and file_location = '" + full_bucket_name + "'") sql_connect.execute( table_sql_str ) #mysql command that changes the file-action flag so file wont be used again processing_table = sql_connect.rowcount ######publish message to sns topic file_submitted_by = "NULL" job_table_name = 'table_file_remover' #get the prefix from the database exe = "SELECT * FROM " + job_table_name + " WHERE file_id=" + str( org_file_id) sql_connect.execute(exe) sqlresult = sql_connect.fetchone() file_submitted_by = "'" + sqlresult[9] + "'" # getting the validation time stamp eastern = dateutil.tz.gettz('US/Eastern') timestampDB = datetime.datetime.now( tz=eastern).strftime('%Y-%m-%d %H:%M:%S') org_file_name = zip_file_name result = { 'org_file_id': str(org_file_id), 'file_status': 'FILE_Processed', 'validation_file_location_list': validation_file_location_list, 'validation_status_list': validation_status_list, 'full_name_list': full_name_list, 'validation_date': timestampDB, 'file_submitted_by': file_submitted_by, 'previous_function': "prevalidator", 'org_file_name': org_file_name } TopicArn_Success = ssm.get_parameter( Name="TopicArn_Success", WithDecryption=True).get("Parameter").get("Value") TopicArn_Failure = ssm.get_parameter( Name="TopicArn_Failure", WithDecryption=True).get("Parameter").get("Value") response = sns_publisher(result, TopicArn_Success, TopicArn_Failure) #################################################################################################################### print('## All Files have been checked, Closing the connections ##') sql_connect.close() conn.commit() conn.close()
try: #@ _DISPLAY_METADATA_ print("Statement: %s" % stmt) # buffer cursor so that rowcount has usable value cursor = conn.cursor(buffered=True) cursor.execute(stmt) # metadata information becomes available at this point ... print("Number of rows: %d" % cursor.rowcount) if cursor.with_rows: ncols = len(cursor.description) else: ncols = 0 # no result set print("Number of columns: %d" % ncols) if ncols == 0: print("Note: statement has no result set") else: for i, col_info in enumerate(cursor.description): # print name, then other information name, type, _, _, _, _, nullable, flags = col_info print("--- Column %d (%s) ---" % (i, name)) print("Type: %d (%s)" % (type, FieldType.get_info(type))) print("Nullable: %d" % (nullable)) print("Flags: %d" % (flags)) cursor.close() #@ _DISPLAY_METADATA_ except mysql.connector.Error as e: print("Error code: %s" % e.errno) print("Error message: %s" % e.msg) conn.close()
def getdatatype(self, datatype): """recupere le type interne associe a un type cx_oracle""" nom = FieldType.get_info(datatype) # print(" getdatatype", datatype, nom, TYPES_A.get(nom, "T")) return nom
def __map_to_adapter_datatypes(self, datatypes): adapter_datatypes = [ self.MYSQL_TO_ADAPTER[FieldType.get_info(d)] for d in datatypes ] return adapter_datatypes
def getData(): print("Executing query..") mycursor = mydb.cursor() mycursor.execute(request.data) print("Done executing query..Retriving results..") cols = mycursor.description columns = [] index=0; for column in cols: mycol = {} mycol['name']=column[0] mytype = FieldType.get_info(column[1]) mycol['dataType']='string' print(mytype+"-"+column[0]) if mytype=='BLOB': mycol['dataType']='string' mycol['isDim']='true' if mytype=='VAR_STRING': mycol['dataType']='string' mycol['isDim']='true' if mytype=='DATE': mycol['dataType']='date' mycol['isDim']='true' if mytype=='TIME': mycol['dataType']='time' mycol['isDim']='true' if mytype=='DATETIME': mycol['dataType']='datetime' mycol['isDim']='true' if mytype=='TIMESTAMP': mycol['dataType']='timestamp' mycol['isDim']='true' if mytype=='DOUBLE': mycol['dataType']='number' mycol['isFact']='true' if mytype=='LONG': mycol['dataType']='number' mycol['isFact']='true' if mytype=='NEWDECIMAL': mycol['dataType']='number' mycol['isFact']='true' mycol['index']=index index=index+1 columns.append(mycol) rows = [] for row in mycursor: mycols=[] for col in row: if isinstance(col,datetime.date): mycols.append(str(col)) else: mycols.append(col) print(mycols) rows.append(mycols) print("Done retriving results..returning..") mycursor.close() data = {} data['rows']=rows data['columns']=columns print(data) return json.dumps(data)