SQLAlchemy Postgres On Conflict Do Update “can’t adapt type ‘method'”

Some time ago I encountered an error in my python code interacting with a Postgresql database:

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

The above exception was the direct cause of the following exception:
...
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'method'
[SQL: INSERT INTO common_platform_enumeration (created_on, changed_on, title, part, vendor, product, version, update, edition, language, sw_edition, target_sw, target_hw, other, created_by_fk, changed_by_fk) VALUES (%(created_on_m0)s, %(changed_on_m0)s, %(title_m0)s, %(part_m0)s, %(vendor_m0)s, %(product_m0)s, %(version_m0)s, %(update_m0)s, %(edition_m0)s, %(language_m0)s, %(sw_edition_m0)s, %(target_sw_m0)s, %(target_hw_m0)s, %(other_m0)s, %(created_by_fk_m0)s, %(changed_by_fk_m0)s) ON CONFLICT (part, vendor, product, version, update, edition, language, sw_edition, target_sw, target_hw, other) DO UPDATE SET changed_on = %(param_1)s, title = excluded.title, version = excluded.version, update = %(param_2)s, edition = excluded.edition, language = excluded.language, sw_edition = excluded.sw_edition, target_sw = excluded.target_sw, target_hw = excluded.target_hw, other = excluded.other, changed_by_fk = %(param_3)s RETURNING common_platform_enumeration.id]
[parameters: {'created_on_m0': datetime.datetime(2021, 2, 8, 9, 40, 9, 550747), 'changed_on_m0': datetime.datetime(2021, 2, 8, 9, 40, 9, 550747), 'title_m0': '$0.99 Kindle Books project $0.99 Kindle Books (aka com.kindle.books.for99) for android 6.0', 'part_m0': 'a', 'vendor_m0': '\\$0.99_kindle_books_project', 'product_m0': '\\$0.99_kindle_books', 'version_m0': '6', 'update_m0': '*', 'edition_m0': '*', 'language_m0': '*', 'sw_edition_m0': '*', 'target_sw_m0': 'android', 'target_hw_m0': '*', 'other_m0': '*', 'created_by_fk_m0': 1, 'changed_by_fk_m0': 1, 'param_1': datetime.datetime(2021, 2, 8, 9, 40, 9, 550747), 'param_2': <bound method ColumnCollection.update of <sqlalchemy.sql.base.ImmutableColumnCollection object at 0x7faeaa4f8380>>, 'param_3': 1}]

The relevant code was inserting Common Platform Enumeration data into the database table:

                insert_new = insert(cpe_table).values(cpe_chunk)
                insert_new = (
                    insert_new.on_conflict_do_update(
                        index_elements=['part', 'vendor', 'product', 'version', 'update', 'edition', 'language',
                                        'sw_edition', 'target_sw', 'target_hw', 'other'],
                        set_={
                            "title": insert_new.excluded.title,
                            "part": insert_new.excluded.part,
                            "vendor": insert_new.excluded.vendor,
                            "product": insert_new.excluded.product,
                            "version": insert_new.excluded.version,
                            "update": insert_new.excluded.update,
                            "edition": insert_new.excluded.edition,
                            "language": insert_new.excluded.language,
                            "sw_edition": insert_new.excluded.sw_edition,
                            "target_sw": insert_new.excluded.target_sw,
                            "target_hw": insert_new.excluded.target_hw,
                            "other": insert_new.excluded.other,
                            "changed_by_fk": service_user_id,
                            "changed_on": now,
                        }
                    )
                    .returning(cpe_table.c.id)
                )
                result = session.execute(insert_new)

I eventually tracked the error down to the column name, the “update” column is conflicting with the “update” method of the “excluded” ColumnCollection object (https://github.com/sqlalchemy/sqlalchemy/blob/496efe51262be77d73ed514c4de16cdc2da2d435/lib/sqlalchemy/sql/base.py#L1201)

The fix is to access the column value using the “get” method or sub-scripting instead:

                            "update": insert_new.excluded['get']