How is lookup table data declared and initialized in SQLAlchemy?

Question

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.

Answer 1

First, I'd argue that in most situations, data that is constant and known at application development time is not generally a good fit for storing in a database. I'd use either a DBMS-based enumeration or a Python-based enumeration and a check constraint to guarantee that each row in the users table had a valid relationship status. Since you said you're not going to do that, it sounds like you're looking for a way to trigger some inserts at the time that your relationship_status table is created. I've adapted the after_create example to insert something into a table instead of altering the table. You should be able to adapt this to go insert your relationship status values.

from sqlalchemy import event
from sqlalchemy import Table, Column, Metadata, Integer

m = MetaData()
some_table = Table('some_table', m, Column('data', Integer))

def after_create(target, connection, **kw):
    connection.execute("insert into  %s values ('1','single');" %
                            (target.name))

event.listen(some_table, "after_create", after_create)