Introduction to SQLAlchemy

Key Concepts

• ORM (Object-Relational Mapping)
• SQLAlchemy Core
• SQLAlchemy ORM
• Declarative System
• Session Management

ORM (Object-Relational Mapping)

ORM is a technique that allows you to interact with a database using objects and classes in your programming language, rather than writing raw SQL queries. SQLAlchemy is a popular ORM for Python that provides a high-level interface to interact with databases.

SQLAlchemy Core

SQLAlchemy Core is the foundation of SQLAlchemy, providing a set of tools for working with databases at a lower level. It includes components like SQL expression language, schema definition, and connection pooling. Core allows you to construct and execute SQL queries programmatically.

from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///example.db')
with engine.connect() as connection:
    result = connection.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)
    

SQLAlchemy ORM

SQLAlchemy ORM builds on top of SQLAlchemy Core, providing a higher-level abstraction for working with databases. It allows you to define database tables as Python classes and map them to database tables. The ORM handles the conversion between Python objects and database records.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(name='John Doe', age=30)
session.add(new_user)
session.commit()
    

Declarative System

The declarative system in SQLAlchemy allows you to define database tables and their corresponding Python classes in a single step. This system simplifies the process of creating and managing database models by combining table definition and class definition.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    

Session Management

Session management in SQLAlchemy ORM is crucial for interacting with the database. A session object acts as a "workspace" for your objects, allowing you to add, update, and delete records. Sessions are created using the sessionmaker function and are typically used within a context manager.

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

user = session.query(User).filter_by(name='John Doe').first()
user.age = 31
session.commit()