pythonsqlalchemypydanticsqlmodel

When using SQLModel and Pydantic create_model how do you specify the database schema and tablename?


I have the code below working to use a dictionary to dynamically create a model and database table. However I'm am struggling to figure out how to specify the tablename and database schema that it will use explicitly. I do not want to use the default schema and the generated tablename is all lowercase.

If this were a normal static model I could use __tablename__ = "MyTableName" and __table_args__ = {'schema' : 'MySchema'} inside the models class. However it seems that pydantics create_model doesn't support these. Is there any way to do this? Thanks.

field_definitions = {
    "Id": (Optional[int], Field(default=None, primary_key=True)),
    "Field1": (UUID,  Field(sa_column=Column(UNIQUEIDENTIFIER(), nullable=False, comment=""))),
    "Field2": (date, Field(sa_column=Column(DATE(), nullable=False, server_default=text("GETUTCDATE()"), comment=""))),
    "Field3": (datetime, Field(sa_column=Column(DATETIME2(7), nullable=False, server_default=text("GETUTCDATE()"), comment=""))),
    "Field4": (Optional[datetime], Field(sa_column=Column(DATETIME2(7), nullable=True, comment=""))),
    "Field5" : (bool, Field(sa_column=Column(BIT(), nullable=False, server_default=text("0"), comment=""))),
    "Field6": (str, Field(sa_column=Column(NVARCHAR(1000), nullable=False, comment="")))
}

Table = create_model(
    "TestTable", 
    __base__=SQLModel, 
    __cls_kwargs__={"table": True},
    **field_definitions
)

SQLModel.metadata.create_all(engine)

Solution

  • It seems that it doesn't support it. A workaround is inheriting this just as a model from a SQLModel table class in which you can define the table name and database schema. This seems to work.

    TestModel = create_model(
      "TestModel", 
      __base__=SQLModel, 
      **field_definitions
    )
    
    Class DBTable(TestModel, table=true):
      """Table model."""
    
      __tablename__ = "db_table_name"
      __table_args__ = {"schema": "customer"}
    
    
      ....