2
This example/recipe has been contributed by Valentino Volonghi (dialtone)
4
Mapping arbitrary objects to a PostgreSQL database with psycopg2
8
You need to store arbitrary objects in a PostgreSQL database without being
9
intrusive for your classes (don't want inheritance from an 'Item' or
15
from datetime import datetime
18
from psycopg2.extensions import adapt, register_adapter
27
# Here is the adapter for every object that we may ever need to
28
# insert in the database. It receives the original object and does
29
# its job on that instance
31
class ObjectMapper(object):
32
def __init__(self, orig, curs=None):
35
self.items, self.fields = self._gatherState()
37
def _gatherState(self):
38
adaptee_name = self.orig.__class__.__name__
39
fields = sorted([(field, getattr(self.orig, field))
40
for field in persistent_fields[adaptee_name]])
42
for item, value in fields:
46
def getTableName(self):
47
return self.orig.__class__.__name__
49
def getMappedValues(self):
52
tmp.append("%%(%s)s"%i)
55
def getValuesDict(self):
56
return dict(self.fields)
61
def generateInsert(self):
63
qry += " " + self.getTableName() + " ("
64
qry += ", ".join(self.getFields()) + ") VALUES ("
65
qry += self.getMappedValues() + ")"
66
return qry, self.getValuesDict()
68
# Here are the objects
72
self.creation_time = datetime.now()
73
self.album_id = self.id
74
Album.id = Album.id + 1
75
self.binary_data = buffer('12312312312121')
80
self.items = ['rice','chocolate']
82
self.order_id = self.id
83
Order.id = Order.id + 1
85
register_adapter(Album, ObjectMapper)
86
register_adapter(Order, ObjectMapper)
88
# Describe what is needed to save on each object
89
# This is actually just configuration, you can use xml with a parser if you
90
# like to have plenty of wasted CPU cycles ;P.
92
persistent_fields = {'Album': ['album_id', 'creation_time', 'binary_data'],
93
'Order': ['order_id', 'items', 'price']
96
print adapt(Album()).generateInsert()
97
print adapt(Album()).generateInsert()
98
print adapt(Album()).generateInsert()
99
print adapt(Order()).generateInsert()
100
print adapt(Order()).generateInsert()
101
print adapt(Order()).generateInsert()
106
Psycopg 2 has a great new feature: adaptation. The big thing about
107
adaptation is that it enable the programmer to glue most of the
108
code out there without many difficulties.
110
This recipe tries to focus the attention on a way to generate SQL queries to
111
insert completely new objects inside a database. As you can see objects do
112
not know anything about the code that is handling them. We specify all the
113
fields that we need for each object through the persistent_fields dict.
115
The most important lines of this recipe are:
116
register_adapter(Album, ObjectMapper)
117
register_adapter(Order, ObjectMapper)
119
In these line we notify the system that when we call adapt with an Album instance
120
as an argument we want it to istantiate ObjectMapper passing the Album instance
121
as argument (self.orig in the ObjectMapper class).
123
The output is something like this (for each call to generateInsert):
125
('INSERT INTO Album (album_id, binary_data, creation_time) VALUES
126
(%(album_id)s, %(binary_data)s, %(creation_time)s)',
128
{'binary_data': <read-only buffer for 0x402de070, ...>,
129
'creation_time': datetime.datetime(2004, 9, 10, 20, 48, 29, 633728),
133
This is a tuple of {SQL_QUERY, FILLING_DICT}, and all the quoting/converting
134
stuff (from python's datetime to postgres s and from python's buffer to
135
postgres' blob) is handled with the same adaptation process hunder the hood
138
At last, just notice that ObjectMapper is working for both Album and Order
139
instances without any glitches at all, and both classes could have easily been
140
coming from closed source libraries or C coded ones (which are not easily
141
modified), whereas a common pattern in todays ORMs or OODBs is to provide
142
a basic 'Persistent' object that already knows how to store itself in the
added
removed
examples/dialtone.py
