def load_manifest(json_data: dict, conn: pyodbc.Connection) -> Exception:
""" Use SQL to load records into the data structure
Args:
json_data: manifest in a Python dictionary
conn: database connection object
Returns:
true if creation is successful, otherwise returns the exception
"""
cursor = conn.cursor()
fieldnamesql = "INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival)"
today = date.today()
mydate = today.strftime("%Y-%m-%d")
try:
items = json_data.get("items")
for item in items:
valsql = fieldnamesql + " VALUES ("
valsql += "'" + item.get("vendor_id") + "', "
valsql += "'" + item.get("vendor_product_code") + "', "
valsql += str(item.get("quantity_kg")) + ", "
valsql += "'" + mydate + "')"
print("Inserting: " + valsql)
cursor.execute(valsql)
conn.commit()
except Exception as exp:
return exp
return None
def transfer_data(source_db_cursor: pyodbc.Cursor, dest_db_cursor: pyodbc.Cursor, dest_db_conn: pyodbc.Connection):
''' Extracts users data from source database and stores them in destination database. '''
print('Extracting users data from source database.')
source_db_cursor.execute('SELECT * FROM users')
rows = source_db_cursor.fetchall()
print('Transferring users data to destination database.')
for row in rows:
dest_db_cursor.execute(SQL_INSERT_DATA, (row.id, row.name, row.city))
print(f'{len(rows)} rows transferred\n')
dest_db_conn.commit()
def update_dolphin_database(dbname: pyodbc.Connection, updatesql: str) -> int:
dolphincursor = dbname.cursor()
try:
if DolphinTestModeEnabled:
logging.info(
"Dolphin Test mode is enabled. We would update Dolphin with {0}"
.format(updatesql))
else:
dolphincursor.execute(updatesql)
dbname.commit()
except Exception as e:
logging.error(
"Dolphin database update failed with {0}, sql = {1}".format(
e, updatesql))
dbname.rollback()
return -1
dolphincursor.close()
return 0
def insert_registros_database(conn: db.Connection,
nm_database: str,
nm_tabela: str,
lt_campos_insert: list,
lt_valores_insert: list,
verbose: bool = False,
log: bool = False,
lt_registros_log: list = None):
""" Manipula registros de INSERT no banco de dados especificado.
Args:
Returns:
Raises:
IOError:
TODOs: (a) implementar o controle de excecoes.
(b) implementar o insert de maneira generica
"""
if conn != None:
if verbose == True:
print("[INFO]: a conexão foi encontrada.")
else:
if verbose == True:
print("[ERRO]: a conexão não foi encontrada.")
return
# formata a saida do metodo em erros e sucesso
lt_msg_erros = []
dict_return_metodo: dict = {}
str_clausula_insert = "INSERT INTO " + nm_database + ".dbo." + nm_tabela
str_clausula_insert += monta_clausula_insert_by_array(lt_campos_insert)
str_clausula_values = 'VALUES ('
len_lt_valores_insert = len(lt_valores_insert)
for idx, valor in enumerate(lt_valores_insert):
if isinstance(valor, (str)):
# tratamento de caracteres especiais
valor = valor.replace('\\', '')
valor = valor.replace('\'', '')
str_clausula_values += "{}{}{}".format('\'', valor, '\'')
elif isinstance(valor, (int)):
str_clausula_values += "{}".format(valor)
elif isinstance(valor, (float)):
str_clausula_values += "{}".format(valor)
if idx < (len_lt_valores_insert - 1):
str_clausula_values += ","
# formata a string de valores do insert
str_clausula_values += ')'
str_clausula_values = str_clausula_values.replace('[', '')
str_clausula_values = str_clausula_values.replace(']', '')
str_insert_formatado = str_clausula_insert + str_clausula_values
try:
cursor = conn.cursor()
cursor.execute(str_insert_formatado)
conn.commit()
dict_return_metodo[
pgcst.SUCESSO_KEY] = "o registro foi inserido com sucesso."
if verbose == True:
print("[INFO]: o registro foi inserido com sucesso.")
print("[INFO]: {}".format(str_insert_formatado))
except db.DataError as de:
msg_except = "[ERRO]: \n {}".format(str_insert_formatado)
lt_msg_erros.append(msg_except)
print(msg_except)
msg_except = "[EXCEPT]: database_util.insert_registros_database() -> {}".format(
de)
lt_msg_erros.append(msg_except)
print(msg_except)
if len(lt_msg_erros) > 0:
dict_return_metodo[pgcst.ERRO_KEY] = lt_msg_erros
return dict_return_metodo