[DB-SIG] db paramstyle for arrays

[Anthony Tuininga]

On Wed, 2004-04-07 at 03:11, M.-A. Lemburg wrote:
> Lee Harr wrote:
> >> LH> Is there a special DB API style for inserting array values
> >> LH> in
> >> LH> to a database?
> >>
> >> What DB API module do you use?  For pyPgSQL the answer is in FAQ
> >> (http://pypgsql.sourceforge.net/pypgsql-faq.html):
> >> If you want to use ARRAYs, you'll now need to wrap your (nested)
> >> lists with PgSQL.PgArray
> >>
> > 
> > I am using psycopg.
> > 
> > grep'ng through the psycopg distribution does not reveal
> > mention of ARRAY, so I guess it does not support that.
> > 
> > Is there anything about it in the DB API? I looked through pep 249
> > but I do not see anything. I could create a patch
> > for psycopg to do something like the way pyPgSQL does it, but
> > it would be good if they were all the same...
> 
> The DB API supports doing bulk inserts via .executemany(),
> but I'm not sure whether the same thing as inserting ARRAYs
> which seem to be a postgresql special data type from your
> description.

Oracle supports arrays as well as named types -- neither of which are
covered by the DB API. I've had to break new ground here with cx_Oracle
but it would be nice to get the opinions of other database module
authors.

Binding arrays is done using standard Python sequences -- nothing
controversial there, I would guess. I've had to "guess" the data type
from the type of data in the array which of course means that problems
occur when there are no elements in the array or when the elements in
the array consist only of nulls. That leads to the next step, which is
telling cx_Oracle what kind of array you would like to bind. Currently,
the DB API specifies

integer -- a string with maximum length equal to the integer value
type object -- an object of the type given

I had to extend it to include a two-tuple of the data type and the
number of elements in the array.

I don't believe anything different is required to support arrays. I
haven't dealt with named types yet so no comments there. In any case,
arrays are definitely not PostgreSQL specific.

-- 
Anthony Tuininga
anthony at computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com