How is lookup table data declared and initialized in SQLAlchemy?
Motivation
Let's take a trivial example where a data column needs to be 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 simple:
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 the database is initialized, tables can be created using the directive Base.metadata.create_all(engine)
. The table user
will be filled in while the application is running; however, the lookup table data relationship_status
remains constant and it seems appropriate to "declare" this data along with the table definition.
However, persisting data in a table is naturally required session
, and unlike the table definitions themselves, SQLAlchemy does not seem to offer any declarative construct for the "expected rows" in a given table (naturally, since most tables in any application look like user
with dynamic data).
Problem
Using SQLAlchemy, how can both the schema and the lookup table data be declared prior to running the application? Ideally, the solution should involve creating some kind of Enum-like structure containing data that can reference other parts of the application.
Study
The creator of SQLAlchemy suggested an enumeration recipe . The only obvious disadvantage of this solution is that the DBMS used must rely on the data type enum
. For the scope of this question, a DBMS independent lookup table solution is preferred .
A related alternative, also suggested by the SQLAlchemy developer, is a unique object recipe . This implementation ensures that the rows returned by lookup table queries are free of duplicates, but the object session
is still required to create any declarations or queries — blurring the separation of concerns between the database definition and the implementation. Also, clients just need to "know" which strings to query, and not have any enum (inside Python) for reference.
The root of the problem may be conceptual and not tied to SQLAlchemy or Python. Any advice would be much appreciated anyway.
source to share
First, I would say that in most cases, data that is persistent and known during application development is usually not suitable for storing in a database. I would use either a DBMS based enum or Python based enum and check constraint to ensure that every row in the users table has a valid relationship status. Since you said you weren't going to do this, it looks like you are looking for a way to trigger some inserts at the time your relationship_status table is created. I adapted the after_create example to insert something into the table instead of changing the table. You should be able to adapt this to insert 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)
source to share