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