🛢️SQLAlchemy Cheat Sheet

pip install sqlalchemy

Optional for SQLite (included by default) or install a specific database driver:

pip install psycopg2  # for PostgreSQL
pip install pymysql   # for MySQL

---

🏗️ Define Models (ORM Style)

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)
    email = Column(String)

---

🔌 Connect to Database

from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydb.db")  # SQLite example

---

🛠️ Create Tables

Base.metadata.create_all(engine)

---

🧵 Create a Session

from sqlalchemy.orm import sessionmaker

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

---

➕ Add Records

new_user = User(name="Alice", email="alice@example.com")
session.add(new_user)
session.commit()

---

🔍 Query Records

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

# Get one user by filter
user = session.query(User).filter_by(name="Alice").first()

# Get by primary key
user = session.get(User, 1)

---

✏️ Update Records

user = session.query(User).filter_by(name="Alice").first()
user.email = "newalice@example.com"
session.commit()

---

❌ Delete Records

session.delete(user)
session.commit()

---

🔗 Relationships

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))
    user = relationship("User", back_populates="posts")

User.posts = relationship("Post", back_populates="user")

---

⚙️ Common Column Types

from sqlalchemy import Integer, String, Float, Boolean, Date, DateTime, Text

---

📌 Useful Tips

• session.rollback() – Undo changes if something goes wrong

• session.close() – Clean up session

• Base.metadata.drop_all(engine) – Drop all tables