How to implement dynamic sql using sqlalchemy in Python

Using SQLAlchemy to implement dynamic SQL in Python, flexible queries can be built in several different ways. SQLAlchemy is a powerful ORM (Object Relational Mapping) tool that provides multiple ways to generate SQL queries, including dynamic SQL. Here are some common methods:

1. Use filter and filter_by

You can add filters dynamically based on conditions.filterandfilter_byMethods can accept multiple parameters and can be called chained.

from sqlalchemy import create_engine, Column, Integer, String
from  import declarative_base
from  import sessionmaker

# Create a database engineengine = create_engine('sqlite:///')

# Create a sessionSession = sessionmaker(bind=engine)
session = Session()

# Define the modelBase = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Dynamic build querydef get_users(filters):
    query = (User)
    
    if 'name' in filters:
        query = ( == filters['name'])
    
    if 'age' in filters:
        query = ( == filters['age'])
    
    return ()

# Sample callfilters = {'name': 'Alice', 'age': 30}
users = get_users(filters)
for user in users:
    print(, )

2. Use and_ and or_

If you need more complex logic combinations, you can useand_andor_function.

from sqlalchemy import and_, or_

def get_users(filters):
    query = (User)
    
    conditions = []
    if 'name' in filters:
        ( == filters['name'])
    
    if 'age' in filters:
        ( == filters['age'])
    
    if conditions:
        query = (and_(*conditions))
    
    return ()

# Sample callfilters = {'name': 'Alice', 'age': 30}
users = get_users(filters)
for user in users:
    print(, )

3. Use text to perform raw SQL query

If you need more flexible controls, you can usetextMethod to write original SQL queries.

from sqlalchemy import text

def get_users(filters):
    query = "SELECT * FROM users WHERE 1=1"
    
    params = {}
    if 'name' in filters:
        query += " AND name = :name"
        params['name'] = filters['name']
    
    if 'age' in filters:
        query += " AND age = :age"
        params['age'] = filters['age']
    
    result = (text(query), params)
    return ()

# Sample callfilters = {'name': 'Alice', 'age': 30}
users = get_users(filters)
for user in users:
    print(, )

4. Use lambda expressions

For more advanced usage, you can uselambdaExpressions andlambdasModule to build dynamic queries.

from sqlalchemy import lambda_
from  import aliased

def get_users(filters):
    query = (User)
    
    if 'name' in filters:
        query = (lambda:  == filters['name'])
    
    if 'age' in filters:
        query = (lambda:  == filters['age'])
    
    return ()

# Sample callfilters = {'name': 'Alice', 'age': 30}
users = get_users(filters)
for user in users:
    print(, )

Summarize

All of the above methods can help you implement dynamic SQL queries in SQLAlchemy. Which method to choose depends on your specific needs and preferences. For simple filtering conditions, usefilterandfilter_byIt is enough; for more complex logic, you can consider using itand_andor_Or write original SQL queries directly.

This is the end of this article about how Python uses sqlalchemy to implement dynamic sql. For more related content on Python sqlalchemy to implement dynamic sql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!