import os import pymysql from typing import List class DatabaseHandler: def __init__(self): self.host = os.getenv("DB_HOST") self.user = os.getenv("DB_USER") self.password = os.getenv("DB_PASSWORD") self.database = os.getenv("DB_NAME") self.connection = None def connect(self): try: self.connection = pymysql.connect( host=self.host, user=self.user, password=self.password, database=self.database ) except pymysql.MySQLError as e: print(f"Error connecting to MySQL: {e}") def close(self): if self.connection: self.connection.close() def create_table_if_not_exists(self, table_name: str, columns: List[str]): if not self.connection: self.connect() column_definitions = [] for col in columns: if col == "isDrillPaid": column_definitions.append(f"`{col}` TINYINT(1)") elif col == "phaseStartDt": column_definitions.append(f"`{col}` VARCHAR(10)") else: column_definitions.append(f"`{col}` VARCHAR(255)") columns_sql = ", ".join(column_definitions) query = f""" CREATE TABLE IF NOT EXISTS `{table_name}` ( {columns_sql}, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); """ try: with self.connection.cursor() as cursor: cursor.execute(query) self.connection.commit() except pymysql.MySQLError as e: print(f"Error creating table: {e}") raise def insert_record(self, table_name: str, record: dict): if not self.connection: self.connect() columns = ", ".join([f"`{key}`" for key in record.keys()]) placeholders = ", ".join(["%s"] * len(record)) query = f"INSERT INTO `{table_name}` ({columns}) VALUES ({placeholders});" try: with self.connection.cursor() as cursor: cursor.execute(query, list(record.values())) self.connection.commit() except pymysql.MySQLError as e: print(f"Error inserting record: {e}") raise