Key Value Pairs Upload Function

[NeroCube]

Transfer

import mysql.connector
import pandas as pd

def upload_prediction(df, version="nero_elastic", host="your host", user="your user", password="your password", schema="coal_forecasting_model"):
    data = df.melt(id_vars=['event_date'], var_name='type', value_name='value')
    data['version'] = version

    if data is not None:
        db = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=schema
        )
        cursor = db.cursor()

        for _, row in data.iterrows(): 
            try:
                sql = f"INSERT INTO `prediction`(`event_date`, `type`, `value`, `version`) VALUES ('{row['event_date']}','{row['type']}',{row['value']},'{row['version']}')"
                cursor.execute(sql)
                db.commit()
            except Exception as e:
                if "1062 (23000): Duplicate entry" in str(e):
                    continue
                print(e)
                raise Exception
        db.close()

Recover

def read_sql(self, connection, table_name, feature_name): 
    sql = "SELECT DATE_FORMAT(event_date, '%Y-%m-%d') AS event_date, type, value FROM " + \
        table_name + " WHERE event_date >= '2010-01-01' AND type IN " + str(tuple(feature_name))
    df = pd.read_sql(sql, con = connection)
    df = df.pivot(index = "event_date", columns = "type", values = "value")
    df = df.reset_index(drop = False)
    return df

Connection

def db_open(self, database): 
    conn = mysql.connector.connect(
        host = self.host, 
        user = self.user, 
        password = self.password, 
        database = database, 
        ssl_disabled = True
        )
    return conn