๐๏ธ SQLAlchemy Basics
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power of SQL.
๐ก Quick Tip:
Mastering this concept will significantly boost your Python data science skills!
๐ป Code Example:
from sqlalchemy import ( create_engine, Column, Integer, String, Float, ForeignKey, DateTime, func ) from sqlalchemy.orm import ( DeclarativeBase, relationship, Session ) from datetime import datetime # 1. Setup engine = create_engine("sqlite:///pynfinity.db", echo=False) class Base(DeclarativeBase): pass # 2. Define models with relationship class PynfinityUser(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) username = Column(String(50), unique=True, nullable=False) email = Column(String(100), unique=True) score = Column(Float, default=0.0) joined = Column(DateTime, default=datetime.utcnow) courses = relationship("Enrollment", back_populates="user") class Enrollment(Base): __tablename__ = "enrollments" id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey("users.id")) course = Column(String(100)) enrolled_at = Column(DateTime, default=datetime.utcnow) user = relationship("PynfinityUser", back_populates="courses") Base.metadata.create_all(engine) # 3. Insert records with Session(engine) as session: u1 = PynfinityUser(username="santoshtvk", email="tvk@pynfinity.com", score=95.5) u2 = PynfinityUser(username="dhruv", email="dhruv@pynfinity.com", score=88.0) session.add_all([u1, u2]) session.flush() # Get IDs before commit session.add_all([ Enrollment(user_id=u1.id, course="Infinite Python"), Enrollment(user_id=u1.id, course="Infinite AI"), Enrollment(user_id=u2.id, course="Infinite Python"), ]) session.commit() # 4. Query โ top scorers with course count with Session(engine) as session: results = ( session.query( PynfinityUser.username, PynfinityUser.score, func.count(Enrollment.id).label("courses_count"), ) .outerjoin(Enrollment) .group_by(PynfinityUser.id) .order_by(PynfinityUser.score.desc()) .all() ) for row in results: print(f"{row.username:<15} Score:{row.score} Courses:{row.courses_count}")
Keep exploring and happy coding! ๐ป
