psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block, dont know how to fix it

Question

I am trying to insert data into my database using psycopg2 and I get this weird error. I tried some things but nothing works. This is my code:

def insert_transaction():
global username
now = datetime.now()
date_checkout = datetime.today().strftime('%d-%m-%Y')
time_checkout = now.strftime("%H:%M:%S")

username = "Peter1"

connection_string = "host='localhost' dbname='Los Pollos Hermanos' user='postgres' password='******'"
conn = psycopg2.connect(connection_string)
cursor = conn.cursor()
try:
    query_check_1 = """(SELECT employeeid FROM employee WHERE username = %s);"""
    cursor.execute(query_check_1, (username,))
    employeeid = cursor.fetchone()[0]
    conn.commit()
except:
    print("Employee error")

try:
    query_check_2 = """SELECT MAX(transactionnumber) FROM Transaction"""
    cursor.execute(query_check_2)
    transactionnumber = cursor.fetchone()[0] + 1
    conn.commit()
except:
    transactionnumber = 1

""""---------INSERT INTO TRANSACTION------------"""


query_insert_transaction = """INSERT INTO transactie (transactionnumber, date, time, employeeemployeeid)
                    VALUES (%s, %s, %s, %s);"""
data = (transactionnumber, date_checkout, time_checkout, employeeid)
cursor.execute(query_insert_transaction, data)
conn.commit()
conn.close()

this is the error:

", line 140, in insert_transaction
cursor.execute(query_insert_transaction, data) psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Answer 1

The error message means that one of the preceding SQL statements has resulted in an error. If an exception occurs while executing an SQL statement you need to call the connection's rollback method (conn.rollback()) to reset the transaction's state. PostgreSQL will not permit further statement execution otherwise.

Ideally you want to record the actual error for later analysis, so your code ought to be structured like this:

try:
    cursor.execute(sql, values)
    conn.commit()
except Exception as e:
    print(f'Error {e}')
    print('Anything else that you feel is useful')
    conn.rollback()