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

[Gerald Britton]

(Forgot the subject)


> >* On 25 Mar 2017, at 15:51, Gerald Britton <gerald.britton at gmail.com <https://mail.python.org/mailman/listinfo/python-ideas>> wrote:
> *> >* On 25 March 2017 at 11:24, Pavel Velikhov <pavel.velikhov at gmail.com <http://gmail.com> <http://gmail.com/ <http://gmail.com/>>> wrote:
> *>* > No, the current solution is temporary because we just don’t have the
> *>* > manpower to
> *>* > implement the full thing: a real system that will rewrite parts of PythonQL
> *>* > queries and
> *>* > ship them to underlying databases. We need a real query optimizer and smart
> *>* > wrappers
> *>* > for this purpose. But we’ll build one of these for demo purposes soon
> *>* > (either a Spark
> *>* > wrapper or a PostgreSQL wrapper).
> *>* One thought, if you're lacking in manpower now, then proposing
> *>* inclusion into core Python means that the core dev team will be taking
> *>* on an additional chunk of code that is already under-resourced. That
> *>* rings alarm bells for me - how would you imagine the work needed to
> *>* merge PythonQL into the core Python grammar would be resourced?
> *>* I should say that in practice, I think that the solution is relatively
> *>* niche, and overlaps quite significantly with existing Python features,
> *>* so I don't really see a compelling case for inclusion. The parallel
> *>* with C# and LINQ is interesting here - LINQ is a pretty cool
> *>* technology, but I don't see it in widespread use in general-purpose C#
> *>* projects (disclaimer: I don't get to see much C# code, so my
> *>* experience is limited).
> *> >* I see lots of C# code, but (thankfully) not so much LINQ to SQL.  Yes, it is a cool technology.  But I sometimes have a problem with the SQL it generates.  Since I'm also a SQL developer, I'm sensitive to how queries are constructed, for performance reasons, as well as how they look, for readability and aesthetic reasons.
> *> >* LINQ queries can generate poorly-performing SQL, since LINQ is a basically a translator, but not an AI.  As far as appearances go, LINQ queries can look pretty gnarly, especially if they include sub queries or a few joins.  That makes it hard for the SQL dev (me!) to read and understand if there are performance problems (which there often are, in my experience)
> *>

We want to go beyond being a basic translator. Especially if the
common use-case will be integrating multiple databases. We can also
introduce decent-looking hints (maybe not always decent looking) to
generate better plans. Not sure about asethetics though...

>* So, I would tend to code the SQL separately and put it in a SQL view, function or stored procedure.  I can still parse the results with LINQ (not LINQ to SQL), which is fine.
> *> >* For similar reasons, I'm not a huge fan of ORMs either.  Probably my bias towards designing the database first and building up queries to meet the business goals before writing a line of Python, C#, or the language de jour.
> *

This sounds completely reasonable, but this means you’re tied to a
specific DBMS (especially if you’re using a lot of built-in functions
that are usually very specific to a database). PythonQL (when it has
enough functionality) should give you independence.


True though not always needed.  e.g. at present I'm working for a large
company with thousands of db servers in all the popular flavors.  The
probability of changing even one of them to a different vendor is
essentially zero.  The costs and dependencies far outweigh any hoped-for
advantage.  At the same time, I'm happy to optimize the SQL for different
target environments.  If I lack the specific expertise, I know where to go
to find it.  The Adapter pattern helps here.

It's actually more important for me to build queries that can be used in
multiple client languages. We're using Java, C++, C#, F#, VB, ...  and
Python, of course (and probably others that I don't know we use). I can
optimize the query once and not worry about the clients messing it up.

-- 
Gerald Britton, MCSE-DP, MVP
LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton