pythonql

PythonQL is an extension to Python that allows language-integrated queries against relational, XML and JSON data, as well an Python's collections

Python has pretty advanced comprehensions, that cover a big chunk of SQL, to the point where PonyORM was able to build a whole ORM system based on comprehensions. However, group by mechanisms, outerjoins and support for semi-structured data are not handled well at all.

We propose the following extensions to Python( that are implemeneted in this demo preprocessor and query executor):

• Path expressions. When working with nested data that has varied structure, path expressions are extremely useful. We have modeled our path expression on XPath, however we use a much simplified verison:

• Child step: for x in data ./ _ or for x in data ./ expr where expr must evaluate to string

• Descendants step: for x in data .// _ or for x in data ../ expr where expr must evaluate to string

So we can write path expression in the query language (and elsewhere in Python expressions) like this:

  for x in data ./ "hotels" .// "room"

• Try-except expressions. Python has try-except statement, but in many cases when working with dirty or semi-structured data, we need to be able to use an expression inside an iterator or the query. So we introduced a try-except expressions:

   try int(x) except 0 for x in values 

• Tuple constructor. Tuples that have named columns are very useful in querying, however Python's native namedtuple is not very convenient. We have extended Python's tuple constructor syntax:

  (id as employee_id, sum(x) as total_salary)

• Query expressions: Our query syntax is a strict superset of Python's comprehensions, we extend the comprehensions to do much more powerful queries than they are capable of now.

 [ select (prod,len(p)) 
   for p in sales 
   let prod = p.prod 
   group by prod ]

At the same time our queries look similar to SQL, but are more flexible and of course most of the expressions in the queres are in pure Python. A lot of functionality is cleaner than in SQL, like the window queries, subqueries in general, etc. As in Python, our query expressions can return generators, list, sets and maps.

Documentation

A short tutorial on PythonQL is available here: https://github.com/pythonql/pythonql/wiki/PythonQL-Intro-and-Tutorial

Examples

We have a whole site dedicated to various scenarios with lots of queries where PythonQL is especially handy: www.pythonql.org

Here is a small example PythonQL program (we're building a demo website with a number of scenarios that are especially good for solving with PythonQL):

#coding: pythonql
#
# This example illustrates the try-catch business in PythonQL.
# Basically, some data might be dirty, but you still want to be able to write a simple query

from collections import namedtuple
ord = namedtuple('Order', ['cust_id','prod_id','price'])
cust = namedtuple('Cust', ['cust_id','cust_name'])

ords = [ ord(1,1,"16.54"),
         ord(1,2,"18.95"),
         ord(1,5,"8.96"),
         ord(2,1,"????"),
         ord(2,2,"20.00") ]

custs = [ cust(1,"John"), cust(2,"Dave"), cust(3,"Boris") ]

# Basic SQL query, but with some data cleaning
res = [select (name, sum(price) as sum)
        for o in ords
        let price = try float(o.price)  except 0
        for c in custs
        where c.cust_id == o.cust_id
        group by c.cust_id as id, c.cust_name as name]

print (res)

Installing pythonql:

Run pip install pythonql to install pythonql for Python2.7, or pip install pythonql3 for Python 3.x.

Running pythonql:

PythonQL is implemented as a special encoding in a normal python script. When this encoding is specified, the pythonql preprocessor is run, which converts the pythonql syntax to pure python.

So you should have a line in the beginning of your script:

#coding: pythonql

result = [ select y for x in [1,2,3] let y = x**2 ]

Uninstalling pythonql:

PythonQL installs a special file in your library to enable the pythonql encoding. If you decide to uninstall pythonql, run pip uninstall pythonql (or pythonql3) and then delete pythonql.pth file from your Python library.

Help/Bugs/Suggestions:

We have a Google group running, where you can ask any questions, report bugs or suggest improvements: https://groups.google.com/forum/#!forum/pythonql