SQLAlchemy Python Library Cheatsheet

SQLAlchemy is a powerful and widely-used Python library for working with relational databases. It provides a flexible and expressive way to interact with databases, allowing developers to build sophisticated and efficient database-driven applications. Whether you are a beginner or an experienced developer, having a cheatsheet for SQLAlchemy can be incredibly handy. In this blog post, we’ll provide a quick reference guide to help you navigate and use SQLAlchemy effectively.

1. Installation

pip install sqlalchemy

2. Importing SQLAlchemy

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

3. Creating an Engine

# SQLite in-memory database
engine = create_engine('sqlite:///:memory:')

# SQLite file database
engine = create_engine('sqlite:///example.db')

# PostgreSQL database
engine = create_engine('postgresql://user:password@localhost/dbname')

4. Defining a Model

Base = declarative_base()

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

5. Creating Tables

Base.metadata.create_all(engine)

6. Session Configuration

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

7. Adding Data

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

8. Querying Data

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

# Filter users by age
young_users = session.query(User).filter(User.age < 30).all()

9. Updating Data

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

10. Deleting Data

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

11. Relationships

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')

12. Joining Tables

result = session.query(User, Address).join(Address).filter(User.id == Address.user_id).all()

13. Rolling Back Changes

try:
    # your code here
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

This cheatsheet provides a quick reference for common SQLAlchemy tasks. However, SQLAlchemy is a feature-rich library, and there is much more to explore. Refer to the official documentation for in-depth information and advanced features.

FAQ