ORM¶
ORM Philosophy¶
It’s common in the ORM world to write your tables schema in your python code. This cause majors issues.
First of the is duplication. Your schema is in your database AND in your python code. Every time one chage, the other has to change.
Second is static schema. Database are not bound to a schema, they are bound to projections. Here is an example, let say you have this database schema:
Table bookstore_store:
id | integer | not NULL default, nextval('bookstore_store_id_seq'::regclass)
name | character varying(250) | not NULL
close_time | integer | not NULL
open_time | integer | not NULL
open_date | date | not NULL
location_id | integer | not NULL
Table bookstore_location:
id | integer | not NULL default, nextval('bookstore_location_id_seq'::regclass)
name | character varying(250) | non NULL
a store object will always have the representation
Store:
id
name
close_time
open_time
open_date
location_id
Let say you only need the name and the location name you will write something like:
for store in stores:
store.name
store.location.name # Your ORM without telling you anything
# will make a query on location for each
# store
In Django for example, you will need to specify a select_related argument to your query to retreive location.name when querying the store table. You can’t get only the store.name and the location.name without loosing the objects paradigm (or using the “only” parameter wich will not raise anything but make a query for each field you forget)
Because database can manage this in an admirable manner, and much more, we decide to create a schemaless ORM without breaking the Object Oriented paradigm. Seems interesting? Let’s take the ride!
Database Management¶
Drunken Boat is focused on performances. Most of current applications lack performances due to ORM. Yes ORM are great when you need Object Oriented programation but they lack a lot of features you can find in modern database like PostgreSQL.
Drunken Boat want to help you write powerful applications where you can use the most of your database and still use Object Oriented programmation.
This is the reason why Drunken Boat does not force you to create your database nor managing table schema in his ORM. Sure it gives you some helpful methods and functions to create database, schema, make ALTER TABLE on your databases but it’s absolutely up to you to manage them the way you like.
Configuration & Table creation¶
In the project created by drunken_run.py the file config.py contains the base detail of a database connection. Change the DATABASE with connection informations of your database.
Even if drunken_boat don’t force you to create table from python, for this tutorial you can use this simple script to generate the table you will use in the next step:
#projection.py
from example_blog.config import DATABASE
def create_tables():
db = DB(**DATABASE)
cur = db.cursor()
cur.execute(
"""select exists(
select * from information_schema.tables where table_name=%s)
""",
('test',))
if not cur.fetchone()[0]:
cur.execute("""CREATE TABLE test (
id serial PRIMARY KEY,
num integer,
data varchar,
birthday timestamp)""");
db.conn.commit()
print("table created")
return
print("table already exists")
Projections¶
Projections are the object based representation of the result of a database query. See them as what you expect from the database.
Let say you make this query:
select name, age(birthdate) from user;
the corresponding projection will just fit:
class UserNameAge(Projection):
name = CharField()
age = Timestamp(name="age(birthdate)")
class Meta:
table = "user"
projection = UserNameAge(DB(**connection_params))
And you can get your results as easily as:
>> users = projection.select()
>> users[0].age
datetime.timedelta(13850, 50160)
results are list of DataBaseObject. because DataBaseObject are objects, you can attach any method you want on it. For example:
from config import DATABASE
from drunken_boat.db.postgresql.fields import Timestamp
from drunken_boat.db.postgresql.projections import (Projection,
DataBaseObject)
class ExampleDataBaseObject(DataBaseObject):
def display_birthyear_and_days(self):
days = self.age.days
year = self.birthdate.year
return "{} days since {}".format(days, year)
class ExampleProjection(Projection):
"""
Here you can write your real projections
"""
age = Timestamp(db_name="age(birthday)", virtual=True)
birthdate = Timestamp()
class Meta:
table = "test"
database_object = ExampleDataBaseObject
example_projection = ExampleProjection(DB(**DATABASE))
>>> from projections import example_projection
>>> t = example_projection.select()
>>> t[0].display_birthyear_and_days()
'13850 days since 1977'
Where¶
One thing you will surely do very often is to use Projection with WHERE clause. Where clause are defined with 2 sides. First side is the clause and the comparison operator, the other side is the parameter.
For example, in the statement:
WHERE id > 4;
id is the clause, > is the comparison operator, and 4 is the parameter.
The first an easier way to make a query with a WHERE clause is simply adding where and parameter to the select statement:
>>> projection.select(where='id=%s', params=(1,))
If it’s perfectly ok to do so, but sometimes you will need to store a WHERE clause to use it in many places in your code. For this the Where object is here to help you.
A where object take a clause, an operator and a value:
from drunken_boat.db.postgresql.query import Where
where = Where("id", "=", "%s")
As you can see a Where object is very similar to the select version. The difference is that you do not define a parameter yet. The parameter will be define when calling the select method of your Projection:
>>> projection.select(where=where, params=(1,))
Multiple Where¶
It’s also possible to use multiple where in a single select using biwise operations. AND, OR and NOT are supported:
AND:
>>> where = Where("id", "=", "%s") & Where("title", "=", "%s")
OR:
>>> where = Where("id", "=", "%s") | Where("title", "=", "%s")
NOT:
>>> where = Where("id", "=", "%s") & ~Where("title", "=", "%s")
NOT can be used as is to make exclude clause:
>>> where = ~Where("title", "=", "%s")
You can also define priorities with parenthesis:
>>> where = Where("id", "=", "%s") | (Where("title", "=", "%s") & Where("intro", "=", "%s"))
this will be rendered as:
id = %s OR (title = %s AND intro = %s)
Insert¶
Even if you do not describe the table schema of your tables, drunken_boat introspect your table schema to give you automatic validation of data before even hitting the database.
To demonstrate this behavior let’s create another table:
Table : test
id serial PRIMARY KEY,
num integer NOT NULL,
data varchar NOT NULL,
birthday timestamp
And another projection:
class ExampleProjection(Projection):
"""
Here you can write your real projections
"""
age = Timestamp(db_name="age(birthday)", virtual=True)
birthday = Timestamp()
class Meta:
table = "test"
database_object = ExampleDataBaseObject
example_projection = ExampleProjection(DB(**DATABASE))
Now, with a shell try to insert some data in the table:
>>> from projections import example_projection
>>> example_projection.insert({"birthday": datetime.datetime.now()})
ValueError: num of type integer is required
data of type character varying is required
Now that you know wich data you must use to insert data you can type:
>>> example_projection.insert({"num": 10,
... "data": "some data"})
You can check that your record is saved in the database:
>>> example_projection.select()
... [<projections.DataBaseObject at 0x7f2ac0447c10>]
Returning¶
You can feel a bit disturbing to do not have a hint on what’s the result of your insert. If you want to get results, you can use returning parameter to get a result from the database:
>>> example_projection.insert({"num": 10,
... "data": "some data"},
... returning="id, num, data")
(6, 10, 'some data')
Last but not least, you can even ask drunken_boat to return the object corresponding to the projection you actually use:
>>> import datetime
>>> obj = example_projection.insert(
... {"data": "hello",
... "num": "6",
... "birthday": datetime.datetime.now()},
... returning="self")
>>> obj.age
datetime.timedelta(-1, 33857, 32595)
>>> obj.birthday
datetime.datetime(2015, 5, 1, 14, 35, 42, 967405)
Relations¶
Foreignkey¶
When you need to manage relation between objects (ForeignKey), you will need a way to tell the Database wich fields of the related table you want to retreive. You will also need to tell the database how to handle the relation. Of course with projections it’s really easy to do.
Of course you need to create the tables in your database. For this purpose you can use something like this:
db = DB(**DATABASE)
cur = db.cursor()
cur.execute(
"""CREATE TABLE author (id serial PRIMARY KEY,
first_name = varchar(250) NOT NULL,
last_name = varchar(250) NOT NULL)
""")
db.conn.commit()
cur.execute(
"""CREATE TABLE blog_post (id serial PRIMARY KEY,
title varchar(250),
introduction text,
body text,
created_at timestamp default now(),
last_edited_at default now(),
author_id integer NOT NULL,
published boolean default False)
""")
db.conn.commit()
cur.execute(
"alter table blog_post add foreign key(author_id)
references author"
)
db.conn.commit()
Then you can create two new projections:
class AuthorProjection(Projection):
first_name = CharField()
last_name = CharField()
birthdate = Timestamp()
class Meta:
table = "author"
author_projection = AuthorProjection(DB(**DATABASE))
class PostProjection(Projection):
title = CharField()
introduction = Text()
body = Text()
created_at = Timestamp()
last_edited_at = Timestamp()
author = ForeignKey(join=["author_id", "id"],
projection=AuthorProjection)
published = Boolean()
class Meta:
table = "blog_post"
post_projection = PostProjection(DB(**DATABASE))
ForeignKey take two mandatory parameters, join and projection.
- join: This is a list of 2 elements. First element is the field on
the table you’re working on. Second element is the field on the related table.
projection:: The projection to use to render the field.
Usage of projections with foreignkeys are straitforward:
>>> from projections import post_projection
>>> post = post_projection.select()[0]
>>> post.__dict__
{'author': <drunken_boat.db.postgresql.projections.DataBaseObject at 0x7f7170187490>,
'body': None,
'created_at': datetime.datetime(2015, 5, 1, 17, 18, 20, 95226),
'introduction': 'Pouet Pouet PimPim',
'last_edited_at': datetime.datetime(2015, 5, 1, 17, 18, 20, 95226),
'published': False,
'title': 'hello'}
>>> post.author.__dict__
{'birthdate': None, 'first_name': 'Paul', 'last_name': 'Eluard'}
ReverseForeignkey¶
Another cas you will encounter a lot is when you want to reverse the relation. In our example, this can be :
How to get the authors with their corresponding posts ?
To solve this case we have to retreive all the posts belonging to one of the author and then dispatch the posts to the corresponding author representation.
ReverseForeign is a type of Field created for this job.
It need to know the related column on the “from” side and the related column on the “to” side. Exactly the opposite of ForeignKey.
In our example we want all the post with an author_id equal to the author.id.
We also need to tell ReverseForeign wich Projection to use for rendering the posts. Here is an example:
class PostProjectionRelated(Projection):
title = CharField()
introduction = Text()
class Meta:
table = "blog_post"
post_projection_related = PostProjectionRelated(DB(**DATABASE))
class AuthorProjectionWithPost(AuthorProjection):
posts = ReverseForeign(join=["id", "author_id"],
projection=PostProjectionRelated)
author_projection_with_post = AuthorProjectionWithPost(DB(**DATABASE))
author_projection_with_post.select() will return a list of Author with the attribute posts containing all the posts of this author:
>>> for author in author_projection_with_post.select():
... print(author.id, [post.__dict__ for post in author.posts])
1, [],
2, [{"title": "a title", "introduction": "an introduction",
"author_id": 2}, {"title": "another title", "introduction": "another
introduction", "author_id":2 ] ...
If the first element of ReverseForeign.join is not in the projection, (id in the example) it will be automaticaly added.
The same go for the ReverseForeign.projection wich will gain the second part of ReverseForeign.join (author_id in the example).
This is the reason why we can get author.id even if id is not on the AuthorProjectionWithPost.fields and post.author_id even if author_id is not on PostProjectionRelated.fields
Filter reverse foreignkey¶
Sometimes getting the related objects is not enought and you will need to filter the related objects.
To do so, drunken_boat offer a simple API. You only need to give to the select method a related argument to hold every related fields where and params:
>>> projection = author_projection_with_post.select(
... related={'posts':
... 'where': 'title=%s',
... 'params': ('a title')})
>>> print post.__dict__ for post in projection[1].posts]
[{"title": "a title", "introduction": "an introduction",
"author_id": 2}]