utkarsh134's picture
for pushin
233222c
Raw History Blame
2.34 kB
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