DEV Community

Cover image for Did you know the two powerful ways in Python to interact with databases?
Negitama
Negitama

Posted on

Did you know the two powerful ways in Python to interact with databases?

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.

Image of Timescale

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.

Try free

Top comments (0)

Gen AI apps are built with MongoDB Atlas

Gen AI apps are built with MongoDB Atlas

MongoDB Atlas is the developer-friendly database for building, scaling, and running gen AI & LLM apps—no separate vector DB needed. Enjoy native vector search, 115+ regions, and flexible document modeling. Build AI faster, all in one place.

Start Free

👋 Kindness is contagious

Discover fresh viewpoints in this insightful post, supported by our vibrant DEV Community. Every developer’s experience matters—add your thoughts and help us grow together.

A simple “thank you” can uplift the author and spark new discussions—leave yours below!

On DEV, knowledge-sharing connects us and drives innovation. Found this useful? A quick note of appreciation makes a real impact.

Okay