[Python-ideas] Proposal: Query language extension to Python (PythonQL)

[Brice PARENT]

Le 27/03/17 à 10:55, Pavel Velikhov a écrit :
> Hi Brice,
>
>> On 27 Mar 2017, at 10:17, Brice PARENT <contact at brice.xyz 
>> <mailto:contact at brice.xyz>> wrote:
>>
>> I prefer this a lot to the original syntax, and I really think this 
>> has much better chances to be integrated (if such an integration had 
>> to be done, and not kept as a separate module).
>>
>> Also, maybe managing this with classes instead of syntax could also 
>> be done easily (without any change to Python), like this:
>>
>> from pyql import PQL, Select, For, Where, GroupBy, Let
>>
>> result = PQL(
>>     Select("x", "sum_y"),
>>     For("x", range(1,8)),
>>     For("y",range(1,7)),
>>     Where(lambda x, y: x %2==0andy %2!=0andx >y, "x", "y"),  # 
>> function, *[arguments to pass to the function]
>>     Where("sum_y", lambda sum_y: sum_y %2!=0)
>>     GroupBy("x"),
>>     Let("sum_y", lambda y: sum(y), "y")
>> )
>>
>>
>
> So here’s the deal: small queries will look pretty decent in pretty 
> much all paradigms, ORM, or PythonQL or your proposal.
> Once they get bigger and combine multiple pain points (say outerjoins, 
> grouping and nested data) - then unless you have a
> really clear and minimal language, folks will get confused and lost.
>
> We’ve gone through a few query languages that failed, including XQuery 
> and others, and the main reason was the need to learn
> a whole new language and a bunch of libraries, nobody wanted to do it. 
> So the main selling point behind PythonQL is: its Python
> that folks hopefully know already, with just a few extensions.
I get it, but it's more a matter of perception. To me, the version I 
described is just Python, while yours is Python + specific syntax. As 
this syntax is only used in PyQL sub-language, it's not really Python 
any more...
Also, what I like with what I used, is that it is object-based, which 
allows any part of the query to be reusable or built dynamically. We 
might also extend such a PQL object's constructor to embed automatically 
whatever default parameters or database connection we want, or shared 
behaviours, like:

class MyPQL(PQL):
     def get_limit(self):
         if self.limit is not None:
             return self.limit

         return 10

     def __init__(self, *args):
         args.append(Let("sum_y", lambda y: sum(y), "y"))
args.append(GroupBy("x"))
         super().__init__(*args)

result = MyPQL(
     Select("x", "sum_y"),
     For("x", range(1,8)),
     For("y",range(1,7)),
     Where(lambda x, y: x %2==0andy %2!=0andx >y, "x", "y"),
     Where("sum_y", lambda sum_y: sum_y %2!=0)
)

Big queries, this way, may be split into smaller parts. And it allows 
you to do the following in a single query, instead of having to write 
one big for each condition

where_from = [For("x", range(1,8)),For("y",range(1,7))]
where = [Where(lambda x, y: x %2==0andy %2!=0andx >y, "x", "y")]
if filter_sum_y:
where.append(Where("sum_y", lambda sum_y: sum_y %2!=0))

if group_by is not None:
     grouping = GroupBy("x")

result = MyPQL(Select("x", "sum_y"), *where_from, *where, *grouping)

Side note : I'm not a big database user, I mostly use ORMs (Django's and 
PonyORM depending on the projects) to access PgSQL and SQLite (for unit 
testing), so I might not even have use cases for what you're trying to 
solve. I just give my point of view here to explain what I think could 
be more easily integrated and (re)used. And as I'm a big fan of the DRY 
mentality, I'm not a fan of the syntax-chaining things (as well as I 
don't really like big nested comprehensions).

-Brice
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-ideas/attachments/20170327/f047019d/attachment.html>