Source code for local_sql

import mysql.connector
from mysql.connector import errorcode

# Database configuration
DB_NAME = 'hg38'
TABLE_NAME = 'refGene'

# Connect to MySQL server
try:
    conn = mysql.connector.connect(
        host='localhost',
        user='genome',
        password='password'
    )
    cursor = conn.cursor()

    # Create the database (if not exists)
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME}")
    conn.database = DB_NAME

    cursor.execute(f"USE {DB_NAME}")

    # Read the SQL file
    with open("primertool/ucsc-local/refGene.sql", "r") as sql_file:
        sql_statements = sql_file.read().split(";")

    # Execute each statement
    for statement in sql_statements:
        if statement.strip():  # Skip empty statements
            cursor.execute(statement)

    cursor.execute(f"DESCRIBE {TABLE_NAME}")
    query_result = cursor.fetchall()
    print(query_result)
    print(f"Database '{DB_NAME}' and table '{TABLE_NAME}' created successfully!")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Access denied. Check your username and password.")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print(f"Database '{DB_NAME}' does not exist.")
    else:
        print(err)
finally:
    cursor.close()
    conn.close()


def fetch_db_table(db_name, table_name: str):

    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='genome',
            password='password'
        )
        cursor = conn.cursor()

        # Create the database (if not exists)
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME}")
        conn.database = DB_NAME

        cursor.execute(f"USE {DB_NAME}")

        # Read the SQL file
        with open("primertool/ucsc-local/refGene.sql", "r") as sql_file:
            sql_statements = sql_file.read().split(";")

        # Execute each statement
        for statement in sql_statements:
            if statement.strip():  # Skip empty statements
                cursor.execute(statement)

        cursor.execute(f"DESCRIBE {TABLE_NAME}")
        query_result = cursor.fetchall()
        print(query_result)
        print(f"Database '{DB_NAME}' and table '{TABLE_NAME}' created successfully!")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Access denied. Check your username and password.")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print(f"Database '{DB_NAME}' does not exist.")
        else:
            print(err)
    finally:
        cursor.close()
        conn.close()