Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
A python expression to MongoDB query translator
Python
branch: master
Failed to load latest commit information.
pql fix Polygon creation (it should be a list of rings and not a list of …
.gitignore update .gitignore
LICENSE add license
README.md update docs with right Polygon syntax
aggregation_tests.py
find_tests.py
setup.py
tox.ini

README.md

PQL

PQL stands for Python-Query-Language. PQL translates python expressions to MongoDB queries.

PQL uses the builtin python ast module for parsing and analysis of python expressions.

PQL is resilient to code injections as it doesn't evaluate the code.

Installation

pip install pql

Follow @alonhorev on twitter for updates. Source located at: http://github.com/alonho/pql

Find Queries

Schema-Free Example

The schema-free parser converts python expressions to mongodb queries with no schema enforcement:

>>> import pql
>>> pql.find("a > 1 and b == 'foo' or not c.d == False")
{'$or': [{'$and': [{'a': {'$gt': 1}}, {'b': 'foo'}]}, {'$not': {'c.d': False}}]}

Schema-Aware Example

The schema-aware parser validates fields exist:

>>> import pql
>>> pql.find('b == 1', schema={'a': pql.DateTimeField()}) 
Traceback (most recent call last):
    ...
pql.ParseError: Field not found: b. options: ['a']

Validates values are of the correct type:

>>> pql.find('a == 1', schema={'a': pql.DateTimeField()})
Traceback (most recent call last):
    ...
pql.ParseError: Unsupported syntax (Num).

Validates functions are called against the appropriate types:

>>> pql.find('a == regex("foo")', schema={'a': pql.DateTimeField()})
Traceback (most recent call last):
    ...
pql.ParseError: Unsupported function (regex). options: ['date', 'exists', 'type']

Referencing Fields

pql mongo
a a
a.b.c a.b.c
"a-b" a-b

Data Types

pql mongo
a == 1 {'a': 1}
a == "foo" {'a': 'foo'}
a == None {'a': None}
a == True {'a': True}
a == False {'a': False}
a == [1, 2, 3] {'a': [1, 2, 3]}
a == {"foo": 1} {'a': {'foo': 1}}
a == date("2012-3-4") {'a': datetime.datetime(2012, 3, 4, 0, 0)}
a == date("2012-3-4 12:34:56") {'a': datetime.datetime(2012, 3, 4, 12, 34, 56)}
a == date("2012-3-4 12:34:56.123") {'a': datetime.datetime(2012, 3, 4, 12, 34, 56, 123000)}
id == id("abcdeabcdeabcdeabcdeabcd") {'id': bson.ObjectId("abcdeabcdeabcdeabcdeabcd")}

Operators

pql mongo
a != 1 {'a': {'$ne': 1}}
a > 1 {'a': {'$gt': 1}}
a >= 1 {'a': {'$gte': 1}}
a < 1 {'a': {'$lt': 1}}
a <= 1 {'a': {'$lte': 1}}
a in [1, 2, 3] {'a': {'$in': [1, 2, 3]}}
a not in [1, 2, 3] {'a': {'$nin': [1, 2, 3]}}

Boolean Logic

pql mongo
not a == 1 {'$not': {'a': 1}}
a == 1 or b == 2 {'$or': [{'a': 1}, {'b': 2}]}
a == 1 and b == 2 {'$and': [{'a': 1}, {'b': 2}]}

Functions

pql mongo
a == all([1, 2, 3]) {'a': {'$all': [1, 2, 3]}}
a == exists(True) {'a': {'$exists': True}}
a == match({"foo": "bar"}) {'a': {'$elemMatch': {'foo': 'bar'}}}
a == mod(10, 3) {'a': {'$mod': [10, 3]}}
a == regex("foo") {'a': {'$regex': 'foo'}}
a == regex("foo", "i") {'a': {'$options': 'i', '$regex': 'foo'}}
a == size(4) {'a': {'$size': 4}}
a == type(3) {'a': {'$type': 3}}

Geo Queries

pql mongo
location == geoWithin(center([1, 2], 3)) {'location': {'$geoWithin': {'$center': [[1, 2], 3]}}}
location == geoWithin(centerSphere([1, 2], 3)) {'location': {'$geoWithin': {'$centerSphere': [[1, 2], 3]}}}
location == geoIntersects(LineString([[1, 2], [3, 4]])) {'location': {'$geoIntersects': {'$geometry': {'type': 'LineString', 'coordinates': [[1, 2], [3, 4]]}}}}
location == geoWithin(Polygon([[[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4], [5, 6]]])) {'location': {'$geoWithin': {'$geometry': {'type': 'Polygon', 'coordinates': [[[1, 2], [3, 4], [5, 6]], [[1, 2], [3, 4], [5, 6]]]}}}}
location == near([1, 2], 10) {'location': {'$maxDistance': 10, '$near': [1, 2]}}
location == near(Point(1, 2), 10) {'location': {'$near': {'$geometry': {'type': 'Point', 'coordinates': [1, 2]}, '$maxDistance': 10}}}
location == nearSphere(Point(1, 2)) {'location': {'$nearSphere': {'$geometry': {'type': 'Point', 'coordinates': [1, 2]}}}}
location == geoWithin(box([[1, 2], [3, 4], [5, 6]])) {'location': {'$geoWithin': {'$box': [[1, 2], [3, 4], [5, 6]]}}}
location == geoWithin(polygon([[1, 2], [3, 4], [5, 6]])) {'location': {'$geoWithin': {'$polygon': [[1, 2], [3, 4], [5, 6]]}}}

Aggregation Queries

Example

Lets say you have a collection of car listings:

>>> list(db.cars.find())
[{'_id': ObjectId('51794ce58c998f1e2b654b50'),
  'made_on': datetime.datetime(1971, 4, 7, 0, 0),
  'model': 'fiat',
  'price': 3},
 {'_id': ObjectId('51794cea8c998f1e2b654b51'),
  'made_on': datetime.datetime(1980, 10, 19, 0, 0),
  'model': 'subaru',
  'price': 5},
 {'_id': ObjectId('51794cf08c998f1e2b654b52'),
  'made_on': datetime.datetime(1983, 2, 27, 0, 0),
  'model': 'kia',
  'price': 4},
 {'_id': ObjectId('51794d3c8c998f1e2b654b53'),
  'made_on': datetime.datetime(1988, 1, 23, 0, 0),
  'model': 'kia',
  'price': 7}]

How do you get the number of cars and the sum of their prices per model per decade:

>>> collection.aggregate(project(model='model', 
                                made_on='year(made_on)', 
                                price='price * 3.7') | 
                         match('made_on > 1975 and made_on < 1990') | 
                         group(_id=project(model='model', 
                                           decade='made_on - (made_on % 10)'), 
                               count='sum(1)', 
                               total='sum(price)'))
{'ok': 1.0,
 'result': [{'_id': {'decade': 1980, 'model': 'subaru'}, 'count': 1,'total': 18.5},
            {'_id': {'decade': 1980, 'model': 'kia'}, 'count': 2, 'total': 40.7}]}

How would it look using the raw syntax:

[{'$project': {'made_on': {'$year': '$made_on'},
               'model': '$model',
               'price': {'$multiply': ['$price', 3.7]}}},
 {'$match': {'$and': [{'made_on': {'$gt': 1975}},
                      {'made_on': {'$lt': 1990}}]}},
 {'$group': {'_id': {'decade': {'$subtract': ['$made_on',
                                              {'$mod': ['$made_on', 10]}]},
                     'model': '$model'},
  'count': {'$sum': 1},
  'total': {'$sum': '$price'}}}]

Referencing Fields

pql mongo
a $a
a.b.c $a.b.c

Arithmetic Operators

pql mongo
a + 1 {'$add': ['$a', 1]}
a / 1 {'$divide': ['$a', 1]}
a % 1 {'$mod': ['$a', 1]}
a * 1 {'$multiply': ['$a', 1]}
a - 1 {'$subtract': ['$a', 1]}
a > 0 {'$gt': ['$a', 0]}
a >= 0 {'$gte': ['$a', 0]}
a < 0 {'$lt': ['$a', 0]}
a <= 0 {'$lte': ['$a', 0]}

Logical Operators

pql mongo
a == 0 {'$eq': ['$a', 0]}
a != 0 {'$ne': ['$a', 0]}
cmp(a, "bar") {'$cmp': ['$a', 'bar']}
a and b {'$and': ['$a', '$b']}
not a {'$not': '$a'}
a or b {'$or': ['$a', '$b']}
a if b > 3 else c {'$cond': [{'$gt': ['$b', 3]}, '$a', '$c']}
ifnull(a + b, 100) {'$ifnull': [{'$add': ['$a', '$b']}, 100]}

Date Operators

pql mongo
dayOfYear(a) {'$dayOfYear': '$a'}
dayOfMonth(a) {'$dayOfMonth': '$a'}
dayOfWeek(a) {'$dayOfWeek': '$a'}
year(a) {'$year': '$a'}
month(a) {'$month': '$a'}
week(a) {'$week': '$a'}
hour(a) {'$hour': '$a'}
minute(a) {'$minute': '$a'}
second(a) {'$second': '$a'}
millisecond(a) {'$millisecond': '$a'}

String Operators

pql mongo
concat("foo", "bar", b) {'$concat': ['foo', 'bar', '$b']}
strcasecmp("foo", b) {'$strcasecmp': ['foo', '$b']}
substr("foo", 1, 2) {'$substr': ['foo', 1, 2]}
toLower(a) {'$toLower': '$a'}
toUpper(a) {'$toUpper': '$a'}

TODO

  1. Generate a schema from a mongoengine or mongokit class.
  2. Add a declarative schema generation syntax.
  3. Add support for $where.
Something went wrong with that request. Please try again.