Describing Databases with MetaData: SQLAlchemy ORM

We will learn how to create the same tables as we have created using Core with ORM. The ORM consists of mapped classes. The mapped classes are declared in the same manner as any Python class is created and we link the attributes of the mapped classes to the Columns of the Table.

In ORM the MetaData object is combined with the ORM-Only object called Registry. We construct the Registry as shown below:

Python3

from sqlalchemy.orm import registry
 
mapper_registry = registry()
mapper_registry.metadata

Output:

MetaData()

In ORM, instead of declaring Table objects directly, we declare them indirectly by using mapped classes. The mapped classes descend from a base directory known as Declarative Base. The declarative base can be created using registry using the registry.generate_base() method.

Python3

Base = mapper_registry.generate_base()

This base class serves as the base class for the ORM mapped classes that we declare. The two tables Student_account and address can be created using ORM as shown below.

Python3

from sqlalchemy.orm import relationship
 
 
class Student(Base):
    __tablename__ = 'student_account'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    age = Column(Integer)
    grade = Column(String)
 
    addresses = relationship("Address", back_populates="student")
 
    def __repr__(self):
        return f"Student(id={self.id!r}, name={self.name!r},\
        age={self.age!r},grade={self.grade!r})"
 
 
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    student_id = Column(Integer, ForeignKey('student_account.id'))
 
    student = relationship("Student", back_populates="addresses")
 
    def __repr__(self):
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

The .__table__ attribute is used to see the Table objects created from declarative mapped classes as shown below:

Python3

Student.__table__

Output:

Table('student_account', MetaData(), Column('id', Integer(),
 table=<student_account>, primary_key=True, nullable=False),
 Column('name', String(length=30), table=<student_account>),
 Column('age', Integer(), table=<student_account>),
 Column('grade', String(), table=<student_account>), schema=None)

Python3

Address.__table__

Output:

Table('address', MetaData(), Column('id', Integer(),
 table=<address>, primary_key=True, nullable=False),
  Column('email_address', String(), table=<address>,
   nullable=False), Column('student_id', Integer(), 
   ForeignKey('student_account.id'), table=<address>), schema=None)

Emitting DDL:

In ORM, for emitting DDL we use the MetaData object associated with our registry and ORM declarative base class.

Python3

mapper_registry.metadata.create_all(engine)
 
Base.metadata.create_all(engine)

Output: