<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><br>
</p>
<p>Pavel,</p>
<p>I like PythonQL. I perform a lot of data transformation, and
often find Python's list comprehensions too limiting; leaving me
wishing for LINQ-like language features. <br>
</p>
<p>As an alternative to extending Python with PythonQL, Terry Reedy
suggested interpreting a DSL string, and Pavel Velikhov alluded to
using magic method tricks found in ORM libraries. I can see how
both these are not satisfactory. <br>
</p>
A third alternative could be to encode the query clauses as JSON
objects. For example: <br>
<pre>result <span class="pl-k">=</span> [ select (x, sum_y)
<span class="pl-k">for</span> x <span class="pl-k">in</span> <span class="pl-c1">range</span>(<span class="pl-c1">1</span>,<span class="pl-c1">8</span>),
y <span class="pl-k">in</span> <span class="pl-c1">range</span>(<span class="pl-c1">1</span>,<span class="pl-c1">7</span>)
where x <span class="pl-k">%</span> <span class="pl-c1">2</span> <span class="pl-k">==</span> <span class="pl-c1">0</span> <span class="pl-k">and</span> y <span class="pl-k">%</span> <span class="pl-c1">2</span> <span class="pl-k">!=</span> <span class="pl-c1">0</span> <span class="pl-k">and</span> x <span class="pl-k">></span> y
group by x
let sum_y = <span class="pl-c1">sum</span>(y)
where sum_y <span class="pl-k">%</span> <span class="pl-c1">2</span> <span class="pl-k">!=</span> <span class="pl-c1">0</span>
]</pre>
<pre>result = pq([
{"select":["x", "sum_y"]},
{"for":<span class="pl-k">{"x": </span><span class="pl-c1">range</span>(<span class="pl-c1">1</span>,<span class="pl-c1">8</span>), "y": <span class="pl-c1">range</span>(<span class="pl-c1">1</span>,<span class="pl-c1">7</span>)}},
{"where": lambda x,y: x <span class="pl-k">%</span> <span class="pl-c1">2</span> <span class="pl-k">==</span> <span class="pl-c1">0</span> <span class="pl-k">and</span> y <span class="pl-k">%</span> <span class="pl-c1">2</span> <span class="pl-k">!=</span> <span class="pl-c1">0</span> <span class="pl-k">and</span> x <span class="pl-k">></span> y},
{"groupby": "x"},
{"with":{"sum_y":{"SUM":"y"}},
{"where": {"neq":[{"mod":["sum_y", 2]}, 0]}}
])
</pre>
This representation does look a little lispy, and it may resemble
PythonQL's parse tree. I think the benefits are:<br>
<br>
1) no python language change<br>
2) easier to parse<br>
3) better than string-based DSL for catching syntax errors<br>
4) {"clause": parameters} format is flexible for handling common
query patterns **<br>
5) works in javascript too<br>
6) easy to compose with automation (my favorite) <br>
<br>
It is probably easy for you to see the drawbacks.<br>
<br>
<br>
** The `where` clause can accept a native lambda function, or an
expression tree<br>
<br>
<br>
<br>
<br>
<br>
"If you are writing a loop, you are doing it wrong!" :)<br>
<br>
<br>
<div class="moz-cite-prefix">On 2017-03-24 11:10, Pavel Velikhov
wrote:<br>
</div>
<blockquote
cite="mid:629E7A2A-7668-4A9B-A812-F846A2785E3D@gmail.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
Hi folks!
<div class=""><br class="">
<div class=""> We started a project to extend Python with a
full-blown query language about a year ago. The project is
call PythonQL, the links are given below in the references
section. We have implemented what is kind of an alpha version
now, and gained some experience and insights about why and
where this is really useful. So I’d like to share those with
you and gather some opinions whether you think we should try
to include these extensions in the Python core.</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Intro</b></div>
<div class=""><br class="">
</div>
<div class=""> What we have done is (mostly) extended Python’s
comprehensions with group by, order by, let and window
clauses, which can come in any order, thus comprehensions
become a query language a bit cleaner and more powerful than
SQL. And we added a couple small convenience extensions, like
a We have identified three top motivations for folks to use
these extensions:</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Our Motivations</b></div>
<div class=""><br class="">
</div>
<div class="">1. This can become a standard for running queries
against database systems. Instead of learning a large number
of different SQL dialects (the pain point here are libraries
of functions and operators that are different for each
vendor), the Python developer needs only to learn PythonQL and
he can query any SQL and NoSQL database.</div>
<div class=""><br class="">
</div>
<div class="">2. A single PythonQL expression can integrate a
number of databases/files/memory structures seamlessly, with
the PythonQL optimizer figuring out which pieces of plans to
ship to which databases. This is a cool virtual database
integration story that can be very convenient, especially now,
when a lot of data scientists use Python to wrangle the data
all day long.</div>
<div class=""><br class="">
</div>
<div class="">3. Querying data structures inside Python with the
full power of SQL (and a bit more) is also really convenient
on its own. Usually folks that are well-versed in SQL have to
resort to completely different means when they need to run a
query in Python on top of some data structures.</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Current Status</b></div>
<div class=""><br class="">
</div>
<div class="">We have PythonQL running, its installed via pip
and an encoding hack, that runs our preprocessor. We currently
compile PythonQL into Python using our executor functions and
execute Python subexpressions via eval. We don’t do any
optimization / rewriting of queries into languages of
underlying systems. And the query processor is basic too, with
naive implementations of operators. But we’ve build DBMS
systems before, so if there is a good amount of support for
this project, we’ll be able to build a real system here.</div>
<div class=""><br class="">
</div>
<div class=""><b class="">Your take on this</b></div>
<div class=""><br class="">
</div>
<div class="">Extending Python’s grammar is surely a painful
thing for the community. We’re now convinced that it is well
worth it, because of all the wonderful functionality and
convenience this extension offers. We’d like to get your
feedback on this and maybe you’ll suggest some next steps for
us.</div>
<div class=""><br class="">
</div>
<div class=""><b class="">References</b></div>
<div class=""><br class="">
</div>
<div class="">PythonQL GitHub page: <a moz-do-not-send="true"
href="https://github.com/pythonql/pythonql" class="">https://github.com/pythonql/pythonql</a></div>
<div class="">PythonQL Intro and Tutorial (this is all User
Documentation we have right now): <a moz-do-not-send="true"
href="https://github.com/pythonql/pythonql/wiki/PythonQL-Intro-and-Tutorial"
class="">https://github.com/pythonql/pythonql/wiki/PythonQL-Intro-and-Tutorial</a></div>
<div class="">A use-case of querying Event Logs and doing
Process Mining with PythonQL: <a moz-do-not-send="true"
href="https://github.com/pythonql/pythonql/wiki/Event-Log-Querying-and-Process-Mining-with-PythonQL"
class="">https://github.com/pythonql/pythonql/wiki/Event-Log-Querying-and-Process-Mining-with-PythonQL</a></div>
<div class="">PythonQL demo site: <a moz-do-not-send="true"
href="http://www.pythonql.org" class="">www.pythonql.org</a></div>
<div class=""><br class="">
</div>
<div class="">Best regards,</div>
<div class="">PythonQL Team</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Python-ideas mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Python-ideas@python.org">Python-ideas@python.org</a>
<a class="moz-txt-link-freetext" href="https://mail.python.org/mailman/listinfo/python-ideas">https://mail.python.org/mailman/listinfo/python-ideas</a>
Code of Conduct: <a class="moz-txt-link-freetext" href="http://python.org/psf/codeofconduct/">http://python.org/psf/codeofconduct/</a>
</pre>
</blockquote>
<br>
</body>
</html>