trunk/psycopg2/examples/myfirstrecipe.py

   1 """
   2 Using a tuple as a bound variable in "SELECT ... IN (...)" clauses
   3 in PostgreSQL using psycopg 2
   4
   5 Some time ago someone asked on the psycopg mailing list how to have a
   6 bound variable expand to the right SQL for an SELECT IN clause:
   7
   8     SELECT * FROM atable WHERE afield IN (value1, value2, value3)
   9
  10 with the values to be used in the IN clause to be passed to the cursor
  11 .execute() method in a tuple as a bound variable, i.e.:
  12
  13     in_values = ("value1", "value2", "value3")
  14     curs.execute("SELECT ... IN %s", (in_values,))
  15
  16 psycopg 1 does support typecasting from Python to PostgreSQL (and back)
  17 only for simple types and this problem has no elegant solution (short or
  18 writing a wrapper class returning the pre-quoted text in an __str__
  19 method.
  20
  21 But psycopg 2 offers a simple and elegant solution by partially
  22 implementing the Object Adaptation from PEP 246. psycopg 2 (still in
  23 beta and currently labeled as 1.99.9) moves the type-casting logic into
  24 external adapters and a somehow broken adapt() function.
  25
  26 While the original adapt() takes 3 arguments, psycopg's one only takes
  27 1: the bound variable to be adapted. The result is an object supporting
  28 a not-yet well defined protocol that we can call IPsycopgSQLQuote:
  29
  30     class IPsycopgSQLQuote:
  31
  32         def getquoted(self):
  33             "Returns a quoted string representing the bound variable."
  34
  35         def getbinary(self):
  36             "Returns a binary quoted string representing the bound variable."
  37
  38         def getbuffer(self):
  39             "Returns the wrapped object itself."
  40
  41         __str__ = getquoted
  42
  43 Then one of the functions (usually .getquoted()) is called by psycopg at
  44 the right time to obtain the right, sql-quoted representation for the
  45 corresponding bound variable.
  46
  47 The nice part is that the default, built-in adapters, derived from
  48 psycopg 1 tyecasting code can be overridden by the programmer, simply
  49 replacing them in the psycopg.extensions.adapters dictionary.
  50
  51 Then the solution to the original problem is now obvious: write an
  52 adapter that adapts tuple objects into the right SQL string, by calling
  53 recursively adapt() on each element.
  54
  55 Note: psycopg 2 adapter code is still very young and will probably move
  56 to a more 'standard' (3 arguments) implementation for the adapt()
  57 function; as long as that does not slow down too much query execution.
  58
  59 Psycopg 2 development can be tracked on the psycopg mailing list:
  60
  61    http://lists.initd.org/mailman/listinfo/psycopg
  62
  63 and on the psycopg 2 wiki:
  64
  65    http://wiki.initd.org/Projects/Psycopg2
  66
  67 """
  68
  69 import psycopg2
  70 import psycopg2.extensions
  71 from psycopg2.extensions import adapt as psycoadapt
  72 from psycopg2.extensions import register_adapter
  73
  74 class AsIs(object):
  75     """An adapter that just return the object 'as is'.
  76
  77     psycopg 1.99.9 has some optimizations that make impossible to call
  78     adapt() without adding some basic adapters externally. This limitation
  79     will be lifted in a future release.
  80     """
  81     def __init__(self, obj):
  82         self.__obj = obj
  83     def getquoted(self):
  84         return self.__obj
  85
  86 class SQL_IN(object):
  87     """Adapt a tuple to an SQL quotable object."""
  88
  89     def __init__(self, seq):
  90         self._seq = seq
  91
  92     def prepare(self, conn):
  93         pass
  94
  95     def getquoted(self):
  96         # this is the important line: note how every object in the
  97         # list is adapted and then how getquoted() is called on it
  98
  99         qobjs = [str(psycoadapt(o).getquoted()) for o in self._seq]
 100
 101         return '(' + ', '.join(qobjs) + ')'
 102
 103     __str__ = getquoted
 104
 105
 106 # add our new adapter class to psycopg list of adapters
 107 register_adapter(tuple, SQL_IN)
 108 register_adapter(float, AsIs)
 109 register_adapter(int, AsIs)
 110
 111 # usually we would call:
 112 #
 113 #     conn = psycopg.connect("...")
 114 #     curs = conn.cursor()
 115 #     curs.execute("SELECT ...", (("this", "is", "the", "tuple"),))
 116 #
 117 # but we have no connection to a database right now, so we just check
 118 # the SQL_IN class by calling psycopg's adapt() directly:
 119
 120 if __name__ == '__main__':
 121     print "Note how the string will be SQL-quoted, but the number will not:"
 122     print psycoadapt(("this is an 'sql quoted' str\\ing", 1, 2.0))