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