[PonyORM-list] How to do a query?

[Matthew Bell]

Thank you for your help!

On 8 December 2015 at 19:03, Alexander Kozlovsky <
alexander.kozlovsky at gmail.com> wrote:

> 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
>>
>>
>
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
>


-- 
Regards,

Matthew Bell
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20151208/fd75f0cc/attachment-0001.html>