Introduction
SQLAlchemy provides a robust way to interact with databases in Python. Filtering rows by checking if a column is NULL or empty is a common requirement. In this tutorial, we’ll master how to execute this using SQLAlchemy elegantly.
Setting Up Our Environment
Before proceeding, ensure you have the following prerequisites installed:
- Python (3.x recommended)
- SQLAlchemy
- A database engine (e.g., SQLite, PostgreSQL)
To install SQLAlchemy, run:
pip install SQLAlchemy
For this tutorial, we’ll use the SQLite database for simplicity.
Defining Our Model
First, we need to define our model representing the table we’ll query against.
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
Adding Dummy Data
Let’s add some example data into our User table for our queries.
users = [
User(name='Alice', email='[email protected]'),
User(name='Bob', email=None),
User(name='', email='[email protected]'),
User(name=None, email='[email protected]')
]
session.bulk_save_objects(users)
session.commit()
Basic Query for NULL or Empty Fields
To find rows where a certain column is NULL or empty, we use or_ and == constructs.
from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import or_
with Session(engine) as session:
null_or_empty_emails = session.query(User).filter(or_(User.email == None, User.email == '')).all()
for user in null_or_empty_emails:
print(f'{user.name} {user.email}')
This will find any users where the email attribute is either NULL or an empty string.
Advanced Use Case: Handling Whitespace and Custom Conditions
In more advanced scenarios, we may want to consider strings that contain only whitespace as empty. SQLAlchemy’s func offers the ability to invoke database functions such as TRIM.
from sqlalchemy import func
with Session(engine) as session:
null_empty_or_whitespace_emails = session.query(User).filter(or_(User.email == None, func.trim(User.email) == '')).all()
for user in null_empty_or_whitespace_emails:
print(f'{user.name} {user.email}')
Using func.trim, we treat any user with an email consisting solely of whitespace as an empty string.
Conclusion
In this tutorial, we’ve learned to select rows with NULL or empty fields using SQLAlchemy, progressing from basic to advanced techniques. Applying these methods allows for effective data manipulation and retrieval in database-related Python projects.