How to map multiple classes to single table with SQLAlchemy

Question

I have a domain that defines two objects: Property and Address. They have a one-to-one relationship where every Property has an Address.

I would like to have a single table that holds the Property and Address data in order to avoid a unnecessary join when queries Properties:

create table properties
(
id integer primary key,
name varchar(50) not null,
address_city varchar(50) not null
)

And have the bellow classes definitions:

class Property(object):
    def __init__(self, id, name, address):
        self.id = id
        self.name = name
        self.address = address

class Address(object):
    def __init__(self, city):
        self.city = city

How can I configure the SQLAlchemy mappers to support this kind of structure? (I saw inheritance configurations but it doesn't fit my case)

Why do you think that the join is unnecessary? I think that's the best way to do it. — Alex
– Alex, Commented Jan 2, 2018 at 18:00
@Alex because it is a one-to-one must have relationship so avoid the join will make queries faster. — Nykolas Lima
– Nykolas Lima, Commented Jan 3, 2018 at 11:54

Ilja Everilä · Accepted Answer · 2018-01-03 12:08:14Z

Though in general I'd vote for creating separate property and address relations, with suitable foreign key constraints, you can map your classes to a single table using a composite column type:

In [2]: class Address:
   ...:     def __init__(self, city):
   ...:         self.city = city
   ...:     def __composite_values__(self):
   ...:         # NOTE: returns a 1-tuple
   ...:         return self.city,
   ...:     def __repr__(self):
   ...:         return "Address(city={!r})".format(self.city)
   ...:     def __eq__(self, other):
   ...:         return isinstance(other, Address) and self.city == other.city
   ...:     def __ne__(self, other):
   ...:         return not self.__eq__(other)

In [3]: class Property(Base):
   ...:     __tablename__ = "properties"
   ...:     id = Column(Integer, primary_key=True)
   ...:     name = Column(Unicode(50), nullable=False)
   ...:     address_city = Column(Unicode(50), nullable=False)
   ...:     address = composite(Address, address_city)
   ...: 
   ...:     def __init__(self, name, address):
   ...:         self.name = name
   ...:         self.address = address

With the above type and model you can then create new properties:

In [5]: session.add(Property('Ye Olde House', Address('London')))

In [6]: session.commit()

and query:

In [7]: session.query(Property).\
   ...:     filter_by(address=Address('London')).\
   ...:     first()
Out[7]: <__main__.Property at 0x7f02dd8892e8>

In [8]: _.address.city
Out[8]: 'London'

In the coming Python 3.7 that implements PEP 557 – Data Classes the Address class could be defined as:

from dataclasses import dataclass, astuple

@dataclass
class Address:
    city: str

    def __composite_values__(self):
        return astuple(self)

Thank you very much for your answer, it's exactly what I needed.

Collectives™ on Stack Overflow

How to map multiple classes to single table with SQLAlchemy

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related