[sqlalchemy] Re: best way to query from a tuple of parameters
Yeah that almost works. I needed to add an and_ around each of the subclauses, otherwise the or_ breaks. abcd = or_(and_(table.col_1 == a, table.col_2 == b), and_(table.col_1 == b, table.col_2 == c)) For posterity, to loop over my rows I found I could put it in a generator over the columns col1 = list of column 1 col2 = list of column 2 or_(and_(table.col_1==p, table.col_2==col2[i]) for i,p in enumerate(col1)) Thanks for the help. On Tuesday, October 27, 2015 at 2:05:31 PM UTC+11, Brian Cherinka wrote: > > > What's the best way to query, with the ORM, based on a list of multiple > parameters without looping over each parameter tuple? > > I have a list of parameters say: > > Col-1, Col-2 > a, b > c, d > > where the combination of the parameters in one row defines a unique table > entry. Such that I would normally do > > object = session.query(table).filter(table.Col-1 == a, table.Col-2 == > b).one() , and > object = session.query(table).filter(table.Col-1 == c, table.Col-2 == > d).one() > > to retrieve the two table objects > > Can I run a query that would grab the list of objects all at once, in one > query, without having to loop over each row, doing each query, and > combining the objects into a list? Something akin to the > table.Col-1..in_([list of values) but with a combinatorial component to it. > > > I know I can do something like > session.query(table).filter(table.Col-1.in_([a,c]).all() to grab all the > objects that have column 1 values of a, or c, but I need to constrain those > to a+b, and c+d > > Does this make sense? > > Cheers, Brian > > -- 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.
[sqlalchemy] best way to query from a tuple of parameters
What's the best way to query, with the ORM, based on a list of multiple parameters without looping over each parameter tuple? I have a list of parameters say: Col-1, Col-2 a, b c, d where the combination of the parameters in one row defines a unique table entry. Such that I would normally do object = session.query(table).filter(table.Col-1 == a, table.Col-2 == b).one() , and object = session.query(table).filter(table.Col-1 == c, table.Col-2 == d).one() to retrieve the two table objects Can I run a query that would grab the list of objects all at once, in one query, without having to loop over each row, doing each query, and combining the objects into a list? Something akin to the table.Col-1..in_([list of values) but with a combinatorial component to it. I know I can do something like session.query(table).filter(table.Col-1.in_([a,c]).all() to grab all the objects that have column 1 values of a, or c, but I need to constrain those to a+b, and c+d Does this make sense? Cheers, Brian -- 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.
[sqlalchemy] Re: best way to query from a tuple of parameters
Are you looking for the `or_` from sqlalchemy.sql import and_, or_ abcd = or_((table.col_1 == a, table.col_2 == b), (table.col_1 == b, table.col_2 == c), ) session.query(table).filter(**abcd).all() that should generate something like SELECT * FROM table WHERE (col_1 == a and col_2 == b) OR (col_1 == c and col_2 == 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.