Documentation for python-stdnet's DEVELOPMENT version. Get the release docs here.

Query your Data

The most powerful feature of stdnet is a comprehensive API for querying, searching and sorting your data in an efficient and elegant way. To retrieve objects from your data server, you construct a Query via a Manager. A Query represents a collection of rules to aggregate objects from your model.

We pick up from the registered models router object created for our tutorial application. Throughout this tutorial, we use the dotted notation for accessing model managers.

Retrieving all objects

The simplest way to retrieve objects for a model is to get all of them. To do this, use the Manager.query() method:

>>> funds =
>>> funds
>>> list(funds)
[Fund: Markowitz]


Query are lazy, they are evaluated only when you iterate over them, or invoke the the Query.all method or slice the query like a list.


This operation is somehow equivalent to a SELECT WHERE statement in a traditional SQL database. To perform such operation, you refine the initial Query by adding filter conditions. Lets create few other objects in the same line as above and try:

>>> eur_funds ='EUR')
>>> eur_funds
Query.filter({'ccy': 'EUR'})
>>> eur_funds.count()
>>> list(eur_funds)
[Fund: Markowitz]

The Query.count() method counts the object in the query without actually retrieving them. It is possible to filter from a list/tuple of values:

qs ='EUR','USD'))

This filter statement is equivalent to an union of two filters statements:

q1 ='EUR')
q2 ='USD')
qs = q1.union(q2)


Filter and exclude lookups can only be done on Field which are indices, i.e. thier Field.index attribute is set to True.

Concatenating filters

You can perform further selection by concatenating filters:

qs = models.instrument.filter(ccy=('EUR','USD')).filter(types=('equity','bond'))

or equivalently:

qs = models.instrument.filter(ccy=('EUR','USD'), types=('equity','bond'))

Which is equivalent to an intersection of two filter statement:

q1 ='EUR', 'USD'))
q2 ='equity','bond'))
qs = q1.intersect(q2)


You can also exclude fields from lookups:

qs = models.instrument.exclude(type='future')

You can exclude a list of fields:

qs = models.instrument.exclude(type=('future','equity'))

Concatenation is also supported:

qs = models.instrument.exclude(ccy=('EUR','USD'), types=('equity','bond'))


Query.filter() and Query.exclude() methods cover most common situations. There is another method which can be used to combine together two or more Query into a different query. The Query.union() method performs just that, an union of queries. Consider the following example:

qs = models.instrument.filter(ccy='EUR', type='equity')

this retrieve all instruments with ccy='EUR' AND type='equity'. What about if we need all instruments with ccy='EUR' OR type='equity'? We use the Query.union() method:

q1 = models.instrument.filter(type='equity')
qs = models.instrument.filter(ccy='EUR').union(q1)

Range lookups

Range lookups is how you refine the query methods you have learned so far. They are specified by appending a suffix to the field name preceded by double underscore __. Range lookups can be applied to any Field which has an internal numerical representation. Such fields are: IntegerField, FloatField, DateField, DateTimeField and so on.

There are four of them:

  • gt, greater than. For example:

    qs = models.position.filter(size__gt=100)
  • ge, greater than or equal to. For example:

    qs = models.position.filter(size__ge=100)
  • lt, less than. For example:

    qs = models.position.filter(size__lt=100)
  • le, less than or equal to. For example:

    qs = models.position.filter(size__le=100)

They can be combined, for example, this is a Query for a size between 10 and 100:

qs = models.position.filter(size__ge=10, size__le=100)

Text lookups

Text lookups is the range lookup for text fields such as SymbolField, CharField and JSONField.

There are four of them:

  • contains, check if a text field contains the text. For example:

    qs ='technology')
  • startswith, check if a text field starts with the given text. For example:

    qs ='The')
  • endswith, check if a text field ends with the given text. For example:

    qs ='a')


Use the Query.where() method to pass a string containing a valid expression to the query system to provide greater flexibility with queries. Consider the following model:

class Data(odm.StdModel):
    flag = odm.CharField()
    a = odm.FloatField()
    b = odm.FloatField()


The following is a query which works for both mongo and redis:

qs ='this.a > this.b')

The where method can be chained in the same way as filter and exclude:

s ='foo', a__lt=4).where('this.a > this.b')


The expression evaluates to lua in redis and to javascript in mongo.

Limit Query Size

When dealing with large amount of data, a Query can be sliced using python array-slicing syntax. For example, this returns the first 10 objects:

>> qs = models.instrument.query()[:10]

This returns the sixth through tenth objects:

>> qs = models.instrument.query()[5:10]

This returns the last 5 objects:

>> qs = models.instrument.query()[-5:]

Table Of Contents

Previous topic


Next topic

Sorting and Ordering

This Page