I wish I knew this before mixing SQLAlchemy ORM with raw SQL: Python Database Access, Two Ways\n\nWorking with databases in Python? You’ve probably used SQLAlchemy, but did you know there are two powerful approaches: the ORM layer and the raw SQL layer? Here’s a deep dive into both, including code, pros & cons, and when to use each.\n\n## 1. Setup: Define Models with SQLAlchemy ORM\nFirst, we create our ORM models. This file (e.g., orm_models.py) defines the schema using SQLAlchemy’s declarative base.\n\n

python\nfrom sqlalchemy import Column, Integer, String, Text, create_engine\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom sqlalchemy.orm import sessionmaker\n\nBase = declarative_base()\n\nclass JobDescription(Base):\n __tablename__ = 'job_description'\n id = Column(Integer, primary_key=True)\n company_id = Column(String, nullable=False)\n company_name = Column(Text, nullable=False)\n job_description = Column(Text, nullable=False)\n\nclass CandidateResume(Base):\n __tablename__ = 'candidate_resume'\n id = Column(Integer, primary_key=True)\n candidate_id = Column(String, nullable=False)\n candidate_name = Column(Text, nullable=False)\n resume = Column(Text, nullable=False)\n\nengine = create_engine('postgresql://user:password@localhost/dbname')\nSession = sessionmaker(bind=engine)\n\nif __name__ == '__main__':\n Base.metadata.create_all(engine)\n

\n\n## 2. Bit-ORM Layer: Repository Using the ORM\nUse Python objects instead of SQL!\n\n

python\nfrom orm_models import JobDescription, Session\n\ndef get_job_descriptions_by_company_orm(company_id: str):\n session = Session()\n try:\n results = session.query(JobDescription)\n .filter(JobDescription.company_id == company_id)\n .all()\n return results\n finally:\n session.close()\n\nif __name__ == '__main__':\n jobs = get_job_descriptions_by_company_orm('COMPANY_123')\n for job in jobs:\n print(f"{job.company_name}: {job.job_description}")\n

\n\n*Advantages:\n- Abstraction & Simplicity: Deal with Python objects, not SQL.\n- Maintainability: Model changes update all queries.\n- Safety: Built-in SQL injection protection.\n- Relationship Handling: Easier joins and associations.\n\nDisadvantages:\n- Overhead: Less efficient for fine-tuned queries.\n- Complexity: Advanced queries can get tricky.\n\n## 3. Bit-Out-ORM Layer: Repository Using Raw SQL\nControl and performance!\n\n

python\nfrom orm_models import engine\n\ndef get_job_descriptions_by_company_raw(company_id: str):\n with engine.connect() as connection:\n result = connection.execute(\n "SELECT id, company_id, company_name, job_description FROM job_description WHERE company_id = :company_id",\n {"company_id": company_id}\n )\n return result.fetchall()\n\nif __name__ == '__main__':\n rows = get_job_descriptions_by_company_raw('COMPANY_123')\n for row in rows:\n print(f"{row['company_name']}: {row['job_description']}")\n

\n\nAdvantages:\n- Fine-Grained Control: Optimize complex queries.\n- Transparency: See the real SQL sent.\n- Flexibility: Use database-specific features.\n\nDisadvantages:\n- Manual Mapping: More code, more risk.\n- Error-Prone: Easy to make mistakes.\n- Repetition: CRUD operations are verbose.\n\n## 4. Summary\nFor most use-cases, use the ORM. For performance-heavy or database-specific tasks, write raw SQL. Combine both for the best of both worlds!\n\nWhich approach do you prefer? Let me know in the comments!*