Using a tuples of values in sqlalchemy mapped collection

Question

In a many-to-many relationship, I have some extra data on the association table to describe the relationship (a quantity and a boolean value). I'd like to use a mapped collection to avoid working directly with the association objects, but I can't figure out how to use a tuple for the values in the mapping. As far as I can tell, Attribute as dict of lists using middleman table with SQLAlchemy is similar, but backwards.

To illustrate this, I want to do something like this:

>>> collection.items[item] = (3, True)
>>> collection.items[item] = (1, False)
>>> colletion.items
{"item name": (3, True), "item name": (1, False)}

This... works... but eventually SQLAlchemy tries to put the tuple into the database (I'll try to recreate that in a bit).

I have also tried using tuples in the key (the related object and one of the other columns), but it looks terrible, and it doesn't work:

>>> collection.items[item, True]  = 3
>>> collection.items[item, False] = 1
>>> collection.items
{(<item>, True): 3, (<item>, False): 1}

I can put the item name and one value in the mapped collection without issue: I had another (structurally identical) form of this relationship which I solved by making two relationships (and association proxies) that divided the association table between them based on the boolean value, and their creator functions set the boolean correctly without any further interference. Unfortunately, in that case the boolean specified a minor semantic difference (application code needs to treat the items as a group), while in the current problem it is a not insignificant cosmetic difference (application code should not treat the items as groups, but the value does alter how the item is displayed and so is needed).

Answer 1

the linked answer has all the components. attribute_mapped_collection and association_proxy can do lots together. First here is the dictionary of string->tuple(int, boolean) (updated for m2m):

from sqlalchemy import Integer, Boolean, String, Column, create_engine, \
    ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'sometable'

    id = Column(Integer, primary_key=True)
    tuple_elements = relationship(
                "TupleAssociation", 
                collection_class=attribute_mapped_collection("name"),
                cascade="all, delete-orphan"
            )
    items = association_proxy("tuple_elements", "as_tuple")

class TupleAssociation(Base):
    __tablename__ = 'tuple_association'
    parent_id = Column(Integer, ForeignKey('sometable.id'), primary_key=True)
    tuple_id = Column(Integer, ForeignKey("tuple_data.id"), primary_key=True)
    name = Column(String)

    tuple_element = relationship("TupleElement")

    def __init__(self, key, tup):
        self.name = key
        self.tuple_element = TupleElement(tup)

    @property
    def as_tuple(self):
        return self.tuple_element.as_tuple

class TupleElement(Base):
    __tablename__ = 'tuple_data'

    id = Column(Integer, primary_key=True)
    col1 = Column(Integer)
    col2 = Column(Boolean)

    def __init__(self, tup):
        self.col1, self.col2 = tup

    @property
    def as_tuple(self):
        return self.col1, self.col2


e = create_engine('sqlite://')
Base.metadata.create_all(e)
s = Session(e)

collection = SomeClass()
collection.items["item name 1"] = (3, True)
collection.items["item name 2"] = (1, False)
print collection.items

s.add(collection)
s.commit()

collection = s.query(SomeClass).first()
print collection.items

Here it is the other way around with the tuples on the association and the name on the endpoint:

from sqlalchemy import Integer, Boolean, String, Column, create_engine, \
    ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'sometable'

    id = Column(Integer, primary_key=True)
    tuple_elements = relationship(
                "TupleAssociation", 
                collection_class=attribute_mapped_collection("name"),
                cascade="all, delete-orphan"
            )
    items = association_proxy("tuple_elements", "as_tuple")

class TupleAssociation(Base):
    __tablename__ = 'tuple_association'
    parent_id = Column(Integer, ForeignKey('sometable.id'), primary_key=True)
    name_id = Column(Integer, ForeignKey("name_data.id"), primary_key=True)

    col1 = Column(Integer)
    col2 = Column(Boolean)

    name_element = relationship("NameElement")

    def __init__(self, key, tup):
        self.name_element = NameElement(name=key)
        self.col1, self.col2 = tup

    @property
    def name(self):
        return self.name_element.name

    @property
    def as_tuple(self):
        return self.col1, self.col2

class NameElement(Base):
    __tablename__ = 'name_data'

    id = Column(Integer, primary_key=True)
    name = Column(String)


e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)

collection = SomeClass()
collection.items["item name 1"] = (3, True)
collection.items["item name 2"] = (1, False)
print collection.items

s.add(collection)
s.commit()

collection = s.query(SomeClass).first()
print collection.items

that's probably all you need. If you're using Postgresql, which supports SQL tuples, you can add more to the above using hybrids plus tuple_(), so that as_tuple can be used at the SQL level also (below also uses one-to-many instead of association object just for example):

from sqlalchemy import Integer, Boolean, String, Column, create_engine, \
    ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext import hybrid
from sqlalchemy.sql import tuple_

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'sometable'

    id = Column(Integer, primary_key=True)
    tuple_elements = relationship(
                "TupleElement", 
                collection_class=attribute_mapped_collection("name"),
                cascade="all, delete-orphan"
            )
    items = association_proxy("tuple_elements", "as_tuple")

class TupleElement(Base):
    __tablename__ = 'tuple_data'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('sometable.id'), nullable=False)
    name = Column(String)
    col1 = Column(Integer)
    col2 = Column(Boolean)

    def __init__(self, key, tup):
        self.name = key
        self.col1, self.col2 = tup

    @hybrid.hybrid_property
    def as_tuple(self):
        return self.col1, self.col2

    @as_tuple.expression
    def as_tuple(self):
        return tuple_(self.col1, self.col2)

e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)

collection = SomeClass()
collection.items["item name 1"] = (3, True)
collection.items["item name 2"] = (1, False)
print collection.items

s.add(collection)
s.commit()

q = s.query(SomeClass).join(SomeClass.tuple_elements)
assert q.filter(TupleElement.as_tuple == (3, True)).first() is collection
assert q.filter(TupleElement.as_tuple == (5, False)).first() is None
print s.query(TupleElement.as_tuple).all()