Skip to main content

A dataframe-base ORM

Project description

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:password@database.url:port',
  'my_second_database': 'https://username:password@database.url: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    john@beatl.es      ...
1    Paul    paul@beatl.es      ...
2    George  george@beatl.es    ...
3    Ringo   ringo@beatl.es     ...

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': 'paul@beatl.es'},
                         order = 'id ASC', limit = 1)
# /* My Great App */ SELECT u.id, u.name FROM users u WHERE u.email = 'paul@beatl.es' 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': 'paul@beatl.es', ...}

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 = 'pete@beatl.es')
# /* My Great App */ INSERT INTO users (name, email) VALUES ('Pete', 'pete@beatl.es') RETURNING id;
# /* My Great App */ SELECT u.* FROM users WHERE u.id = 4;
>>> user
id   name    email
4    Pete    pete@beatl.es

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

Watermark and trace

Multiple databases

Project details


Release history Release notifications

History Node

0.19.3

History Node

0.19.2

History Node

0.19.1

History Node

0.19.0

History Node

0.18.4

History Node

0.18.3

History Node

0.18.2

History Node

0.18.1

History Node

0.18.0

History Node

0.17.3

History Node

0.17.2

History Node

0.17.1

History Node

0.17.0

History Node

0.16.0

History Node

0.15.0

History Node

0.14.8

History Node

0.14.7

History Node

0.14.6

History Node

0.14.5

History Node

0.14.4

History Node

0.14.3

History Node

0.14.2

History Node

0.14.1

History Node

0.14.0

This version
History Node

0.13.3

History Node

0.13.2

History Node

0.13.1

History Node

0.13.0

History Node

0.10.0

History Node

0.9.15

History Node

0.9.14

History Node

0.9.13

History Node

0.9.12

History Node

0.9.11

History Node

0.9.10

History Node

0.9.9

History Node

0.9.8

History Node

0.9.7

History Node

0.9.6

History Node

0.9.5

History Node

0.9.4

History Node

0.9.3

History Node

0.9.2

History Node

0.9.1

History Node

0.9.0

History Node

0.8.3

History Node

0.8.2

History Node

0.8.1

History Node

0.8.0

History Node

0.7.5

History Node

0.7.4

History Node

0.7.3

History Node

0.7.2

History Node

0.7.1

History Node

0.7.0

History Node

0.6.3

History Node

0.6.2

History Node

0.6.1

History Node

0.6.0

History Node

0.5.7

History Node

0.5.6

History Node

0.5.5

History Node

0.5.4

History Node

0.5.3

History Node

0.5.2

History Node

0.5.1

History Node

0.5.0

History Node

0.4.0.1

History Node

0.4.0

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
jardin-0.13.3.tar.gz (13.6 kB) Copy SHA256 hash SHA256 Source None Feb 13, 2018

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging CloudAMQP CloudAMQP RabbitMQ AWS AWS Cloud computing Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page