Integration and Analytics Data with Oracle and Python

Main Tech.

• Python and Libs

python = "^3.10"
cx-Oracle = "^8.3.0"
python-dotenv = "^0.21.0"
pandas = "^1.5.2"
openpyxl = "^3.0.10"
faker = "^15.3.4"

• Oracle Database

Database = "Oracle Database 21c XE"

SQL Categories:

• DDL – Data Definition Language (CREATE, DROP, ALTER...)
• DQL – Data Query Language (SELECT)
• DML – Data Manipulation Language (INSERT, UPDATE, DELETE...)
• DCL – Data Control Language (GRANT, REVOKE)
• TCL - Transaction Control Language (COMMIT, ROLLBACK)

Tables created in Python:

• tb_itens_notas_fiscais
• tb_notas_fiscais
• tb_clientes
• tb_produtos
• tb_vendedores

Schema:

---

How to use:

1. Install and Create Oracle Database 21c xe

• (nov/2022) https://www.oracle.com/br/database/technologies/xe-downloads.html

2. Create .env file

# DSN
host=<host>
port=<port>
sid=<sid>

# CONNECTION
user=<user>
password=<password>

3. Python (cx_Oracle)

Schema creation and Data load

• Run Main: (Start a interactive menu)

poetry run py main.py

• Option 1 - DDL:

  • Run automatically create_schema() from criacao_esquema.py

• Opcção 2 - DML:

  • Run automatically load_registrations() from carga_cadastros.py

• Opcção 3 - DML:

  • Opcção 0 - Origem: Load a Origem data (with 87k invoice´s data and 200k items invoice

    1. Invoce / Notas Fiscais

       • Run automatically load_invoce() from carga_notas_fiscais.py

    2. Items Invoce / Itens Notas Fiscais

       • Run automatically load_items_invoce() from carga_itens_notas_fiscais.py

  • Opcção 1 - Today: Load a Daily data (with random invoice´s data and items invoice

    1. Fake Data

       • Run automatically FakerDailySalesData from faker_vendas_diaria.py

    2. Invoce / Notas Fiscais

       • Run automatically load_invoce() from carga_notas_fiscais.py

    3. Items Invoce / Itens Notas Fiscais

       • Run automatically load_items_invoce() from carga_itens_notas_fiscais.py

help and knowledge

• Python Database API Specification v2.0 https://peps.python.org/pep-0249/
• Python cx_Oracle https://oracle.github.io/python-cx_Oracle/
• Python Pandas https://pandas.pydata.org/docs/
• Python Faker https://faker.readthedocs.io/en/master/