SQLAlchemy - String replacement for complex queries

Question

I have a query that I know works:

SELECT title FROM paper WHERE MATCH (title) AGAINST ('+exact +representations' IN BOOLEAN MODE)

This returns any papers with both the words "exact" and "representations". Using SQLAlchemy's string replacement, I do:

keyword = '+exact +representations'
sql = 'SELECT title FROM paper WHERE MATCH (title) AGAINST (:keyword IN BOOLEAN MODE)'
conn = db.engine.connect()
t = conn.execute(text(sql), keyword=keyword)

This fails to match just papers with both keywords. Instead, it returns papers with either word.

My guess is that SQLAlchemy is mangling keyword in its preprocessing step. How can I use SQLAlchemy's string replacement feature but still do what I want?

Enable query logging `create_engine(..., echo=True)` to see what query is sent to MySQL. — plaes, Jun 16 '17 at 04:15
Have you tried it with your substitution in quotes `AGAINST (":keyword" IN`, as your original SQL has it in quotes. — Nigel Ren, Jun 16 '17 at 06:01
@NigelRen Placeholder substitution will handle quoting properly. No manual quoting required, which is the main selling point of using placeholders (not to mention not having to worry about SQL injections). — Ilja Everilä, Jun 16 '17 at 06:35
This'll not help you, but tried your exact query and keywords on test data including 'exact', 'representations', 'exact representations', 'exact representation', and 'ex representation'. It matched only the row with both words. I'd check that you're really running your query as you've written it here. It'd also help if you'd include a small test data set in your question and the outputs you're getting and what you expected instead. — Ilja Everilä, Jun 16 '17 at 06:47
@IljaEverilä, I did re-run the queries and got four rows for "exact representations". For what it's worth, that is the specification: https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html, e.g. `'apple banana'` will "find rows that contain at least one of the two words." — jds, Jun 17 '17 at 19:25
When you say got four rows for "exact representations", is that what you expected, or not? That query's missing the + prefixes that signal AND, so it should indeed return rows that match either word. In your question's example code the + prefixes are present, and that should (and did) return only rows matching both. — Ilja Everilä, Jun 17 '17 at 19:33
I misinterpreted what you meant by your test. You meant the test data was "exact", "representations", and so on, not the queries. Anyway, I think we're on the same page. When I add `+` prefixes, I get results that are either, not both. This may be a MySQL issue, though, since this is true if I just issue the query from Sequel Pro instead of SQLAlchemy. The second of these two solutions does work for me in Sequel Pro (https://stackoverflow.com/a/6717350/1830334) but I cannot use SQLAlchemy to do this. — jds, Jun 17 '17 at 19:55

score 0 · Accepted Answer · answered Jun 17 '17 at 21:20

I was able to get a solution using SQLAlchemy's built-in match function:

keyword = '"exact representations"'
ids += db.session.query(models.Paper)\
    .filter(models.Paper.title.match(keyword))\
    .all()

FWIW, I was not able to match exactly both words using '+exact +representations' per the MySQL spec. But this seems independent of SQLAlchemy.

SQLAlchemy - String replacement for complex queries

1 Answers1