SQLAlchemy How to map single column of one-to-one relationship using declarative

Question

This is related to this question converting to declarative method and column property, which has never been answered.

We are trying to set up a Flask-SQLAlchemy project on an existing schema (one we cannot change), and decided on the declarative syntax so that we can organize the classes into multiple files in a sane way for maintenance. This works for most of our relationships except for something that we call, for lack of a better term, attribute tables. These are one-to-one leaf tables off some primary object, and typically contain some kind of controlled vocabulary for the attribute. The goal in the ORM is to map all of these (of which there are many) types of tables as if they were properties of the primary table.

Here is an SQA example with two tables:

class MarkerType(db.Model):
    __tablename__="mrk_types"
    _marker_type_key = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String())

class Marker(db.Model):
    __tablename__="mrk_marker"
    _marker_key=db.Column(db.Integer,primary_key=True)
    _marker_type_key=db.Column(db.Integer())

We want to access MarkerType.name as if we were saying Marker.markertype, or in a query as Marker.markertype=='thing'. The only way I could manage that is with a column_property in the Marker class, like so:

markertype = db.column_property(
            db.select([MarkerType.name]).
            where(MarkerType._marker_type_key==_marker_type_key)
    )

However, I cannot seem to find how to do this in a declarative way, and maybe that way does not exist. Is there a sane way I can achieve this without having to worry about my imports, or even worse the order of my classes? Since we have hundreds of tables to map, I can see this being a maintenance nightmare if we have to worry about class and import order.

If all of this is totally impossible, wishful thinking, what is a better approach to mapping these tables?

Answer 1

Use a relationship to allow access to marker_type from the Marker table and specify a ForeignKey constraint so SQLAlchemy understands the relationship between the tables.

This allows you to easily access the MarkerType attribute from a Marker record as well to query against MarkerType.name. The following shows inserting two records and then filtering based on the attribute name.

>>> db.session.add(Marker(marker_type=MarkerType(name="blue")))
>>> db.session.add(Marker(marker_type=MarkerType(name="red")))
>>> db.session.commit()


>>> markers = Marker.query.all()
>>> print({m._marker_key: m.marker_type.name for m in markers})

{1: 'blue', 2: 'red'}

>>> result = Marker.query.filter(Marker._marker_type_key==MarkerType._marker_type_key) \
...                      .filter(MarkerType.name=='blue').all()
>>> print({m._marker_key: m.marker_type.name for m in result})

{1: 'blue'}

The declaration order of the classes does not matter and the classes need not be declared together. Still, the schema must be registered against the same instance of db and when you query against the tables the tables classes you reference will need to be imported.

After adding the relationship and ForeignKey to Marker the declarative schema would become:

class MarkerType(db.Model):
    __tablename__="mrk_types"
    _marker_type_key = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())

class Marker(db.Model):
    __tablename__="mrk_marker"
    _marker_key=db.Column(db.Integer, primary_key=True)
    _marker_type_key=db.Column(db.Integer(), db.ForeignKey('mrk_types._marker_type_key'))
    marker_type=db.relationship('MarkerType')

Answer 2

This sounds like an excellent use case for the Association Proxy. This proxies a field of a related model. In this case the implementation would be:

from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy

class MarkerType(db.Model):
    __tablename__="mrk_types"
    _marker_type_key = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())

class Marker(db.Model):
    __tablename__="mrk_marker"
    _marker_key=db.Column(db.Integer,primary_key=True)
    _marker_type_key=db.Column(db.Integer, ForeignKey('mrk_types._marker_type_key')

    mt = relationship(MarkerType, uselist=False)
    marker_type = association_proxy('mt', 'name')

This allows querying like session.query(Marker).filter_by(marker_type='my_marker_type')

The marker_type field is a proxy of the name field on the MarkerType object. That object can be referenced by the mt field (the relationship field)

Note the uselist=False. This indicates that each Marker has 1 marker type. The relationship automatically detects the ForeignKey and uses it.

Answer 3

So from what I gather, you are stuck with two tables. One with an integer col, one with a string col.

Class Marker
    _marker_key_ primary_key
    # _ = Integer ## This can be ignored as its unnecessary.

The other has a

Class MarkerType
    _marker_type_key = primary_key
    name = String

As I read it, you want Class Marker to have many Class MarkerType strings that you can easily manipulate or call upon. Though, I'm not really sure if that's what you want.

If it is, you can achieve this assuming you control seeding the database. You can build a flag at the start of each name that points to the Markers primary key.

Example: MarkerType.name = 10324_Orange

I am not familiar in using SQLAlchemy without sessions, and don't really feel like doing the research, so I'm just gonna write my answer assuming you are using SQLAlchemy sessions, so you can get the concept and can adjust as needed.

### !!! ASSUME 'create_session' method exists that 
####    creates a sqlalchemy session instance

Class Marker:
    # ... initialize and such
    # ... then add these helper methods

    ## Get all properties linked to this primary table row
    def marker_types():
        return db.query(MarkerType).
            filter(MarkerType.name.like(str(self._marker_key_)+"_%")).all()
    ## Get specific property linked to this primary table row
    def marker_type(marker_type_name):
        db = create_session()
        marker_type_list = db.query(MarkerType).
            filter(MarkerType.name.like(str(self._marker_key_)+"_%")
            AND marker_type_name == MarkerType.name ).first()
        db.close()
        return marker_type_list

    def update_marker_type(old_val, new_val)
        db = create_session()
        updated_marker_type = marker_type(old_val)
        updated_marker_type.name = str(self._marker_key_)+" "+new_val
        db.close()
        return True

    def create_marker_type(val)
        marker_type = MarkerType(name = str(self._marker_key_)+" "+val)
        db = create_session()
        db.add(marker_type)
        db.commit()
        db.close()
        return marker_type._marker_type_key

From here you can add additional flags to the name string. Things like attribute type.

Marker.id = 193

MarkerType.id = 1
MarkerType.name = "193_color_Black"
MarkerType.id = 2
MarkerType.name = "193_style_Fine"

This additional flag can allow you to search for general attributes specific names linked to your specific row, and is vastly more useable, though slightly more complicated. Really depends on your use case.