Query your Data¶

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 = models.fund.query()
>>> funds
Query
>>> list(funds)
[Fund: Markowitz]

Filtering¶

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 = models.fund.filter(ccy='EUR')
>>> eur_funds
Query.filter({'ccy': 'EUR'})
>>> eur_funds.count()
1
>>> 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 = models.fund.filter(ccy=('EUR','USD'))

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

q1 = models.fund.filter(ccy='EUR')
q2 = models.fund.filter(ccy='USD')
qs = q1.union(q2)

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 = models.fund.filter(ccy=('EUR', 'USD'))
q2 = models.fund.filter(types=('equity','bond'))
qs = q1.intersect(q2)

Exclude¶

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'))

Union¶

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 = models.fund.filter(description__contains='technology')
  

• startswith, check if a text field starts with the given text. For example:

  qs = models.fund.filter(description__starts='The')
  

• endswith, check if a text field ends with the given text. For example:

  qs = models.fund.filter(description__endswith='a')
  

Where¶

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()
    ...

models.register(Data)

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

qs = models.data.query().where('this.a > this.b')

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

s = models.data.filter(flag='foo', a__lt=4).where('this.a > this.b')

Related Fields¶

The query API goes even further by allowing to operate on Fields of ForeignKey models. For example, lets consider the Position model in our example application. The model has a ForeignKey to the Instrument model.

Using the related field query API one can construct a query to fetch positions an subset of instruments in this way:

qs = models.position.filter(instrument__ccy='EUR')

that is the name of the ForeignKey field, followed by a double underscore __, followed by the name of the field in the related model.

This is merely a syntactic sugar in place of this equivalent query:

qi = models.instrument.filter(ccy='EUR')
qs = models.position.filter(instrument=qi)

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:]