SQLAlchemy is a powerful Object-Relational Mapping (ORM) tool for Python that simplifies database interactions. Instead of writing raw SQL queries, SQLAlchemy allows developers to interact with databases using Python classes and objects.
1. Why Use SQLAlchemy?
Eliminates Manual SQL Writing – Work with Python objects instead of raw queries.
Database Independence – Supports multiple databases (SQLite, MySQL, PostgreSQL).
Security – Protects against SQL injection.
Flexibility – Provides both ORM and Core (SQL-like syntax).
2. Installing SQLAlchemy
pip install sqlalchemy
For MySQL or PostgreSQL, install additional drivers:
pip install pymysql # MySQL
pip install psycopg2 # PostgreSQL
3. Connecting to a Database
Example: SQLite Connection
from sqlalchemy import create_engine
# Create an SQLite database (or connect if it exists)
engine = create_engine("sqlite:///mydatabase.db")
print("Database connected successfully!")
✅ The engine object handles communication with the database.
Other Database Connections
- MySQL:
create_engine("mysql+pymysql://user:password@localhost/mydb") - PostgreSQL:
create_engine("postgresql+psycopg2://user:password@localhost/mydb")
4. Defining ORM Models
Create a Database Table Using ORM
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
# Base class for ORM models
Base = declarative_base()
class User(Base):
__tablename__ = "users" # Table name
id = Column(Integer, primary_key=True) # Primary Key
name = Column(String(50), nullable=False)
email = Column(String(100), unique=True, nullable=False)
# Create tables in the database
Base.metadata.create_all(engine)
print("Table created successfully!")
Base is the foundation for all ORM classes.
Base.metadata.create_all(engine) generates tables automatically.
5. Creating a Database Session
To interact with the database, we need a session:
from sqlalchemy.orm import sessionmaker
# Create a session factory
Session = sessionmaker(bind=engine)
session = Session()
session handles transactions with the database.
6. Inserting Data into the Database
Add a Single Record
new_user = User(name="John Doe", email="john@example.com")
session.add(new_user) # Add to session
session.commit() # Save changes
print("User added successfully!")
Add Multiple Records
users = [
User(name="Alice", email="alice@example.com"),
User(name="Bob", email="bob@example.com")
]
session.add_all(users) # Add multiple users
session.commit()
print("Multiple users added successfully!")
commit() permanently saves the changes.
rollback() can undo changes before committing.
7. Querying Data from the Database
Fetch All Records
users = session.query(User).all()
for user in users:
print(user.name, user.email)
Fetch a Single Record
user = session.query(User).filter_by(name="Alice").first()
print(user.email)
Filter with Conditions
users = session.query(User).filter(User.name.like("%o%")).all()
for user in users:
print(user.name)
filter() supports operators like ==, >, <, like, in_.
8. Updating Data in the Database
Update a Record
user = session.query(User).filter_by(name="Alice").first()
user.email = "alice_new@example.com"
session.commit()
print("User updated successfully!")
Changes take effect after commit().
9. Deleting Data from the Database
Delete a Record
user = session.query(User).filter_by(name="Bob").first()
session.delete(user)
session.commit()
print("User deleted successfully!")
Deleting a record requires commit() to apply changes.
10. Using Relationships in ORM
Define a One-to-Many Relationship
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
content = Column(String(500))
user_id = Column(Integer, ForeignKey("users.id")) # Foreign key linking to User
user = relationship("User", back_populates="posts")
User.posts = relationship("Post", order_by=Post.id, back_populates="user")
Base.metadata.create_all(engine)
ForeignKey("users.id") creates a relationship between Post and User.
relationship() establishes a Python-side link.
Insert Data with Relationships
user = session.query(User).filter_by(name="John Doe").first()
new_post = Post(title="My First Post", content="Hello World!", user=user)
session.add(new_post)
session.commit()
Query Data with Relationships
user = session.query(User).filter_by(name="John Doe").first()
for post in user.posts:
print(post.title, "-", post.content)
Relationships simplify fetching related data without additional queries.
11. Indexing and Performance Optimization
Add an Index to a Column
from sqlalchemy import Index
Index("idx_email", User.email)
Indexes improve query performance for large datasets.
12. Exporting and Importing Data
Export Data to JSON
import json
users = session.query(User).all()
data = [{"name": user.name, "email": user.email} for user in users]
with open("users.json", "w") as file:
json.dump(data, file, indent=4)
print("Data exported successfully!")
Import Data from JSON
with open("users.json", "r") as file:
users = json.load(file)
for user in users:
session.add(User(name=user["name"], email=user["email"]))
session.commit()
print("Data imported successfully!")
Useful for data backups and migrations.
13. Closing the Database Connection
session.close()
print("Database session closed!")
Always close sessions after use.