File size: 2,343 Bytes
233222c
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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