Sling Academy
Home/SQLAlchemy/How to use LIKE operator in SQLAlchemy

How to use LIKE operator in SQLAlchemy

Last updated: January 04, 2024

Introduction

The LIKE operator in SQL is a powerful tool for pattern matching within strings. When using SQLAlchemy as an ORM in Python, leveraging the LIKE operator can greatly enhance searching capabilities in your databases. This tutorial covers how to effectively utilize the LIKE operator in SQLAlchemy with clear examples.

Getting Started

Before diving into the LIKE operator, make sure you have SQLAlchemy installed:

pip install sqlalchemy

And have a configured database with a table to work with. We’re going to assume a simple users table for the purposes of this tutorial.

Basic LIKE Queries

To start with, the LIKE operator can be used to find patterns in strings. In SQLAlchemy, it’s as simple as:

from sqlalchemy import create_engine, select, Table, Column, String, MetaData

# Define engine and metadata
engine = create_engine('sqlite:///mydatabase.db')
metadata = MetaData()

# Define table
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('username', String),
    Column('email', String)
)

# Query using LIKE
with engine.connect() as conn:
    s = select([users]).where(users.c.username.like('%john%'))
    result = conn.execute(s)
    for row in result:
        print(row)

This basic query will match any username containing the substring ‘john’.

Case-Insensitive Searches

In SQL, LIKE is case-sensitive by default. However, you might need to perform case-insensitive searches. This can be done by using the `ilike` method.

with engine.connect() as conn:
    s = select([users]).where(users.c.username.ilike('%john%'))
    result = conn.execute(s)
    for row in result:
        print(row)

This will match ‘John’, ‘john’, ‘JOHN’, and any other case variations.

Escaping Characters

Sometimes you need to search for strings that include the percentage (%) or underscore (_) symbols that are used for wildcard purposes in LIKE. To escape these characters in SQLAlchemy, you simply prefix them with a backslash (\).

with engine.connect() as conn:
    s = select([users]).where(users.c.username.like('100\% True'))
    result = conn.execute(s)
    for row in result:
        print(row)

Complex Patterns with Regular Expressions

For more advanced searching, you may need to perform pattern matching with regular expressions. This can be done with the `regexp` operator, if your database backend supports it.

with engine.connect() as conn:
    s = select([users]).where(users.c.username.op('regexp')('^[A-Za-z]+'))
    result = conn.execute(s)
    for row in result:
        print(row)

This query searches for usernames beginning with one or more letters.

Using the LIKE Operator with Joins

You can also use the LIKE operator in more complex queries that involve joins. Here’s an example with a join on a posts table.

posts = Table('posts', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', ForeignKey('users.id')),
    Column('content', String)
)

# Query using LIKE with a join
with engine.connect() as conn:
    s = select([users, posts]).select_from(users.join(posts)).\n        where(posts.c.content.like('%SQLAlchemy%'))
    result = conn.execute(s)
    for row in result:
        print(row)

This query looks for posts with ‘SQLAlchemy’ in the content and also retrieves the user data for those posts.

Conclusion

Throughout this tutorial, we’ve explored how to use the LIKE operator in SQLAlchemy to perform basic and advanced pattern matching in your queries. Starting with simple substring matches, case insensitive searches, and escaping characters, to utilizing regular expressions and complex joins, the LIKE operator is a versatile and essential part of querying databases with SQLAlchemy.

Next Article: How to use IN and NOT IN operators in SQLAlchemy

Previous Article: AND & OR Operators in SQLAlchemy

Series: SQLAlchemy Tutorials: From Basic to Advanced

SQLAlchemy

You May Also Like

  • SQLAlchemy: Counting rows for each category (2 approaches)
  • SQLAlchemy: Adding a calculated column to SELECT query
  • SQLAlchemy: Grouping data on multiple columns
  • SQLAlchemy: How to temporarily delete a row
  • SQLAlchemy Composite Indexes: The Complete Guide
  • Full-Text Search in SQLAlchemy: The Ultimate Guide
  • SQLAlchemy: What if you don’t close database connections?
  • SQLAlchemy: How to Remove FOREIGN KEY Constraints (2 Ways)
  • SQLAlchemy: How to Create and Use Temporary Tables
  • SQLAlchemy: Saving Categories and Subcategories in the Same Table
  • SQLAlchemy: How to Automatically Delete Old Records
  • Weighted Random Selection in SQLAlchemy: An In-Depth Guide
  • SQLAlchemy: created_at and updated_at columns
  • How to Use Regular Expressions in SQLAlchemy
  • SQLAlchemy: Ways to Find Results by a Keyword
  • SQLAlchemy: How to Connect to MySQL Database
  • SQLAlchemy: How to Bulk Insert Data into a Table
  • SQLAlchemy: How to Update a Record by ID
  • SQLAlchemy: Singular vs Plural Table Names