psycopg2.ProgrammingError: can't adapt type 'DictRow'

Question

I want to use dict cursor in psycopg2:

self.__db_conn = psycopg2.extras.DictConnection("host=... dbname=...")

here is my query:

cur.execute('INSERT INTO scm_main.tbl_ack(ack_summary_id, ack_local_timestamp, ack_user_id) '
    'SELECT summary_id AS ack_summary_id, now() AS ack_local_timestamp, us.user_id AS ack_user_id '
    'FROM scm_main.tbl_summary AS s '
    'INNER JOIN scm_main.vu_usr_stn AS us ON (s.summary_station_id = us.station_axis_id) '
    'WHERE ((s.summary_id > (SELECT COALESCE(max(a.ack_summary_id),0) FROM scm_main.tbl_ack AS a WHERE a.ack_user_id = %(user_id)s)) '
    'AND (s.summary_company_specific_id <> 0) '
    'AND (us.user_name = %(user_name)s) AND (s.summary_timestamp < (now() - \'00:25:00\'::interval))) '
    'ORDER BY s.summary_id ASC', { 'user_id': self.__user_id, 'user_name': self.__company })

But it gives me this:

<class 'psycopg2.ProgrammingError'> exception: can't adapt type 'DictRow'

can anyone help?

Answer 1

Complex types, like DictRow (that is a full results row, indexable by column name) can't be automatically adapted to simple SQL types and psycopg is just telling you that. Your code seems fine so the error almost surely is that you wanted to put the result of a query in the self.__user_id and self.__company attributes but ended up putting the whole result set, i.e., the DictRow, in one or both of them. Check the code that fetches the results.