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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
