<div dir="ltr">Thank you for your help!<br></div><div class="gmail_extra"><br><div class="gmail_quote">On 8 December 2015 at 19:03, Alexander Kozlovsky <span dir="ltr"><<a href="mailto:alexander.kozlovsky@gmail.com" target="_blank">alexander.kozlovsky@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Maybe we need to add special support for such expressions. Right now you can do the task using raw SQL fragment.<br><br>-------<br><br><div>Assuming the list is [(p1, p2), (p3, p4), (p5, p6)], the following raw SQL expression syntax is compatible both with SQLite and PostgreSQL:<br><br> (x.a = p1 and x.b = p2) or (x.a = p3 and x.b = p4) or (x.a = p5 and x.b = p.6)<br><br>Here x is the table alias, a and b are columns, and p1, p2, etc are constant values or query parameters. PonyORM uses iterator variable name as a table alias name, and by default each column name is equal to the name of the corresponding attribute, so we can know all necessary SQL names to write correct raw SQL expression.</div><div><br></div><div>-------<br><br>if p1, p2, etc. are integers, then the SQL expression can be generated using the following Python code:<br><br> sql_expr = " or ".join("(x.a = %d and x.b = %d)" % (p, q) for p, q in list)<br><br>Now to execute a query we can write:<br><br> select(x for x in X if not raw_sql(sql_expr))<br><br>-------<br><br>if p1, p2, etc. are strings, they values need to be taken in quotes:<br><br> sql_expr = " or ".join("(x.a = '%s' and x.b = '%s')" % (p, q) for p, q in list)<br><br>Writing the above query we have a risk of SQL injection if some value of p1, p2, etc. may contains quote. To be safe, we can use parameters. With parameters, the sql fragment will look like this:<br><br> (x.a = $list[0][0] and x.b = $list[0][1]) or (x.a = $list[1][0] and x.b = $list[1][1]) or (x.a = $list[2][0] and x.b = $list[2][1])<br><br>The above raw SQL expression can be generated using the following Python code:<br><br> sql_expr = " or ".join("x.a = $list[%d][0] and x.b = $list[%d][1]" %(i, i) for i in range(len(list)))<br><br>When raw_sql(sql_expr) is calculated, it evaluates python subexpressions like "list[0][1]" and replaces them with a corresponding properly escaped parameter<br><br>So the query select(x for x in X if not raw_sql(sql_expr)) will return correct result.<br><br><br>-------<br><br>Hope that helps. We can add direct support of such expressions, but it will take some time to implement it.<br><br>Regards,<br>Alexander<br><br></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Dec 8, 2015 at 8:51 PM, Matthew Bell <span dir="ltr"><<a href="mailto:matthewrobertbell@gmail.com" target="_blank">matthewrobertbell@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div>Hi!<br><br></div>For testing, SQLite. In production, postgres. On the order of hundreds of pairs.<br><br></div>Thanks!<br></div><div class="gmail_extra"><div><div><br><div class="gmail_quote">On 8 December 2015 at 17:47, Alexander Kozlovsky <span dir="ltr"><<a href="mailto:alexander.kozlovsky@gmail.com" target="_blank">alexander.kozlovsky@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Matthew! Two questions:<br><br>1) What database do you use?<br>2) How many pairs are in y potentially?</div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On Tue, Dec 8, 2015 at 4:37 PM, Matthew Bell <span dir="ltr"><<a href="mailto:matthewrobertbell@gmail.com" target="_blank">matthewrobertbell@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><div><div><div>Hello,<br><br></div>I am trying to do this:<br><br>Assuming X.a and X.b are both unicode:<br><br> y = [('1', '2'), ('3', '4')]<br><br> select(x for X in x if (x.a, x.b) not in y)<br><br></div>This query doesn't work. How can I exclude the tuples in y from results? <br><br></div>Thanks<span><font color="#888888"><br clear="all"><div><div><div><div><br>-- <br><div>Regards,<br><br>Matthew Bell<br></div> </div></div></div></div></font></span></div> <br></div></div>_______________________________________________<br> ponyorm-list mailing list<br> <a href="mailto:ponyorm-list@ponyorm.org" target="_blank">ponyorm-list@ponyorm.org</a><br> <a href="/ponyorm-list" rel="noreferrer" target="_blank">/ponyorm-list</a><br> <br></blockquote></div><br></div> <br>_______________________________________________<br> ponyorm-list mailing list<br> <a href="mailto:ponyorm-list@ponyorm.org" target="_blank">ponyorm-list@ponyorm.org</a><br> <a href="/ponyorm-list" rel="noreferrer" target="_blank">/ponyorm-list</a><br> <br></blockquote></div><br><br clear="all"><br></div></div><span><font color="#888888">-- <br><div>Regards,<br><br>Matthew Bell<br></div> </font></span></div> <br>_______________________________________________<br> ponyorm-list mailing list<br> <a href="mailto:ponyorm-list@ponyorm.org" target="_blank">ponyorm-list@ponyorm.org</a><br> <a href="/ponyorm-list" rel="noreferrer" target="_blank">/ponyorm-list</a><br> <br></blockquote></div><br></div> </div></div><br>_______________________________________________<br> ponyorm-list mailing list<br> <a href="mailto:ponyorm-list@ponyorm.org">ponyorm-list@ponyorm.org</a><br> <a href="/ponyorm-list" rel="noreferrer" target="_blank">/ponyorm-list</a><br> <br></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature">Regards,<br><br>Matthew Bell<br></div> </div>