docs.facundoitest.space

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Saving BCRA variables on MariaDB database ======

This application serves as an automated data collection tool, enabling users to retrieve BCRA statistics and store them in a local database for further analysis or usage.

==== SQL Command: ====

The SQL command creates a table named "tabla_bcra" in a MySQL database. This table is designed to store data related to BCRA (Banco Central de la República Argentina) statistics, with fields for an auto-incrementing primary key, a variable ID, a date, and a decimal value.

<code sql>
CREATE TABLE tabla_bcra (
    id INT AUTO_INCREMENT PRIMARY KEY,
    idvariable INT NOT NULL,
    fecha DATE,
    valor DECIMAL(20, 5)
);
</code>

==== Python App (main.py): ====

The Python script is an application designed to fetch data from the BCRA API and store it in the MySQL database created with the aforementioned SQL command. It utilizes the mariadb library to establish a connection to the MySQL database and the requests library to make HTTP requests to the BCRA API. The script retrieves JSON data from the API, processes it, and inserts relevant information into the MySQL database table. Additionally, it includes error handling to manage potential issues with the API request or JSON decoding.

<code python: main.py>
import mariadb
import requests
import json
import datetime

# Function to convert date string to MySQL date format
def convert_date(date_str):
    # Parse the date string assuming it's in 'DD/MM/YYYY' format
    date_obj = datetime.datetime.strptime(date_str, '%d/%m/%Y')
    # Format the date object into 'YYYY-MM-DD' format
    return date_obj.strftime('%Y-%m-%d')

# Conexión a la base de datos MySQL
db = mariadb.connect(
    host="localhost",
    port=3306,
    user="********",
    password="**********",
    database="BCRAstats"
)

cursor = db.cursor()

# URL de la API de Estadísticas del BCRA
url = "https://api.bcra.gob.ar/estadisticas/v1/principalesvariables"

try:
    # Realiza la solicitud GET a la API
    response = requests.get(url, verify=False)

    # Verifica que la respuesta sea exitosa (código 200)
    if response.status_code == 200:
        # Convierte la respuesta JSON en un diccionario
        data = json.loads(response.text)

        # Itera sobre los datos y ejecuta consultas INSERT
        for item in data["results"]:
            id_bcra = item["idVariable"]
            # Inside the loop where you iterate over data["results"]
            valor_bcra_cleaned = item["valor"].replace('.', '')  # Remove dots (thousands separators)
            valor_bcra_cleaned = valor_bcra_cleaned.replace(',', '.')  # Replace commas with dots (decimal separators)
            valor_bcra_truncated = round(float(valor_bcra_cleaned), 5)  # Round to 5 decimal places and convert to float            fecha_bcra_formatted = convert_date(item["fecha"])

            query = "INSERT INTO tabla_bcra (idvariable, fecha, valor) VALUES (?, ?, ?)"
            values = (id_bcra, fecha_bcra_formatted, valor_bcra_truncated)
            #print(values)
            cursor.execute(query, values)

        db.commit()  # Confirmar los cambios
        print("Datos guardados en la base de datos")
    else:
        print(f"Error en la respuesta: {response.status_code}")
except json.JSONDecodeError as e:
    print(f"Error al decodificar JSON: {e}")
except requests.RequestException as e:
    print(f"Error en la solicitud HTTP: {e}")
finally:
    # Cierra la conexión
    db.close()
</code>

==== Results ====

{{:estadisticasbcra.jpg?nolink|}}

Querying the database from a Grafana instance produces something like this:

{{:variablesbcra_grafana.jpg?nolink|}}

{{:variablesbcra_grafana02.jpg?nolink|}}