Back to index

psycopg2  2.4.5
myfirstrecipe.py
Go to the documentation of this file.
00001 """
00002 Using a tuple as a bound variable in "SELECT ... IN (...)" clauses
00003 in PostgreSQL using psycopg2
00004 
00005 Some time ago someone asked on the psycopg mailing list how to have a
00006 bound variable expand to the right SQL for an SELECT IN clause:
00007 
00008     SELECT * FROM atable WHERE afield IN (value1, value2, value3)
00009 
00010 with the values to be used in the IN clause to be passed to the cursor
00011 .execute() method in a tuple as a bound variable, i.e.:
00012 
00013     in_values = ("value1", "value2", "value3")
00014     curs.execute("SELECT ... IN %s", (in_values,))
00015 
00016 psycopg 1 does support typecasting from Python to PostgreSQL (and back)
00017 only for simple types and this problem has no elegant solution (short or
00018 writing a wrapper class returning the pre-quoted text in an __str__
00019 method.
00020 
00021 But psycopg2 offers a simple and elegant solution by partially
00022 implementing the Object Adaptation from PEP 246. psycopg2 moves
00023 the type-casting logic into external adapters and a somehow
00024 broken adapt() function.
00025 
00026 While the original adapt() takes 3 arguments, psycopg2's one only takes
00027 1: the bound variable to be adapted. The result is an object supporting
00028 a not-yet well defined protocol that we can call ISQLQuote:
00029 
00030     class ISQLQuote:
00031 
00032         def getquoted(self):
00033             "Returns a quoted string representing the bound variable."
00034 
00035         def getbinary(self):
00036            "Returns a binary quoted string representing the bound variable."
00037     
00038         def getbuffer(self):
00039             "Returns the wrapped object itself."
00040 
00041         __str__ = getquoted
00042 
00043 Then one of the functions (usually .getquoted()) is called by psycopg2 at
00044 the right time to obtain the right, sql-quoted representation for the
00045 corresponding bound variable.
00046 
00047 The nice part is that the default, built-in adapters, derived from
00048 psycopg 1 tyecasting code can be overridden by the programmer, simply
00049 replacing them in the psycopg.extensions.adapters dictionary.
00050 
00051 Then the solution to the original problem is now obvious: write an
00052 adapter that adapts tuple objects into the right SQL string, by calling
00053 recursively adapt() on each element.
00054 
00055 psycopg2 development can be tracked on the psycopg mailing list:
00056 
00057    http://lists.initd.org/mailman/listinfo/psycopg
00058 
00059 """
00060 
00061 # Copyright (C) 2001-2010 Federico Di Gregorio  <fog@debian.org>
00062 #
00063 # psycopg2 is free software: you can redistribute it and/or modify it
00064 # under the terms of the GNU Lesser General Public License as published
00065 # by the Free Software Foundation, either version 3 of the License, or
00066 # (at your option) any later version.
00067 #
00068 # psycopg2 is distributed in the hope that it will be useful, but WITHOUT
00069 # ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
00070 # FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public
00071 # License for more details.
00072 
00073 import psycopg2
00074 import psycopg2.extensions
00075 from psycopg2.extensions import adapt as psycoadapt
00076 from psycopg2.extensions import register_adapter
00077 
00078 class AsIs(object):
00079     """An adapter that just return the object 'as is'.
00080 
00081     psycopg 1.99.9 has some optimizations that make impossible to call
00082     adapt() without adding some basic adapters externally. This limitation
00083     will be lifted in a future release.
00084     """
00085     def __init__(self, obj):
00086         self.__obj = obj
00087     def getquoted(self):
00088         return self.__obj
00089     
00090 class SQL_IN(object):
00091     """Adapt a tuple to an SQL quotable object."""
00092     
00093     def __init__(self, seq):
00094        self._seq = seq
00095 
00096     def prepare(self, conn):
00097         pass
00098 
00099     def getquoted(self):
00100         # this is the important line: note how every object in the
00101         # list is adapted and then how getquoted() is called on it
00102 
00103        qobjs = [str(psycoadapt(o).getquoted()) for o in self._seq]
00104 
00105        return '(' + ', '.join(qobjs) + ')'
00106        
00107     __str__ = getquoted
00108 
00109     
00110 # add our new adapter class to psycopg list of adapters
00111 register_adapter(tuple, SQL_IN)
00112 register_adapter(float, AsIs)
00113 register_adapter(int, AsIs)
00114 
00115 # usually we would call:
00116 #
00117 #     conn = psycopg.connect("...")
00118 #     curs = conn.cursor()
00119 #     curs.execute("SELECT ...", (("this", "is", "the", "tuple"),))
00120 #     
00121 # but we have no connection to a database right now, so we just check
00122 # the SQL_IN class by calling psycopg's adapt() directly:
00123 
00124 if __name__ == '__main__':
00125     print "Note how the string will be SQL-quoted, but the number will not:"
00126     print psycoadapt(("this is an 'sql quoted' str\\ing", 1, 2.0))