Define sqlalchmey tables based on function input

Question

This is a follow up question to this question.

I'm trying to implement a function that generates a table.

The function looks like this:

import sqlalchemy as sql
import sqlalchemy.orm

TableBase = sql.orm.declarative_base()

class _RealTimeDataBase(TableBase):
    __tablename__ = "TBL_RealTimeData"

    id = sql.Column(sql.INTEGER, primary_key=True)
    SubTestId = sql.Column(sql.INT)
    CellOrCup = sql.Column(sql.INT)
    TimeInfo = sql.Column(sql.INT)
    TestTemp = sql.Column(sql.FLOAT)
    OxygenHumid = sql.Column(sql.FLOAT)
    NitrogenHumi = sql.Column(sql.FLOAT)
    OTR = sql.Column(sql.FLOAT)
    TimeDe = sql.Column(sql.INT)
    LocalTime = sql.Column(sql.DATETIME)


def RealTimeData(testType: int):
    if testType == 1:
        class RealTimeDataFilm(_RealTimeDataBase): # Error occurs here (line 148) 
            CoffOxy = sql.Column(sql.FLOAT)

        return RealTimeDataFilm
    else:
        class RealTimeDataPackage(_RealTimeDataBase):
            CoffOxy = None

        return RealTimeDataPackage

Basically the CoffOxy column only exits if the TestType is 1 otherwise it should be None.

Unfortunately when I call the function the second time I run into this error:

 File "/home/gianl/code/Source Graphics/labthink-permeability-reports/permeability/sqliteModels.py", line 148, in RealTimeData
    class RealTimeDataFilm(_RealTimeDataBase):
  File "/opt/anaconda/envs/labthink-permeability-reports/lib/python3.9/site-packages/sqlalchemy/orm/decl_api.py", line 72, in __init__
    _as_declarative(reg, cls, dict_)
  File "/opt/anaconda/envs/labthink-permeability-reports/lib/python3.9/site-packages/sqlalchemy/orm/decl_base.py", line 126, in _as_declarative
    return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
  File "/opt/anaconda/envs/labthink-permeability-reports/lib/python3.9/site-packages/sqlalchemy/orm/decl_base.py", line 177, in setup_mapping
    return cfg_cls(registry, cls_, dict_, table, mapper_kw)
  File "/opt/anaconda/envs/labthink-permeability-reports/lib/python3.9/site-packages/sqlalchemy/orm/decl_base.py", line 312, in __init__
    self._setup_inheritance(mapper_kw)
  File "/opt/anaconda/envs/labthink-permeability-reports/lib/python3.9/site-packages/sqlalchemy/orm/decl_base.py", line 889, in _setup_inheritance
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Column 'CoffOxy' on class <class 'permeability.sqliteModels.RealTimeData.<locals>.RealTimeDataFilm'> conflicts with existing column 'TBL_RealTimeData.CoffOxy'

Although CoffOxy is not defined yet it still complains that I redefine it. How could I solve this error? Or is there another way to get the same logic working?

Live example on colab (comment if the link is not working)

Answer 1

Figured it out I just need to define the classes outside of the function scope.

import sqlalchemy as sql
import sqlalchemy.orm

TableBase = sql.orm.declarative_base()

class _RealTimeDataBase(TableBase):
    __tablename__ = "TBL_RealTimeData"

    id = sql.Column(sql.INTEGER, primary_key=True)
    SubTestId = sql.Column(sql.INT)
    CellOrCup = sql.Column(sql.INT)
    TimeInfo = sql.Column(sql.INT)
    TestTemp = sql.Column(sql.FLOAT)
    OxygenHumid = sql.Column(sql.FLOAT)
    NitrogenHumi = sql.Column(sql.FLOAT)
    OTR = sql.Column(sql.FLOAT)
    TimeDe = sql.Column(sql.INT)
    LocalTime = sql.Column(sql.DATETIME)


class _RealTimeDataPackage(_RealTimeDataBase):
    CoffOxy = None


class _RealTimeDataFilm(_RealTimeDataBase):
    CoffOxy = sql.Column(sql.FLOAT)


def RealTimeData(testType: int):
    if testType == 1:
        return _RealTimeDataFilm
    else:
        return _RealTimeDataPackage