Motivation

Take a trivial example where a column of data needs to be represented by an enumerated type in SQL:

+------------------------------------------+
| user                                     |
+----+------+-----+------------------------+
| id | name | age | relationship_status_id |
+----+------+-----+------------------------+
| 1  | John | 27  | 3                      |
| 2  | Mary | 77  | 1                      |
| 3  | Jack | 40  | 4                      |
+----+------+-----+------------------------+

+---------------------+
| relationship_status |
+----+----------------+
| id | name           |
+----+----------------+
| 1  | married        |
| 2  | widowed        |
| 3  | single         |
| 4  | divorced       |
+----+----------------+

Defining (declaring) the tables themselves in SQLAlchemy is relatively straightforward:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    relationship_status_id = Column(Integer, ForeignKey('relationship_status.id'))


class RelationshipStatus(Base):
    __tablename__ = 'relationship_status'

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

When initializing the database, tables can be created with a Base.metadata.create_all(engine) directive. The user table would be filled during the application's running lifetime; however, the relationship_status lookup table's data remains constant, and it seems appropriate to "declare" this data along with the table definition.

Yet persisting data to a table naturally requires a session, and unlike the table definitions themselves, SQLAlchemy does not seem to offer any declarative construct for "expected rows" in a given table (naturally, since the majority of tables in any application are like user with dynamic data).

Problem

Using SQLAlchemy, how can one declare both the schema and data of a lookup table before application runtime? Ideally, the solution would involve the creation of some Enum-like construct containing the data that other parts of the application could reference.

Research

The creator of SQLAlchemy proposed the enum recipe. The only apparent downside of such a solution is that one must rely on the enum datatype within the DBMS being used. For the scope of this question, a DBMS-independent lookup table solution is preferred.

A related alternative also proposed by SQLAlchemy's creator is the unique object recipe. Such an implementation ensures that the rows returned by lookup table queries remain without duplicates, but a session object is still required to make any declarations or requests--blurring the separation of concerns between database definition and implementation. Furthermore, clients would all simply need to "just know" what rows to ask for rather than having some sort of enum (within Python) for reference.

The root of the issue may be conceptual rather than tied to SQLAlchemy or Python. In either case, any advice would be much appreciated.