CODEWITHSUNDEEP
pythonarrayspostgresqlsqlalchemy
Eric van Zanten
Eric van Zanten

Reputation: 95

SQLAlchemy default PostgreSQL ARRAY behavior

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.

Upvotes: 4

Views: 1762

Answers (1)

reptilicus
reptilicus

Reputation: 10397

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

Upvotes: 1

Related Questions