On Tue, Aug 29, 2017 at 9:49 PM, Ken MacKenzie <[email protected]> wrote:
> I have a query I have constructed and I had to deal with a composite primary
> key to select items;
>
> q = s.query(cls)
> or_cond = []
> for x in id_batch:
>
> pkf = [text(f + "=='" + v + "'") for f,v in zip(cls.SQL_PK,
> x)]
> and_cond = (and_(*pkf))
> or_cond.append(and_cond)
>
>
> q = q.filter(or_(*or_cond)).delete(synchronize_session=False)
>
> cls.SQL_PK is a tuple of the primary key fields for the model described by
> class. This is a class method that is part of a inherited class to the
> model
>
> The current target is SQL Server. My concern is using text('field =
> 'value'), is that going to work for other DB targets like say postgres?
>
> The first round of doing this I tried using a
> tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the resulting
> SQL id not work in SSMS leading me to believe that SQL Server (or at least
> the version we are using) does not support tuples.
>
Textual SQL is not DB-neutral in general. Luckily, in this case you
shouldn't even need it. Try something like this:
for x in id_batch:
cols = [getattr(cls, colname) for colname in cls.SQL_PK]
pkf = [(col == v) for (col, v) in zip(cols, x)]
and_cond = and_(*pkf)
or_cond.append(and_cond)
ie. use "getattr" to retrieve the actual column property from the class.
Hope that helps,
Simon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
