SQLAlchemy query where a column is a substring of another string

Question

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?

univerio · Accepted Answer · 2017-07-20 20:23:18Z

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.

answered Jul 20, 2017 at 20:23

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kurt Peek Over a year ago

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).

SumanKalyan · Accepted Answer · 2017-07-20 15:15:45Z

1

You can use like

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

answered Jul 20, 2017 at 15:15

SumanKalyan

1,77116 silver badges24 bronze badges

1 Comment

Kurt Peek Over a year ago

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.

Collectives™ on Stack Overflow

SQLAlchemy query where a column is a substring of another string

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related