diff options
Diffstat (limited to 'examples')
| -rw-r--r-- | examples/binary.py | 88 | ||||
| -rw-r--r-- | examples/cursor.py | 64 | ||||
| -rw-r--r-- | examples/dialtone.py | 145 | ||||
| -rw-r--r-- | examples/dt.py | 91 | ||||
| -rw-r--r-- | examples/encoding.py | 102 | ||||
| -rw-r--r-- | examples/lastrowid.py | 59 | ||||
| -rw-r--r-- | examples/mogrify.py | 47 | ||||
| -rw-r--r-- | examples/myfirstrecipe.py | 117 | ||||
| -rw-r--r-- | examples/simple.py | 52 | ||||
| -rw-r--r-- | examples/somehackers.jpg | bin | 0 -> 22565 bytes | |||
| -rw-r--r-- | examples/threads.py | 160 | ||||
| -rw-r--r-- | examples/tz.py | 62 | ||||
| -rw-r--r-- | examples/whereareyou.jpg | bin | 0 -> 34980 bytes |
13 files changed, 987 insertions, 0 deletions
diff --git a/examples/binary.py b/examples/binary.py new file mode 100644 index 0000000..3b543e5 --- /dev/null +++ b/examples/binary.py @@ -0,0 +1,88 @@ +# binary.py - working with binary data +# +# Copyright (C) 2001-2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below tis line (except for experimenting) + +import sys, psycopg + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +print "Encoding for this connection is", conn.encoding + +curs = conn.cursor() +try: + curs.execute("CREATE TABLE test_binary (id int4, name text, img bytea)") +except: + conn.rollback() + curs.execute("DROP TABLE test_binary") + curs.execute("CREATE TABLE test_binary (id int4, name text, img bytea)") +conn.commit() + +# first we try two inserts, one with an explicit Binary call and the other +# using a buffer on a file object. + +data1 = {'id':1, 'name':'somehackers.jpg', + 'img':psycopg.Binary(open('somehackers.jpg').read())} +data2 = {'id':2, 'name':'whereareyou.jpg', + 'img':buffer(open('whereareyou.jpg').read())} + +curs.execute("""INSERT INTO test_binary + VALUES (%(id)d, %(name)s, %(img)s)""", data1) +curs.execute("""INSERT INTO test_binary + VALUES (%(id)d, %(name)s, %(img)s)""", data2) + +# now we try to extract the images as simple text strings + +print "Extracting the images as strings..." +curs.execute("SELECT * FROM test_binary") + +for row in curs.fetchall(): + name, ext = row[1].split('.') + new_name = name + '_S.' + ext + print " writing %s to %s ..." % (name+'.'+ext, new_name), + open(new_name, 'wb').write(row[2]) + print "done" + print " python type of image data is", type(row[2]) + +# extract exactly the same data but using a binary cursor + +print "Extracting the images using a binary cursor:" + +curs.execute("""DECLARE zot CURSOR FOR + SELECT img, name FROM test_binary FOR READ ONLY""") +curs.execute("""FETCH ALL FROM zot""") + +for row in curs.fetchall(): + name, ext = row[1].split('.') + new_name = name + '_B.' + ext + print " writing %s to %s ..." % (name+'.'+ext, new_name), + open(new_name, 'wb').write(row[0]) + print "done" + print " python type of image data is", type(row[0]) + +# this rollback is requires because we can't drop a table with a binary cusor +# declared and still open +conn.rollback() + +curs.execute("DROP TABLE test_binary") +conn.commit() + +print "\nNow try to load the new images, to check it worked!" diff --git a/examples/cursor.py b/examples/cursor.py new file mode 100644 index 0000000..2205278 --- /dev/null +++ b/examples/cursor.py @@ -0,0 +1,64 @@ +# cursor.py - how to subclass the cursor type +# +# Copyright (C) 2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below this line (except for experimenting) + +import sys +import psycopg +import psycopg.extensions + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dsn:", DSN + +conn = psycopg.connect(DSN) +print "Encoding for this connection is", conn.encoding + + +class NoDataError(psycopg.ProgrammingError): + """Exception that will be raised by our cursor.""" + pass + +class Cursor(psycopg.extensions.cursor): + """A custom cursor.""" + + def fetchone(self): + """Like fetchone but raise an exception if no data is available. + + Note that to have .fetchmany() and .fetchall() to raise the same + exception we'll have to override them too; even if internally psycopg + uses the same function to fetch rows, the code path from Python is + different. + """ + d = psycopg.extensions.cursor.fetchone(self) + if d is None: + raise NoDataError("no more data") + return d + +curs = conn.cursor(factory=Cursor) +curs.execute("SELECT 1 AS foo") +print "Result of fetchone():", curs.fetchone() + +# now let's raise the exception +try: + curs.fetchone() +except NoDataError, err: + print "Exception caugth:", err + +conn.rollback() diff --git a/examples/dialtone.py b/examples/dialtone.py new file mode 100644 index 0000000..9e453c3 --- /dev/null +++ b/examples/dialtone.py @@ -0,0 +1,145 @@ +""" +This example/recipe has been contributed by Valentino Volonghi (dialtone) + +Mapping arbitrary objects to a PostgreSQL database with psycopg2 + +- Problem + +You need to store arbitrary objects in a PostgreSQL database without being +intrusive for your classes (don't want inheritance from an 'Item' or +'Persistent' object). + +- Solution +""" + +from datetime import datetime + +import psycopg +from psycopg.extensions import adapters, adapt + +try: sorted() +except NameError: + def sorted(seq): + seq.sort() + return seq + +# Here is the adapter for every object that we may ever need to +# insert in the database. It receives the original object and does +# its job on that instance + +class ObjectMapper(object): + def __init__(self, orig): + self.orig = orig + self.tmp = {} + self.items, self.fields = self._gatherState() + + def _gatherState(self): + adaptee_name = self.orig.__class__.__name__ + fields = sorted([(field, getattr(self.orig, field)) + for field in persistent_fields[adaptee_name]]) + items = [] + for item, value in fields: + items.append(item) + return items, fields + + def getTableName(self): + return self.orig.__class__.__name__ + + def getMappedValues(self): + tmp = [] + for i in self.items: + tmp.append("%%(%s)s"%i) + return ", ".join(tmp) + + def getValuesDict(self): + return dict(self.fields) + + def getFields(self): + return self.items + + def generateInsert(self): + qry = "INSERT INTO" + qry += " " + self.getTableName() + " (" + qry += ", ".join(self.getFields()) + ") VALUES (" + qry += self.getMappedValues() + ")" + return qry, self.getValuesDict() + +# Here are the objects +class Album(object): + id = 0 + def __init__(self): + self.creation_time = datetime.now() + self.album_id = self.id + Album.id = Album.id + 1 + self.binary_data = buffer('12312312312121') + +class Order(object): + id = 0 + def __init__(self): + self.items = ['rice','chocolate'] + self.price = 34 + self.order_id = self.id + Order.id = Order.id + 1 + +adapters.update({Album: ObjectMapper, Order: ObjectMapper}) + +# Describe what is needed to save on each object +# This is actually just configuration, you can use xml with a parser if you +# like to have plenty of wasted CPU cycles ;P. + +persistent_fields = {'Album': ['album_id', 'creation_time', 'binary_data'], + 'Order': ['order_id', 'items', 'price'] + } + +print adapt(Album()).generateInsert() +print adapt(Album()).generateInsert() +print adapt(Album()).generateInsert() +print adapt(Order()).generateInsert() +print adapt(Order()).generateInsert() +print adapt(Order()).generateInsert() + +""" +- Discussion + +Psycopg 2 has a great new feature: adaptation. The big thing about +adaptation is that it enable the programmer to glue most of the +code out there without many difficulties. + +This recipe tries to focus the attention on a way to generate SQL queries to +insert completely new objects inside a database. As you can see objects do +not know anything about the code that is handling them. We specify all the +fields that we need for each object through the persistent_fields dict. + +The most important line of this recipe is this one: + adapters.update({Album: ObjectMapper, Order: ObjectMapper}) + +In this line we notify the system that when we call adapt with an Album instance +as an argument we want it to istantiate ObjectMapper passing the Album instance +as argument (self.orig in the ObjectMapper class). + +adapters is just a python dict with a Key that represents the type +we need to adapt from and a value that is the adapter +which will adapt to the wanted interface. + +The output is something like this (for each call to generateInsert): + +('INSERT INTO Album (album_id, binary_data, creation_time) VALUES + (%(album_id)s, %(binary_data)s, %(creation_time)s)', + + {'binary_data': <read-only buffer for 0x402de070, ...>, + 'creation_time': datetime.datetime(2004, 9, 10, 20, 48, 29, 633728), + 'album_id': 1} +) + +This is a tuple of {SQL_QUERY, FILLING_DICT}, and all the quoting/converting +stuff (from python's datetime to postgres s and from python's buffer to +postgres' blob) is handled with the same adaptation process hunder the hood +by psycopg2. + +At last, just notice that ObjectMapper is working for both Album and Order +instances without any glitches at all, and both classes could have easily been +coming from closed source libraries or C coded ones (which are not easily +modified), whereas a common pattern in todays ORMs or OODBs is to provide +a basic 'Persistent' object that already knows how to store itself in the +database. +""" diff --git a/examples/dt.py b/examples/dt.py new file mode 100644 index 0000000..f285c7a --- /dev/null +++ b/examples/dt.py @@ -0,0 +1,91 @@ +# datetime.py - example of using date and time types +# +# Copyright (C) 2001-2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below tis line (except for experimenting) + +import sys, psycopg +import mx.DateTime +import datetime + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +curs = conn.cursor() + +try: + curs.execute("""CREATE TABLE test_dt (k int4, d date, t time, dt timestamp, + z interval)""") +except: + conn.rollback() + curs.execute("DROP TABLE test_dt") + curs.execute("""CREATE TABLE test_dt (k int4, + d date, t time, dt timestamp, + z interval)""") +conn.commit() + +# build and insert some data using mx.DateTime +mx1 = ( + 1, + mx.DateTime.Date(2004, 10, 19), + mx.DateTime.Time(0, 11, 17.015), + mx.DateTime.Timestamp(2004, 10, 19, 0, 11, 17.5), + mx.DateTime.DateTimeDelta(13, 15, 17, 59.9)) + +print "Inserting mx.DateTime values..." +curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", mx1) + +# build and insert some values using the datetime adapters +dt1 = ( + 2, + datetime.date(2004, 10, 19), + datetime.time(0, 11, 17, 15000), + datetime.datetime(2004, 10, 19, 0, 11, 17, 500000), + datetime.timedelta(13, 15*3600+17*60+59, 900000)) + +print "Inserting Python datetime values..." +curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", dt1) + +# now extract the row from database and print them +print "Extracting values inserted with mx.DateTime wrappers:" +curs.execute("SELECT d, t, dt, z FROM test_dt WHERE k = 1") +for n, x in zip(mx1[1:], curs.fetchone()): + try: + # this will work only is psycopg has been compiled with datetime + # as the default typecaster for date/time values + s = repr(n) + "\n -> " + repr(x) + "\n -> " + x.isoformat() + except: + s = repr(n) + "\n -> " + repr(x) + "\n -> " + str(x) + print s +print + +print "Extracting values inserted with Python datetime wrappers:" +curs.execute("SELECT d, t, dt, z FROM test_dt WHERE k = 2") +for n, x in zip(dt1[1:], curs.fetchone()): + try: + # this will work only is psycopg has been compiled with datetime + # as the default typecaster for date/time values + s = repr(n) + "\n -> " + repr(x) + "\n -> " + x.isoformat() + except: + s = repr(n) + "\n -> " + repr(x) + "\n -> " + str(x) + print s +print + +curs.execute("DROP TABLE test_dt") +conn.commit() diff --git a/examples/encoding.py b/examples/encoding.py new file mode 100644 index 0000000..1506252 --- /dev/null +++ b/examples/encoding.py @@ -0,0 +1,102 @@ +# encoding.py - how to change client encoding (and test it works) +# -*- encoding: latin-1 -*- +# +# Copyright (C) 2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below this line (except for experimenting) + +import sys, psycopg +import psycopg.extensions + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +print "Initial encoding for this connection is", conn.encoding + +print "\n** This example is supposed to be run in a UNICODE terminal! **\n" + +print "Available encodings:" +for a, b in psycopg.extensions.encodings.items(): + print " ", a, "<->", b + +print "Using STRING typecaster" +print "Setting backend encoding to LATIN1 and executing queries:" +conn.set_client_encoding('LATIN1') +curs = conn.cursor() +curs.execute("SELECT %s::TEXT AS foo", ('אטלעש',)) +x = curs.fetchone()[0] +print " ->", unicode(x, 'latin-1').encode('utf-8'), type(x) +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", unicode(x, 'latin-1').encode('utf-8'), type(x) + +print "Setting backend encoding to UTF8 and executing queries:" +conn.set_client_encoding('UNICODE') +curs = conn.cursor() +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש'.encode('utf-8'),)) +x = curs.fetchone()[0] +print " ->", x, type(x) +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", x, type(x) + +print "Using UNICODE typecaster" +psycopg.extensions.register_type(psycopg.extensions.UNICODE) + +print "Setting backend encoding to LATIN1 and executing queries:" +conn.set_client_encoding('LATIN1') +curs = conn.cursor() +curs.execute("SELECT %s::TEXT AS foo", ('אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) + +print "Setting backend encoding to UTF8 and executing queries:" +conn.set_client_encoding('UNICODE') +curs = conn.cursor() +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש'.encode('utf-8'),)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) +curs.execute("SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) + +print "Executing full UNICODE queries" + +print "Setting backend encoding to LATIN1 and executing queries:" +conn.set_client_encoding('LATIN1') +curs = conn.cursor() +curs.execute(u"SELECT %s::TEXT AS foo", ('אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) +curs.execute(u"SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) + +print "Setting backend encoding to UTF8 and executing queries:" +conn.set_client_encoding('UNICODE') +curs = conn.cursor() +curs.execute(u"SELECT %s::TEXT AS foo", (u'אטלעש'.encode('utf-8'),)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) +curs.execute(u"SELECT %s::TEXT AS foo", (u'אטלעש',)) +x = curs.fetchone()[0] +print " ->", x.encode('utf-8'), ":", type(x) diff --git a/examples/lastrowid.py b/examples/lastrowid.py new file mode 100644 index 0000000..aa57850 --- /dev/null +++ b/examples/lastrowid.py @@ -0,0 +1,59 @@ +# lastrowid.py - example of using .lastrowid attribute +# +# Copyright (C) 2001-2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below tis line (except for experimenting) + +import sys, psycopg + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +curs = conn.cursor() + +try: + curs.execute("CREATE TABLE test_oid (name text, surname text)") +except: + conn.rollback() + curs.execute("DROP TABLE test_oid") + curs.execute("CREATE TABLE test_oid (name text, surname text)") +conn.commit() + +data = ({'name':'Federico', 'surname':'Di Gregorio'}, + {'name':'Pierluigi', 'surname':'Di Nunzio'}) + +curs.execute("""INSERT INTO test_oid + VALUES (%(name)s, %(surname)s)""", data[0]) + +foid = curs.lastrowid +print "Oid for %(name)s %(surname)s" % data[0], "is", foid + +curs.execute("""INSERT INTO test_oid + VALUES (%(name)s, %(surname)s)""", data[1]) +moid = curs.lastrowid +print "Oid for %(name)s %(surname)s" % data[1], "is", moid + +curs.execute("SELECT * FROM test_oid WHERE oid = %d", (foid,)) +print "Oid", foid, "selected %s %s" % curs.fetchone() + +curs.execute("SELECT * FROM test_oid WHERE oid = %d", (moid,)) +print "Oid", moid, "selected %s %s" % curs.fetchone() + +curs.execute("DROP TABLE test_oid") +conn.commit() diff --git a/examples/mogrify.py b/examples/mogrify.py new file mode 100644 index 0000000..ad6d11c --- /dev/null +++ b/examples/mogrify.py @@ -0,0 +1,47 @@ +# mogrify.py - test all possible type mogrifications +# -*- encoding: latin1 -*- +# +# Copyright (C) 2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below this line (except for experimenting) + +import sys, psycopg + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN + +conn = psycopg.connect(DSN) +print "Encoding for this connection is", conn.encoding + +curs = conn.cursor() +curs.execute("SELECT %(foo)s AS foo", {'foo':'bar'}) +curs.execute("SELECT %(foo)s AS foo", {'foo':None}) +curs.execute("SELECT %(foo)s AS foo", {'foo':True}) +curs.execute("SELECT %(foo)f AS foo", {'foo':42}) +curs.execute("SELECT %(foo)s AS foo", {'foo':u'yattא!'}) +curs.execute("SELECT %(foo)s AS foo", {'foo':u'bar'}) + +print curs.mogrify("SELECT %(foo)s AS foo", {'foo':'bar'}) +print curs.mogrify("SELECT %(foo)s AS foo", {'foo':None}) +print curs.mogrify("SELECT %(foo)s AS foo", {'foo':True}) +print curs.mogrify("SELECT %(foo)f AS foo", {'foo':42}) +print curs.mogrify("SELECT %(foo)s AS foo", {'foo':u'yattא!'}) +print curs.mogrify("SELECT %(foo)s AS foo", {'foo':u'bar'}) + +conn.rollback() diff --git a/examples/myfirstrecipe.py b/examples/myfirstrecipe.py new file mode 100644 index 0000000..8457c67 --- /dev/null +++ b/examples/myfirstrecipe.py @@ -0,0 +1,117 @@ +""" +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 psycopg +import psycopg.extensions +from psycopg.extensions import adapt as psycoadapt + +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 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 +psycopg.extensions.adapters[tuple] = SQL_IN +psycopg.extensions.adapters[float] = AsIs +psycopg.extensions.adapters[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)) diff --git a/examples/simple.py b/examples/simple.py new file mode 100644 index 0000000..08a3607 --- /dev/null +++ b/examples/simple.py @@ -0,0 +1,52 @@ +# simple.py - very simple example of plain DBAPI-2.0 usage +# currently used as test-me-stress-me script for psycopg 2.0 +# +# Copyright (C) 2001-2003 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below this line (except for experimenting) + +class SimpleQuoter(object): + def sqlquote(x=None): + return "'bar'" + +import sys, psycopg + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +print "Encoding for this connection is", conn.encoding + +curs = conn.cursor() +curs.execute("SELECT 1 AS foo") +print curs.fetchone() +curs.execute("SELECT 1 AS foo") +print curs.fetchmany() +curs.execute("SELECT 1 AS foo") +print curs.fetchall() + +conn.rollback() + +sys.exit(0) + +curs.execute("SELECT 1 AS foo", async=1) + +curs.execute("SELECT %(foo)s AS foo", {'foo':'bar'}) +curs.execute("SELECT %(foo)s AS foo", {'foo':None}) +curs.execute("SELECT %(foo)f AS foo", {'foo':42}) +curs.execute("SELECT %(foo)s AS foo", {'foo':SimpleQuoter()}) diff --git a/examples/somehackers.jpg b/examples/somehackers.jpg Binary files differnew file mode 100644 index 0000000..8bb6e01 --- /dev/null +++ b/examples/somehackers.jpg diff --git a/examples/threads.py b/examples/threads.py new file mode 100644 index 0000000..ca67104 --- /dev/null +++ b/examples/threads.py @@ -0,0 +1,160 @@ +# threads.py -- example of multiple threads using psycopg +# -*- encoding: latin1 -*- +# +# Copyright (C) 2001-2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## some others parameters +INSERT_THREADS = ('A', 'B', 'C') +SELECT_THREADS = ('1', '2') + +ROWS = 1000 + +COMMIT_STEP = 20 +SELECT_SIZE = 10000 +SELECT_STEP = 500 +SELECT_DIV = 250 + +# the available modes are: +# 0 - one connection for all insert and one for all select threads +# 1 - connections generated using the connection pool + +MODE = 1 + +## don't modify anything below tis line (except for experimenting) + +import sys, psycopg, threading +from psycopg.pool import ThreadedConnectionPool + +if len(sys.argv) > 1: + DSN = sys.argv[1] +if len(sys.argv) > 2: + MODE = int(sys.argv[2]) + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +curs = conn.cursor() + +try: + curs.execute("""CREATE TABLE test_threads ( + name text, value1 int4, value2 float)""") +except: + conn.rollback() + curs.execute("DROP TABLE test_threads") + curs.execute("""CREATE TABLE test_threads ( + name text, value1 int4, value2 float)""") +conn.commit() + + +## this function inserts a big number of rows and creates and destroys +## a large number of cursors + +def insert_func(conn_or_pool, rows): + name = threading.currentThread().getName() + + if MODE == 0: + conn = conn_or_pool + else: + conn = conn_or_pool.getconn() + + for i in range(rows): + if divmod(i, COMMIT_STEP)[1] == 0: + conn.commit() + if MODE == 1: + conn_or_pool.putconn(conn) + s = name + ": COMMIT STEP " + str(i) + print s + if MODE == 1: + conn = conn_or_pool.getconn() + c = conn.cursor() + try: + c.execute("INSERT INTO test_threads VALUES (%s, %d, %f)", + (str(i), i, float(i))) + except psycopg.ProgrammingError, err: + print name, ": an error occurred; skipping this insert" + print err + conn.commit() + +## a nice select function that prints the current number of rows in the +## database (and transefer them, putting some pressure on the network) + +def select_func(conn_or_pool, z): + name = threading.currentThread().getName() + + if MODE == 0: + conn = conn_or_pool + conn.set_isolation_level(0) + + for i in range(SELECT_SIZE): + if divmod(i, SELECT_STEP)[1] == 0: + try: + if MODE == 1: + conn = conn_or_pool.getconn() + conn.set_isolation_level(0) + c = conn.cursor() + c.execute("SELECT * FROM test_threads WHERE value2 < %s", + (int(i/z),)) + l = c.fetchall() + if MODE == 1: + conn_or_pool.putconn(conn) + s = name + ": number of rows fetched: " + str(len(l)) + print s + except psycopg.ProgrammingError, err: + print name, ": an error occurred; skipping this select" + print err + +## create the connection pool or the connections +if MODE == 0: + conn_insert = psycopg.connect(DSN) + conn_select = psycopg.connect(DSN) +else: + m = len(INSERT_THREADS) + len(SELECT_THREADS) + n = m/2 + conn_insert = conn_select = ThreadedConnectionPool(n, m, DSN) + +## create the threads +threads = [] + +print "Creating INSERT threads:" +for name in INSERT_THREADS: + t = threading.Thread(None, insert_func, 'Thread-'+name, + (conn_insert, ROWS)) + t.setDaemon(0) + threads.append(t) + +print "Creating SELECT threads:" +for name in SELECT_THREADS: + t = threading.Thread(None, select_func, 'Thread-'+name, + (conn_select, SELECT_DIV)) + t.setDaemon(0) + threads.append(t) + +## really start the threads now +for t in threads: + t.start() + +# and wait for them to finish +for t in threads: + t.join() + print t.getName(), "exited OK" + + +conn.commit() +curs.execute("SELECT count(name) FROM test_threads") +print "Inserted", curs.fetchone()[0], "rows." + +curs.execute("DROP TABLE test_threads") +conn.commit() diff --git a/examples/tz.py b/examples/tz.py new file mode 100644 index 0000000..ba760f2 --- /dev/null +++ b/examples/tz.py @@ -0,0 +1,62 @@ +# tz.py - example of datetime objects with time zones +# -*- encoding: latin1 -*- +# +# Copyright (C) 2004 Federico Di Gregorio <fog@debian.org> +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by the +# Free Software Foundation; either version 2, or (at your option) any later +# version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY +# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License +# for more details. + +## put in DSN your DSN string + +DSN = 'dbname=test' + +## don't modify anything below this line (except for experimenting) + +import sys, psycopg +import datetime + +from psycopg.tz import ZERO, LOCAL, FixedOffsetTimezone + +if len(sys.argv) > 1: + DSN = sys.argv[1] + +print "Opening connection using dns:", DSN +conn = psycopg.connect(DSN) +curs = conn.cursor() + +try: + curs.execute("CREATE TABLE test_tz (t timestamp with time zone)") +except: + conn.rollback() + curs.execute("DROP TABLE test_tz") + curs.execute("CREATE TABLE test_tz (t timestamp with time zone)") +conn.commit() + +d = datetime.datetime(1971, 10, 19, 22, 30, 0, tzinfo=LOCAL) +curs.execute("INSERT INTO test_tz VALUES (%s)", (d,)) +print "Inserted timestamp with timezone:", d +print "Time zone:", d.tzinfo.tzname(d), "offset:", d.tzinfo.utcoffset(d) + +tz = FixedOffsetTimezone(-5*60, "EST") +d = datetime.datetime(1971, 10, 19, 22, 30, 0, tzinfo=tz) +curs.execute("INSERT INTO test_tz VALUES (%s)", (d,)) +print "Inserted timestamp with timezone:", d +print "Time zone:", d.tzinfo.tzname(d), "offset:", d.tzinfo.utcoffset(d) + +curs.tzinfo_factory = FixedOffsetTimezone +curs.execute("SELECT * FROM test_tz") +for d in curs: + u = d[0].utcoffset() or ZERO + print "UTC time: ", d[0] - u + print "Local time:", d[0] + print "Time zone:", d[0].tzinfo.tzname(d[0]), d[0].tzinfo.utcoffset(d[0]) + +curs.execute("DROP TABLE test_tz") +conn.commit() diff --git a/examples/whereareyou.jpg b/examples/whereareyou.jpg Binary files differnew file mode 100644 index 0000000..f508c0b --- /dev/null +++ b/examples/whereareyou.jpg |
