I am running the following SQL query using Python and psycopg2 and is getting errors. SQL query: Errors: Variables and their values Python Functions: How should i fix this? Answer I solved this issue by converting the values of in the list ['timestamps'] to str from datetime.

psycopg2: syntax error at or near “offset”, datetime object

I am running the following SQL query using Python and psycopg2 and is getting errors.

SQL query:

UPDATE consumers SET is_valid = False WHERE system_id = 'SAMPLEID607' AND time IN (datetime.datetime(2020, 7, 21, 17, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 7, 0, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 22, 55, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)));

Errors:

syntax error at or near "offset"
LINE 1: ...7, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330...
                                                             ^
ERROR:root:SyntaxError: Error in invalidate_values function,
Traceback (most recent call last):
  File "/location...../validation.py", line 246, in invalidate_values
    CURSOR.execute(query)
psycopg2.errors.SyntaxError: syntax error at or near "offset"
LINE 1: ...7, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330...
                                                             ^

UPDATE mqtt_consumer SET is_valid = True WHERE created_at >= '2020-08-22 12:50:26.935465';
ERROR:root:SyntaxError: Error in invalidate_values function, InFailedSqlTransaction: Error in validate_values function,
Traceback (most recent call last):
  File "/location....../validation.py", line 265, in validate_values
    CURSOR.execute(query)
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Variables and their values

SYSTEMS = {'SAMPLEID607': [{'timestamps': [datetime.datetime(2020, 7, 21, 17, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 7, 0, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 22, 55, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None))], 'values': [58788430000.0, 58788430000.0, 9.697836e-38]}]}

LAST_UNVALIDATED = '2020-08-22 12:50:26.935465'

Python Functions:

def invalidate_values():
    for k, v in SYSTEMS.items():
        query = '''UPDATE consumers SET is_valid = False WHERE system_id = '{}' AND time IN {};'''.format(k, tuple(v[0]['timestamps']))
        CURSOR.execute(query)
    CONNECTION.commit()
    return 1

def validate_values():
    # validate all the rest of systems based on LAST_UNVALIDATED date
    query = "UPDATE consumers SET is_valid = True WHERE created_at >= '{}';".format(LAST_UNVALIDATED)
    CURSOR.execute(query)
    CONNECTION.commit()
    return 1

How should i fix this?

Answer

I solved this issue by converting the values of in the list ['timestamps'] to str from datetime.

Advertisement

Answer