Back to index

psycopg2  2.4.5
dialtone.py
Go to the documentation of this file.
00001 """
00002 This example/recipe has been contributed by Valentino Volonghi (dialtone)
00003 
00004 Mapping arbitrary objects to a PostgreSQL database with psycopg2
00005 
00006 - Problem
00007 
00008 You need to store arbitrary objects in a PostgreSQL database without being
00009 intrusive for your classes (don't want inheritance from an 'Item' or 
00010 'Persistent' object).
00011 
00012 - Solution
00013 """
00014 
00015 from datetime import datetime
00016  
00017 import psycopg2
00018 from psycopg2.extensions import adapt, register_adapter
00019 
00020 try:
00021     sorted()
00022 except:
00023     def sorted(seq):
00024         seq.sort()
00025         return seq
00026 
00027 # Here is the adapter for every object that we may ever need to 
00028 # insert in the database. It receives the original object and does
00029 # its job on that instance
00030 
00031 class ObjectMapper(object):
00032     def __init__(self, orig, curs=None):
00033         self.orig = orig
00034         self.tmp = {}
00035         self.items, self.fields = self._gatherState()
00036  
00037     def _gatherState(self):
00038         adaptee_name = self.orig.__class__.__name__
00039         fields = sorted([(field, getattr(self.orig, field))
00040                         for field in persistent_fields[adaptee_name]])
00041         items = []
00042         for item, value in fields:
00043             items.append(item)
00044         return items, fields
00045  
00046     def getTableName(self):
00047         return self.orig.__class__.__name__
00048  
00049     def getMappedValues(self):
00050         tmp = []
00051         for i in self.items:
00052             tmp.append("%%(%s)s"%i)
00053         return ", ".join(tmp)
00054  
00055     def getValuesDict(self):
00056         return dict(self.fields)
00057  
00058     def getFields(self):
00059         return self.items
00060 
00061     def generateInsert(self):
00062         qry = "INSERT INTO"
00063         qry += " " + self.getTableName() + " ("
00064         qry += ", ".join(self.getFields()) + ") VALUES ("
00065         qry += self.getMappedValues() + ")"
00066         return qry, self.getValuesDict()
00067 
00068 # Here are the objects
00069 class Album(object):    
00070     id = 0 
00071     def __init__(self):
00072         self.creation_time = datetime.now()
00073         self.album_id = self.id
00074         Album.id = Album.id + 1
00075         self.binary_data = buffer('12312312312121')
00076  
00077 class Order(object):
00078      id = 0
00079      def __init__(self):
00080         self.items = ['rice','chocolate']
00081         self.price = 34
00082         self.order_id = self.id
00083         Order.id = Order.id + 1
00084 
00085 register_adapter(Album, ObjectMapper)
00086 register_adapter(Order, ObjectMapper)
00087     
00088 # Describe what is needed to save on each object
00089 # This is actually just configuration, you can use xml with a parser if you
00090 # like to have plenty of wasted CPU cycles ;P.
00091 
00092 persistent_fields = {'Album': ['album_id', 'creation_time', 'binary_data'],
00093                               'Order':  ['order_id', 'items', 'price']
00094                             }
00095  
00096 print adapt(Album()).generateInsert()
00097 print adapt(Album()).generateInsert()
00098 print adapt(Album()).generateInsert()
00099 print adapt(Order()).generateInsert()
00100 print adapt(Order()).generateInsert()
00101 print adapt(Order()).generateInsert()
00102 
00103 """
00104 - Discussion
00105 
00106 Psycopg 2 has a great new feature: adaptation. The big thing about 
00107 adaptation is that it enables the programmer to glue most of the 
00108 code out there without many difficulties.
00109 
00110 This recipe tries to focus attention on a way to generate SQL queries to 
00111 insert  completely new objects inside a database. As you can see objects do 
00112 not know anything about the code that is handling them. We specify all the 
00113 fields that we need for each object through the persistent_fields dict.
00114 
00115 The most important lines of this recipe are:
00116     register_adapter(Album, ObjectMapper)
00117     register_adapter(Order, ObjectMapper)
00118 
00119 In these lines we notify the system that when we call adapt with an Album instance 
00120 as an argument we want it to istantiate ObjectMapper passing the Album instance  
00121 as argument (self.orig in the ObjectMapper class).
00122 
00123 The output is something like this (for each call to generateInsert):
00124     
00125 ('INSERT INTO Album (album_id, binary_data, creation_time) VALUES 
00126    (%(album_id)s, %(binary_data)s, %(creation_time)s)', 
00127       
00128   {'binary_data': <read-only buffer for 0x402de070, ...>, 
00129     'creation_time':   datetime.datetime(2004, 9, 10, 20, 48, 29, 633728), 
00130     'album_id': 1}
00131 )
00132 
00133 This is a tuple of {SQL_QUERY, FILLING_DICT}, and all the quoting/converting 
00134 stuff (from python's datetime to postgres s and from python's buffer to 
00135 postgres' blob) is handled with the same adaptation process hunder the hood 
00136 by psycopg2.
00137 
00138 At last, just notice that ObjectMapper is working for both Album and Order 
00139 instances without any glitches at all, and both classes could have easily been 
00140 coming from closed source libraries or C coded ones (which are not easily 
00141 modified), whereas a common pattern in todays ORMs or OODBs is to provide 
00142 a basic 'Persistent' object that already knows how to store itself in the 
00143 database.
00144 """