greetings, i have a project of transferring everything from an old API to a new one. the new API uses sqlalchemy ORM exclusively and my old used raw sql with the py-postgresql driver. i need help converting some of the more complex statements into ORM, or at least into a textual statement with some bound parameters. this statement is an "upsert" type that will update if exists, or insert if not. additionally, i'm dealing with an array input (python list) and i can't figure out how to bind subgroups using .params(). it is easily done in ORM with .in_() but i lack the experience in how to convert this set of queries into ORM.
given the following data: vid: 'CVE-2002-2443' dept: 'SecEng' subgroups: ['Archive', 'Desktop', 'DB', 'API'] status: 'n/a' and the following existing SQL statement (postgresql): WITH new_values (vid,dept,subgroups,status) AS ( VALUES (:vid, :dept, :subgroups, :status)), persub AS ( SELECT :vid as vid,dept,subgroup FROM sme_subgroups sg WHERE sg.dept = :dept AND sg.subgroup = ANY (:subgroups)), upsert AS ( UPDATE sme_vuln_status sv SET status = nv.status, published = now() at time zone 'UTC' FROM new_values nv WHERE sv.vid = nv.vid AND sv.dept = nv.dept AND sv.subgroup = ANY (nv.subgroups) RETURNING sv.*)INSERT INTO sme_vuln_status (vid,dept,subgroup,status,published) SELECT vid,dept,subgroup,:status,now() at time zone 'UTC' FROM persub WHERE NOT EXISTS (SELECT * FROM upsert up WHERE up.vid = persub.vid AND up.dept = persub.dept AND up.subgroup = persub.subgroup ) how can i properly do either of: - bind a list as a variable for a raw .from_statement().params(..., subgroups=<?>) or similar set of methods - build an ORM query matching this raw statement? thank you :) -d -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.