CODEWITHSUNDEEP
pythonsqlalchemy
Kurt Peek
Kurt Peek

Reputation: 57471

SQLAlchemy query where a column is a substring of another string

This question is similar to SQLAlchemy query where a column contains a substring, but the other way around: I'm trying to query a column containing a string which is a sub-string of another given string. How can I achieve this?

Here is a code example of a database set up using the ORM:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import exists

engine = create_engine('sqlite:///:memory:')
Base = declarative_base()

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

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
    User(url='john', fullname='John Doe'),
    User(url='mary', fullname='Mary Contrary')
])

session.commit()

The following works:

e = session.query(exists().where(User.url == 'john')).scalar()

upon which e has the value True. However, I would like to do something like

e = session.query(exists().where(User.url in 'johndoe')).scalar()

where in is in the sense of the __contains__ method of Python's string type. Is this possible?

Upvotes: 1

Views: 2556

Answers (2)

univerio
univerio

Reputation: 20518

It's just like (heh) the linked question, except you turn it around:

SELECT ... WHERE 'johndoe' LIKE '%' || url || '%';

You'll need to take care to escape special characters if you've got those in your table:

SELECT ... WHERE 'johndoe' LIKE '%' || replace(replace(replace(url, '\', '\\'), '%', '\%'), '_', '\_') ESCAPE '\';

In SQLAlchemy:

escaped_url = func.replace(func.replace(func.replace(User.url, "\\", "\\\\"),
                                        "%", "\\%"),
                           "_", "\\_")
session.query(... .where(literal("johndoe").like("%" + escaped_url + "%", escape="\\")))

Note the escaped backslashes in Python.

Upvotes: 3

SumanKalyan
SumanKalyan

Reputation: 1761

You can use like 

e = session.query(exists().where(User.url.like("%{}%".format('put your string here')))).scalar()

Upvotes: 1

Related Questions