+++ /dev/null
-"""
-Using a tuple as a bound variable in "SELECT ... IN (...)" clauses
-in PostgreSQL using psycopg 2
-
-Some time ago someone asked on the psycopg mailing list how to have a
-bound variable expand to the right SQL for an SELECT IN clause:
-
- SELECT * FROM atable WHERE afield IN (value1, value2, value3)
-
-with the values to be used in the IN clause to be passed to the cursor
-.execute() method in a tuple as a bound variable, i.e.:
-
- in_values = ("value1", "value2", "value3")
- curs.execute("SELECT ... IN %s", (in_values,))
-
-psycopg 1 does support typecasting from Python to PostgreSQL (and back)
-only for simple types and this problem has no elegant solution (short or
-writing a wrapper class returning the pre-quoted text in an __str__
-method.
-
-But psycopg 2 offers a simple and elegant solution by partially
-implementing the Object Adaptation from PEP 246. psycopg 2 (still in
-beta and currently labeled as 1.99.9) moves the type-casting logic into
-external adapters and a somehow broken adapt() function.
-
-While the original adapt() takes 3 arguments, psycopg's one only takes
-1: the bound variable to be adapted. The result is an object supporting
-a not-yet well defined protocol that we can call IPsycopgSQLQuote:
-
- class IPsycopgSQLQuote:
-
- def getquoted(self):
- "Returns a quoted string representing the bound variable."
-
- def getbinary(self):
- "Returns a binary quoted string representing the bound variable."
-
- def getbuffer(self):
- "Returns the wrapped object itself."
-
- __str__ = getquoted
-
-Then one of the functions (usually .getquoted()) is called by psycopg at
-the right time to obtain the right, sql-quoted representation for the
-corresponding bound variable.
-
-The nice part is that the default, built-in adapters, derived from
-psycopg 1 tyecasting code can be overridden by the programmer, simply
-replacing them in the psycopg.extensions.adapters dictionary.
-
-Then the solution to the original problem is now obvious: write an
-adapter that adapts tuple objects into the right SQL string, by calling
-recursively adapt() on each element.
-
-Note: psycopg 2 adapter code is still very young and will probably move
-to a more 'standard' (3 arguments) implementation for the adapt()
-function; as long as that does not slow down too much query execution.
-
-Psycopg 2 development can be tracked on the psycopg mailing list:
-
- http://lists.initd.org/mailman/listinfo/psycopg
-
-and on the psycopg 2 wiki:
-
- http://wiki.initd.org/Projects/Psycopg2
-
-"""
-
-import psycopg2
-import psycopg2.extensions
-from psycopg2.extensions import adapt as psycoadapt
-from psycopg2.extensions import register_adapter
-
-class AsIs(object):
- """An adapter that just return the object 'as is'.
-
- psycopg 1.99.9 has some optimizations that make impossible to call
- adapt() without adding some basic adapters externally. This limitation
- will be lifted in a future release.
- """
- def __init__(self, obj):
- self.__obj = obj
- def getquoted(self):
- return self.__obj
-
-class SQL_IN(object):
- """Adapt a tuple to an SQL quotable object."""
-
- def __init__(self, seq):
- self._seq = seq
-
- def prepare(self, conn):
- pass
-
- def getquoted(self):
- # this is the important line: note how every object in the
- # list is adapted and then how getquoted() is called on it
-
- qobjs = [str(psycoadapt(o).getquoted()) for o in self._seq]
-
- return '(' + ', '.join(qobjs) + ')'
-
- __str__ = getquoted
-
-
-# add our new adapter class to psycopg list of adapters
-register_adapter(tuple, SQL_IN)
-register_adapter(float, AsIs)
-register_adapter(int, AsIs)
-
-# usually we would call:
-#
-# conn = psycopg.connect("...")
-# curs = conn.cursor()
-# curs.execute("SELECT ...", (("this", "is", "the", "tuple"),))
-#
-# but we have no connection to a database right now, so we just check
-# the SQL_IN class by calling psycopg's adapt() directly:
-
-if __name__ == '__main__':
- print "Note how the string will be SQL-quoted, but the number will not:"
- print psycoadapt(("this is an 'sql quoted' str\\ing", 1, 2.0))
git://git.onelab.eu / plcapi.git / blobdiff