import sys
from os import remove
import psycopg2
from psycopg2 import OperationalError, errorcodes, errors


def show_psycopg2_exception(err):
    """
    Handles and parses psycopg2 exceptions.
    """
    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()
    # get the line number when exception occurred
    line_n = traceback.tb_lineno
    # print the connect() error
    print("\npsycopg2 ERROR:", err, "on line number:", line_n)
    print("psycopg2 traceback:", traceback, "-- type:", err_type)
    # psycopg2 extensions.Diagnostics object attribute
    print("\nextensions.Diagnostics:", err.diag)
    # print the pgcode and pgerror exceptions
    print("pgerror:", err.pgerror)
    print("pgcode:", err.pgcode, "\n")


def connect():
    """
    The function will attempt to establish a connection with the database and return the connection if successful.
    If unsuccessful the error will be shown.
    """
    connection = None
    try:
        print("Establishing a connection with the database...")
        connection = psycopg2.connect(
            database="d8i0j90u4q6n4k",
            user="vqnngkqzkiedsf",
            password="<PASSWORD>",
            host="ec2-54-155-87-214.eu-west-1.compute.amazonaws.com",
            port="5432"
        )
        print("Connection successful!")
    except OperationalError as err:
        # passing exception to function
        show_psycopg2_exception(err)
        # set the connection to 'None' in case of error
        connection = None

    return connection


def copy_from_df(connection, data, table):
    """
    This function will upload the data from a csv file into a postgresql table.
    The connection, csv file and the table name need to be provided.
    If the function is unsuccessful the error will be shown.
    """
    string_execute = f"{data}.csv"
    with open(string_execute) as csv:
        cursor = connection.cursor()
        try:
            cursor.copy_from(csv, table, sep=";")
            connection.commit()
            print("Data uploaded successfully")
            cursor.close()
            connection.close()
        except (Exception, psycopg2.DatabaseError) as err:
            show_psycopg2_exception(err)
            cursor.close()


def make_table(keyword: str):
    """

    """
    connection = connect()
    connection.autocommit = True
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DROP TABLE IF EXISTS " + keyword)
            string_execute = f'''CREATE TABLE {keyword} (
            id SERIAL PRIMARY KEY,
            title varchar(100) DEFAULT 'brand unknown',
            price varchar(50),
            item_link text NOT NULL,
            image_link text NOT NULL,
            type INT,
            CONSTRAINT item_type
                FOREIGN KEY (type)
                    REFERENCES types (id)
                    ON DELETE CASCADE
            );'''
            cursor.execute(string_execute)
            print(f"Table {keyword} created successfully")
            cursor.close()
            connection.close()

        except OperationalError as err:
            # pass exception to function
            show_psycopg2_exception(err)
            # set the connection to 'None' in case of error
            connection = None


def item_types_table(type1: str, type2: str, type3: str):
    connection = connect()
    connection.autocommit = True
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DROP TABLE IF EXISTS types")
            cursor.execute('''CREATE TABLE types (
            id SERIAL PRIMARY KEY,
            type varchar(100) 
            );''')
            print("Table types created successfully")

            string_execute = f'''
            INSERT INTO types (type) VALUES (\'{type1}\');
            INSERT INTO types (type) VALUES (\'{type2}\');
            INSERT INTO types (type) VALUES (\'{type3}\');
            '''
            cursor.execute(string_execute)
            cursor.close()
            connection.close()

        except OperationalError as err:
            # pass exception to function
            show_psycopg2_exception(err)
            # set the connection to 'None' in case of error
            connection = None

import sys
from os import remove
import psycopg2
from psycopg2 import OperationalError, errorcodes, errors


def show_psycopg2_exception(err):
    """
    Handles and parses psycopg2 exceptions.
    """
    # get details about the exception
    err_type, err_obj, traceback = sys.exc_info()
    # get the line number when exception occurred
    line_n = traceback.tb_lineno
    # print the connect() error
    print("\npsycopg2 ERROR:", err, "on line number:", line_n)
    print("psycopg2 traceback:", traceback, "-- type:", err_type)
    # psycopg2 extensions.Diagnostics object attribute
    print("\nextensions.Diagnostics:", err.diag)
    # print the pgcode and pgerror exceptions
    print("pgerror:", err.pgerror)
    print("pgcode:", err.pgcode, "\n")


def connect():
    """
    The function will attempt to establish a connection with the database and return the connection if successful.
    If unsuccessful the error will be shown.
    """
    connection = None
    try:
        print("Establishing a connection with the database...")
        connection = psycopg2.connect(
            database="d8i0j90u4q6n4k",
            user="vqnngkqzkiedsf",
            password="<PASSWORD>",
            host="ec2-54-155-87-214.eu-west-1.compute.amazonaws.com",
            port="5432"
        )
        print("Connection successful!")
    except OperationalError as err:
        # passing exception to function
        show_psycopg2_exception(err)
        # set the connection to 'None' in case of error
        connection = None

    return connection


def copy_from_df(connection, data, table):
    """
    This function will upload the data from a csv file into a postgresql table.
    The connection, csv file and the table name need to be provided.
    If the function is unsuccessful the error will be shown.
    """
    string_execute = f"{data}.csv"
    with open(string_execute) as csv:
        cursor = connection.cursor()
        try:
            cursor.copy_from(csv, table, sep=";")
            connection.commit()
            print("Data uploaded successfully")
            cursor.close()
            connection.close()
        except (Exception, psycopg2.DatabaseError) as err:
            show_psycopg2_exception(err)
            cursor.close()


def make_table(keyword: str):
    """

    """
    connection = connect()
    connection.autocommit = True
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DROP TABLE IF EXISTS " + keyword)
            string_execute = f'''CREATE TABLE {keyword} (
            id SERIAL PRIMARY KEY,
            title varchar(100) DEFAULT 'brand unknown',
            price varchar(50),
            item_link text NOT NULL,
            image_link text NOT NULL,
            type INT,
            CONSTRAINT item_type
                FOREIGN KEY (type)
                    REFERENCES types (id)
                    ON DELETE CASCADE
            );'''
            cursor.execute(string_execute)
            print(f"Table {keyword} created successfully")
            cursor.close()
            connection.close()

        except OperationalError as err:
            # pass exception to function
            show_psycopg2_exception(err)
            # set the connection to 'None' in case of error
            connection = None


def item_types_table(type1: str, type2: str, type3: str):
    connection = connect()
    connection.autocommit = True
    if connection is not None:
        try:
            cursor = connection.cursor()
            cursor.execute("DROP TABLE IF EXISTS types")
            cursor.execute('''CREATE TABLE types (
            id SERIAL PRIMARY KEY,
            type varchar(100) 
            );''')
            print("Table types created successfully")

            string_execute = f'''
            INSERT INTO types (type) VALUES (\'{type1}\');
            INSERT INTO types (type) VALUES (\'{type2}\');
            INSERT INTO types (type) VALUES (\'{type3}\');
            '''
            cursor.execute(string_execute)
            cursor.close()
            connection.close()

        except OperationalError as err:
            # pass exception to function
            show_psycopg2_exception(err)
            # set the connection to 'None' in case of error
            connection = None

Handles and parses psycopg2 exceptions. # get details about the exception # get the line number when exception occurred # print the connect() error # psycopg2 extensions.Diagnostics object attribute # print the pgcode and pgerror exceptions The function will attempt to establish a connection with the database and return the connection if successful.
     If unsuccessful the error will be shown. # passing exception to function # set the connection to 'None' in case of error This function will upload the data from a csv file into a postgresql table.
     The connection, csv file and the table name need to be provided.
     If the function is unsuccessful the error will be shown. CREATE TABLE {keyword} (
             id SERIAL PRIMARY KEY,
             title varchar(100) DEFAULT 'brand unknown',
             price varchar(50),
             item_link text NOT NULL,
             image_link text NOT NULL,
             type INT,
             CONSTRAINT item_type
                 FOREIGN KEY (type)
                     REFERENCES types (id)
                     ON DELETE CASCADE
             ); # pass exception to function # set the connection to 'None' in case of error CREATE TABLE types (
             id SERIAL PRIMARY KEY,
             type varchar(100) 
             ); INSERT INTO types (type) VALUES (\'{type1}\');
             INSERT INTO types (type) VALUES (\'{type2}\');
             INSERT INTO types (type) VALUES (\'{type3}\'); # pass exception to function # set the connection to 'None' in case of error