PostgreSQL multidimensional arrays in SQLAlchemy

Question

I am using SQLAlchemy 0.6.3 with PostgreSQL 8.4 when compressing Debian. I want a table in which a single column stores something in PostgreSQL, which is displayed in Python as a list of whole lists or tuples of whole tuples. For example.

((1,2), (3,4), (5,6,7)) 

In the example below, this column is model . I thought a reasonable approach could be to store the material as a PG 2 size table, which in PG looks like integer[][] . I don't know in what form SQLA will return this in Python, but I hope this is something like a tuple of tuples.

However, I cannot figure out how to say SQLA to give me a two-dimensional Integer array. documentation for sqlalchemy.dialects.postgresql.ARRAY says

item_type - The data type of the elements of this array. Note that dimension does not matter here, so multidimensional arrays like INTEGER [] [] are built as ARRAY (Integer), not as ARRAY (ARRAY (integer)) or such. The type display is displayed on the fly.

Unfortunately, I have no idea what that means. How can display type be displayed on the fly? It must create the correct DDL. My first and only guess on how to do this would be ARRAY(ARRAY(Integer)) . I am currently

  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 next DDL

 CREATE TABLE crossval ( id integer NOT NULL, created timestamp without time zone, sample integer[], model integer[], "time" double precision ); 

which is wrong, of course. What am I missing?

Answer 1

I answer this here since Mike Bayer answered this question on sqlalchemy users.

See thread on sqlalchemy user where Mike Bayer answers this question. As Mike explained, and as I skipped reading the PG documentation, PG does not actually use array sizes, nor SQLA. Thus, you can write integer[][] , but PG does not treat it differently than integer[] . In particular, both PG and SQLA will accept an array expression of any dimension. I do not know why this is so. As Mike quotes, PG Arrays documentation says

The current implementation does not apply the declared number of sizes either. Arrays of a certain type of element - all are considered to be of the same type, regardless of size or quantity. Overall dimensions. So, declaring an array size or number of dimensions in a CREATE TABLE is just documentation; This does not affect runtime behavior.

See also the ticket that he opened. This seems to be necessary for sizing at the SQLA level.

Answer 2

I tried this

 from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker engine = create_engine('postgresql://:5432/test', echo=True) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects import postgresql Base = declarative_base() from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) sample = Column(postgresql.ARRAY(Integer)) Base.metadata.create_all(engine) Session = sessionmaker(engine) s = Session() a = User() a.name='test' a.sample = [[1,2], [3,4]] s.add(a) s.commit() 

I think this will solve your problem. Because in the doc they mention.

However, the current implementation ignores any given array size limits, i.e. the behavior is the same as for arrays of indefinite length.

So, if you are not declaring anything, this will be the array you want.