current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

share|improve this question

This question has an open bounty worth +50 reputation from fgregg ending in 2 days.

This question has not received enough attention.

1 Answer 1

up vote 1 down vote

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
share|improve this answer
    
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 Mar 24 at 20:55
1  
ahhh, I got ya. hmm, I'll have a think on it... –  reptilicus Mar 24 at 20:58
    
Great answer, though. Just for a slightly different problem ;P –  Eric van Zanten Mar 24 at 21:07
    
sub-classing array might work, check the updated answer. Havent tested it though. –  reptilicus Mar 24 at 21:21
    
How would I use that? Is it possible to register that as a global thing that the postgresql dialect will use by default? –  Eric van Zanten Mar 24 at 21:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.