jardin 0.22.12

A Pandas dataframe-based ORM

Jardin is a pandas.DataFrame-based ORM for Python applications.

Getting started

In your working directory (the root of your app), create a file named jardin_conf.py:

# jardin_conf.py

DATABASES = {
  'my_first_database': 'https://username:[e-mail]:port',
  'my_second_database': 'https://username:[e-mail]:port'
}

LOG_LEVEL = logging.DEBUG

WATERMARK = 'My Great App'

Then, in your app, say you have a table called users:

# app.py
import jardin

class User(jardin.Model):
  db_names = {'read': 'my_first_database', 'write': 'my_second_database'}

In the console:

>>> from app import Users
>>> users = User.last(4)
# /* My Great App */ SELECT * FROM users ORDER BY u.created_at DESC LIMIT 4;
>>> users
id   name    email              ...
0    John    [e-mail]      ...
1    Paul    [e-mail]      ...
2    George  [e-mail]    ...
3    Ringo   [e-mail]     ...

The resulting object is a pandas dataframe:

>>> import pandas
>>> isinstance(users, pandas.DataFrame)
True
>>> isinstance(users, jardin.Collection)
True

Queries

SELECT queries

Here is the basic syntax to select records from the database

>>> users = User.select(select = ['id', 'name'], where = {'email': '[e-mail]'},
                         order = 'id ASC', limit = 1)
# /* My Great App */ SELECT u.id, u.name FROM users u WHERE u.email = '[e-mail]' ORDER BY u.id ASC LIMIT 1;
>>> users
id   name
1    Paul

Arguments

• select – The list of columns to return. If not provided, all columns will be returned.

• where – conditions. Many different formats can be used to provide conditions. See docs.

• inner_join, left_join – List of tables to join with their join condition. Can also be a list of classes if the appropriate associations have been declared. See docs.

• order – order clause

• limit – limit clause

• group – grouping clause

• scopes – list of pre-defined scopes. See docs.

where argument

Here are the different ways to feed a condition clause to a query. * where = "name = 'John'" * where = {'name': 'John'} * where = {'id': (0, 3)} – selects where id is between 0 and 3 * where = {'id': [0, 1, 2]} – selects where id is in the array * where = [{'id': (0, 10), 'instrument': 'drums'}, ["created_at > %(created_at)s", {'created_at': '1963-03-22'}]]

inner_join, left_join arguments

The simplest way to join another table is as follows

>>> User.select(inner_join = ["instruments i ON i.id = u.instrument_id"])

If you have configured your models associations, see here, you can simply pass the class as argument:

>>> User.select(inner_join = [Instruments])

Individual record selection

You can also look-up a single record by id:

>>> User.find(1)
# /* My Great App */ SELECT * FROM users u WHERE u.id = 1;
{'id': 1, 'name': 'Paul', 'email': '[e-mail]', ...}

Note that the returned object is a Record object which allows you to access attributes in those way:

>>> user['name']
Paul
>>> user.name
Paul

INSERT queries

>>> user = User.insert(name = 'Pete', email = '[e-mail]')
# /* My Great App */ INSERT INTO users (name, email) VALUES ('Pete', '[e-mail]') RETURNING id;
# /* My Great App */ SELECT u.* FROM users WHERE u.id = 4;
>>> user
id   name    email
4    Pete    [e-mail]

UPDATE queries

>>> users = User.update(values = {'hair': 'long'}, where = {'name': 'John'})
# /* My Great App */ UPDATE users u SET (u.hair) = ('long') WHERE u.name = 'John' RETURNING id;
# /* My Great App */ SELECT * FROM users u WHERE u.name = 'John';

DELETE queries

>>> User.delete(where = {'id': 1})
# /* My Great App */ DELETE FROM users u WHERE u.id = 1;

Associations

It is possible to define associations between models. For example, if each user has multiple instruments:

# app.py

import jardin

class MyModel(jardin.Model):
  db_names = {'read': 'my_first_database', 'write': 'my_second_database'}

class Instrument(MyModel):
  belongs_to = {'users': 'user_id'}

class User(MyModel):
  has_many = [Instruments]

and then you can query the associated records:

>>> users = User.select()
# /* My Great App */ SELECT * FROM users u;
>>> instruments = users.instruments()
# /* My Great App */ SELECT * FROM instruments i WHERE i.id IN (0, 1, ...);

Or you can declare joins more easily

>>> users = User.select(inner_join = [Instruments])

Scopes

Queries conditions can be generalized across your app:

# app.py

class User(jardin.Model):
  scopes = {
    'alive': {'name': ['Paul', 'Ringo']},
    'guitarists': {'name': ['John', 'George']}
  }

The key is the name of the scope, and the value is the conditions to be applied. Anything that can be fed to the where argument of Model#select can be used to define a scope.

Use them as such:

>>> users = User.select(scopes = ['alive'], ...)
# /* My Great App */ SELECT * FROM users u WHERE u.name IN ('Paul', 'Ringo') AND ...;

Misc

Caching

Jardin implements a LRU caching mechanism for the jardin.query method.

Setup

To confgure, add in jardin_conf.py.

# jardin_conf.py

# to configure cache methods:

CACHE = {
    'methods' : {
        'disk': {
            'dir': <path to cache directory>, # default to `/tmp/jardin_cache`
            'limit': 100000, # maximum size in bytes of cached files. when size of cache is above limit, files are deleted based on LRU # default to None
            },
        's3': {
            'bucket_name': <bucket name>,
            'path': <path>, # subfolder path where all cached files will be placed
            'delete_expired_files': False # default is False

        }
    },
    'method': <default method> # default to None
}

Methods supported

• disk (files saved in feather format)

• S3

• memcached (coming soon)

Usage

Then, you can use it with:

>>> df = jardin.query(sql, params, db="jardin_db", cache=True, ttl=10, cache_method="s3")

Watermark and trace

Multiple databases