1

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?

Kurt Peek
  • 52,165
  • 91
  • 301
  • 526

2 Answers2

3

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.

univerio
  • 19,548
  • 3
  • 66
  • 68
  • 1
    For the sake of completeness, what worked for me was `from sqlalchemy.sql.expression import literal` followed by `e = session.query(exists().where(literal('johndoe').like('%' + User.url + '%'))).scalar()`, upon which `e` is `True`. (This actually also worked with 'real' URLs without any escaping). – Kurt Peek Jul 21 '17 at 09:32
1

You can use like 

e = session.query(exists().where(User.url.like("%{}%".format('put your string here')))).scalar()
SumanKalyan
  • 1,681
  • 14
  • 24
  • This doesn't work: if I run `e = session.query(exists().where(User.url.like('%johndoe%'))).scalar()`, then `e` is `False`, whereas I want it to be `True`. – Kurt Peek Jul 20 '17 at 16:01