Introduction to SQLAlchemy

 

What is SQLAlchemy?

SQLAlchemy is a powerful Python library that allows developers to interact with databases using Python code instead of writing raw SQL queries.

It provides two main approaches:

  • ORM (Object Relational Mapping) → Work with Python classes
  • Core (SQL Expression Language) → Write SQL-like queries in Python

Why SQLAlchemy is So Popular

Here’s why developers love SQLAlchemy:

  • Works with multiple databases (MySQL, PostgreSQL, SQLite)
  • Reduces boilerplate SQL code
  • Flexible (ORM + raw SQL support)
  • Production-ready and scalable
  • Used in frameworks like FastAPI

SQLAlchemy Architecture (Simple Explanation)

Application (Python Code)
        ↓
SQLAlchemy ORM/Core
        ↓
Database (SQLite/MySQL/PostgreSQL)

Installation

pip install sqlalchemy

Your First SQLAlchemy Program
from sqlalchemy import create_engine

# Create database engine
engine = create_engine('sqlite:///example.db')

# Connect to database
connection = engine.connect()

# Execute SQL
result = connection.execute("SELECT 'Hello, SQLAlchemy!'")

for row in result:
    print(row)
Output:
Hello, SQLAlchemy!

Key Concepts You Must Know

1. Engine

  • The entry point to the database
  • Manages connections 
engine = create_engine('sqlite:///example.db')

2. Connection

  • Used to execute raw SQL 
conn = engine.connect()

ORM vs Core

Feature        ORMCore
Syntax            Python classes            SQL-like
Use Case            App development            Complex queries
Learning curve            Easy            Moderate

Real-World Use Case

Imagine building:

  • E-commerce app
  • Blog system
  • Banking system

Instead of writing:

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

You write:

user = User(name="John", email="john@example.com")
session.add(user)

Common Beginner Mistakes

  • Confusing Engine with Session
  • Forgetting to commit transactions
  • Mixing ORM and raw SQL incorrectly

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more