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
Beispiel #2
0
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()
Beispiel #3
0
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