The open blogging platform. Say no to algorithms and paywalls.

Understanding SQLAlchemy’s Declarative Base and Metadata in Python

A deep dive into SQLAlchemy’s declarative base and metadata and providing practical Python examples to illustrate their use.

Source: https://hotpot.ai/art-generator

When it comes to working with databases in Python, SQLAlchemy is a top choice for many developers. Within SQLAlchemy, the Declarative Base and Metadata are two essential components that facilitate database interactions. In this article, we’ll take a deep dive into these concepts and provide practical Python examples to illustrate their use.

SQLAlchemy Declarative Base

At its core, SQLAlchemy offers a flexible and intuitive way to interact with databases in Python. The Declarative Base is a critical part of SQLAlchemy’s Object-Relational Mapping (ORM) system, bridging the gap between your application’s object-oriented structure and the relational database model.

To get started with SQLAlchemy’s Declarative Base, you’ll need to import it:

from sqlalchemy.ext.declarative import declarative_base

Now, let’s create a basic Python class that represents a database table using the Declarative Base:

Base = declarative_base()  
  
class User(Base):  
    __tablename__ = 'users'  
    id = Column(Integer, primary_key=True)  
    username = Column(String(50), unique=True)  
    email = Column(String(100))  
    def __init__(self, username, email):  
        self.username = username  
        self.email = email

In this example, we define a User class that inherits from Base. By specifying the __tablename__ attribute, we associate the User class with the "users" table in the database.

Each class attribute corresponds to a column in the table, complete with data types and constraints. The id, username, and email columns in the example map to their respective fields in the database table.


SQLAlchemy Metadata

Metadata in SQLAlchemy serves as a container for various database-related objects, such as tables, columns, and indexes. It plays a vital role in representing the schema of your database. While SQLAlchemy can auto-discover this information, you can also explicitly define it using MetaData objects.

To work with SQLAlchemy’s Metadata, you’ll need to import it:

from sqlalchemy import MetaData

Let’s create a MetaData object and bind it to our database engine:

from sqlalchemy import create_engine  
engine = create_engine('sqlite:///mydatabase.db')  
metadata = MetaData(bind=engine)

In this example, we create a SQLite database engine and bind it to our metadata object. This allows SQLAlchemy to maintain awareness of the database's structure and schema.

Bringing It All Together

Now that we’ve covered both SQLAlchemy’s Declarative Base and Metadata, let’s integrate them into a complete example. We’ll define a table using the Declarative Base and associate it with our Metadata.

from sqlalchemy import Column, Integer, String  
from sqlalchemy.ext.declarative import declarative_base  
from sqlalchemy import create_engine, MetaData  
  
Base = declarative_base()  
  
class User(Base):  
    __tablename__ = 'users'  
    id = Column(Integer, primary_key=True)  
    username = Column(String(50), unique=True)  
    email = Column(String(100))  
  
engine = create_engine('sqlite:///mydatabase.db')  
metadata = MetaData(bind=engine)  
# Create the "users" table in the database  
Base.metadata.create_all(engine)

In this example, we create a User class using SQLAlchemy's Declarative Base and define its table structure. We then bind a metadata object to a database engine and use Base.metadata.create_all(engine) to create the corresponding table in the database.

The connection between the Declarative Base (Base) and Metadata (metadata) is established through the Base.metadata attribute, which allows SQLAlchemy to work with metadata and create database tables based on the ORM model definitions. The User table is created by calling Base.metadata.create_all(engine), where engine is the database engine associated with the metadata.


That’s it for this article! Feel free to leave feedback or questions in the comments. If you found this an exciting read, leave some claps and follow! I love coffee, so feel free to buy me a coffee; XD. Cheers!

PythonCodeNemesis writes Python Technical Articles and Tutorials on Medium




Continue Learning