Reflecting Metadata
SQLAlchemy can act as an Object-relational Map (ORM). This enables you to treat records of a database table as instantiable records. To leverage this functionality, you must reflect the underlying metadata in one of the following ways.
Note: The following examples employ SQLAlchemy 1.4.
Modeling Data Using a Mapping Class
Use "sqlalchemy.ext.declarative.declarative_base" to declare a mapping class for the table you wish to model in the ORM. A known table in the data model is modeled either partially or completely, as shown in the following example:from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class WorkItems(Base): __tablename__ = "WorkItems" Id = Column(String, primary_key=True) Id = Column(String) BuildNumber = Column(String)
Automatically Reflecting Metadata
Rather than mapping tables manually, SQLAlchemy can discover the metadata for one or more tables automatically. To accomplish this across the entire data model, use automap_base:from sqlalchemy import MetaData from sqlalchemy.ext.automap import automap_base meta = MetaData() abase = automap_base(metadata=meta) abase.prepare(autoload_with=engine) WorkItems = abase.classes.WorkItems
You can also reflect a single table with an inspector. When reflecting this way, providing a list of specific columns to map is optional:
from sqlalchemy import MetaData, Table from sqlalchemy import inspect meta = MetaData() insp = inspect(engine) WorkItems_table = Table("WorkItems", meta) insp.reflect_table(WorkItems_table, ["Id","BuildNumber"])