A simple PostgreSQL-Python relation-object mapper.
Project description
A simple PostgreSQL-Python relation-object mapper.
half_orm
maps an existing PostgreSQL database into Python objects with inheritance as defined in PostgreSQL.
half_orm
only deals with the data manipulation language (DML) part of SQL, basically the INSERT
, SELECT
, UPDATE
and DELETE
commands.
You have a PostgreSQL database ready at hand, here is what coding with halfORM looks like :
from half_orm.model import Model
from half_orm.relation import singleton
halftest = Model('halftest') # We connect to the PostgreSQL database
# print(halftest) to get the list of relations in the database
class Post(halftest.get_relation_class('blog.post')):
"""blog.post is a table of the halftest database (<schema>.<relation>)
To get a full description of the relation, use print(Post())
"""
Fkeys = { # we set some aliases for the foreign keys (direct AND reverse) (half_orm >= 0.6.5)
'comments': '_reverse_fkey_halftest_blog_comment_post_id', # a post is referenced by comments
'author': 'author' # the post references a person
}
class Person(halftest.get_relation_class('actor.person')):
Fkeys = {
'posts': '_reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date',
'comments': '_reverse_fkey_halftest_blog_comment_author_id'
}
@singleton # we ensure that self is a singleton of the actor.person table
def add_post(self, title: str=None, content: str=None) -> dict:
return self.posts(title=title, content=content).insert()[0] # we use the insert method
@singleton
def add_comment(self, post: Post=None, content: str=None) -> dict:
return self.comments(content=content, post_id=post.id.value).insert()[0]
def main():
# let's define a Person set (a singleton here) by instanciating a set with some constraints
gaston = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')
gaston.delete() # the delete method
if gaston.is_empty(): # always true since we've just deleted gaston
gaston.insert()
post_dct = gaston.add_post(title='Easy', content='halfORM is fun!')
post = Post(**post_dct)
gaston.add_comment(content='This is a comment on the newly created post.', post=post)
print(list(post.comments().select()))
post.update(title='Super easy')
gaston.delete()
Learn half_orm
in half an hour
Install half_orm
run pip install half_orm
in a virtual environment.
Set your HALFORM_CONF_DIR
Create a directory to store your connection files and set the shell variable HALFORM_CONF_DIR
:
% mkdir ~/.half_orm
% export HALFORM_CONF_DIR=~/.half_orm
Set your HALFORM_CONF_DIR for windows users:
- select settings in the menu
- search for "variable"
- select "Edit environment variables for your account"
Create a connection file in the $HALFORM_CONF_DIR
containing the following information (with your values):
[database]
name = db_name
user = username
password = password
host = localhost
port = 5432
Your ready to go!
Connect to the database
>>> from half_orm.model import Model
>>> my_db = Model('my_database')
The my_database
is the name of the connexion file. It will be fetched in the directory referenced by
the environment variable HALFORM_CONF_DIR
if defined, in /etc/half_orm
otherwise.
Get a rapid description of the database structure
Once connected to the database, you can easily have an overview of its structure:
print(my_db)
It displays as many lines as there are relations, views or materialized views in your database. Each row has the form:
<relation type> <"schema name"."relation name">
Where relation type
is one of r
, p
, v
, m
, f
:
r
for a relation,p
for a partitioned table,v
for a view,m
for a materialized view,f
for foreign data.
for instance (using the halftest database):
r "actor"."person"
r "blog"."comment"
r "blog"."event"
r "blog"."post"
v "blog.view"."post_comment"
Check if a relation exists in the database
>>> my_db.has_relation('blog.view.post_comment')
True
Get the class of a relation (the Model.get_relation_class
method)
To work with a table of your database, you must instanciate the corresponding class:
class Person(halftest.get_relation_class('actor.person')):
pass
class PostComment(halftest.get_relation_class('blog.view.post_comment')):
pass
The argument passed to get_relation_class
is as string of the form:
<schema_name>.<relation_name>
.
Note: Dots are only allowed in schema names.
To get a full description of the corresponding relation, print an instance of the class:
>>> print(Person())
__RCLS: <class 'half_orm.relation.Table_HalftestActorPerson'>
This class allows you to manipulate the data in the PG relation:
TABLE: "halftest":"actor"."person"
DESCRIPTION:
The table actor.person contains the people of the blogging system.
The id attribute is a serial. Just pass first_name, last_name and birth_date
to insert a new person.
FIELDS:
- id: (int4) UNIQUE NOT NULL
- first_name: (text) PK
- last_name: (text) PK
- birth_date: (date) PK
FOREIGN KEYS:
- _reverse_fkey_halftest_blog_comment_author_id: ("id")
↳ "halftest":"blog"."comment"(author_id)
- _reverse_fkey_halftest_blog_event_author_first_name_author_last_name_author_birth_date: ("birth_date", "first_name", "last_name")
↳ "halftest":"blog"."event"(author_first_name, author_last_name, author_birth_date)
- _reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date: ("birth_date", "first_name", "last_name")
↳ "halftest":"blog"."post"(author_first_name, author_last_name, author_birth_date)
To use the foreign keys as direct attributes of the class, copy/paste the Fkeys bellow in
your code as a class attribute and replace the empty string(s) key(s) with the alias you
want to use. The aliases must be unique and different from any of the column names. Empty
string keys are ignored.
Fkeys = {
'': '_reverse_fkey_halftest_blog_comment_author_id',
'': '_reverse_fkey_halftest_blog_event_author_first_name_author_last_name_author_birth_date',
'': '_reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date',
}
It provides you with information extracted from the database metadata:
- description: the comment on the relationship if there is one,
- fields: the list of columns, their types and contraints
- foreign keys: the list of FKs if any. A
_reverse_*
FK is a FK made on the current relation.
Constraining a relation
When you instantiate an object with no arguments, its intention corresponds to all the data present in the corresponding relation.
Person()
represents the set of people contained in the actor.person
table (ie. there is no constraint on the set). You can get the number of elements in a relation whith the len
function as in len(Person())
.
To constrain a set, you must specify one or more values for the fields/columns in the set with a tuple of the form: (comp, value)
.
comp
(=
if ommited) is either a
comparison operator or a pattern matching operator (like or POSIX regular expression).
You can constrain a relation object at instanciation:
Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')
Person(last_name=('ilike', '_a%'))
Person(birth_date='1957-02-28')
You can also constrain an instanciated object:
gaston = Person()
gaston.last_name = ('ilike', 'l%')
gaston.first_name = 'Gaston'
half_orm
prevents you from making typos:
gaston.lost_name = 'Lagaffe'
# raises a half_orm.relation_errors.IsFrozenError Exception
The NULL
value
half_orm
provides the NULL
value:
from half_orm.null import NULL
nobody = Person()
nobody.last_name = NULL
assert len(nobody) == 0 # last_name is part of the PK
Set operators
You can use the set operators to set more complex constraints on your relations:
&
,|
,^
and-
forand
,or
,xor
andnot
. Take a look at the algebra test file.- you can also use the
==
,!=
andin
operators to compare two sets.
my_selection = Person(last_name=('ilike', '_a%')) | Person(first_name=('ilike', 'A%'))
my_selection
represents the set of people whose second letter of the name is an a
or whose first letter of the first name is an a
.
DML. The insert
, select
, update
, delete
methods.
These methods trigger their corresponding SQL querie on the database. For debugging purposes, you can print the SQL query built by half_orm when the DML method is invoked using the _mogrify() method.
people._mogrify()
people.select()
Insert
To insert a tuple in the relation, use the insert
method as shown below:
Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28').insert()
insert
returns the row as a dict in a list. So, to get the id
of the newly inserted row, you can write:
lagaffe = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')
lagaffe_id = lagaffe.insert()[0]['id']
You can trigger a transaction for any combination of insert, modify or delete operations using the Relation.Transaction
decorator.
class Person(halftest.get_relation_class('actor.person')):
# [...]
def insert_many(self, *data):
"""Insert multiple people in a single transaction."""
@self.Transaction
def insert(self, *data):
for d_pers in data:
self(**d_pers).insert()
insert(self, *data)
people = Person()
people.insert_many(*[
{'last_name':'Lagaffe', 'first_name':'Gaston', 'birth_date':'1957-02-28'},
{'last_name':'Fricotin', 'first_name':'Bibi', 'birth_date':'1924-10-05'},
{'last_name':'Maltese', 'first_name':'Corto', 'birth_date':'1975-01-07'},
{'last_name':'Talon', 'first_name':'Achile', 'birth_date':'1963-11-07'},
{'last_name':'Jourdan', 'first_name':'Gil', 'birth_date':'1956-09-20'}
])
Note: half_orm works in autocommit mode by default. Without transaction, all the missing data would be inserted.
Select
The select
method is a generator. It returns all the data of the relation that match the constraint defined on the Relation object.
The data is returned in a list of RealDictRow
s. A RealDictRow
is a subclass of dict
provided by psycopg2.
>>> people = Person()
>>> print(list(people.select()))
[RealDictRow([('id', 159), ('first_name', 'Gil'), ('last_name', 'Jourdan'), ('birth_date', datetime.date(1956, 9, 20))]), RealDictRow([('id', 160), ('first_name', 'Gaston'), ('last_name', 'Lagaffe'), ('birth_date', datetime.date(1957, 2, 28))]), RealDictRow([('id', 161), ('first_name', 'Bibi'), ('last_name', 'Fricotin'), ('birth_date', datetime.date(1924, 10, 5))]), RealDictRow([('id', 162), ('first_name', 'Corto'), ('last_name', 'Maltese'), ('birth_date', datetime.date(1975, 1, 7))]), RealDictRow([('id', 163), ('first_name', 'Achile'), ('last_name', 'Talon'), ('birth_date', datetime.date(1963, 11, 7))])]
>>>
You can set a limit or an offset:
>>> people.offset(1).limit(2)
>>> print([dict(elt) for elt in list(people.select())])
[{'id': 232, 'first_name': 'Gaston', 'last_name': 'Lagaffe', 'birth_date': datetime.date(1957, 2, 28)}, {'id': 233, 'first_name': 'Bibi', 'last_name': 'Fricotin', 'birth_date': datetime.date(1924, 10, 5)}]
You can also get a subset of the attributes by passing a list of columns names to select
:
>>> print(list(people.select('last_name')))
[{'last_name': 'Lagaffe'}, {'last_name': 'Fricotin'}]
Note: The set offset and limit still apply.
Select one: the get
method
The get
method returns an object whose fields are constrained with the values of the corresponding row in the database.
It raises an ExpectedOneError
Exception if 0 or more than 1 rows match the intention. The returned object is a singleton (see below).
gaston = Person(last_name='Lagaffe').get()
is equivalent to
lagaffe = Person(last_name='Lagaffe')
if lagaffe.is_empty() or len(lagaffe) > 1:
raise ExcpetedOneError
gaston = Person(**next(lagaffe.select()))
gaston._is_singleton = True
Is it a set? Is it an element of the set?
Let's go back to our definition of the class Person
. We would like to write a property that
returns the full name of a person.
class Person(halftest.get_relation_class('actor.person')):
# [...]
@property
def full_name(self):
return f'{self.first_name} {self.last_name}'
Used in the following context, the full_name
property wouldn't make much sens:
lagaffe = Person(last_name='Lagaffe')
lagaffe.full_name # returns 'None Lagaffe'
In this case, you can use the @singleton
decorator to ensure that the self object references one and only one element:
from half_orm.relation import singleton
class Person(halftest.get_relation_class('actor.person')):
@property
@singleton
def full_name(self):
return f'{self.first_name} {self.last_name}'
gaston = Person(first_name='Gaston')
gaston.full_name # now returns 'Gaston Lagaffe'
If more than one person has Gaston as first name in the actor.person
table, a NotASingletonError
exception would be raised:
half_orm.relation_errors.NotASingletonError: Not a singleton. Got X tuples
You can also get or set the singleton value. Be careful when using this possiblity. Under the hood a get
is made for every element returned by select
. Here is a common usage:
class Person(halftest.get_relation_class('actor.person')):
# [...]
def do_something(self):
for elt in self.select():
elt._is_singleton = True
...
This example works for two reasons:
select
is called without argument ensuring that all columns are retreived from the database,- The constraints of the
actor.person
table make it a set (ie. each element returned by select is indeed a singleton).
Update
To update a subset, you first define the subset an then invoque the udpate
method with the new values passed as argument.
gaston = Person(first_name='Gaston')
gaston.update(birth_date='1970-01-01')
Let's look at how we could turn the last name into capital letters for a subset of people:
class Person(halftest.get_relation_class('actor.person')):
# [...]
def upper_last_name(self):
"tranform last name to upper case."
@self.Transaction
def update(self):
for d_pers in self.select('id', 'last_name'):
pers = Person(**d_pers) # IMPORTANT! See the warning below.
pers.update(last_name=d_pers['last_name'].upper())
# ^^^^^^ here is the actual update
update(self)
Again, we insure the atomicity of the transaction using the Relation.Transaction
decorator.
>>> a_pers = Person(last_name=('ilike', '_a%'))
>>> print([elt.last_name for elt in list(a_pers.select())])
>>> a_pers = Person(last_name = ('ilike', '_a%'))
>>> print([elt['last_name'] for elt in a_pers.select('last_name')])
['Lagaffe', 'Maltese', 'Talon']
>>> a_pers.upper_last_name()
>>> print([elt['last_name'] for elt in a_pers.select('last_name')])
['LAGAFFE', 'MALTESE', 'TALON']
WARNING! The following code won't update the database. people.select()
returns a list of dictionaries and the update
method invoked here would only update the corresponding dictonary. It's a common pitfall.
for pers in people.select():
pers.update(...) # Won't work (pers is a dict)!
Update all data in a table
If you want to update all the data in a relation, you must set the argument update_all
to True
. A RuntimeError
is raised otherwise.
Person().update(birth_date='1970-01-01', update_all=True)
Delete
The delete method allows you to remove a set of elements from a table:
gaston = Person(first_name='Gaston')
gaston.delete()
To remove every tuples from a table, you must use the delete_all
argument with a True
value. A RuntimeError
is raised otherwise.
Person().delete(delete_all=True)
if not Person().is_empty():
print('Weird! You should check your "on delete cascade".')
Well, there is not much left after this in the actor.person
table.
Working with foreign keys [WIP]
This is a work in progress
A relational object integrates all the material necessary to process its foreign keys and the foreign keys that point to this object. When you print the object, its representation ends with the information about the foreign keys:
To use the foreign keys as direct attributes of the class, copy/paste the Fkeys bellow in
your code as a class attribute and replace the empty string(s) key(s) with the alias you
want to use. The aliases must be unique and different from any of the column names. Empty
string keys are ignored.
Fkeys = {
[...]
}
Let's see an example with the blog.post
relation:
>>> class Post(halftest.get_relation_class('blog.post')):
... pass
...
>>> Post()
__RCLS: <class '__main__.Post'>
This class allows you to manipulate the data in the PG relation:
TABLE: "halftest":"blog"."post"
DESCRIPTION:
The table blog.post contains all the post
made by a person in the blogging system.
FIELDS:
- id: (int4) PK
- title: (text)
- content: (text)
- author_first_name: (text)
- author_last_name: (text)
- author_birth_date: (date)
FOREIGN KEYS:
- _reverse_fkey_halftest_blog_comment_post_id: ("id")
↳ "halftest":"blog"."comment"(post_id)
- author: ("author_birth_date", "author_first_name", "author_last_name")
↳ "halftest":"actor"."person"(first_name, last_name, birth_date)
To use the foreign keys as direct attributes of the class, copy/paste the Fkeys bellow in
your code as a class attribute and replace the empty string(s) key(s) with the alias you
want to use. The aliases must be unique and different from any of the column names. Empty
string keys are ignored.
Fkeys = {
'': '_reverse_fkey_halftest_blog_comment_post_id',
'': 'author',
}
It has two foreign keys named _reverse_fkey_halftest_blog_comment_post_id
and author
:
author
is the foreign key that refrences anactor.person
from the tableblog.post
._reverse_fkey_halftest_blog_comment_post_id
is the foreign key that references ablog.post
from the tableblog.comment
. The foreign key is traversed in opposite direction (fromblog.post
toblog.comment
).
We redefine our class to add the aliases for our foreign keys:
class Post(halftest.get_relation_class('blog.post')):
Fkeys = {
'comments_rfk': '_reverse_fkey_halftest_blog_comment_post_id',
'author_fk': 'author'
}
Note: By convention, we suffix by _fk
the foreign keys and by _rfk
the foreign keys traversed in reverse.
The plural in comments_rfk
indicates that a post can be referenced by many comments.
A foreign key is a transitional object, so when you instanciate a FKey object, you get the relation it points to. The original constraint is propagated through the foreign key.
Given a post defined by a constraint
:
a_post = Post(**constraint)
comments_on_a_post = a_post.comments_rfk()
author_of_a_post = a_post.author_fk()
You can also add a filter on a foreign key.
comments_on_a_post_containing_simple = a_post.comment_rfk(content=('ilike', '%simple%'))
The Fkey class has the set
method which allows you to constrain a foreign key with a Relation object.
To get the comments made by Gaston, we simply constraint the author_fk
Fkey to reference the entry corresponding to Gaston in the actor.person table. To do so, we use the Fkey.set()
method:
gaston = Person(first_name='Gaston')
gaston_comments = Comment()
gaston_comments.author_fk.set(gaston)
print(list(gaston_comments.select())
TODO. Some documentation about the chaining of FKeys
The join
method
The join
method allows you to integrate the data associated to a Relation object in the result obtained by the select
method by using foreign keys of the object or referencing the object.
Unlike the select
method (which is a generator), the join
method returns a list.
It takes a list of tuples each having two or three elements:
-
a remote Relation object which must be reachable using a direct or "reverse" foreign key,
-
the name of the key under which the associated data would be stored,
-
an optional list of columns (str[]) or the name of a column (str) to be extracted from the remote object.
If the third argument is omitted, all columns are retreived.
The following code
lagaffe = Person(last_name='Lagaffe')
res = lagaffe.join(
(Comment(), 'comments', ['id', 'post_id']),
(Post(), 'posts', 'id')
)
would return the list of people named Lagaffe
with two
additional attributes : comments
and posts
.
The data associated with comments
is a list of dictionaries whose keys are 'id' and 'post_id'.
The data associated with posts
is a simple list of values corresponding to the 'id' column.
Last: SQL queries
If you realy need to invoke a SQL query not managed by half_orm, use
the Model.execute_query
method:
from half_orm.model import Model
halftest = Model('halftest')
halftest.execute_query('select 1')
By the way, this is the code used in the Model.ping
method that makes sure the connection is established and attempts a reconnection if it is not.
That's it! You've learn pretty much everything there is to know about half_orm
.
Next: hop
, the half_orm
packager
The hop
command, provided by the package half_orm_packager
, allows you to create a Python package corresponding to the model of your database, to patch the model and the corresponding Python code, to test your database model and your business code. For more information, see https://github.com/collorg/halfORM_packager.
Want to contribute?
Fork me on Github: https://github.com/collorg/halfORM
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for half_orm-0.7.0rc0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | b557d0ad7b775d5d6ef2e03458332b69f3130399f3ec95414712525a0d8a33a5 |
|
MD5 | 26fb0b7e4ff5b70de78fd06fb879f872 |
|
BLAKE2b-256 | d980ace51e130152f4919de13d3e7452e35553fae5a7ca62f4e0ebdd27664b7a |