0

I am trying to run some database queries with SQLAlchemy. But I am having an issue querying for strings that have apostrophes or any kind of non-alphanumeric character.

For example: test = "I\'ve"

I used:

matchList = session.query(Quote).filter(Quote.quote.contains(test)).all()

There is 1 record in the database that contains "I've" as a substring. But, matchList is turning out to be an empty list. What am I doing wrong?

Full Code:

sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy_declarative import Base, Quote
from sqlalchemy_utils import escape_like

engine = create_engine("sqlite:///quotes.db")
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()

test = "I\'ve"
qList = session.query(Quote).filter(Quote.quote.contains(test.replace('\'', ''))).all()
print qList

It seems like SQLAlchemy doesn't handle escape sequence. Could anyone help

python
  • 4,403
  • 13
  • 56
  • 103

2 Answers2

2

The python string literal

"I\'ve"

Evaluates to the string

"I've"

However, you likely need an actual backslash in your database input, so try escaping the backslash instead of the single quote:

"I\\'ve"

All that said, there is probably a better way to escape queries in SQLAlchemy that I don't know about. Something like db.find(Quote.quote == "I've") where it does the escaping for you.

audiodude
  • 1,865
  • 16
  • 22
0

I just had the same issue, but escaping did not help because what I didn't realize was that was was being stored in the database was not the apostrophe I thought it was, but smart apostrophes instead. For some reason the Genius API was returning the smart apostrophes, and that is what I was adding to the database.

# In the database (smart apostrophe returned by Genius):
"Cat’s in the Cradle"

# What I was searching for (regular apostrophe)
"Cat's in the Cradle"

I think one possible solution is to replace the smart apostrophe with a regular one during the search.

raphael
  • 2,469
  • 2
  • 7
  • 19