Merge from trunk

[plcapi.git] / trunk / psycopg2 / examples / myfirstrecipe.py

diff --git a/trunk/psycopg2/examples/myfirstrecipe.py b/trunk/psycopg2/examples/myfirstrecipe.py
new file mode 100644 (file)
index 0000000..4ddc65e
--- /dev/null
+++ b/trunk/psycopg2/examples/myfirstrecipe.py
@@ -0,0 +1,122 @@
+"""
+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))