I wish I knew this before approaching Database Access in Python with SQLAlchemy\n\nBelow is an in-depth example that shows two approaches in Python using SQLAlchemy: one that leverages the ORM ("bit-ORM layer") and one that uses raw SQL queries ("bit-out-ORM layer"). In the example, we define a simple model for job descriptions and then provide two repository functions that return job descriptions for a given company. At the end, I discuss the advantages and disadvantages of each approach.\n\n## 1. Setup: Define Models with SQLAlchemy ORM\n\nFirst, we create our ORM models. This file (e.g., orm_models.py) defines the schema in code using SQLAlchemy’s declarative base.\n

python\nfrom sqlalchemy import Column, Integer, String, Text, create_engine\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom sqlalchemy.orm import sessionmaker\n\n# Base class for our ORM models\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\n# Create the engine (adjust connection string as needed)\nengine = create_engine('postgresql://user:password@localhost/dbname')\n\n# Create a configured "Session" class\nSession = sessionmaker(bind=engine)\n\n# Optionally, create tables in the database (for development)\nif __name__ == '__main__':\n Base.metadata.create_all(engine)\n

\n\n## 2. Bit-ORM Layer: Repository Using the ORM\n\nThis repository function uses the ORM—meaning we work with Python objects. The ORM layer automatically converts our model objects into the appropriate SQL queries. Save this in (e.g.) repository_orm.py.\n

python\nfrom orm_models import JobDescription, Session\n\ndef get_job_descriptions_by_company_orm(company_id: str):\n """\n Retrieve JobDescriptions for a given company using ORM.\n Returns a list of JobDescription objects.\n """\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\n# Example usage:\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 of the ORM Approach\n\nAbstraction & Simplicity: You deal with Python objects; the ORM hides the SQL details.\nMaintainability: Changes to models update all the underlying queries.\nSafety & Consistency: Automatic parameter binding prevents SQL injection.\nRelationship Handling: ORMs make it easier to navigate relationships among models.\n\n### Disadvantages of the ORM Approach\n\nAbstraction Overhead: Sometimes it can be less efficient if you need very fine-tuned queries.\nComplexity for Advanced Queries: Very complex queries or performance optimizations may require custom SQL.\n\n## 3. Bit-Out-ORM Layer: Repository Using Raw SQL\n\nIn contrast, this repository function uses raw SQL queries via SQLAlchemy’s connection API. Save this as repository_raw.py.\n

python\nfrom orm_models import engine\n\ndef get_job_descriptions_by_company_raw(company_id: str):\n """\n Retrieve job descriptions for a given company using raw SQL.\n Returns a list of RowProxy objects.\n """\n with engine.connect() as connection:\n # Use parameterized queries to avoid SQL injection\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\n# Example usage:\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\n### Advantages of the Raw SQL Approach\n\nFine-Grained Control: Directly write and optimize SQL queries for complex or performance-critical tasks.\nTransparency: You see exactly what SQL is sent to the database.\nFlexibility: Use database-specific features or functions not directly exposed by the ORM.\n\n### Disadvantages of the Raw SQL Approach\n\nManual Mapping: You must manually convert results to Python objects if needed.\nError-Prone: Writing raw SQL can lead to mistakes, and maintaining it over time may be more challenging.\nRepetition: Common CRUD operations might require writing repetitive SQL queries when an ORM could generate them automatically.\n\n## 4. Summary and Comparison\n\nIn a full-scale system, it’s common to use an ORM for most database operations while falling back on raw SQL for advanced use cases that require fine-tuning. This hybrid approach allows you to benefit from high productivity when possible while retaining control when needed.