Inheritance¶
Joined Table¶
SQLAlchemy includes a feature where a model class hierarchy is implemented by
giving each class it’s own table. For example, an Employee
might have
an id
, name
and type
. Then a Manager
model
might be defined that derives from Employee
and adds the
manager_data
column.
See also
- Joined Table Inheritance
OpenAlchemy documentation for joined table inheritance.
- SQLAlchemy joined table inheritance documentation
Documentation for SQLAlchemy joined table inheritance.
The following example defines joined tabled inheritance where Employee
is the parent and Manager
and Engineer
derive from it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | openapi: "3.0.0" info: title: Test Schema description: API to illustrate OpenAlchemy joined inheritance. version: "0.1" paths: /employee: get: summary: Used to retrieve all employees. responses: 200: description: Return all employees from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Employee" /manager: get: summary: Used to retrieve all managers. responses: 200: description: Return all managers from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Manager" /engineer: get: summary: Used to retrieve all engineers. responses: 200: description: Return all engineers from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Engineer" components: schemas: Employee: description: Person that works for a company. type: object x-tablename: employee properties: id: type: integer description: Unique identifier for the employee. example: 0 x-primary-key: true name: type: string description: The name of the employee. example: David Andersson type: type: string description: The type of the employee. example: employee x-kwargs: __mapper_args__: polymorphic_on: type polymorphic_identity: employee Manager: allOf: - $ref: "#/components/schemas/Employee" - x-inherits: true x-tablename: manager type: object properties: id: type: integer description: Unique identifier for the manager. example: 0 x-primary-key: true x-foreign-key: employee.id manager_data: type: string description: Data for the manager. example: some data x-kwargs: __mapper_args__: polymorphic_identity: manager Engineer: allOf: - $ref: "#/components/schemas/Employee" - x-inherits: true x-tablename: engineer type: object properties: id: type: integer description: Unique identifier for the engineer. example: 0 x-primary-key: true x-foreign-key: employee.id engineer_info: type: string description: Information for the manager. example: some info x-kwargs: __mapper_args__: polymorphic_identity: engineer |
The following file uses OpenAlchemy to generate the SQLAlchemy models:
1 2 3 | from open_alchemy import init_yaml init_yaml("joined-example-spec.yml", models_filename="joined_models_auto.py") |
The SQLAlchemy models generated by OpenAlchemy are equivalent to the following traditional models file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Employee(Base): """Person that works for a company.""" __tablename__ = "employee" id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String) type = sa.Column(sa.String) __mapper_args__ = {"polymorphic_on": type, "polymorphic_identity": "employee"} class Manager(Employee): """Person that works for a company.""" __tablename__ = "manager" id = sa.Column(sa.Integer, sa.ForeignKey("employee.id"), primary_key=True) manager_data = sa.Column(sa.String) __mapper_args__ = {"polymorphic_identity": "manager"} class Engineer(Employee): """Person that works for a company.""" __tablename__ = "engineer" id = sa.Column(sa.Integer, sa.ForeignKey("employee.id"), primary_key=True) engineer_info = sa.Column(sa.String) __mapper_args__ = {"polymorphic_identity": "engineer"} |
OpenAlchemy will generate the following typed models:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 | """Autogenerated SQLAlchemy models based on OpenAlchemy models.""" # pylint: disable=no-member,super-init-not-called,unused-argument import typing import sqlalchemy from sqlalchemy import orm from open_alchemy import models Base = models.Base # type: ignore class EmployeeDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" id: typing.Optional[int] name: typing.Optional[str] type: typing.Optional[str] class TEmployee(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties id: "sqlalchemy.Column[typing.Optional[int]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TEmployee": """ Construct from a dictionary (eg. a POST payload). Args: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TEmployee": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> EmployeeDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Employee: typing.Type[TEmployee] = models.Employee # type: ignore class ManagerDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" id: typing.Optional[int] manager_data: typing.Optional[str] name: typing.Optional[str] type: typing.Optional[str] class TManager(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: id: Unique identifier for the manager. manager_data: Data for the manager. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties id: "sqlalchemy.Column[typing.Optional[int]]" manager_data: "sqlalchemy.Column[typing.Optional[str]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, id: typing.Optional[int] = None, manager_data: typing.Optional[str] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: id: Unique identifier for the manager. manager_data: Data for the manager. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, id: typing.Optional[int] = None, manager_data: typing.Optional[str] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TManager": """ Construct from a dictionary (eg. a POST payload). Args: id: Unique identifier for the manager. manager_data: Data for the manager. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TManager": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> ManagerDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Manager: typing.Type[TManager] = models.Manager # type: ignore class EngineerDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" id: typing.Optional[int] engineer_info: typing.Optional[str] name: typing.Optional[str] type: typing.Optional[str] class TEngineer(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: id: Unique identifier for the engineer. engineer_info: Information for the manager. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties id: "sqlalchemy.Column[typing.Optional[int]]" engineer_info: "sqlalchemy.Column[typing.Optional[str]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, id: typing.Optional[int] = None, engineer_info: typing.Optional[str] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: id: Unique identifier for the engineer. engineer_info: Information for the manager. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, id: typing.Optional[int] = None, engineer_info: typing.Optional[str] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TEngineer": """ Construct from a dictionary (eg. a POST payload). Args: id: Unique identifier for the engineer. engineer_info: Information for the manager. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TEngineer": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> EngineerDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Engineer: typing.Type[TEngineer] = models.Engineer # type: ignore |
Single Table¶
Single table inheritance is very similar to Joined Table Inheritance with the difference that all classes are linked to the same table and there is no foreign key relationship between the models.
See also
- Single Table Inheritance
OpenAlchemy documentation for single table inheritance.
- SQLAlchemy single table inheritance documentation
Documentation for SQLAlchemy single table inheritance.
The following example defines single tabled inheritance where Employee
is the parent and Manager
and Engineer
derive from it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | openapi: "3.0.0" info: title: Test Schema description: API to illustrate OpenAlchemy single inheritance. version: "0.1" paths: /employee: get: summary: Used to retrieve all employees. responses: 200: description: Return all employees from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Employee" /manager: get: summary: Used to retrieve all managers. responses: 200: description: Return all managers from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Manager" /engineer: get: summary: Used to retrieve all engineers. responses: 200: description: Return all engineers from the database. content: application/json: schema: type: array items: "$ref": "#/components/schemas/Engineer" components: schemas: Employee: description: Person that works for a company. type: object x-tablename: employee properties: id: type: integer description: Unique identifier for the employee. example: 0 x-primary-key: true name: type: string description: The name of the employee. example: David Andersson type: type: string description: The type of the employee. example: employee x-kwargs: __mapper_args__: polymorphic_on: type polymorphic_identity: employee Manager: allOf: - $ref: "#/components/schemas/Employee" - x-inherits: true type: object properties: manager_data: type: string description: Data for the manager. example: some data x-kwargs: __mapper_args__: polymorphic_identity: manager Engineer: allOf: - $ref: "#/components/schemas/Employee" - x-inherits: true type: object properties: engineer_info: type: string description: Information for the manager. example: some info x-kwargs: __mapper_args__: polymorphic_identity: engineer |
The following file uses OpenAlchemy to generate the SQLAlchemy models:
1 2 3 | from open_alchemy import init_yaml init_yaml("single-example-spec.yml", models_filename="single_models_auto.py") |
The SQLAlchemy models generated by OpenAlchemy are equivalent to the following traditional models file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Employee(Base): """Person that works for a company.""" __tablename__ = "employee" id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String) type = sa.Column(sa.String) __mapper_args__ = {"polymorphic_on": type, "polymorphic_identity": "employee"} class Manager(Employee): """Person that works for a company.""" manager_data = sa.Column(sa.String) __mapper_args__ = {"polymorphic_identity": "manager"} class Engineer(Employee): """Person that works for a company.""" engineer_info = sa.Column(sa.String) __mapper_args__ = {"polymorphic_identity": "engineer"} |
OpenAlchemy will generate the following typed models:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 | """Autogenerated SQLAlchemy models based on OpenAlchemy models.""" # pylint: disable=no-member,super-init-not-called,unused-argument import typing import sqlalchemy from sqlalchemy import orm from open_alchemy import models Base = models.Base # type: ignore class EmployeeDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" id: typing.Optional[int] name: typing.Optional[str] type: typing.Optional[str] class TEmployee(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties id: "sqlalchemy.Column[typing.Optional[int]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TEmployee": """ Construct from a dictionary (eg. a POST payload). Args: id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TEmployee": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> EmployeeDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Employee: typing.Type[TEmployee] = models.Employee # type: ignore class ManagerDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" manager_data: typing.Optional[str] id: typing.Optional[int] name: typing.Optional[str] type: typing.Optional[str] class TManager(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: manager_data: Data for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties manager_data: "sqlalchemy.Column[typing.Optional[str]]" id: "sqlalchemy.Column[typing.Optional[int]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, manager_data: typing.Optional[str] = None, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: manager_data: Data for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, manager_data: typing.Optional[str] = None, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TManager": """ Construct from a dictionary (eg. a POST payload). Args: manager_data: Data for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TManager": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> ManagerDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Manager: typing.Type[TManager] = models.Manager # type: ignore class EngineerDict(typing.TypedDict, total=False): """TypedDict for properties that are not required.""" engineer_info: typing.Optional[str] id: typing.Optional[int] name: typing.Optional[str] type: typing.Optional[str] class TEngineer(typing.Protocol): """ SQLAlchemy model protocol. Person that works for a company. Attrs: engineer_info: Information for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ # SQLAlchemy properties __table__: sqlalchemy.Table __tablename__: str query: orm.Query # Model properties engineer_info: "sqlalchemy.Column[typing.Optional[str]]" id: "sqlalchemy.Column[typing.Optional[int]]" name: "sqlalchemy.Column[typing.Optional[str]]" type: "sqlalchemy.Column[typing.Optional[str]]" def __init__( self, engineer_info: typing.Optional[str] = None, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> None: """ Construct. Args: engineer_info: Information for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. """ ... @classmethod def from_dict( cls, engineer_info: typing.Optional[str] = None, id: typing.Optional[int] = None, name: typing.Optional[str] = None, type: typing.Optional[str] = None, ) -> "TEngineer": """ Construct from a dictionary (eg. a POST payload). Args: engineer_info: Information for the manager. id: Unique identifier for the employee. name: The name of the employee. type: The type of the employee. Returns: Model instance based on the dictionary. """ ... @classmethod def from_str(cls, value: str) -> "TEngineer": """ Construct from a JSON string (eg. a POST payload). Returns: Model instance based on the JSON string. """ ... def to_dict(self) -> EngineerDict: """ Convert to a dictionary (eg. to send back for a GET request). Returns: Dictionary based on the model instance. """ ... def to_str(self) -> str: """ Convert to a JSON string (eg. to send back for a GET request). Returns: JSON string based on the model instance. """ ... Engineer: typing.Type[TEngineer] = models.Engineer # type: ignore |
See also