SQLModel Dynamic Creation with Multiple Inheritance Issue #1638
-
First Check
Commit to Help
Example Code"""
Minimal reproducible example for SQLModel dynamic creation issue.
Problem: Creating a SQLModel dynamically with pydantic.create_model() and multiple inheritance
fails on second call with "Table already defined" error, even when trying to pass extend_existing=True.
Run this file to see the error:
python sqlmodel_issues.py
"""
from typing import Optional
from datetime import datetime
from pydantic import BaseModel, create_model, Field as PydanticField
from sqlmodel import SQLModel, Field
# ============================================================================
# Step 1: Base Model (static, shared across all dynamic models)
# ============================================================================
class GenericRecordModel(SQLModel):
"""Base SQLModel with standard database fields for all tables."""
id: int = Field(primary_key=True)
azureDestination: Optional[str] = Field(default=None, max_length=100)
timestamp: Optional[datetime] = Field(default=None)
# ============================================================================
# Step 2: Simulated LLM Model (in real app, this comes from JSON Schema)
# ============================================================================
class SimulatedLLMModel(BaseModel):
"""
In production, this is generated from JSON Schema using json-schema-to-pydantic.
Contains validation constraints (maxLength, etc.) that must be preserved.
"""
usdot: Optional[str] = PydanticField(default=None, max_length=50)
vehicleType: Optional[str] = PydanticField(default=None, max_length=50)
# ============================================================================
# Step 3: Dynamic Model Creation Function
# ============================================================================
def create_dynamic_table_model(table_name: str, llm_model: type[BaseModel]) -> type[SQLModel]:
"""
Create a SQLModel dynamically with multiple inheritance.
Goal: Combine llm_model validation + GenericRecordModel fields + SQLModel ORM
Issue: Second call fails with "Table 'table_name' is already defined" error.
"""
# CURRENT ATTEMPT - FAILS ON SECOND CALL
output_model = create_model(
table_name, # Model name = table name
__base__=(llm_model, GenericRecordModel, SQLModel), # Multiple inheritance
__cls_kwargs__={"table": True} # Enable SQLModel table mode
)
# Tried setting __table_args__ here, but error occurs DURING create_model()
# output_model.__table_args__ = {"extend_existing": True} # ❌ Too late
return output_model
# ============================================================================
# Step 4: Demonstrate the Problem
# ============================================================================
if __name__ == "__main__":
print("="*80)
print("SQLModel Dynamic Creation Issue - Minimal Reproducible Example")
print("="*80)
print()
table_name = "ge_snapshot_side_truck"
# First call - works fine
print("Step 1: Creating model for the FIRST time...")
try:
model1 = create_dynamic_table_model(table_name, SimulatedLLMModel)
print(f"✅ SUCCESS: Model '{model1.__name__}' created")
print(f" Table: {getattr(model1, '__tablename__', 'N/A')}")
print(f" Fields: {list(model1.model_fields.keys())}")
print()
except Exception as e:
print(f"❌ FAILED: {e}")
print()
# Second call - FAILS
print("Step 2: Creating the SAME model again (simulates hot reload / test re-run)...")
try:
model2 = create_dynamic_table_model(table_name, SimulatedLLMModel)
print(f"✅ SUCCESS: Model '{model2.__name__}' created")
print(f" This should work but currently fails!")
print()
except Exception as e:
print(f"❌ FAILED with error:")
print(f" {type(e).__name__}: {e}")
print()
print("This is the problem we need to solve!")
print()
print("="*80)
print("Expected Behavior:")
print("="*80)
print("Both calls should succeed. The second call should either:")
print("1. Reuse the existing table definition (extend_existing=True)")
print("2. Or properly override it without errors")
print()
print("Question: How to properly pass extend_existing=True when using")
print(" pydantic.create_model() with SQLModel and multiple inheritance?")
print("="*80)DescriptionSQLModel Dynamic Creation: Is Multiple Inheritance the Right Approach?Problem OverviewI need to dynamically generate SQLModel classes that combine:
My current approach uses multiple inheritance with I'm encountering this error when recreating the same model (tests, hot reload): However, my main concern is: Am I using the right architecture to merge these two models? Current CodeBase Model (Static)from typing import Optional
from datetime import datetime
from sqlmodel import SQLModel, Field
class GenericRecordModel(SQLModel):
"""Base SQLModel with standard database fields."""
id: int = Field(primary_key=True)
azureDestination: Optional[str] = Field(default=None, max_length=100)
timestamp: Optional[datetime] = Field(default=None)Dynamic Model Creationfrom pydantic import BaseModel, create_model
from sqlmodel import SQLModel
from json_schema_to_pydantic import create_model as json_schema_create_model
def create_dynamic_sqlmodel(table_name: str, json_schema: dict) -> type[SQLModel]:
"""
Creates a SQLModel dynamically from JSON Schema with multiple inheritance.
Goal: OutputModel should inherit from:
- llm_model (Pydantic validation from JSON Schema)
- GenericRecordModel (database fields: id, timestamp, etc.)
- SQLModel (ORM functionality)
"""
# Step 1: Generate Pydantic model from JSON Schema
# This model contains validated fields with constraints (maxLength, etc.)
llm_model = json_schema_create_model(json_schema)
# Step 2: Create SQLModel with multiple inheritance
# Problem: How to properly pass extend_existing=True?
output_model = create_model(
table_name, # Model name = table name
__base__=(llm_model, GenericRecordModel, SQLModel),
__cls_kwargs__={"table": True}
)
return output_modelExample Usage# JSON Schema from database
schema = {
"type": "object",
"properties": {
"usdot": {"type": ["string", "null"], "maxLength": 50},
"vehicleType": {"type": ["string", "null"], "maxLength": 50}
}
}
# First call - works fine
model1 = create_dynamic_sqlmodel("ge_snapshot_side_truck", schema)
# Second call - FAILS with "Table already defined" error
model2 = create_dynamic_sqlmodel("ge_snapshot_side_truck", schema)What I've TriedAttempt 1: Setting
|
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
|
The exception is telling you that the factory is creating a second ORM mapping for the same I would not use https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table.params.extend_existing There are two architectural cases. Arbitrary JSON Schema at runtime: keep validation and persistence separateIf fields can change based on a schema stored in the database, use the generated Pydantic model as a validator and persist the validated payload in one stable JSON column: from datetime import datetime
from typing import Any
from sqlalchemy import Column, JSON
from sqlmodel import Field, SQLModel
class GenericRecord(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
schema_id: str = Field(index=True)
azure_destination: str | None = Field(default=None, max_length=100)
timestamp: datetime | None = None
payload: dict[str, Any] = Field(sa_column=Column(JSON, nullable=False))
validator = json_schema_create_model(schema)
validated = validator.model_validate(raw_payload)
record = GenericRecord(
schema_id=schema_id,
payload=validated.model_dump(mode="json"),
)On read, select the validator associated with Real SQL columns: generate once and migrate explicitlyIf every generated property must be a queryable SQL column, the schema is no longer just runtime validation; it is database DDL. In that case:
Conceptually: _models: dict[tuple[str, str], type[SQLModel]] = {}
def get_table_model(table_name: str, schema: dict) -> type[SQLModel]:
fingerprint = canonical_schema_hash(schema)
key = (table_name, fingerprint)
if key in _models:
return _models[key]
# Convert the generated model's FieldInfo objects into field definitions,
# then derive from GenericRecordModel only once.
model = build_sqlmodel_once(table_name, schema)
_models[key] = model
return modelAlso avoid For tests, create a fresh registry/ So the direct answer is: the second call should reuse the existing model, not redefine its table. If this gives you a workable direction, please mark it as the accepted answer so future readers can find the distinction quickly. |
Beta Was this translation helpful? Give feedback.
The exception is telling you that the factory is creating a second ORM mapping for the same
(schema, table_name)inside the sameMetaData. That is different from the database table already existing: SQLAlchemy normally expects one in-memoryTableidentity per name in aMetaDatacollection.I would not use
extend_existing=Trueto make repeated model-class creation the normal path. It can merge newTable(...)arguments into the existing metadata object, but it does not turn two separately generated ORM classes into one coherent mapping, and it does not migrate the physical database schema. SQLAlchemy documents that distinction here:https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlal…