Source code for postgres.orm


It's somewhat of a fool's errand to introduce a Python ORM in 2013, with
`SQLAlchemy`_ ascendant (`Django's ORM`_ not-withstanding). And yet here we
are. SQLAlchemy is mature and robust and full-featured. This makes it complex,
difficult to learn, and kind of scary. The ORM we introduce here is simpler: it
targets PostgreSQL only, it depends on raw SQL (it has no object model for
schema definition nor one for query construction), and it never updates your
database for you. You are in full, direct control of your application's
database usage.

.. _SQLAlchemy:
.. _Django's ORM:

The fundamental technique we employ, introduced by `Michael Robbelard at PyOhio
2013`_, is to write SQL queries that "typecast" results to table types, and then
use a :class:`~psycopg2.extras.CompositeCaster` subclass to map
these to Python objects.  This means we get to define our schema in SQL, and we
get to write our queries in SQL, and we get to explicitly indicate in our SQL
queries how Python should map the results to objects, and then we can write
Python objects that contain only business logic and not schema definitions.

.. _Michael Robbelard at PyOhio 2013:

Introducing Table Types

Every table in PostgreSQL has a type associated with it, which is the column
definition for that table. These are composite types just like any other
composite type in PostgreSQL, meaning we can use them to cast query results.
When we do, we get a single field that contains our query result, nested one

    test=# CREATE TABLE foo (bar text, baz int);
    test=# INSERT INTO foo VALUES ('blam', 42);
    INSERT 0 1
    test=# INSERT INTO foo VALUES ('whit', 537);
    INSERT 0 1
    test=# SELECT * FROM foo;
    | bar  | baz |
    | blam |  42 |
    | whit | 537 |
    (2 rows)

    test=# SELECT foo FROM foo;
    |    foo     |
    | (blam,42)  |
    | (whit,537) |
    (2 rows)


The same thing works for views::

    test=# CREATE VIEW bar AS SELECT bar FROM foo;
    test=# SELECT * FROM bar;
    | bar  |
    | blam |
    | whit |
    (2 rows)

    test=# SELECT bar FROM bar;
    |  bar   |
    | (blam) |
    | (whit) |
    (2 rows)


:mod:`psycopg2` provides a :func:`~psycopg2.extras.register_composite`
function that lets us map PostgreSQL composite types to Python objects. This
includes table and view types, and that is the basis for
:mod:`postgres.orm`. We map based on types, not tables.

.. _orm-tutorial:

ORM Tutorial

First, write a Python class that subclasses :class:`~postgres.orm.Model`::

    >>> from postgres.orm import Model
    >>> class Foo(Model):
    ...     typname = "foo"

Your model must have a :attr:`typname` attribute, which is the name of the
PostgreSQL type for which this class is an object mapping. (``typname``,
spelled without an "e," is the name of the relevant column in the ``pg_type``
table in your database.)

Second, register your model with your :class:`~postgres.Postgres` instance:

    >>> db.register_model(Foo)

That will plug your model into the :mod:`psycopg2` composite casting
machinery, and you'll now get instances of your model back from
:meth:`` and :meth:`~postgres.Postgres.all` when
you cast to the relevant type in your query. If your query returns more than
one column, you'll need to dereference the column containing the model just as
with any other query:

    >>> rec =\"""
    ...     SELECT foo, bar.*
    ...       FROM foo
    ...       JOIN bar ON =
    ...   ORDER BY
    ...      LIMIT 1
    ... \""")

And as usual, if your query only returns one column, then
:meth:`` and :meth:`~postgres.Postgres.all`
will do the dereferencing for you:

    >>> foo ="SELECT foo FROM foo WHERE bar='blam'")
    >>> [ for foo in db.all("SELECT foo FROM foo")]
    ['blam', 'whit']

To update your database, add a method to your model:

    >>> db.unregister_model(Foo)
    >>> class Foo(Model):
    ...     typname = "foo"
    ...     def update_baz(self, baz):
    ..."UPDATE foo SET baz=%s WHERE bar=%s", (baz,
    ...         self.set_attributes(baz=baz)
    >>> db.register_model(Foo)

Then use that method to update the database:

    >>>"SELECT baz FROM foo WHERE bar='blam'")
    >>> foo ="SELECT foo FROM foo WHERE bar='blam'")
    >>> foo.update_baz(90210)
    >>> foo.baz
    >>>"SELECT baz FROM foo WHERE bar='blam'")

We never update your database for you. We also never sync your objects for you:
note the use of the :meth:`~postgres.orm.Model.set_attributes` method to
sync our instance after modifying the database.

The Model Base Class


# Exceptions
# ==========

[docs]class ReadOnlyAttribute(AttributeError): def __str__(self): return "{} is a read-only attribute. Your Model should implement " \ "methods to change data; use set_attributes from your methods " \ "to sync local state.".format(self.args[0])
[docs]class UnknownAttributes(AttributeError): def __str__(self): return "The following attribute(s) are unknown to us: {}." \ .format(", ".join(self.args[0]))
# Stuff # =====
[docs]class Model: """This is the base class for models in :mod:`postgres.orm`. Instances of subclasses of :class:`~postgres.orm.Model` will have an attribute for each field in the composite type for which the subclass is registered (for table and view types, these will be the columns of the table or view). These attributes are read-only. We don't update your database. You are expected to do that yourself in methods on your subclass. To keep instance attributes in sync after a database update, use the :meth:`~postgres.orm.Model.set_attributes` helper. """ __slots__ = () typname = None # an entry in pg_type db = None # will be set to a Postgres object attnames = None # set in ModelCaster._from_db() def __init__(self, values): if getattr(self, '__slots__', None): _setattr = super(Model, self).__setattr__ for name, value in zip(self.__class__.attnames, values): _setattr(name, value) else: self.__dict__.update(zip(self.__class__.attnames, values)) def __setattr__(self, name, value): if name in self.__class__.attnames: raise ReadOnlyAttribute(name) return super(Model, self).__setattr__(name, value)
[docs] def set_attributes(self, **kw): """Set instance attributes, according to :attr:`kw`. :raises: :exc:`~postgres.orm.UnknownAttributes` Call this when you update state in the database and you want to keep instance attributes in sync. Note that the only attributes we can set here are the ones that were given to us by the :mod:`psycopg2` composite caster machinery when we were first instantiated. These will be the fields of the composite type for which we were registered, which will be column names for table and view types. """ unknown = None attnames = self.__class__.attnames for name in kw: if name not in attnames: if unknown is None: unknown = [name] else: unknown.append(name) if unknown: raise UnknownAttributes(unknown) _setattr = super(Model, self).__setattr__ for name, value in kw.items(): _setattr(name, value)
if __name__ == '__main__': # pragma: no cover from postgres import Postgres db = Postgres()"DROP SCHEMA IF EXISTS public CASCADE")"CREATE SCHEMA public")"DROP TABLE IF EXISTS foo CASCADE")"CREATE TABLE foo (bar text, baz int)")"INSERT INTO foo VALUES ('blam', 42)")"INSERT INTO foo VALUES ('whit', 537)")"CREATE VIEW bar AS SELECT bar FROM foo") import doctest doctest.testmod()