SQLAlchemy default PostgreSQL ARRAY behavior

Question

I have a table I am creating and interacting with using the SQLAlchemy expression engine and I am needing to be able to fetch the values from the ARRAY column as a python tuple not a list. Since the constructor for ARRAY columns allows you to specify if you want the values as tuples, I ended up just reflecting the table out of the database, looking for ARRAY columns, replacing them with an ARRAY column that has the appropriate keyword argument and then using that to construct expressions. Like so:

from sqlalchemy import Table, Column, String, MetaData, select

def swapArrayColumns(table):
    new_cols = []
    for col in table.columns:
        if isinstance(col.type, ARRAY):
            new_cols.append(Column(col.name, ARRAY(String, as_tuple=True)))
        else:
        new_cols.append(Column(col.name, col.type))
    return new_cols

engine = create_engine('postgresql://localhost:5432/dbname')    
meta = MetaData()
table = Table('table_name', meta, autoload=True, autoload_with=engine)
new_cols = swapArrayColumns(table)

# delete old meta since we are re-mapping table
del meta
meta = MetaData()
new_table = Table('table_name', meta, *new_cols)

# Now I can use the new table object to generate expressions
sel = select([new_table]).where(new_table.c.foo == 'bar')
rows = list(engine.execute(sel))

Since this gets used quite a lot, I'm wondering if there's a more elegant way of accomplishing this same thing perhaps by creating a custom sqlalchemy dialect that just does this for me. All I really want is my ARRAY columns to come back as python tuples by default.

reptilicus · Accepted Answer · 2015-03-24 21:18:57Z

1

could look at using a custom sqlalchemy type, something like this:

from sqlalchemy.dialects.postgresql import ARRAY,
class Tuple(types.TypeDecorator):
    impl = ARRAY

    def process_bind_param(self, value, dialect):
        return list(value)

    def process_result_value(self, value, dialect):
        return tuple(value)

and in the model itself (using declarative):

class MyModel(Base):
    id = Column(Integer, primary_key=True)
    data = Column(Tuple)

Another way perhaps would be to sub-class ARRAY:

from  sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY
class ARRAY(PG_ARRAY):
    def __init__(self, *args, **kwargs):
        super(PG_ARRAY, self ).__init__(*args, *kwargs)
        self.as_tuple = True

edited Mar 24, 2015 at 21:18

answered Mar 24, 2015 at 20:23

reptilicus

10.4k6 gold badges59 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eric van Zanten Over a year ago

I guess the problem is that the tables are dynamically created, (sometimes with array columns, sometimes without) and, because of that, I'm not using the ORM parts of SQLAlchemy, just the table reflection and SQL expression generator parts. The ideal would just be to override this when I create the engine that I use to connect to the database (so that every ARRAY I fetched from the database was a turned into a Python tuple), that's why I was thinking the Dialect approach might work.

Eric van Zanten Over a year ago

Great answer, though. Just for a slightly different problem ;P

reptilicus Over a year ago

sub-classing array might work, check the updated answer. Havent tested it though.

Eric van Zanten Over a year ago

How would I use that? Is it possible to register that as a global thing that the postgresql dialect will use by default?

Collectives™ on Stack Overflow

SQLAlchemy default PostgreSQL ARRAY behavior

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related