2 Using a tuple as a bound variable in "SELECT ... IN (...)" clauses
3 in PostgreSQL using psycopg 2
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:
8 SELECT * FROM atable WHERE afield IN (value1, value2, value3)
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.:
13 in_values = ("value1", "value2", "value3")
14 curs.execute("SELECT ... IN %s", (in_values,))
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__
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.
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:
30 class IPsycopgSQLQuote:
33 "Returns a quoted string representing the bound variable."
36 "Returns a binary quoted string representing the bound variable."
39 "Returns the wrapped object itself."
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.
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.
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.
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.
59 Psycopg 2 development can be tracked on the psycopg mailing list:
61 http://lists.initd.org/mailman/listinfo/psycopg
63 and on the psycopg 2 wiki:
65 http://wiki.initd.org/Projects/Psycopg2
70 import psycopg2.extensions
71 from psycopg2.extensions import adapt as psycoadapt
72 from psycopg2.extensions import register_adapter
75 """An adapter that just return the object 'as is'.
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.
81 def __init__(self, obj):
87 """Adapt a tuple to an SQL quotable object."""
89 def __init__(self, seq):
92 def prepare(self, conn):
96 # this is the important line: note how every object in the
97 # list is adapted and then how getquoted() is called on it
99 qobjs = [str(psycoadapt(o).getquoted()) for o in self._seq]
101 return '(' + ', '.join(qobjs) + ')'
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)
111 # usually we would call:
113 # conn = psycopg.connect("...")
114 # curs = conn.cursor()
115 # curs.execute("SELECT ...", (("this", "is", "the", "tuple"),))
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:
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))