SQLAlchemy composite type

Question

Does SQLAlchemy support PostgreSQL's composite types (http://www.postgresql.org/docs/devel/static/rowtypes.html)? I found it has something similar (sqlalchemy.orm.composite), but as far as I can see, it's not good enough for my purpose.

I'd like to make a separate type, for example Amount having value and currency, and then be able to refer to it repeatedly, and possibly multiple times from the same class:

class Transfer(base):
    __tablename__ = "transfer"
    source_amount = Column(Amount)
    target_amount = Column(Amount)

All the examples I've seen (for example this one) require me to specify the names of the columns, which makes such approach as above impossible (column names would clash).

Besides, I'd rather have one column per Amount instead of two (since value doesn't really make much sense without currency, I'd rather the database take care of never separating or mixing them). It seems Postgres can do that, I just need a way to explain to SQLAlchemy what I need. I tried reading http://docs.sqlalchemy.org/en/latest/core/custom_types.html, but it seems to focus on augmenting existing types, not creating new ones as composites (I don't know what to put under impl).

Maybe I should let go of SQLAlchemy and communicate with psycopg2 directly? It has a psycopg2.extras.register_composite function, but once I do that, I still don't know how to convince SQLAlchemy to recognize the registration. I would probably have to somehow pass the cursor from psycopg2 to SQLAlchemy, but I don't know how to do that.

Answer 1

SQLalchemy does not support postgresql composite types natively.

However, sqlalchemy_utils supports postgresql composite types natively.

Here's an example from their documentation:

from collections import OrderedDict
    
import sqlalchemy as sa
from sqlalchemy_utils import CompositeType, CurrencyType

class Account(Base):
    __tablename__ = 'account'
    id = sa.Column(sa.Integer, primary_key=True)
    balance = sa.Column(
        CompositeType(
            'money_type',
            [
                sa.Column('currency', CurrencyType),
                sa.Column('amount', sa.Integer)
            ]
        )
    )

Usage:

session.query(Account).filter(Account.balance.amount > 5000)