SQLAlchemy Cheat Sheet

Below is a cheat sheet for some common actions and features in SQLAlchemy, a popular SQL toolkit and Object-Relational Mapping (ORM) library for Python:

SQLAlchemy Basics

Install SQLAlchemy:

pip install sqlalchemy

Import SQLAlchemy:

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

Create an Engine:

engine = create_engine('sqlite:///example.db', echo=True)  # Use 'postgresql://user:password@localhost/db' for PostgreSQL

Create a Base:

Base = declarative_base()

Define a Model

Create a Class for a Table:

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

Create Tables:

Base.metadata.create_all(engine)

CRUD Operations

Create a Record:

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

Read Records:

users = session.query(User).all()

Update a Record:

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

Delete a Record:

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

Querying

Filtering:

young_users = session.query(User).filter(User.age < 30).all()

Sorting:

sorted_users = session.query(User).order_by(User.age).all()

Joining:

 class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship('User', back_populates='addresses')

User.addresses = relationship('Address', order_by=Address.id, back_populates='user')

# Query with join
users_with_addresses = session.query(User).join(Address).all()

Session Management

Create a Session:

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

Commit Changes:

session.commit()

Rollback:

session.rollback()

Close Session:

session.close()

Advanced Features

Using Transactions:

with session.begin():
    # Perform operations within the transaction block
    ...

Using Context Manager:

with engine.connect() as connection:
    result = connection.execute('SELECT * FROM users')

Using ORM Relationships:

class User(Base):
    ...
    addresses = relationship('Address', back_populates='user')

class Address(Base):
    ...
    user = relationship('User', back_populates='addresses')

This cheat sheet covers basic SQLAlchemy usage, model definition, CRUD operations, querying, session management, and some advanced features. Adjust code snippets based on your specific use case and database system. Refer to the official SQLAlchemy documentation for more in-depth information.