CODEWITHSUNDEEP
psycopg2psql
pythonpsql
pythonpsql

Reputation: 15

psycopg2 string composition error

I am doing an upsert. 

owner.sq_properties = ['name', 'place', 'email_address',                      'preferred_transport', 'note', 'address_line_one',                      'address_line_two', 'address_line_three', 'contact_one',                'contact_two', 'contact_three', 'gst_number', 'nickname']

statement:

sq = sql.SQL("insert into master.customer select * from public.customer on conflict (id) do update set({})=({})").format(sql.SQL(',').join(sql.Identifier(n) for n in owner.sq_properties),sql.SQL(',').join(sql.Identifier("excluded."+n) for n in owner.sq_properties))

when I execute:

print(sq.as_string(cursor))

output is :

insert into master.customer select * from public.customer on conflict (id) do update set ("name", "place", "email_address", "preferred_transport", "note", "address_line_one", "address_line_two", "address_line_three", "contact_one", "contact_two", "contact_three", "gst_number", "nickname") = ("excluded.name", "excluded.place", "excluded.email_address", "excluded.preferred_transport", "excluded.note", "excluded.address_line_one", "excluded.address_line_two", "excluded.address_line_three", "excluded.contact_one", "excluded.contact_two", "excluded.contact_three", "excluded.gst_number", "excluded.nickname")

error:

cursor.execute(sq)
psycopg2.ProgrammingError: column "excluded.name" does not exist
LINE 1: ...o", "contact_three", "gst_number", "nickname") = ("excluded..

What am I doing wrong?

Upvotes: 0

Views: 166

Answers (1)

pythonpsql
pythonpsql

Reputation: 15

Figured it out. excluded. should be an sql identifier.

joined =sql.SQL(',').join(sql.SQL('excluded.')+sql.Identifier(n) for n in owner.sq_properties)

corrected statement:

sql.SQL("insert into master.customer select * from public.customer on conflict (id) do update set ({}) = ({})      returning id").format(sql.SQL(',').join(sql.Identifier(n) for n in owner.sq_properties), joined)

Upvotes: 1

Related Questions