Spaces:
Sleeping
Sleeping
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 |