Edwin Ruiz

				
					import mysql.connector
import pandas as pd

SQL_SELECT_CUSTOMERS ="SELECT * FROM customers;"
SQL_INSERT_CUSTOMER ="INSERT INTO customers (customer_id, first_name, last_name, email) VALUES (%s, %s, %s, %s);"
SQL_INSERT_ADDRESS ="INSERT INTO address (address, geometry, zip_code,latitude,longitude, customers_customer_id)VALUES ( %s, %s, %s, %s,%s,%s);"
SQL_INSERT_ORDER = "INSERT INTO orders (order_id, order_date, order_time, order_price, customers_customer_id) VALUES (%s, %s, %s, %s, %s);"

class vancouver_sales:
    def __init__(self,**arguments):
        print(arguments)
        self.conn = mysql.connector.connect(
            host=arguments['host'],
            user=arguments['user'],
            password=arguments['password'],
            database=arguments['database']
        )

    def test_connection(self):
        try:
            self.conn.ping(reconnect=True)  # Try to ping the database to check the connection status
            print("Database connection is active.")
        except mysql.connector.Error as e:
            print(f"Error: {e}")
            print("Database connection is not active.")

    def insert_customer_pandas(self, df):
        cursor = self.conn.cursor()
        for index, row in df.iterrows():
            cursor.execute(SQL_INSERT_CUSTOMER, (row['customer_id'], row['first_name'], row['last_name'], row['email']))
        self.conn.commit()
        cursor.close()

    def read_customers(self):
        cursor = self.conn.cursor()
        cursor.execute(SQL_SELECT_CUSTOMERS)
        print(cursor)
        print(cursor.fetchall())
        cursor.close()

    def insert_address_pandas(self, df):
        cursor = self.conn.cursor()
        for index, row in df.iterrows():
            cursor.execute(SQL_INSERT_ADDRESS, (row['address'], row['geometry'], row['zip_code'],row['latitude'],row['longitude'],row['customer_id']))
        self.conn.commit()
        cursor.close()

    def insert_order_pandas(self, df):
        cursor = self.conn.cursor()
        for index, row in df.iterrows():
            cursor.execute(SQL_INSERT_ORDER, (row['order_id'], row['order_date'], row['order_time'], row['order_price'], row['customer_id']))
        self.conn.commit()
        cursor.close()


def main():
    df=pd.read_csv("Path")
    df.fillna('na',inplace=True)
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': '{YOUR_PASSWORD}',
        'database': 'vancouver_sales'
                }
                
#Creating the instances
    vancouver_sales_instance = vancouver_sales(**db_config)
    vancouver_sales_instance.test_connection()
    #vancouver_sales_instance.insert_customer_pandas(df)
    #vancouver_sales_instance.insert_address_pandas(df)
    vancouver_sales_instance.insert_order_pandas(df)


if __name__ == "__main__":
    main()