PostgreSQL Multidimensional arrays in SQLAlchemy
Faheem Mitha
Hi,
I posted
http://stackoverflow.com/questions/9729175/multidimensional-arrays-in-sqlalchemy
to StackOverflow. Reproduced below. Please CC me on any reply. Thanks.
Regards, Faheem
################################################################################
I'm using SQLAlchemy 0.6.3 with PostgreSQL 8.4 on Debian squeeze. I want a
table where one column stores something in PostgreSQL that shows up in
Python as a list of integer lists or tuples of integer tuples. E.g.
((1,2), (3,4), (5,6,7))
In the example below the column is `model`. I thought that a reasonable
approach might be to store stuff as an PG 2 dimensional table, which in PG
looks like `integer[][]`. I don't know in what form SQLA will return this
to Python, but I'm hoping it is something like a tuple of tuples.
However, I can't figure out how to tell SQLA to give me a two dimensional
Integer array. The
[documentation](http://docs.sqlalchemy.org/en/rel_0_6/dialects/postgresql.html#postgresql-data-types)
for `sqlalchemy.dialects.postgresql.ARRAY` says
> item_type – The data type of items of this array. Note that
> dimensionality is irrelevant here, so multi-dimensional arrays like
> INTEGER[][], are constructed as ARRAY(Integer), not as
> ARRAY(ARRAY(Integer)) or such. The type mapping figures out on the fly.
Unfortunately, I have no idea what that means. How can the type mapping
figure this out on the fly? It needs to create the correct DDL.
My first and only guess for how to do this would have been
`ARRAY(ARRAY(Integer))`. Currently I have
crossval_table = Table(
name, meta,
Column('id', Integer, primary_key=True),
Column('created', TIMESTAMP(), default=now()),
Column('sample', postgresql.ARRAY(Integer)),
Column('model', postgresql.ARRAY(Integer)),
Column('time', Float),
schema = schema,
This creates the following DDL
CREATE TABLE crossval (
id integer NOT NULL,
created timestamp without time zone,
sample integer[],
model integer[],
"time" double precision
);
which isn't right, of course. What am I missing?
######################################################################
Michael Bayer
it's probably not a supported use case at the moment. There's an old ticket, still open, that's lost interest on fixing up ARRAY, http://www.sqlalchemy.org/trac/ticket/1591 . I've never been involved in the ARRAY implementation directly so it seems like the API decisions made thus far haven't really addressed the full capability of ARRAY.
You might want to try a hack like this:
class TwoDArray(Array):
pass
from sqlalchemy.ext.compiler import compiles
@compiles(TwoDArray)
def arr(element, compiler, **kw):
return "INTEGER[][]"
a quick^H^H^H^H comprehensive patch on our end would be to at least add a parameter "dimension" to help with this. I've added http://www.sqlalchemy.org/trac/ticket/2441 for this.
>
> ######################################################################
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Faheem Mitha
On Fri, 16 Mar 2012 10:26:54 -0700, Michael Bayer <mik...@zzzcomputing.com> wrote:
> it's probably not a supported use case at the moment. There's an
> old ticket, still open, that's lost interest on fixing up ARRAY,
> http://www.sqlalchemy.org/trac/ticket/1591 . I've never been
> involved in the ARRAY implementation directly so it seems like the
> API decisions made thus far haven't really addressed the full
> capability of ARRAY.
> You might want to try a hack like this:
> class TwoDArray(Array):
> pass
>
> from sqlalchemy.ext.compiler import compiles
> @compiles(TwoDArray)
> def arr(element, compiler, **kw):
> return "INTEGER[][]"
> a quick^H^H^H^H comprehensive patch on our end would be to at least
> add a parameter "dimension" to help with this. I've added
> http://www.sqlalchemy.org/trac/ticket/2441 for this.
Thanks for the reply. I'll try the hack. How do I use this? Does the
usage look like
Column('model', TwoDArray)
or something else?
I assume this just emits the appropriate DDL. The really important
question is how to get support from the ORM. I'd like to pass a tuple
of tuples as an argument to the model arg in the corresponding class
(see mapper below), and get a tuple of tuples back to python from PG
the other way.
Would I need to add further logic to the TwoDArray class to do this?
class Crossval(object):
def __init__(self, sample, model, time=None):
self.sample = sample
self.model = model
self.time = time def
__repr__(self):
return '<Crossval %s>'%self.id
crossval_table = Table(
name, meta,
Column('id', Integer, primary_key=True),
Column('created', TIMESTAMP(), default=now()),
Column('sample', postgresql.ARRAY(Integer)),
Column('model', postgresql.ARRAY(Integer)),
Column('time', Float),
schema = schema,
)
mapper(Crossval, crossval_table)
Regards, Faheem
Michael Bayer
Thanks for the reply. I'll try the hack. How do I use this? Does the
usage look like
Faheem Mitha
> On Mar 16, 2012, at 1:51 PM, Faheem Mitha wrote:
>> Thanks for the reply. I'll try the hack. How do I use this? Does
>> the usage look like
> disregard the hack; the ARRAY type works as is (though I'd still
> like to change it for 0.8). The ARRAY type is multidimensional
> regardless of how you create it:
> http://www.postgresql.org/docs/9.1/static/arrays.html
> "The current implementation does not enforce the declared number of dimensi=
> ons either. Arrays of a particular element type are all considered to be of=
> the same type, regardless of size or number of dimensions. So, declaring t=
> he array size or number of dimensions in CREATE TABLE is simply documentati=
> on; it does not affect run-time behavior."
>
> so just use the ARRAY normally, it will accept data of any dimension regard=
> less of how it's created.
Hi Mike,
Thanks for the explanation. I looked at that page, of course, but it
failed to register.
So, what do you plan to change in SQLAlchemy? Even though PG does not
enforce the array type, there is no reason SQLA could not. I.e. if one
tried to pass an array value of the wrong dimensions, it would reject
it.
BTW, I was wondering why you didn't respond directly to the SO
question. I've seen you respond to SO SQLA questions there
before. That way I'd have a canonical answer to accept. :-) I'll add a
link to this thread to the question, as well as the issue you opened.
Regards, Faheem
Michael Bayer
On Mar 19, 2012, at 4:06 AM, Faheem Mitha wrote:
>
> Hi Mike,
>
> Thanks for the explanation. I looked at that page, of course, but it
> failed to register.
>
> So, what do you plan to change in SQLAlchemy? Even though PG does not
> enforce the array type, there is no reason SQLA could not. I.e. if one
> tried to pass an array value of the wrong dimensions, it would reject
> it.
I'll be adding a "dimension" attribute in a future release, maybe 0.8. http://www.sqlalchemy.org/trac/ticket/2441 has the patch.
>
> BTW, I was wondering why you didn't respond directly to the SO
> question. I've seen you respond to SO SQLA questions there
> before.
i answer the ones that come up on twitter and otherwise don't dig in much unless I see a lot going unanswered.