[PyGreSQL] DB API-2.0 + copy from stdin

[Christopher Sean Hilton]

On Sep 24, 2008, at 4:24 AM, Christoph Zwerschke wrote:

> I think that's a reasonable suggestion. If I understand you right,  
> then
> all we need to do is:
>
> 1) Add a parameter "db" to the pg.DB class. If it is set to None (the
> default), then a new connection will be established, otherwise the  
> given
> connection will be used. (I suggest the name "db" instead of "cnx"
> because the name of the corresponding attribute is already "db".)
>
> 2) Add an attribute "_closeable" to pg.DB which will be set when the
> instance has not been created with a preestablihed connection. The
> close() and reopen() methods of pg.DB will then raise an exception  
> when
> _closeable is false.
>
> 3) Add a pdb.pgdbCnx method for getting a classic pg.DB instance.
> Maybe we don't even need that, since getting it is as easy as
> DB(db=con._cnx) when con is a pgdb connection. So the pgdb module  
> would
> normally not need to import the pg module, and it would be clearer  
> that
> you're leaving the DB-API2 world if you're using this feature.
>
> An additional suggestion from my site is making the (pseudo) private
> attributes of pg.DB public, as we did with the private attributes in
> pgdb. A single underscore should suffice to indicate that these should
> not be used from outside unless really necessary as in cases like  
> this.
>

I'm attaching a patch that implements 1 and 2 on the latest version of  
pg.py that I could get from the CVS web front end. For 3 I'm tempted  
to implement a method anyhow for exactly the reason that you said,  
people should be very clear about which interface that they are using.  
My style is to use DB-API wherever possible and use the older  
interfaces only where SQL doesn't properly specify tools for an  
application's needs (e.g. PostgreSQL's COPY table... or MySQL's LOAD  
DATA INFILE..).

It looks like the original code used TABs for indenting. I preserved  
that but to do it I had to untab and then retab the whole thing in  
emacs. That produced a harmless?  whitespace difference in the new  
Decimal code as well as a couple of similarly harmless changes in some  
of the doc strings.

The new usage example looks like this.

#! /usr/bin/env python

"""Example code to that primarily uses a DB-API 2.0 interface to  
postgresql
    but drops town to the classic interface to load a temporary table.  
This is
    useful when you have a lot of data to load and multiple INSERTS  
are too slow."""

import pg, pgdb

dbApiDb = pgdb.connect('message_board')
myCursor = dbApiDb.cursor()
myCursor.execute("CREATE TEMPORARY TABLE new_author(..."))

classicDb = pg.DB(dbApiDb._cnx)
classicDb.query("COPY new_author FROM stdin")
for row in data:
     classicDb.putline("\t".join(row) + "\n")
classicDb.putline("\\.\n")
classicDb.endcopy()

del classicDb
...
del myCursor
dbApiDb.commit()
dbApiDb.close()


-------------- next part --------------
A non-text attachment was scrubbed...
Name: pg.py.patch
Type: application/octet-stream
Size: 4912 bytes
Desc: not available
Url : http://mailman.vex.net/pipermail/pygresql/attachments/20080924/24eb069a/attachment.obj 
-------------- next part --------------



Chris Hilton                                   e: chris|at|vindaloo| 
dot|com
----------------------------------------------------------------------------
                   "The pattern juggler lifts his hand; The orchestra  
begin.
       As slowly turns the grinding wheel in the court of the crimson  
king."
                                            -- Ian McDonald / Peter  
Sinfield