Did you know… In Python, there are two powerful ways to interact with databases using SQLAlchemy: the ORM layer and raw SQL queries. Here’s a deep dive into both approaches and when to use each.\n\n# Setup: Define Models with SQLAlchemy ORM\nFirst, we create our ORM models. This file (e.g., orm_models.py) defines the schema in code 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\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# Bit-ORM Layer: Repository Using the ORM\nThis repository function uses the ORM—meaning we work with Python objects.\n\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- Abstraction & Simplicity: You deal with Python objects; the ORM hides the SQL details.\n- Maintainability: Changes to models update all the underlying queries.\n- Safety & Consistency: Automatic parameter binding prevents SQL injection.\n- Relationship Handling: ORMs make it easier to navigate relationships among models.\n\n## Disadvantages of the ORM Approach\n- Abstraction Overhead: Sometimes it can be less efficient if you need very fine-tuned queries.\n- Complexity for Advanced Queries: Very complex queries or performance optimizations may require custom SQL.\n\n# Bit-Out-ORM Layer: Repository Using Raw SQL\nIn contrast, this repository function uses raw SQL queries via SQLAlchemy’s connection API.\n\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- Fine-Grained Control: Directly write and optimize SQL queries for complex or performance-critical tasks.\n- Transparency: You see exactly what SQL is sent to the database.\n- Flexibility: Use database-specific features or functions not directly exposed by the ORM.\n\n## Disadvantages of the Raw SQL Approach\n- Manual Mapping: You must manually convert results to Python objects if needed.\n- Error-Prone: Writing raw SQL can lead to mistakes, and maintaining it over time may be more challenging.\n- Repetition: Common CRUD operations might require writing repetitive SQL queries when an ORM could generate them automatically.\n\n# Summary and Comparison\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.

Timescale – the developer's data platform for modern apps, built on PostgreSQL
Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)