import psycopg2

def main():

    """- Employee table. Here to show column list
    CREATE TABLE employee (
    fname character varying(15) NOT NULL,
    minit character varying(1),
    lname character varying(15) NOT NULL,
    ssn character(9) NOT NULL,
    bdate date,
    address character varying(50),
    sex character(1),
    salary numeric(10,2),
    superssn character(9),
    dno integer
    );
    """

    try:
        firstName = input("Please Enter the employee's First Name: ")
        minit = input("Please Enter the employee's middle initial: ")
        lastName = input("Please Enter the employee's Last Name: ")
        ssn = input("Please Enter the employee's Ssn: ")
        dno = input("lease Enter the employee's Department Number: ")

        dno_int = int(dno)

        # Add the additional data feilds here

    except (IOError, ValueError):
        print("Error reading input")

    print("\n****Inserting a New Employee*****\n");

    # The query template.
    # The list returns parentheses (...), so we don't need them for VALUES(...)
    sql = """INSERT INTO employee(fname, minit,lname,ssn,dno) VALUES%s;"""

    # Create a row list to insert
    # This is where you will need to update the code to include
    # the data entered by the user.

    insert_employee_list = (firstName, minit, lastName, ssn, dno)

    conn = None
    try:
       # Connect to database
       conn = psycopg2.connect(dbname='esadgro2_apps_p5', 
                               user='esadgro2_apps', 
                               host='127.0.0.1', 
                               password='220069300')
       # Create database cursor
       cur = conn.cursor()

       # Execute statement allowing execute to sanitize 
       cur.execute(sql,(insert_employee_list,))

       # Commit changes
       conn.commit()

       print("Query Executed Successfully...exiting")

    except (Exception, psycopg2.DatabaseError) as error:
       print("Error connecting to database: ", error)
    # If it is successful or it fails, always close connection
    finally:
        if(conn):
            cur.close()
            conn.close()
            print("Connection closed")



if __name__ == "__main__":
    main()