Did you know that you can choose between using SQLAlchemy ORM and raw SQL queries for database interactions in Python? Let's explore how each approach could benefit your development.
Setup: Define Models with SQLAlchemy ORM
First, we create our ORM models. This file (e.g., orm_models.py) defines the schema in code using SQLAlchemy’s declarative base.
from sqlalchemy import Column, Integer, String, Text, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class JobDescription(Base):
__tablename__ = 'job_description'
id = Column(Integer, primary_key=True)
company_id = Column(String, nullable=False)
company_name = Column(Text, nullable=False)
job_description = Column(Text, nullable=False)
class CandidateResume(Base):
__tablename__ = 'candidate_resume'
id = Column(Integer, primary_key=True)
candidate_id = Column(String, nullable=False)
candidate_name = Column(Text, nullable=False)
resume = Column(Text, nullable=False)
engine = create_engine('postgresql://user:password@localhost/dbname')
Session = sessionmaker(bind=engine)
if __name__ == '__main__':
Base.metadata.create_all(engine)
Bit-ORM Layer: Repository Using the ORM
This 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.
from orm_models import JobDescription, Session
def get_job_descriptions_by_company_orm(company_id: str):
session = Session()
try:
results = session.query(JobDescription)\
.filter(JobDescription.company_id == company_id)\
.all()
return results
finally:
session.close()
if __name__ == '__main__':
jobs = get_job_descriptions_by_company_orm('COMPANY_123')
for job in jobs:
print(f"{job.company_name}: {job.job_description}")
Advantages of the ORM Approach
- Abstraction & Simplicity: You deal with Python objects; the ORM hides the SQL details.
- Maintainability: Changes to models update all the underlying queries.
- Safety & Consistency: Automatic parameter binding prevents SQL injection.
- Relationship Handling: ORMs make it easier to navigate relationships among models.
Disadvantages of the ORM Approach
- Abstraction Overhead: Sometimes it can be less efficient if you need very fine-tuned queries.
- Complexity for Advanced Queries: Very complex queries or performance optimizations may require custom SQL.
Bit-Out-ORM Layer: Repository Using Raw SQL
In contrast, this repository function uses raw SQL queries via SQLAlchemy’s connection API. Save this as repository_raw.py.
from orm_models import engine
def get_job_descriptions_by_company_raw(company_id: str):
with engine.connect() as connection:
result = connection.execute(
"SELECT id, company_id, company_name, job_description FROM job_description WHERE company_id = :company_id",
{"company_id": company_id}
)
return result.fetchall()
if __name__ == '__main__':
rows = get_job_descriptions_by_company_raw('COMPANY_123')
for row in rows:
print(f"{row['company_name']}: {row['job_description']}")
Advantages of the Raw SQL Approach
- Fine-Grained Control: Directly write and optimize SQL queries for complex or performance-critical tasks.
- Transparency: You see exactly what SQL is sent to the database.
- Flexibility: Use database-specific features or functions not directly exposed by the ORM.
Disadvantages of the Raw SQL Approach
- Manual Mapping: You must manually convert results to Python objects if needed.
- Error-Prone: Writing raw SQL can lead to mistakes, and maintaining it over time may be more challenging.
- Repetition: Common CRUD operations might require writing repetitive SQL queries when an ORM could generate them automatically.
Summary and Comparison
In 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.
Top comments (0)