Python SQLAlchemy

SQLAlchemy is an ORM (Object Relational Mapper).

        Meaning:
        - you work with Python classes
        - SQLAlchemy converts that into SQL queries
        - it helps prevent SQL injection
        - it makes code easier to maintain
        
        ## Install SQLAlchemy
        
        ```bash
        pip install sqlalchemy
        ```
        
        ## Step 1: Create a model (table as class)
        
        ```python
        from sqlalchemy import create_engine, Column, Integer, String
        from sqlalchemy.orm import declarative_base, sessionmaker
        
        Base = declarative_base()
        
        class User(Base):
            __tablename__ = "users"
        
            id = Column(Integer, primary_key=True)
            name = Column(String)
            email = Column(String)
        
        engine = create_engine("sqlite:///users.db")
        Base.metadata.create_all(engine)
        
        Session = sessionmaker(bind=engine)
        session = Session()
        ```
        
        ## Step 2: Insert data
        
        ```python
        user = User(name="Tom", email="tom@example.com")
        session.add(user)
        session.commit()
        ```
        
        ## Step 3: Query data
        
        ```python
        users = session.query(User).all()
        for u in users:
            print(u.name, u.email)
        
        tom = session.query(User).filter_by(name="Tom").first()
        print(tom.email)
        ```
        
        ## Step 4: Update
        
        ```python
        tom = session.query(User).filter_by(name="Tom").first()
        tom.email = "tom_new@example.com"
        session.commit()
        ```
        
        ## Step 5: Delete
        
        ```python
        tom = session.query(User).filter_by(name="Tom").first()
        session.delete(tom)
        session.commit()
        ```
        
        ## Relationships (one-to-many)
        
        ```python
        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", backref="posts")
        ```
        
        ## Graph: ORM mapping concept
        
        ```mermaid
        flowchart LR
          A[Python class User] --> B[Table users]
          C[User object] --> D[Row in users]
          E[session.query] --> F[SQL executed safely]
        ```
        
        ## Remember
        
        - Session is your DB work unit
        - Always commit after changes
        - ORM keeps code cleaner than raw SQL in many apps