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)