pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPython DB-API problem with PostgresSQL pgdb.
2+ messages / 2 participants
[nested] [flat]
* Python DB-API problem with PostgresSQL pgdb.
@ 2003-01-27 05:06 Carwyn Edwards <cedward1@inf.ed.ac.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Carwyn Edwards @ 2003-01-27 05:06 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: andre@via.ecp.fr; pgsql-admin@postgresql.org
The Python DB-API (2.0) specifies that on creation a connection object
is to start a transaction if the database supports them. The pgdb
implementation of the DB-API does this:
>>> Begin Listing 1
class pgdbCnx:
def __init__(self, cnx):
self.__cnx = cnx
self.__cache = pgdbTypeCache(cnx)
try:
src = self.__cnx.source()
src.execute("BEGIN")
except:
raise OperationalError, "invalid connection."
>>> End Listing 1
This is all well and good except there does not seem to be any way of
NOT starting a transaction block. This is a real problem if you are
trying to create admin scripts that automate database creation as CREATE
DATABASE in PostgreSQL can not be called from within a transaction
block. Listing 2 shows the raw SQL typed into psql that simulates what
happens from the pgdb module when you try and create a database:
>>> Begin Listing 2
cedward1=# begin;
BEGIN
cedward1=# create database thingy;
ERROR: CREATE DATABASE: may not be called in a transaction block
>>> End Listing 2
I'll let the experts argue as to whether this is a problem for Python's
DB-API or a PostgreSQL issue.
Carwyn
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Python DB-API problem with PostgresSQL pgdb.
@ 2003-01-28 09:00 Gerhard Häring <haering_postgresql@gmx.de>
0 siblings, 0 replies; 2+ messages in thread
From: Gerhard Häring @ 2003-01-28 09:00 UTC (permalink / raw)
To: pgsql-interfaces
Carwyn Edwards <cedward1@inf.ed.ac.uk> wrote:
> The Python DB-API (2.0) specifies that on creation a connection object
> is to start a transaction if the database supports them. The pgdb
> implementation of the DB-API does this:
>
> >>> Begin Listing 1
> class pgdbCnx:
>
> def __init__(self, cnx):
> self.__cnx = cnx
> self.__cache = pgdbTypeCache(cnx)
> try:
> src = self.__cnx.source()
> src.execute("BEGIN")
> except:
> raise OperationalError, "invalid connection."
>
> >>> End Listing 1
pyPgSQL (and psycopg, IIRC) solves this problem by having a .autocommit
connection level setting. Just do con.autocommit = 1 before issuing any
statements that PostgreSQL cannot execute within a transaction.
I'd suggest you simply switch to one of these Python DB-API modules. Apart from
the .connect and import call you very likely won't have to change *anything* in
your code.
Btw. if anybody wants to patch PyGreSQL accordingly, they're welcome to help
themselves with the pyPgSQL source, as it's under a MIT-like license as well.
Btw. IIRC there are a few other DB-API modules that implement .autocommit as
well. Maybe one day this will make it into the DB-API optional extensions.
-- Gerhard
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-01-28 09:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-27 05:06 Python DB-API problem with PostgresSQL pgdb. Carwyn Edwards <cedward1@inf.ed.ac.uk>
2003-01-28 09:00 Re: Python DB-API problem with PostgresSQL pgdb. Gerhard Häring <haering_postgresql@gmx.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox