[PonyORM-list] How to do a query?

[Alexander Kozlovsky]

Maybe we need to add special support for such expressions. Right now you
can do the task using raw SQL fragment.

-------

Assuming the list is [(p1, p2), (p3, p4), (p5, p6)], the following raw SQL
expression syntax is compatible both with SQLite and PostgreSQL:

    (x.a = p1 and x.b = p2) or (x.a = p3 and x.b = p4) or (x.a = p5 and x.b
= p.6)

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.

-------

if p1, p2, etc. are integers, then the SQL expression can be generated
using the following Python code:

    sql_expr = " or ".join("(x.a = %d and x.b = %d)" % (p, q) for p, q in
list)

Now to execute a query we can write:

    select(x for x in X if not raw_sql(sql_expr))

-------

if p1, p2, etc. are strings, they values need to be taken in quotes:

    sql_expr = " or ".join("(x.a = '%s' and x.b = '%s')" % (p, q) for p, q
in list)

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:

    (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])

The above raw SQL expression can be generated using the following Python
code:

    sql_expr = " or ".join("x.a = $list[%d][0] and x.b = $list[%d][1]" %(i,
i) for i in range(len(list)))

When raw_sql(sql_expr) is calculated, it evaluates python subexpressions
like "list[0][1]" and replaces them with a corresponding properly escaped
parameter

So the query select(x for x in X if not raw_sql(sql_expr)) will return
correct result.


-------

Hope that helps. We can add direct support of such expressions, but it will
take some time to implement it.

Regards,
Alexander


On Tue, Dec 8, 2015 at 8:51 PM, Matthew Bell <matthewrobertbell at gmail.com>
wrote:

> Hi!
>
> For testing, SQLite. In production, postgres. On the order of hundreds of
> pairs.
>
> Thanks!
>
> On 8 December 2015 at 17:47, Alexander Kozlovsky <
> alexander.kozlovsky at gmail.com> wrote:
>
>> Hi Matthew! Two questions:
>>
>> 1) What database do you use?
>> 2) How many pairs are in y potentially?
>>
>> On Tue, Dec 8, 2015 at 4:37 PM, Matthew Bell <matthewrobertbell at gmail.com
>> > wrote:
>>
>>> Hello,
>>>
>>> I am trying to do this:
>>>
>>> Assuming X.a and X.b are both unicode:
>>>
>>>     y = [('1', '2'), ('3', '4')]
>>>
>>>     select(x for X in x if (x.a, x.b) not in y)
>>>
>>> This query doesn't work. How can I exclude the tuples in y from results?
>>>
>>> Thanks
>>>
>>> --
>>> Regards,
>>>
>>> Matthew Bell
>>>
>>> _______________________________________________
>>> ponyorm-list mailing list
>>> ponyorm-list at ponyorm.com
>>> /ponyorm-list
>>>
>>>
>>
>> _______________________________________________
>> ponyorm-list mailing list
>> ponyorm-list at ponyorm.com
>> /ponyorm-list
>>
>>
>
>
> --
> Regards,
>
> Matthew Bell
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20151208/236d403d/attachment.html>