# `half_orm`: A simple PostgreSQL to Python mapper
[](https://pypi.org/project/half-orm/)
[](https://pypi.org/project/half-orm/)
[](https://www.python.org)
[](https://www.postgresql.org)
[](https://github.com/collorg/halfORM/actions/workflows/python-package.yml)
[](https://github.com/collorg/halfORM/actions/workflows/postgresql-releases.yml)
[](https://coveralls.io/github/collorg/halfORM?branch=main)
[](https://clickpy.clickhouse.com/dashboard/half-orm)
[](https://github.com/collorg/halfORM/graphs/contributors)
Nowadays, most applications require interacting with a relational database. While full-fledged ORMs like SQLAlchemy are very powerful, their complexity, steep learning curve, and some of their limitations can be a hindrance. This is the context in which half_orm was born, a minimalist ORM specifically designed for PostgreSQL. The main motivation is to allow modeling the database directly in SQL, taking full advantage of the capabilities offered by the PostgreSQL engine (triggers, views, functions, stored procedures, inheritance handling...), while avoiding the "impedance mismatch" issues, loss of control over generated SQL, and rigidity encountered with full-fledged ORMs that model the schema at the object level. Half_orm intentionally excludes the DDL aspects (schema creation) of the SQL language. The goal is to provide a lightweight abstraction layer over standard SQL queries while maintaining transparent access to the underlying database engine. With half_orm, writing INSERT, SELECT, UPDATE, and DELETE queries becomes as simple as with SQL, but in the comfort of Python. Its operation aims to be intuitive thanks to a lean API that emphasizes productivity and code readability.
Here is what coding with `half_orm` looks like :
```py
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())
"""
# The Fkeys template is provided by print(Post()). Just fill in the keys names.
Fkeys = {
'comments_rfk': '_reverse_fkey_halftest_blog_comment_post_id', # a post is referenced by comments
'author_fk': 'author' # the post references a person
}
class Person(halftest.get_relation_class('actor.person')):
Fkeys = {
'posts_rfk': '_reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date',
'comments_rfk': '_reverse_fkey_halftest_blog_comment_author_id'
}
@singleton # This ensures that the author of the post is well defined.
def add_post(self, title: str=None, content: str=None) -> dict:
return self.posts_rfk(title=title, content=content).ho_insert()
@singleton
def add_comment(self, post: Post=None, content: str=None) -> dict:
return self.comments_rfk(content=content, post_id=post.id.value).ho_insert()
def main():
gaston = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')
gaston.ho_delete()
if gaston.ho_is_empty(): # gaston defines a subset of the actor.person table.
gaston.ho_insert()
post = Post(**gaston.add_post(title='Easy', content='halfORM is fun!'))
gaston.add_comment(content='This is a comment on the newly created post.', post=post)
print(list(post.comments_rfk())) # The relational objects are iterators
post.ho_update(title='Super easy')
gaston.ho_delete()
```
If you want to build or patch a model, see the [`half_orm packager`](#next-hop-the-gitops-half_orm-packager-wipalpha).
# Tutorial: 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`
(by default, `half_orm` looks in the /etc/half_orm directory):
```sh
% 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):
```ini
[database]
name = db_name
user = username
password = password
host = localhost
port = 5432
```
You are ready to go!
## Connect to the database
```py
>>> 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 you are connected, you can easily have an overview of the structure of the database:
```py
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"
```
**Note**: We only allow dots in schema names.
## Check if a relation exists in the database
```py
>>> 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:
```py
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**: Again, dots are only allowed in schema names.
To get a full description of the corresponding relation, print an instance of the class:
```py
>>> print(Person())
```
```
DATABASE: halftest
SCHEMA: actor
TABLE: person
DESCRIPTION:
The table actor.person contains the persons 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) NOT NULL
- first_name: (text) NOT NULL
- last_name: (text) NOT NULL
- birth_date: (date) NOT NULL
PRIMARY KEY (first_name, last_name, birth_date)
UNIQUE CONSTRAINT (id)
UNIQUE CONSTRAINT (first_name)
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: ("first_name", "last_name", "birth_date")
↳ "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: ("first_name", "last_name", "birth_date")
↳ "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 below into
your code as a class attribute and replace the empty string key(s) with the alias(es) 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 intent corresponds to all the data present in the corresponding relation.
`Person()` represents the set of persons contained in the `actor.person` table (i.e., there is no constraint on the set).
To define a subset, you need to specify constraints on the values of the fields/columns:
* with a single value for an exact match,
* with a tuple of the form `(comp, value)` otherwise.
The `comp` value is either a SQL
[comparison operator](https://www.postgresql.org/docs/current/static/functions-comparison.html) or a [pattern matching operator (like or POSIX regular expression)](https://www.postgresql.org/docs/current/static/functions-matching.html).
You can constrain a relation object at instanciation:
```py
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 using the `Field.set` method:
```py
gaston = Person()
gaston.last_name.set(('ilike', 'l%'))
gaston.first_name.set('Gaston')
```
`half_orm` prevents you from making typos:
```py
gaston(lost_name='Lagaffe')
# raises a half_orm.relation_errors.IsFrozenError Exception
```
# DML. The `ho_insert`, `ho_update`, `ho_delete`, `ho_select` 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 ho_mogrify() method.
```py
people.ho_mogrify()
people.ho_select()
```
## ho_insert
To insert a tuple in the relation, use the `ho_insert` method as shown below:
```py
Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28').ho_insert()
```
By default, `ho_insert` returns the inserted row as a dict:
```py
lagaffe = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')
lagaffe_id = lagaffe.ho_insert()['id']
```
As of version 0.13, the `Relation.ho_transaction` decorator is replaced by the `transaction`
decorator. It uses the new `Transaction(<model>)` context manager:
```py
from half_orm.relation import transaction
# [...]
class Person(halftest.get_relation_class('actor.person')):
# [...]
@transaction
def insert_many(self, *data):
"""Insert serveral people in a single transaction."""
for d_pers in data:
self(**d_pers).ho_insert()
```
```py
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 a transaction, any missing data
would be inserted.
### Returned values
By default `ho_insert` returns all the inserted values as a dictionary. You can specify the columns
you want to get by passing their names as argurments to `ho_insert`.
## ho_update
To update a subset, you first define the subset an then invoque the `ho_udpate`
method with the new values passed as argument.
```py
gaston = Person(first_name='Gaston')
gaston.ho_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:
```py
class Person(halftest.get_relation_class('actor.person')):
# [...]
def upper_last_name(self):
"tranform last name to upper case."
def update(self):
for d_pers in self.ho_select('id', 'last_name'):
pers = Person(**d_pers)
pers.ho_update(last_name=d_pers['last_name'].upper())
with Transaction(self._ho_model):
update(self)
```
We here use the `Transaction` context manager to insure the atomicity of
the operation.
```
>>> a_pers = Person(last_name=('ilike', '_a%'))
>>> print([elt.last_name for elt in list(a_pers.ho_select())])
>>> a_pers = Person(last_name=('ilike', '_a%'))
>>> print([elt['last_name'] for elt in a_pers.ho_select('last_name')])
['Lagaffe', 'Maltese', 'Talon']
>>> a_pers.upper_last_name()
>>> print([elt['last_name'] for elt in a_pers.ho_select('last_name')])
['LAGAFFE', 'MALTESE', 'TALON']
```
### Returning values
To return the updated values, you can add to `ho_update` the column names you want to get, or `*` if you want to get all the columns.
```py
>>> gaston.ho_update('*', birth_date='1970-01-01')
```
### 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.
```py
Person().ho_update(birth_date='1970-01-01', update_all=True)
```
## ho_delete
The `ho_delete` method allows you to remove a set of elements from a table:
```py
gaston = Person(first_name='Gaston')
gaston.ho_delete()
```
To remove every tuples from a table, you must set the argument `delete_all` to `True`. A `RuntimeError` is raised otherwise.
```py
Person().ho_delete(delete_all=True)
if not Person().ho_is_empty():
print('Weird! You should check your "on delete cascade".')
```
Well, there is not much left after this in the `actor.person` table.
### Returning values
As for `ho_update`, to return the deleted values, you can add to `ho_delete` the column names you want to get, or `*` if you want to get all the columns.
```py
>>> gaston.ho_delete('first_name', 'last_name', 'birth_date')
```
## ho_select
The `ho_select` method is a generator. It returns all the data of the relation that matches the constraint defined on the Relation object.
The data is returned in a list of `dict`s.
```py
>>> people = Person()
>>> print(list(people.ho_select()))
[{'id': 6753, 'first_name': 'Gaston', 'last_name': 'Lagaffe', 'birth_date': datetime.date(1957, 2, 28)}, {'id': 6754, 'first_name': 'Bibi', 'last_name': 'Fricotin', 'birth_date': datetime.date(1924, 10, 5)}, {'id': 6755, 'first_name': 'Corto', 'last_name': 'Maltese', 'birth_date': datetime.date(1975, 1, 7)}, {'id': 6756, 'first_name': 'Achile', 'last_name': 'Talon', 'birth_date': datetime.date(1963, 11, 7)}, {'id': 6757, 'first_name': 'Gil', 'last_name': 'Jourdan', 'birth_date': datetime.date(1956, 9, 20)}]
>>> print(list(people.ho_select('id', 'last_name')))
[{'id': 6753, 'last_name': 'Lagaffe'}, {'id': 6754, 'last_name': 'Fricotin'}, {'id': 6755, 'last_name': 'Maltese'}, {'id': 6756, 'last_name': 'Talon'}, {'id': 6757, 'last_name': 'Jourdan'}]
>>>
```
You can set a limit or an offset:
```py
>>> people.ho_offset(1).ho_limit(2)
>>> print(list(people)) # Relation objects are iterators. so ho_select is optional
[{'id': 6754, 'first_name': 'Bibi', 'last_name': 'Fricotin', 'birth_date': datetime.date(1924, 10, 5)}, {'id': 6755, 'first_name': 'Corto', 'last_name': 'Maltese', 'birth_date': datetime.date(1975, 1, 7)}]
```
You can also get a subset of the attributes by passing a list of columns names to `ho_select`:
```py
>>> print(list(people.ho_select('last_name')))
[{'last_name': 'Lagaffe'}, {'last_name': 'Fricotin'}]
```
**Note**: The offset and limit still apply.
### Select one: the `ho_get` method
The `ho_get` method returns an Relation object whose fields are populated with the values from the corresponding row in the database.
It raises an [ExpectedOneError](https://github.com/collorg/halfORM/blob/main/half_orm/relation_errors.py)
Exception if 0 or more than 1 rows match the intention. The returned object is a singleton (see below).
```py
gaston = Person(last_name='Lagaffe').ho_get()
```
is equivalent to
```py
lagaffe = Person(last_name='Lagaffe')
if lagaffe.ho_is_empty() or lagaffe.ho_count() > 1:
raise ExcpetedOneError
gaston = Person(**next(lagaffe.ho_select()))
gaston._ho_is_singleton = True
```
You could use `ho_get` to retreive the `id` of the row:
```py
gaston_id = Person(last_name='Lagaffe').ho_get('id').id.value
```
### 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.
```py
class Person(halftest.get_relation_class('actor.person')):
# [...]
@property
def full_name(self):
return f'{self.first_name} {self.last_name}'
```
As such, the `full_name` property wouldn't make much sense:
```py
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 refers to one and only one element:
```py
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 zero or more than one person in the `actor.person` table had *Gaston* as their first name, a `NotASingletonError` exception would be raised:
```
half_orm.relation_errors.NotASingletonError: Not a singleton. Got X tuples
```
### Forcing `_ho_is_singleton` attribute. (*advanced*)
By forcing the attribute `_ho_is_singleton` of a Relation object to True, you can avoid some unnecessary `ho_get()` that a `@singleton` decorator would have triggered. Here is an example:
```py
class Person(halftest.get_relation_class('actor.person')):
# [...]
@singleton
def do_something_else(self):
"Needs self to be a singleton"
...
def do_something(self):
for elt in self.ho_select():
pers = Person(**elt)
pers._ho_is_singleton = True # You must be pretty sure of what you're doing here. See the warning and the explanation.
pers.do_something_else() # Warning! do_something_else won't check that pers is indeed a singleton
```
**Warning!** By setting `_ho_is_singleton` value to `True`, you disable the check that `@singleton` would have made before executing `do_something_else`.
This example works for two reasons:
1. `ho_select` is called without argument ensuring that all columns are retreived from the database.
Note: Calling `ho_select` with columns corresponding to the primary key as arguments would also have worked;
2. The table `actor.person` has a primary key which makes it a set (ie. each element returned by select is
indeed a singleton).
## ho_count: cardinality of a set
**[BREAKING CHANGE]** From version 0.12 onward, the *`__len__`* method has been deprecated. It has been replaced by the `ho_count` method.
*The code `len(Person())` must be replaced by `Person().ho_count()`*.
You can get the number of elements in a relation with the `ho_count` method, as in `Person().ho_count()`.
### The `NULL` value
`half_orm` provides the `NULL` value:
```py
from half_orm.null import NULL
nobody = Person()
nobody.last_name.set(NULL)
assert nobody.ho_count() == 0 # last_name is part of the PK
[...]
```
The `None` value, unsets a constraint on a field:
```py
[...]
nobody.last_name.set(None)
assert nobody.ho_count() == Person().ho_count()
```
## Set operators
You can use the set operators to set more complex constraints on your relations:
- `&`, `|`, `^` and `-` for `and`, `or`, `xor` and `not`.
Take a look at [the algebra test file](https://github.com/collorg/halfORM/blob/main/test/relation/algebra_test.py).
- you can also use the `==`, `!=` and `in` operators to compare two sets.
```py
my_selection = Person(last_name=('ilike', '_a%')) | Person(first_name=('like', 'A%'))
```
`my_selection` represents the set of people whose second letter of the name is in `['a', 'A']` or whose first letter of the first name is an `A`.
# 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 below into
your code as a class attribute and replace the empty string key(s) with the alias(es) 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:
```py
>>> class Post(halftest.get_relation_class('blog.post')):
... pass
...
>>> Post()
```
```
Inherits: <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) NOT NULL
- title: (text)
- content: (text)
- author_first_name: (text)
- author_last_name: (text)
- author_birth_date: (date)
PRIMARY KEY (id)
UNIQUE CONSTRAINT (title, content)
FOREIGN KEYS:
- _reverse_fkey_halftest_blog_comment_post_id: ("id")
↳ "halftest":"blog"."comment"(post_id)
- author: ("author_first_name", "author_last_name", "author_birth_date")
↳ "halftest":"actor"."person"(first_name, last_name, birth_date)
To use the foreign keys as direct attributes of the class, copy/paste the Fkeys below into
your code as a class attribute and replace the empty string key(s) with the alias(es) 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 an `actor.person` from the table `blog.post`.
* `_reverse_fkey_halftest_blog_comment_post_id` is the foreign key that references a `blog.post` from the table `blog.comment`. The foreign key is traversed in opposite direction (from `blog.post` to `blog.comment`).
We add the aliases of our foreign keys by defining the class attribute `Fkeys` :
```py
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`:
```py
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.
```py
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:
```py
gaston = Person(first_name='Gaston')
gaston_comments = Comment()
gaston_comments.author_fk.set(gaston)
print(list(gaston_comments.ho_select())
```
## Chaining foreign keys
**Important note**: Foreign key chaining will only work if the modules corresponding to the tables are ordered
according to the names of the tables in the package. See the `hop` command.
You can easily chain foreign keys. For example, if you want to get all the comments made by Gaston
on his own posts:
```py
gaston = {'last_name':'Lagaffe', 'first_name':'Gaston', 'birth_date':'1957-02-28'}
gaston_id = Person(**gaston).ho_get('id').id.value # we ensure that Gaston is a singleton
list(gaston
.post_rfk(**gaston)
.comment_rfk(author_id=gaston_id))
```
**Note**: the `blog.post` table declares a foreign key on `actor.person(first_name, last_name, birth_date)`
while the `blog.comment` table declares a foreign key on `actor.person(id)`.
## The *`ho_join`* method [deprecated]
From version 0.11 onward, the *`ho_join`* method has been deprecated. It was too messy and can easily be replaced using the foreign keys.
For example, the old code:
```py
lagaffe = Person(last_name='Lagaffe')
res = lagaffe.ho_join(
(Comment(), 'comments', ['id', 'post_id']),
(Post(), 'posts', 'id')
)
```
becomes:
```py
res = []
lagaffe = Person(last_name='Lagaffe')
for idx, pers in enumerate(lagaffe):
res.append(pers)
res[idx] = {}
posts = Person(**pers).post_rfk()
res[idx]['posts'] = list(posts.ho_select('id'))
res[idx]['comments'] = list(posts.comment_rfk().ho_select('id', 'post_id'))
```
# PostgreSQL functions and stored procedures
`half_orm.model.Model` class provides two methods to deal with functions and stored procedures:
`execute_function` and `call_procedure`. You can
pass parameters as a list or a dictionary (for named parameters). The returned value of
`execute_function` is a list of `dict` like objects.
```py
from half_orm.model import Model
halftest = Model('halftest')
res = halftest.execute_function('schema.my_function', *args)
res = halftest.execute_function('schema.my_function', **kwargs) # for named parameters
half_test.call_procedure('schema.my_procedure', *args)
half_test.call_procedure('schema.my_procedure', **kwargs) # for named parameters
```
# Last: SQL queries
If you realy need to invoke a SQL query not managed by half_orm, use
the `Model.execute_query` method:
```py
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.
**WARING: SQL INJECTION RISK!**
This method calls the psycopg2 method
[cursor.execute](https://www.psycopg.org/docs/cursor.html?highlight=execute#cursor.execute).
Make sure you read the psycopg2 documentation on
[passing parameters to SQL queries](https://www.psycopg.org/docs/usage.html#query-parameters)
if you need to use `execute_query`.
That's it! You've learn pretty much everything there is to know about `half_orm`.
# Next: `hop`, the GitOps `half_orm` packager [WIP][alpha]
The [`hop`](https://github.com/collorg/halfORM/blob/main/doc/hop.md) command, directly provided in this package (from version 0.8.0rc1), allows you to:
* ***create*** a Python package corresponding to the model of your database;
* ***patch*** the model and the corresponding Python code;
* ***test*** your database model and your business code.
More at https://github.com/collorg/halfORM/blob/main/doc/hop.md
# Want to contribute?
Fork me on Github: https://github.com/collorg/halfORM
Raw data
{
"_id": null,
"home_page": "https://github.com/collorg/halfORM",
"name": "half-orm",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": null,
"author": "Jo\u00ebl Ma\u00efzi",
"author_email": "joel.maizi@collorg.org",
"download_url": "https://files.pythonhosted.org/packages/78/87/35e86e5174b63c29e904405d83da562796a272a67fc7e40f2795911d86b3/half_orm-0.13.5.tar.gz",
"platform": null,
"description": "# `half_orm`: A simple PostgreSQL to Python mapper\n\n[](https://pypi.org/project/half-orm/)\n[](https://pypi.org/project/half-orm/)\n[](https://www.python.org)\n[](https://www.postgresql.org)\n[](https://github.com/collorg/halfORM/actions/workflows/python-package.yml)\n[](https://github.com/collorg/halfORM/actions/workflows/postgresql-releases.yml)\n[](https://coveralls.io/github/collorg/halfORM?branch=main)\n[](https://clickpy.clickhouse.com/dashboard/half-orm)\n[](https://github.com/collorg/halfORM/graphs/contributors)\n\nNowadays, most applications require interacting with a relational database. While full-fledged ORMs like SQLAlchemy are very powerful, their complexity, steep learning curve, and some of their limitations can be a hindrance. This is the context in which half_orm was born, a minimalist ORM specifically designed for PostgreSQL. The main motivation is to allow modeling the database directly in SQL, taking full advantage of the capabilities offered by the PostgreSQL engine (triggers, views, functions, stored procedures, inheritance handling...), while avoiding the \"impedance mismatch\" issues, loss of control over generated SQL, and rigidity encountered with full-fledged ORMs that model the schema at the object level. Half_orm intentionally excludes the DDL aspects (schema creation) of the SQL language. The goal is to provide a lightweight abstraction layer over standard SQL queries while maintaining transparent access to the underlying database engine. With half_orm, writing INSERT, SELECT, UPDATE, and DELETE queries becomes as simple as with SQL, but in the comfort of Python. Its operation aims to be intuitive thanks to a lean API that emphasizes productivity and code readability.\n\nHere is what coding with `half_orm` looks like :\n\n```py\nfrom half_orm.model import Model\nfrom half_orm.relation import singleton\n\nhalftest = Model('halftest') # We connect to the PostgreSQL database\n# print(halftest) to get the list of relations in the database\n\nclass Post(halftest.get_relation_class('blog.post')):\n \"\"\"blog.post is a table of the halftest database (<schema>.<relation>)\n To get a full description of the relation, use print(Post())\n \"\"\"\n # The Fkeys template is provided by print(Post()). Just fill in the keys names.\n Fkeys = {\n 'comments_rfk': '_reverse_fkey_halftest_blog_comment_post_id', # a post is referenced by comments\n 'author_fk': 'author' # the post references a person\n }\n\nclass Person(halftest.get_relation_class('actor.person')):\n Fkeys = {\n 'posts_rfk': '_reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date',\n 'comments_rfk': '_reverse_fkey_halftest_blog_comment_author_id'\n }\n @singleton # This ensures that the author of the post is well defined.\n def add_post(self, title: str=None, content: str=None) -> dict:\n return self.posts_rfk(title=title, content=content).ho_insert()\n @singleton\n def add_comment(self, post: Post=None, content: str=None) -> dict:\n return self.comments_rfk(content=content, post_id=post.id.value).ho_insert()\n\ndef main():\n gaston = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')\n gaston.ho_delete()\n if gaston.ho_is_empty(): # gaston defines a subset of the actor.person table.\n gaston.ho_insert()\n post = Post(**gaston.add_post(title='Easy', content='halfORM is fun!'))\n gaston.add_comment(content='This is a comment on the newly created post.', post=post)\n print(list(post.comments_rfk())) # The relational objects are iterators\n post.ho_update(title='Super easy')\n gaston.ho_delete()\n```\n\nIf you want to build or patch a model, see the [`half_orm packager`](#next-hop-the-gitops-half_orm-packager-wipalpha).\n\n# Tutorial: Learn `half_orm` in half an hour\n\n\n\n## Install `half_orm`\n\nrun `pip install half_orm` in a virtual environment.\n\n### Set your HALFORM_CONF_DIR\n\nCreate a directory to store your connection files and set the shell variable `HALFORM_CONF_DIR`\n(by default, `half_orm` looks in the /etc/half_orm directory):\n\n```sh\n% mkdir ~/.half_orm\n% export HALFORM_CONF_DIR=~/.half_orm\n```\n\n> Set your HALFORM_CONF_DIR for windows users:\n> - select settings in the menu\n> - search for \"variable\"\n> - select \"Edit environment variables for your account\"\n\nCreate a connection file in the `$HALFORM_CONF_DIR` containing the following information (with your values):\n\n```ini\n[database]\nname = db_name\nuser = username\npassword = password\nhost = localhost\nport = 5432\n```\n\nYou are ready to go!\n## Connect to the database\n\n```py\n>>> from half_orm.model import Model\n>>> my_db = Model('my_database')\n```\n\nThe `my_database` is the name of the connexion file. It will be fetched in the directory referenced by\nthe environment variable `HALFORM_CONF_DIR` if defined, in `/etc/half_orm` otherwise.\n\n\n## Get a rapid description of the database structure\n\nOnce you are connected, you can easily have an overview of the structure of the database:\n\n```py\nprint(my_db)\n```\n\nIt displays as many lines as there are relations, views or materialized views in your\ndatabase. Each row has the form:\n\n```\n<relation type> <\"schema name\".\"relation name\">\n```\n\nWhere `relation type` is one of `r`, `p`, `v`, `m`, `f`:\n\n* `r` for a relation,\n* `p` for a partitioned table,\n* `v` for a view,\n* `m` for a materialized view,\n* `f` for foreign data.\n\nfor instance (using the halftest database):\n\n```\nr \"actor\".\"person\"\nr \"blog\".\"comment\"\nr \"blog\".\"event\"\nr \"blog\".\"post\"\nv \"blog.view\".\"post_comment\"\n```\n\n**Note**: We only allow dots in schema names.\n\n## Check if a relation exists in the database\n\n```py\n>>> my_db.has_relation('blog.view.post_comment')\nTrue\n```\n\n## Get the class of a relation (the `Model.get_relation_class` method)\n\nTo work with a table of your database, you must instanciate the corresponding class:\n\n```py\nclass Person(halftest.get_relation_class('actor.person')):\n pass\nclass PostComment(halftest.get_relation_class('blog.view.post_comment')):\n pass\n```\n\nThe argument passed to `get_relation_class` is as string of the form:\n`<schema_name>.<relation_name>`.\n\n**Note**: Again, dots are only allowed in schema names.\n\nTo get a full description of the corresponding relation, print an instance of the class:\n\n```py\n>>> print(Person())\n```\n```\nDATABASE: halftest\nSCHEMA: actor\nTABLE: person\n\nDESCRIPTION:\nThe table actor.person contains the persons of the blogging system.\nThe id attribute is a serial. Just pass first_name, last_name and birth_date\nto insert a new person.\nFIELDS:\n- id: (int4) NOT NULL\n- first_name: (text) NOT NULL\n- last_name: (text) NOT NULL\n- birth_date: (date) NOT NULL\n\nPRIMARY KEY (first_name, last_name, birth_date)\nUNIQUE CONSTRAINT (id)\nUNIQUE CONSTRAINT (first_name)\nFOREIGN KEYS:\n- _reverse_fkey_halftest_blog_comment_author_id: (\"id\")\n \u21b3 \"halftest\":\"blog\".\"comment\"(author_id)\n- _reverse_fkey_halftest_blog_event_author_first_name_author_last_name_author_birth_date: (\"first_name\", \"last_name\", \"birth_date\")\n \u21b3 \"halftest\":\"blog\".\"event\"(author_first_name, author_last_name, author_birth_date)\n- _reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date: (\"first_name\", \"last_name\", \"birth_date\")\n \u21b3 \"halftest\":\"blog\".\"post\"(author_first_name, author_last_name, author_birth_date)\n\nTo use the foreign keys as direct attributes of the class, copy/paste the Fkeys below into\nyour code as a class attribute and replace the empty string key(s) with the alias(es) you\nwant to use. The aliases must be unique and different from any of the column names. Empty\nstring keys are ignored.\n\nFkeys = {\n '': '_reverse_fkey_halftest_blog_comment_author_id',\n '': '_reverse_fkey_halftest_blog_event_author_first_name_author_last_name_author_birth_date',\n '': '_reverse_fkey_halftest_blog_post_author_first_name_author_last_name_author_birth_date',\n}\n```\n\nIt provides you with information extracted from the database metadata:\n\n* description: the comment on the relationship if there is one,\n* fields: the list of columns, their types and contraints\n* foreign keys: the list of FKs if any. A `_reverse_*` FK is a FK made on the current relation.\n\n\n## Constraining a relation\n\nWhen you instantiate an object with no arguments, its intent corresponds to all the data present in the corresponding relation.\n`Person()` represents the set of persons contained in the `actor.person` table (i.e., there is no constraint on the set).\n\nTo define a subset, you need to specify constraints on the values of the fields/columns:\n* with a single value for an exact match,\n* with a tuple of the form `(comp, value)` otherwise.\nThe `comp` value is either a SQL\n[comparison operator](https://www.postgresql.org/docs/current/static/functions-comparison.html) or a [pattern matching operator (like or POSIX regular expression)](https://www.postgresql.org/docs/current/static/functions-matching.html).\n\nYou can constrain a relation object at instanciation:\n\n```py\nPerson(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')\nPerson(last_name=('ilike', '_a%'))\nPerson(birth_date='1957-02-28')\n```\n\nYou can also constrain an instanciated object using the `Field.set` method:\n\n```py\ngaston = Person()\ngaston.last_name.set(('ilike', 'l%'))\ngaston.first_name.set('Gaston')\n```\n\n`half_orm` prevents you from making typos:\n\n```py\ngaston(lost_name='Lagaffe')\n# raises a half_orm.relation_errors.IsFrozenError Exception\n```\n\n# DML. The `ho_insert`, `ho_update`, `ho_delete`, `ho_select` methods.\n\nThese methods trigger their corresponding SQL querie on the database.\nFor debugging purposes, you can print the SQL query built\nby half_orm when the DML method is invoked using the ho_mogrify() method.\n\n```py\npeople.ho_mogrify()\npeople.ho_select()\n```\n\n## ho_insert\nTo insert a tuple in the relation, use the `ho_insert` method as shown below:\n```py\nPerson(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28').ho_insert()\n```\n\nBy default, `ho_insert` returns the inserted row as a dict:\n\n```py\nlagaffe = Person(last_name='Lagaffe', first_name='Gaston', birth_date='1957-02-28')\nlagaffe_id = lagaffe.ho_insert()['id']\n```\n\nAs of version 0.13, the `Relation.ho_transaction` decorator is replaced by the `transaction`\ndecorator. It uses the new `Transaction(<model>)` context manager:\n\n```py\nfrom half_orm.relation import transaction\n# [...]\n\nclass Person(halftest.get_relation_class('actor.person')):\n # [...]\n\n @transaction\n def insert_many(self, *data):\n \"\"\"Insert serveral people in a single transaction.\"\"\"\n for d_pers in data:\n self(**d_pers).ho_insert()\n\n```\n\n```py\npeople = Person()\npeople.insert_many(*[\n {'last_name':'Lagaffe', 'first_name':'Gaston', 'birth_date':'1957-02-28'},\n {'last_name':'Fricotin', 'first_name':'Bibi', 'birth_date':'1924-10-05'},\n {'last_name':'Maltese', 'first_name':'Corto', 'birth_date':'1975-01-07'},\n {'last_name':'Talon', 'first_name':'Achile', 'birth_date':'1963-11-07'},\n {'last_name':'Jourdan', 'first_name':'Gil', 'birth_date':'1956-09-20'}\n])\n```\n\n**Note**: half_orm works in autocommit mode by default. Without a transaction, any missing data\nwould be inserted.\n\n### Returned values\n\nBy default `ho_insert` returns all the inserted values as a dictionary. You can specify the columns\nyou want to get by passing their names as argurments to `ho_insert`.\n\n## ho_update\n\nTo update a subset, you first define the subset an then invoque the `ho_udpate`\nmethod with the new values passed as argument.\n\n```py\ngaston = Person(first_name='Gaston')\ngaston.ho_update(birth_date='1970-01-01')\n```\n\nLet's look at how we could turn the last name into capital letters for a subset of people:\n\n```py\nclass Person(halftest.get_relation_class('actor.person')):\n # [...]\n\n def upper_last_name(self):\n \"tranform last name to upper case.\"\n\n def update(self):\n for d_pers in self.ho_select('id', 'last_name'):\n pers = Person(**d_pers)\n pers.ho_update(last_name=d_pers['last_name'].upper())\n with Transaction(self._ho_model):\n update(self)\n```\n\nWe here use the `Transaction` context manager to insure the atomicity of \nthe operation.\n\n```\n>>> a_pers = Person(last_name=('ilike', '_a%'))\n>>> print([elt.last_name for elt in list(a_pers.ho_select())])\n>>> a_pers = Person(last_name=('ilike', '_a%'))\n>>> print([elt['last_name'] for elt in a_pers.ho_select('last_name')])\n['Lagaffe', 'Maltese', 'Talon']\n>>> a_pers.upper_last_name()\n>>> print([elt['last_name'] for elt in a_pers.ho_select('last_name')])\n['LAGAFFE', 'MALTESE', 'TALON']\n```\n\n### Returning values\n\nTo return the updated values, you can add to `ho_update` the column names you want to get, or `*` if you want to get all the columns.\n\n```py\n>>> gaston.ho_update('*', birth_date='1970-01-01')\n```\n\n### Update all data in a table\n\nIf you want to update all the data in a relation, you must set the argument `update_all` to `True`. A `RuntimeError` is raised otherwise.\n\n```py\nPerson().ho_update(birth_date='1970-01-01', update_all=True)\n```\n\n## ho_delete\n\nThe `ho_delete` method allows you to remove a set of elements from a table:\n\n```py\ngaston = Person(first_name='Gaston')\ngaston.ho_delete()\n```\n\nTo remove every tuples from a table, you must set the argument `delete_all` to `True`. A `RuntimeError` is raised otherwise.\n\n```py\nPerson().ho_delete(delete_all=True)\nif not Person().ho_is_empty():\n print('Weird! You should check your \"on delete cascade\".')\n```\nWell, there is not much left after this in the `actor.person` table.\n\n### Returning values\n\nAs for `ho_update`, to return the deleted values, you can add to `ho_delete` the column names you want to get, or `*` if you want to get all the columns.\n\n```py\n>>> gaston.ho_delete('first_name', 'last_name', 'birth_date')\n```\n\n## ho_select\nThe `ho_select` method is a generator. It returns all the data of the relation that matches the constraint defined on the Relation object.\nThe data is returned in a list of `dict`s.\n\n```py\n>>> people = Person()\n>>> print(list(people.ho_select()))\n[{'id': 6753, 'first_name': 'Gaston', 'last_name': 'Lagaffe', 'birth_date': datetime.date(1957, 2, 28)}, {'id': 6754, 'first_name': 'Bibi', 'last_name': 'Fricotin', 'birth_date': datetime.date(1924, 10, 5)}, {'id': 6755, 'first_name': 'Corto', 'last_name': 'Maltese', 'birth_date': datetime.date(1975, 1, 7)}, {'id': 6756, 'first_name': 'Achile', 'last_name': 'Talon', 'birth_date': datetime.date(1963, 11, 7)}, {'id': 6757, 'first_name': 'Gil', 'last_name': 'Jourdan', 'birth_date': datetime.date(1956, 9, 20)}]\n>>> print(list(people.ho_select('id', 'last_name')))\n[{'id': 6753, 'last_name': 'Lagaffe'}, {'id': 6754, 'last_name': 'Fricotin'}, {'id': 6755, 'last_name': 'Maltese'}, {'id': 6756, 'last_name': 'Talon'}, {'id': 6757, 'last_name': 'Jourdan'}]\n>>>\n```\n\nYou can set a limit or an offset:\n```py\n>>> people.ho_offset(1).ho_limit(2)\n>>> print(list(people)) # Relation objects are iterators. so ho_select is optional\n[{'id': 6754, 'first_name': 'Bibi', 'last_name': 'Fricotin', 'birth_date': datetime.date(1924, 10, 5)}, {'id': 6755, 'first_name': 'Corto', 'last_name': 'Maltese', 'birth_date': datetime.date(1975, 1, 7)}]\n```\n\nYou can also get a subset of the attributes by passing a list of columns names to `ho_select`:\n\n```py\n>>> print(list(people.ho_select('last_name')))\n[{'last_name': 'Lagaffe'}, {'last_name': 'Fricotin'}]\n```\n\n**Note**: The offset and limit still apply.\n\n### Select one: the `ho_get` method\n\nThe `ho_get` method returns an Relation object whose fields are populated with the values from the corresponding row in the database.\nIt raises an [ExpectedOneError](https://github.com/collorg/halfORM/blob/main/half_orm/relation_errors.py)\nException if 0 or more than 1 rows match the intention. The returned object is a singleton (see below).\n\n```py\ngaston = Person(last_name='Lagaffe').ho_get()\n```\n\nis equivalent to\n\n```py\nlagaffe = Person(last_name='Lagaffe')\nif lagaffe.ho_is_empty() or lagaffe.ho_count() > 1:\n raise ExcpetedOneError\ngaston = Person(**next(lagaffe.ho_select()))\ngaston._ho_is_singleton = True\n```\n\nYou could use `ho_get` to retreive the `id` of the row:\n\n```py\ngaston_id = Person(last_name='Lagaffe').ho_get('id').id.value\n```\n\n### Is it a set? Is it an element of the set?\n\nLet's go back to our definition of the class `Person`. We would like to write a property that\nreturns the full name of **a** person.\n\n```py\nclass Person(halftest.get_relation_class('actor.person')):\n # [...]\n @property\n def full_name(self):\n return f'{self.first_name} {self.last_name}'\n```\n\nAs such, the `full_name` property wouldn't make much sense:\n\n```py\nlagaffe = Person(last_name='Lagaffe')\nlagaffe.full_name # returns 'None Lagaffe'\n```\n\nIn this case, you can use the `@singleton` decorator to ensure that the self object refers to one and only one element:\n\n```py\nfrom half_orm.relation import singleton\n\nclass Person(halftest.get_relation_class('actor.person')):\n @property\n @singleton\n def full_name(self):\n return f'{self.first_name} {self.last_name}'\n\ngaston = Person(first_name='Gaston')\ngaston.full_name # now returns 'Gaston Lagaffe'\n```\n\nIf zero or more than one person in the `actor.person` table had *Gaston* as their first name, a `NotASingletonError` exception would be raised:\n\n```\nhalf_orm.relation_errors.NotASingletonError: Not a singleton. Got X tuples\n```\n\n### Forcing `_ho_is_singleton` attribute. (*advanced*)\n\nBy forcing the attribute `_ho_is_singleton` of a Relation object to True, you can avoid some unnecessary `ho_get()` that a `@singleton` decorator would have triggered. Here is an example:\n\n```py\nclass Person(halftest.get_relation_class('actor.person')):\n # [...]\n @singleton\n def do_something_else(self):\n \"Needs self to be a singleton\"\n ...\n\n def do_something(self):\n for elt in self.ho_select():\n pers = Person(**elt)\n pers._ho_is_singleton = True # You must be pretty sure of what you're doing here. See the warning and the explanation.\n pers.do_something_else() # Warning! do_something_else won't check that pers is indeed a singleton\n```\n\n**Warning!** By setting `_ho_is_singleton` value to `True`, you disable the check that `@singleton` would have made before executing `do_something_else`.\nThis example works for two reasons:\n\n1. `ho_select` is called without argument ensuring that all columns are retreived from the database.\nNote: Calling `ho_select` with columns corresponding to the primary key as arguments would also have worked;\n2. The table `actor.person` has a primary key which makes it a set (ie. each element returned by select is\nindeed a singleton).\n\n## ho_count: cardinality of a set\n\n**[BREAKING CHANGE]** From version 0.12 onward, the *`__len__`* method has been deprecated. It has been replaced by the `ho_count` method.\n\n*The code `len(Person())` must be replaced by `Person().ho_count()`*.\n\n\nYou can get the number of elements in a relation with the `ho_count` method, as in `Person().ho_count()`.\n\n### The `NULL` value\n\n`half_orm` provides the `NULL` value:\n\n```py\nfrom half_orm.null import NULL\n\nnobody = Person()\nnobody.last_name.set(NULL)\nassert nobody.ho_count() == 0 # last_name is part of the PK\n[...]\n```\n\nThe `None` value, unsets a constraint on a field:\n\n```py\n[...]\nnobody.last_name.set(None)\nassert nobody.ho_count() == Person().ho_count()\n```\n\n## Set operators\n\nYou can use the set operators to set more complex constraints on your relations:\n- `&`, `|`, `^` and `-` for `and`, `or`, `xor` and `not`.\nTake a look at [the algebra test file](https://github.com/collorg/halfORM/blob/main/test/relation/algebra_test.py).\n- you can also use the `==`, `!=` and `in` operators to compare two sets.\n\n```py\nmy_selection = Person(last_name=('ilike', '_a%')) | Person(first_name=('like', 'A%'))\n```\n\n`my_selection` represents the set of people whose second letter of the name is in `['a', 'A']` or whose first letter of the first name is an `A`.\n\n\n# Working with foreign keys [WIP]\n\n> This is a work in progress\n\nA relational object integrates all the material necessary to process its foreign keys and the\nforeign keys that point to this object. When you print the object, its representation ends\nwith the information about the foreign keys:\n\n```\nTo use the foreign keys as direct attributes of the class, copy/paste the Fkeys below into\nyour code as a class attribute and replace the empty string key(s) with the alias(es) you\nwant to use. The aliases must be unique and different from any of the column names. Empty\nstring keys are ignored.\n\nFkeys = {\n [...]\n}\n```\n\nLet's see an example with the `blog.post` relation:\n\n```py\n>>> class Post(halftest.get_relation_class('blog.post')):\n... pass\n...\n>>> Post()\n```\n```\nInherits: <class '__main__.Post'>\n\nThis class allows you to manipulate the data in the PG relation:\nTABLE: \"halftest\":\"blog\".\"post\"\nDESCRIPTION:\nThe table blog.post contains all the post\nmade by a person in the blogging system.\nFIELDS:\n- id: (int4) NOT NULL\n- title: (text)\n- content: (text)\n- author_first_name: (text)\n- author_last_name: (text)\n- author_birth_date: (date)\n\nPRIMARY KEY (id)\nUNIQUE CONSTRAINT (title, content)\nFOREIGN KEYS:\n- _reverse_fkey_halftest_blog_comment_post_id: (\"id\")\n \u21b3 \"halftest\":\"blog\".\"comment\"(post_id)\n- author: (\"author_first_name\", \"author_last_name\", \"author_birth_date\")\n \u21b3 \"halftest\":\"actor\".\"person\"(first_name, last_name, birth_date)\n\nTo use the foreign keys as direct attributes of the class, copy/paste the Fkeys below into\nyour code as a class attribute and replace the empty string key(s) with the alias(es) you\nwant to use. The aliases must be unique and different from any of the column names. Empty\nstring keys are ignored.\n\nFkeys = {\n '': '_reverse_fkey_halftest_blog_comment_post_id',\n '': 'author',\n}\n```\n\nIt has two foreign keys named `_reverse_fkey_halftest_blog_comment_post_id` and `author`:\n* `author` is the foreign key that refrences an `actor.person` from the table `blog.post`.\n* `_reverse_fkey_halftest_blog_comment_post_id` is the foreign key that references a `blog.post` from the table `blog.comment`. The foreign key is traversed in opposite direction (from `blog.post` to `blog.comment`).\n\nWe add the aliases of our foreign keys by defining the class attribute `Fkeys` :\n\n```py\nclass Post(halftest.get_relation_class('blog.post')):\n Fkeys = {\n 'comments_rfk': '_reverse_fkey_halftest_blog_comment_post_id',\n 'author_fk': 'author'\n }\n```\n\n**Note**: By convention, we suffix by `_fk` the foreign keys and by `_rfk` the foreign keys traversed in reverse.\nThe plural in `comments_rfk` indicates that a post can be referenced by many comments.\n\nA foreign key is a transitional object, so when you instanciate a FKey object,\nyou get the relation it points to. The original constraint is propagated through the foreign key.\n\nGiven a post defined by a `constraint`:\n\n```py\na_post = Post(**constraint)\ncomments_on_a_post = a_post.comments_rfk()\nauthor_of_a_post = a_post.author_fk()\n```\n\nYou can also add a filter on a foreign key.\n\n```py\ncomments_on_a_post_containing_simple = a_post.comment_rfk(content=('ilike', '%simple%'))\n```\n\nThe Fkey class has the `set` method which allows you to constrain a foreign key with a Relation object.\nTo 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:\n\n```py\ngaston = Person(first_name='Gaston')\ngaston_comments = Comment()\ngaston_comments.author_fk.set(gaston)\nprint(list(gaston_comments.ho_select())\n```\n## Chaining foreign keys\n\n**Important note**: Foreign key chaining will only work if the modules corresponding to the tables are ordered\naccording to the names of the tables in the package. See the `hop` command.\n\nYou can easily chain foreign keys. For example, if you want to get all the comments made by Gaston\non his own posts:\n\n```py\ngaston = {'last_name':'Lagaffe', 'first_name':'Gaston', 'birth_date':'1957-02-28'}\ngaston_id = Person(**gaston).ho_get('id').id.value # we ensure that Gaston is a singleton\nlist(gaston\n .post_rfk(**gaston)\n .comment_rfk(author_id=gaston_id))\n```\n\n**Note**: the `blog.post` table declares a foreign key on `actor.person(first_name, last_name, birth_date)`\nwhile the `blog.comment` table declares a foreign key on `actor.person(id)`.\n\n## The *`ho_join`* method [deprecated]\n\nFrom version 0.11 onward, the *`ho_join`* method has been deprecated. It was too messy and can easily be replaced using the foreign keys.\n\nFor example, the old code:\n\n```py\nlagaffe = Person(last_name='Lagaffe')\nres = lagaffe.ho_join(\n (Comment(), 'comments', ['id', 'post_id']),\n (Post(), 'posts', 'id')\n)\n```\n\nbecomes:\n\n```py\nres = []\nlagaffe = Person(last_name='Lagaffe')\nfor idx, pers in enumerate(lagaffe):\n res.append(pers)\n res[idx] = {}\n posts = Person(**pers).post_rfk()\n res[idx]['posts'] = list(posts.ho_select('id'))\n res[idx]['comments'] = list(posts.comment_rfk().ho_select('id', 'post_id'))\n```\n\n# PostgreSQL functions and stored procedures\n\n`half_orm.model.Model` class provides two methods to deal with functions and stored procedures:\n`execute_function` and `call_procedure`. You can\npass parameters as a list or a dictionary (for named parameters). The returned value of\n`execute_function` is a list of `dict` like objects.\n\n```py\nfrom half_orm.model import Model\nhalftest = Model('halftest')\n\nres = halftest.execute_function('schema.my_function', *args)\nres = halftest.execute_function('schema.my_function', **kwargs) # for named parameters\n\nhalf_test.call_procedure('schema.my_procedure', *args)\nhalf_test.call_procedure('schema.my_procedure', **kwargs) # for named parameters\n```\n\n# Last: SQL queries\n\nIf you realy need to invoke a SQL query not managed by half_orm, use\nthe `Model.execute_query` method:\n\n```py\nfrom half_orm.model import Model\nhalftest = Model('halftest')\nhalftest.execute_query('select 1')\n```\n\nBy 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.\n\n**WARING: SQL INJECTION RISK!**\nThis method calls the psycopg2 method\n[cursor.execute](https://www.psycopg.org/docs/cursor.html?highlight=execute#cursor.execute).\nMake sure you read the psycopg2 documentation on\n[passing parameters to SQL queries](https://www.psycopg.org/docs/usage.html#query-parameters)\nif you need to use `execute_query`.\n\n\n\nThat's it! You've learn pretty much everything there is to know about `half_orm`.\n\n# Next: `hop`, the GitOps `half_orm` packager [WIP][alpha]\n\nThe [`hop`](https://github.com/collorg/halfORM/blob/main/doc/hop.md) command, directly provided in this package (from version 0.8.0rc1), allows you to:\n\n* ***create*** a Python package corresponding to the model of your database;\n* ***patch*** the model and the corresponding Python code;\n* ***test*** your database model and your business code.\n\nMore at https://github.com/collorg/halfORM/blob/main/doc/hop.md\n\n# Want to contribute?\n\nFork me on Github: https://github.com/collorg/halfORM\n",
"bugtrack_url": null,
"license": "GPLv3",
"summary": "A simple PostgreSQL to Python mapper.",
"version": "0.13.5",
"project_urls": {
"Homepage": "https://github.com/collorg/halfORM"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "243023c7e5ef99ce4abf3ea851a61c8ff72758df25b7829508a38bbb119b20e1",
"md5": "fd7c43793901269274cb86d8b2522fb0",
"sha256": "c52940b0b639316aa7941a5032fd0c8740f914e7ebd2e26664657cc5f455c13d"
},
"downloads": -1,
"filename": "half_orm-0.13.5-py3-none-any.whl",
"has_sig": false,
"md5_digest": "fd7c43793901269274cb86d8b2522fb0",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 69250,
"upload_time": "2024-11-21T16:53:04",
"upload_time_iso_8601": "2024-11-21T16:53:04.765730Z",
"url": "https://files.pythonhosted.org/packages/24/30/23c7e5ef99ce4abf3ea851a61c8ff72758df25b7829508a38bbb119b20e1/half_orm-0.13.5-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "788735e86e5174b63c29e904405d83da562796a272a67fc7e40f2795911d86b3",
"md5": "99ff60666f4f2de42a469a941473e81f",
"sha256": "43c14f7fe8f7e7119e23c74d9b220096eda89f55bab9a9adf5b318033567bd81"
},
"downloads": -1,
"filename": "half_orm-0.13.5.tar.gz",
"has_sig": false,
"md5_digest": "99ff60666f4f2de42a469a941473e81f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 66390,
"upload_time": "2024-11-21T16:53:06",
"upload_time_iso_8601": "2024-11-21T16:53:06.944073Z",
"url": "https://files.pythonhosted.org/packages/78/87/35e86e5174b63c29e904405d83da562796a272a67fc7e40f2795911d86b3/half_orm-0.13.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-11-21 16:53:06",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "collorg",
"github_project": "halfORM",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"requirements": [],
"tox": true,
"lcname": "half-orm"
}
JSON
