Postgres.py¶
This is a PostgreSQL client library for humans.
Installation¶
postgres
is available on GitHub and on PyPI:
$ pip install postgres
postgres
requires psycopg2 version 2.8 or higher.
We currently test against
Python 3.6, 3.7, 3.8 and 3.9. We don’t have a testing matrix for different
versions of psycopg2
or PostgreSQL.
postgres
is released under the MIT license.
See Also¶
The sql library provides a run / one / all API for any DB API 2.0 driver.
The Records library provides a similar top-level API, and integration with SQLAlchemy and Tablib.
Tutorial¶
Instantiate a Postgres
object when your application starts:
>>> from postgres import Postgres
>>> db = Postgres()
Use run
to run SQL statements:
>>> db.run("CREATE TABLE foo (bar text, baz int)")
>>> db.run("INSERT INTO foo VALUES ('buz', 42)")
>>> db.run("INSERT INTO foo VALUES ('bit', 537)")
Use one
to run SQL and fetch one result or
None
:
>>> db.one("SELECT * FROM foo WHERE bar='buz'")
Record(bar='buz', baz=42)
>>> db.one("SELECT * FROM foo WHERE bar='blam'")
Use all
to run SQL and fetch all results:
>>> db.all("SELECT * FROM foo ORDER BY bar")
[Record(bar='bit', baz=537), Record(bar='buz', baz=42)]
If your queries return one column then you get just the value or a list of values instead of a record or list of records:
>>> db.one("SELECT baz FROM foo WHERE bar='buz'")
42
>>> db.all("SELECT baz FROM foo ORDER BY bar")
[537, 42]
Jump ahead for the ORM Tutorial.
Bind Parameters¶
In case you’re not familiar with bind parameters in DB-API 2.0, the basic
idea is that you put %(foo)s
in your SQL strings, and then pass in a second
argument, a dict
, containing parameters that psycopg2
(as
an implementation of DB-API 2.0) will bind to the query in a way that is safe
against SQL injection. (This is inspired by old-style Python string
formatting, but it is not the same.)
>>> db.one("SELECT * FROM foo WHERE bar=%(bar)s", {"bar": "buz"})
Record(bar='buz', baz=42)
As a convenience, passing parameters as keyword arguments is also supported:
>>> db.one("SELECT * FROM foo WHERE bar=%(bar)s", bar="buz")
Record(bar='buz', baz=42)
Never build SQL strings out of user input!
Always pass user input as bind parameters!
Context Managers¶
Eighty percent of your database usage should be covered by the simple
run
, one
,
all
API introduced above. For the other 20%,
postgres
provides two context managers for working at increasingly
lower levels of abstraction. The lowest level of abstraction in
postgres
is a psycopg2
connection pool that we configure and manage for
you. Everything in postgres
, both the simple API and the context
managers, uses this connection pool.
Use the get_cursor
context manager to work
directly with a simple cursor, while still taking advantage of connection
pooling and automatic transaction management:
>>> with db.get_cursor() as cursor:
... cursor.run("INSERT INTO foo VALUES ('blam')")
... cursor.all("SELECT * FROM foo ORDER BY bar")
...
[Record(bar='bit', baz=537), Record(bar='blam', baz=None), Record(bar='buz', baz=42)]
Note that other calls won’t see the changes on your transaction until the end of your code block, when the context manager commits the transaction for you:
>>> db.run("DELETE FROM foo WHERE bar='blam'")
>>> with db.get_cursor() as cursor:
... cursor.run("INSERT INTO foo VALUES ('blam')")
... db.all("SELECT * FROM foo ORDER BY bar")
...
[Record(bar='bit', baz=537), Record(bar='buz', baz=42)]
>>> db.all("SELECT * FROM foo ORDER BY bar")
[Record(bar='bit', baz=537), Record(bar='blam', baz=None), Record(bar='buz', baz=42)]
The get_cursor
method gives you a context manager
that wraps a simple cursor. It has autocommit
turned off on its
connection. If the block under management raises an exception, the connection
is rolled back. Otherwise it’s committed. Use this when you want a series of
statements to be part of one transaction, but you don’t need fine-grained
control over the transaction. For fine-grained control, use
get_connection
to get a connection straight from
the connection pool:
>>> db.run("DELETE FROM foo WHERE bar='blam'")
>>> with db.get_connection() as connection:
... cursor = connection.cursor()
... cursor.all("SELECT * FROM foo ORDER BY bar")
...
[Record(bar='bit', baz=537), Record(bar='buz', baz=42)]
A connection gotten in this way will have autocommit
turned off, and
it’ll never be implicitly committed otherwise. It’ll actually be rolled back
when you’re done with it, so it’s up to you to explicitly commit as needed.
This is the lowest-level abstraction that postgres
provides,
basically just a pre-configured connection pool from psycopg2
that
uses simple cursors.
The Postgres Object¶
-
class
postgres.
Postgres
(url='', minconn=1, maxconn=10, idle_timeout=600, readonly=False, cursor_factory=<class 'postgres.cursors.SimpleNamedTupleCursor'>, back_as_registry={<class 'tuple'>: <function return_tuple_as_is>, 'tuple': <function return_tuple_as_is>, <class 'dict'>: <function make_dict>, 'dict': <function make_dict>, <function namedtuple>: <function make_namedtuple>, 'namedtuple': <function make_namedtuple>, <class 'postgres.cursors.Row'>: <class 'postgres.cursors.Row'>, 'Row': <class 'postgres.cursors.Row'>}, pool_class=<class 'psycopg2_pool.ThreadSafeConnectionPool'>, cache=None)[source]¶ Interact with a PostgreSQL database.
Parameters: - url (str) – A
postgres://
URL or a PostgreSQL connection string - minconn (int) – The minimum size of the connection pool
- maxconn (int) – The maximum size of the connection pool
- idle_timeout (int) – How many seconds to wait before closing an idle connection.
- readonly (bool) – Setting this to
True
makes all connections and cursors readonly by default. - cursor_factory (type) – The type of cursor to use when none is specified.
Defaults to
SimpleNamedTupleCursor
. - back_as_registry (dict) – Defines the values that can be passed to
various methods as a
back_as
argument. - pool_class (type) – The type of pool to use. Defaults to
ThreadSafeConnectionPool
. - cache (Cache) – An instance of
postgres.cache.Cache
.
This is the main object that
postgres
provides, and you should have one instance per process for each PostgreSQL database your process wants to talk to using this library.>>> import postgres >>> db = postgres.Postgres()
(Note that importing
postgres
under Python 2 will cause the registration of typecasters withpsycopg2
to ensure that you get unicode instead of bytestrings for text data, according to this advice.)The libpq environment variables are used to determine the connection parameters which are not explicitly passed in the
url
argument.When instantiated, this object creates a connection pool by calling pool_class with the minconn, maxconn and idle_timeout arguments. Everything this object provides runs through this connection pool. See the documentation of the
ConnectionPool
class for more information.cursor_factory
sets the default cursor that connections managed by thisPostgres
instance will use. See the Simple Cursors documentation below for additional options. Whatever default you set here, you can override that default on a per-call basis by passingcursor_factory
toget_cursor
.The names in our simple API,
run
,one
, andall
, were chosen to be short and memorable, and to not directly conflict with the DB-API 2.0execute
,fetchone
, andfetchall
methods, which have slightly different semantics (under DB-API 2.0 you callexecute
on a cursor and then call one of thefetch*
methods on the same cursor to retrieve records; with our simple API there is no secondfetch
step, and we also provide automatic dereferencing). See issues 16 and 20 for more of the rationale behind these names. The context managers on this class are named starting withget_
to set them apart from the simple-case API.-
run
(sql, parameters=None, **kw)[source]¶ Execute a query and discard any results.
Returns: None
This is a convenience method, it passes all its arguments to
SimpleCursorBase.run
like this:with self.get_cursor() as cursor: cursor.run(sql, parameters, **kw)
-
one
(sql, parameters=None, **kw)[source]¶ Execute a query and return a single result or a default value.
Returns: a single record or value, or default
(ifdefault
is not anException
)Raises: TooFew
orTooMany
, ordefault
(ifdefault
is anException
)This is a convenience method, it passes all its arguments to
SimpleCursorBase.one
like this:with self.get_cursor() as cursor: return cursor.one(sql, parameters, **kw)
-
all
(sql, parameters=None, **kw)[source]¶ Execute a query and return all results.
Returns: list
of records orlist
of single valuesThis is a convenience method, it passes all its arguments to
SimpleCursorBase.all
like this:with self.get_cursor() as cursor: return cursor.all(sql, parameters, **kw)
-
get_cursor
(cursor=None, **kw)[source]¶ Return a
CursorContextManager
that uses our connection pool.Parameters: - cursor – use an existing cursor instead of creating a new one (see the explanations and caveats below)
- kw – passed through to
CursorContextManager
orCursorSubcontextManager
>>> with db.get_cursor() as cursor: ... cursor.all("SELECT * FROM foo") ... [Record(bar='buz', baz=42), Record(bar='bit', baz=537)]
You can use our simple
run
,one
,all
API, and you can also use the traditional DB-API 2.0 methods:>>> with db.get_cursor() as cursor: ... cursor.execute("SELECT * FROM foo") ... cursor.fetchall() ... [Record(bar='buz', baz=42), Record(bar='bit', baz=537)]
By default the cursor will have
autocommit
turned off on its connection. If your code block inside thewith
statement raises an exception, the transaction will be rolled back. Otherwise, it’ll be committed. The context manager closes the cursor when the block ends and puts the connection back in the pool. The cursor is destroyed after use.Use this when you want a series of statements to be part of one transaction, but you don’t need fine-grained control over the transaction.
The cursor argument enables running queries in a subtransaction. The major difference between a transaction and a subtransaction is that the changes in the database are not committed (nor rolled back) at the end of a subtransaction.
The cursor argument is typically used inside functions which have an optional cursor argument themselves, like this:
>>> def do_something(cursor=None): ... with db.get_cursor(cursor=cursor) as c: ... foo = c.one("...") ... # ... do more stuff ... # Warning: At this point you cannot assume that the changes have ... # been committed, so don't do anything that could be problematic ... # or incoherent if the transaction ends up being rolled back.
When the cursor argument isn’t
None
, the back_as argument is still supported, but the other arguments (autocommit, readonly, and the arguments of thepsycopg2:connection.cursor
method) are not supported.
-
get_connection
(**kw)[source]¶ Return a
ConnectionContextManager
that uses our connection pool.Parameters: kw – passed through to ConnectionContextManager
>>> with db.get_connection() as connection: ... cursor = connection.cursor() ... cursor.all("SELECT * FROM foo") ... [Record(bar='buz', baz=42), Record(bar='bit', baz=537)]
Use this when you want to take advantage of connection pooling and our simple
run
,one
,all
API, but otherwise need full control, for example, to do complex things with transactions.Cursors from connections gotten this way also support the traditional DB-API 2.0 methods:
>>> with db.get_connection() as connection: ... cursor = connection.cursor() ... cursor.execute("SELECT * FROM foo") ... cursor.fetchall() ... [Record(bar='buz', baz=42), Record(bar='bit', baz=537)]
-
unregister_model
(ModelSubclass)[source]¶ Unregister an ORM model.
Parameters: ModelSubclass – the Model
subclass to unregisterRaises: NotAModel
,NotRegistered
If
ModelSubclass
is registered for multiple types, it is unregistered for all of them.
- url (str) – A
-
postgres.
make_Connection
(postgres)[source]¶ Define and return a subclass of
psycopg2.extensions.connection
.Parameters: postgres – the Postgres
instance to bind toReturns: a Connection
classThe class defined and returned here will be linked to the instance of
Postgres
that is passed in aspostgres
, which will use this class as theconnection_factory
for its connection pool.The
cursor
method of this class accepts aback_as
keyword argument. By default the valid values forback_as
aretuple
,namedtuple
,dict
andRow
(or the stringstuple
,namedtuple
,dict
,Row
), andNone
. Ifback_as
is notNone
, then it modifies the default row type of the cursor.We also set client encoding to
UTF-8
.
The Context Managers¶
-
class
postgres.context_managers.
CursorContextManager
(pool, autocommit=False, readonly=False, **cursor_kwargs)[source]¶ Instantiated once per
get_cursor
call.Parameters: During construction, a connection is checked out of the connection pool and its
autocommit
andreadonly
attributes are set, then apsycopg2:cursor
is created from that connection.Upon exit of the
with
block, the connection is rolled back if an exception was raised, or committed otherwise. There are two exceptions to this:- if
autocommit
isTrue
, then the connection is neither rolled back nor committed; - if
readonly
isTrue
, then the connection is always rolled back, never committed.
In all cases the cursor is closed and the connection is put back in the pool.
- if
-
class
postgres.context_managers.
ConnectionCursorContextManager
(conn, autocommit=False, readonly=False, **cursor_kwargs)[source]¶ Creates a cursor from the given connection, then wraps it in a context manager that automatically commits or rolls back the changes on exit.
Parameters: During construction, the connection’s
autocommit
andreadonly
attributes are set, thenpsycopg2:connection.cursor
is called with cursor_kwargs.Upon exit of the
with
block, the connection is rolled back if an exception was raised, or committed otherwise. There are two exceptions to this:- if
autocommit
isTrue
, then the connection is neither rolled back nor committed; - if
readonly
isTrue
, then the connection is always rolled back, never committed.
In all cases the cursor is closed.
- if
-
class
postgres.context_managers.
CursorSubcontextManager
(cursor, back_as=<object object>)[source]¶ Wraps a cursor so that it can be used for a subtransaction.
See
get_cursor
for an explanation of subtransactions.Parameters: - cursor – the
psycopg2:cursor
to wrap - back_as – temporarily overwrites the cursor’s
back_as
attribute
- cursor – the
-
class
postgres.context_managers.
ConnectionContextManager
(pool, autocommit=False, readonly=False)[source]¶ Instantiated once per
get_connection
call.Parameters: This context manager checks out a connection out of the specified pool, sets its
autocommit
andreadonly
attributes.The
__enter__
method returns theConnection
.The
__exit__
method rolls back the connection and puts it back in the pool.
Simple Cursors¶
The postgres
library extends the cursors provided by
psycopg2
to add simpler API methods: run
, one
,
and all
.
-
class
postgres.cursors.
SimpleCursorBase
[source]¶ This is a mixin to provide a simpler API atop the usual DB-API 2.0 API provided by
psycopg2
. Any custom cursor class you would like to use as thecursor_factory
argument toPostgres
must subclass this base.>>> from psycopg2.extras import LoggingCursor >>> from postgres.cursors import SimpleCursorBase >>> class SimpleLoggingCursor(LoggingCursor, SimpleCursorBase): ... pass ... >>> from postgres import Postgres >>> db = Postgres(cursor_factory=SimpleLoggingCursor)
If you try to use a cursor that doesn’t subclass
SimpleCursorBase
as the defaultcursor_factory
for aPostgres
instance, we won’t let you:>>> db = Postgres(cursor_factory=LoggingCursor) # doctest: +NORMALIZE_WHITESPACE ... Traceback (most recent call last): ... postgres.NotASimpleCursor: We can only work with subclasses of SimpleCursorBase, LoggingCursor doesn't fit the bill.
However, we do allow you to use whatever you want as the
cursor_factory
argument for individual calls:>>> with db.get_cursor(cursor_factory=LoggingCursor) as cursor: ... cursor.all("SELECT * FROM foo") Traceback (most recent call last): ... AttributeError: 'LoggingCursor' object has no attribute 'all'
-
back_as
¶ Determines which type of row is returned by the various methods. The valid values are the keys of the
back_as_registry
.
-
run
(sql, parameters=None, **kw)[source]¶ Execute a query, without returning any results.
Parameters: - sql (str) – the SQL statement to execute
- parameters (dict or tuple) – the bind parameters for the SQL statement
- kw – alternative to passing a
dict
as parameters
Example usage:
>>> db.run("DROP TABLE IF EXISTS foo CASCADE") >>> db.run("CREATE TABLE foo (bar text, baz int)") >>> bar, baz = 'buz', 42 >>> db.run("INSERT INTO foo VALUES (%s, %s)", (bar, baz)) >>> db.run("INSERT INTO foo VALUES (%(bar)s, %(baz)s)", dict(bar=bar, baz=baz)) >>> db.run("INSERT INTO foo VALUES (%(bar)s, %(baz)s)", bar=bar, baz=baz)
-
one
(sql, parameters=None, default=None, back_as=None, max_age=None, **kw)[source]¶ Execute a query and return a single result or a default value.
Parameters: - sql (str) – the SQL statement to execute
- parameters (dict or tuple) – the bind parameters for the SQL statement
- default – the value to return or raise if no results are found
- back_as (type or string) – the type of record to return
- max_age (float) – how long to keep the result in the cache (in seconds)
- kw – alternative to passing a
dict
as parameters
Returns: a single record or value, or
default
(ifdefault
is not anException
)Raises: TooFew
orTooMany
, ordefault
(ifdefault
is anException
)Use this for the common case where there should only be one record, but it may not exist yet.
>>> db.one("SELECT * FROM foo WHERE bar='buz'") Record(bar='buz', baz=42)
If the record doesn’t exist, we return
None
:>>> record = db.one("SELECT * FROM foo WHERE bar='blam'") >>> if record is None: ... print("No blam yet.") ... No blam yet.
If you pass
default
we’ll return that instead ofNone
:>>> db.one("SELECT * FROM foo WHERE bar='blam'", default=False) False
If you pass an
Exception
instance or subclass fordefault
, we will raise that for you:>>> db.one("SELECT * FROM foo WHERE bar='blam'", default=Exception) Traceback (most recent call last): ... Exception
We specifically stop short of supporting lambdas or other callables for the
default
parameter. That gets complicated quickly, and it’s easy to just check the return value in the caller and do your extra logic there.You can use
back_as
to override the type associated with the defaultcursor_factory
for yourPostgres
instance:>>> db.default_cursor_factory <class 'postgres.cursors.SimpleNamedTupleCursor'> >>> db.one( "SELECT * FROM foo WHERE bar='buz'" ... , back_as=dict ... ) {'bar': 'buz', 'baz': 42}
That’s a convenience so you don’t have to go to the trouble of remembering where
SimpleDictCursor
lives and importing it in order to get dictionaries back.If the query result has only one column, then we dereference that for you.
>>> db.one("SELECT baz FROM foo WHERE bar='buz'") 42
And if the dereferenced value is
None
, we return the value ofdefault
:>>> db.one("SELECT sum(baz) FROM foo WHERE bar='nope'", default=0) 0
Dereferencing isn’t performed if a
back_as
argument is provided:>>> db.one("SELECT null as foo", back_as=dict) {'foo': None}
-
all
(sql, parameters=None, back_as=None, max_age=None, **kw)[source]¶ Execute a query and return all results.
Parameters: Returns: Use it like this:
>>> db.all("SELECT * FROM foo ORDER BY bar") [Record(bar='bit', baz=537), Record(bar='buz', baz=42)]
You can use
back_as
to override the type associated with the defaultcursor_factory
for yourPostgres
instance:>>> db.default_cursor_factory <class 'postgres.cursors.SimpleNamedTupleCursor'> >>> db.all("SELECT * FROM foo ORDER BY bar", back_as=dict) [{'bar': 'bit', 'baz': 537}, {'bar': 'buz', 'baz': 42}]
That’s a convenience so you don’t have to go to the trouble of remembering where
SimpleDictCursor
lives and importing it in order to get dictionaries back.If the query results in records with a single column, we return a list of the values in that column rather than a list of records of values.
>>> db.all("SELECT baz FROM foo ORDER BY bar") [537, 42]
Unless a
back_as
argument is provided:>>> db.all("SELECT baz FROM foo ORDER BY bar", back_as=dict) [{'baz': 537}, {'baz': 42}]
-
-
class
postgres.cursors.
SimpleTupleCursor
[source]¶ A simple cursor that returns tuples.
This type of cursor is especially well suited if you need to fetch and process a large number of rows at once, because tuples occupy less memory than dicts.
-
class
postgres.cursors.
SimpleNamedTupleCursor
[source]¶ A simple cursor that returns namedtuples.
This type of cursor is especially well suited if you need to fetch and process a large number of similarly-structured rows at once, and you also need the row objects to be more evolved than simple tuples.
-
class
postgres.cursors.
SimpleDictCursor
[source]¶ A simple cursor that returns dicts.
This type of cursor is especially well suited if you don’t care about the order of the columns and don’t need to access them as attributes.
-
class
postgres.cursors.
SimpleRowCursor
[source]¶ A simple cursor that returns
Row
objects.This type of cursor is especially well suited if you want rows to be mutable.
The Row class implements both dict-style and attribute-style lookups and assignments, in addition to index-based lookups. However, index-based assigments aren’t allowed.
>>> from postgres import Postgres >>> from postgres.cursors import SimpleRowCursor >>> db = Postgres(cursor_factory=SimpleRowCursor) >>> row = db.one("SELECT 1 as key, 'foo' as value") >>> row[0] == row['key'] == row.key == 1 True >>> key, value = row >>> (key, value) (1, 'foo') >>> row.value = 'bar' >>> row.timestamp = '2019-09-20 13:15:22.060537+00' >>> row Row(key=1, value='bar', timestamp='2019-09-20 13:15:22.060537+00')
Although Row objects support item lookups and assigments, they are not instances of the
dict
class and they don’t have its methods (get
,items
, etc.).
Caching¶
A query’s results can be stored in the cache
attribute of the
Postgres
object to avoid burdening the database with
redundant requests. The caching is enabled by the max_age argument of the
one and all methods. For example, this call fetches a row from the foo
table and caches it for 5 seconds:
>>> db.one("SELECT * FROM foo WHERE bar = 'bit'", max_age=5)
Record(bar='bit', baz=537)
Any other thread trying to send the same query while it’s still being processed waits for the results to be received instead of sending a duplicate query.
The cache key is the query as it’s sent to the server, so any difference in the parameter values, casing or even whitespace, will result in a cache miss. You might need to refactor your code if it sends queries that are similar but not exactly identical.
The max_age argument doesn’t interfere with the back_as argument. Moreover, the one and all methods can each use the results cached by the other. Consequently, the following call hits the cache if it’s executed within 5 seconds of the previous call above:
>>> db.all("SELECT * FROM foo WHERE bar = 'bit'", back_as=dict, max_age=5)
[{'bar': 'bit', 'baz': 537}]
It’s also possible to use different max_age values for the same query. If a specified max_age is greater than the previous one, then the lifetime of the cache entry is extended accordingly.
The Cache class¶
-
class
postgres.cache.
Cache
(max_size=128)[source]¶ A cache for rows fetched from a database.
Parameters: max_size (int) – The maximum number of entries allowed in the cache. Warning
This cache is only designed to be thread-safe in CPython >= 3.6 and similar Python implementations in which the
OrderedDict
class is thread-safe.A separate lock is used for each entry so that unrelated queries don’t block each other.
After inserting a new entry, the oldest one is removed if the cache now has more than max_size entries.
-
lookup
(key, max_age)[source]¶ Look up a cache entry and check its age.
This function returns
None
if there isn’t an entry in the cache for the specified key or if the entry is older than max_age.
-
An Object-Relational Mapper (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.
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 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.
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 level:
test=# CREATE TABLE foo (bar text, baz int);
CREATE TABLE
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)
test=#
The same thing works for views:
test=# CREATE VIEW bar AS SELECT bar FROM foo;
CREATE VIEW
test=# SELECT * FROM bar;
+------+
| bar |
+------+
| blam |
| whit |
+------+
(2 rows)
test=# SELECT bar FROM bar;
+--------+
| bar |
+--------+
| (blam) |
| (whit) |
+--------+
(2 rows)
test=#
psycopg2
provides a 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
postgres.orm
. We map based on types, not tables.
ORM Tutorial¶
First, write a Python class that subclasses Model
:
>>> from postgres.orm import Model
>>> class Foo(Model):
... typname = "foo"
...
Your model must have a 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 Postgres
instance:
>>> db.register_model(Foo)
That will plug your model into the psycopg2
composite casting
machinery, and you’ll now get instances of your model back from
one
and 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 = db.one("""
... SELECT foo, bar.*
... FROM foo
... JOIN bar ON foo.bar = bar.bar
... ORDER BY foo.bar
... LIMIT 1
... """)
>>> rec.foo.bar
'blam'
>>> rec.bar
'blam'
And as usual, if your query only returns one column, then
one
and all
will do the dereferencing for you:
>>> foo = db.one("SELECT foo FROM foo WHERE bar='blam'")
>>> foo.bar
'blam'
>>> [foo.bar 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):
... self.db.run("UPDATE foo SET baz=%s WHERE bar=%s", (baz, self.bar))
... self.set_attributes(baz=baz)
...
>>> db.register_model(Foo)
Then use that method to update the database:
>>> db.one("SELECT baz FROM foo WHERE bar='blam'")
42
>>> foo = db.one("SELECT foo FROM foo WHERE bar='blam'")
>>> foo.update_baz(90210)
>>> foo.baz
90210
>>> db.one("SELECT baz FROM foo WHERE bar='blam'")
90210
We never update your database for you. We also never sync your objects for you:
note the use of the set_attributes
method to
sync our instance after modifying the database.
The Model Base Class¶
-
class
postgres.orm.
Model
(values)[source]¶ This is the base class for models in
postgres.orm
.Instances of subclasses of
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 theset_attributes
helper.-
set_attributes
(**kw)[source]¶ Set instance attributes, according to
kw
.Raises: 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
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.
-
Changelog¶
4.0 (Sep 20, 2021)
- implemented caching query results (#97)
- BREAKING: the methods
one
andall
now have a max_age argument; make sure your code doesn’t use it as a parameter name - dropped support for Python 2.7 and 3.5 (#96)
3.0.0 (Oct 19, 2019)
- the
ReadOnly
exception has been renamed toReadOnlyAttribute
, and the_read_only_attributes
attribute of theModel
class has been renamed toattnames
(#91) - the ORM has been optimized and now supports __slots__ (#88)
- BREAKING: the
check_registration
method now always returns a list (#87) - PostgreSQL versions older than 9.2 are no longer supported (#83)
- idle connections are now kept open for up to 10 minutes by default (#81)
- the methods
run
,one
andall
now support receiving query parameters as keyword arguments (#79) - BREAKING: the methods
run
,one
andall
no longer pass extra arguments toget_cursor
(#79 and #67) - subtransactions are now supported (#78 and #90)
- BREAKING: single-column rows aren’t unpacked anymore when the back_as argument is provided (#77)
- the cursor methods now also support the back_as argument (#77)
- a new row type and cursor subclass are now available, see
SimpleRowCursor
for details (#75) - the ORM now supports non-default schemas (#74)
- connections now also have a
get_cursor
method (#73 and #82) - the values accepted by the back_as argument can now be customized (#72)
- the
one
andall
no longer fail when a row is made up of a single column namedvalues
(#71) - any
InterfaceError
exception raised during an automatic rollback is now suppressed (#70) - the
get_cursor
method has two new optional arguments: autocommit and readonly (#69) Postgres
objects now have areadonly
attribute (#69)- the url argument is no longer required when creating a
Postgres
object (#68)
2.2.2 (Sep 12, 2018)
- the only dependency was changed from
psycopg2 >= 2.5.0
topsycopg2-binary >= 2.7.5
(#64) - the license was changed from CC0 to MIT (#62)
2.2.1 (Oct 10, 2015)
- a bug in the URL-to-DSN conversion function was fixed (#53)
2.2.0 (Sep 12, 2015)
- the ORM was modified to detect some schema changes (#43)