-4

I'm trying to get my db.execute to work, managed to resolve the syntax error when using the LIKE operator along with a variable passed in from HTML, but it still doesn't give me results. (got closed by admin, so re-posting)

Used flask console to print and find out if any values passed at all, and it didn't. found variable not returning anything from dbExecute function

Why are my results not getting passed from db.execute?

My code extracted below:

@app.route("/search", methods=["POST"])
def search():
    """Search for books"""

    # best example, but no data passed:
    found = db.execute("SELECT * FROM books_table WHERE (isbn LIKE :lookingFor) OR (title LIKE :lookingFor) OR (title LIKE :lookingFor) OR (year::text LIKE :lookingFor)", {"lookingFor": f"%{searchBookVariableOnApplication_py}%"}).fetchall();

    return render_template("search.html", found=found)
nvs0000
  • 107
  • 1
  • 5
  • 1
    You're not interpolating your variable in the f string. You want `f"\"%{searchBookVariableOnApplication_py}%\""` – OregonTrail Mar 29 '20 at 05:40
  • Please copy the solution provided in your previous question *as is* (or like @oregontrail pointed out) and remove those extra escaped quotes (`\"`) you have introduced. – Ilja Everilä Mar 29 '20 at 06:18
  • Is `db` the engine from `create_engine`, or a connection from `Engine.connect()` for example? If so, remember to wrap your statement (the query) in a call to `sqlalchemy.text()` before passing to `execute()`, or SQLA will not handle the `:` named paramstyle conversion. Did you remove the extra `\"`? – Ilja Everilä Mar 29 '20 at 07:16
  • hey @IljaEverilä, yep i did. updated the code in the question. but gives the same results. I've linked to the repo as well. It's quite the puzzle... Is there another way to do this without Object Relation Model, sticking to postgreSQL? – nvs0000 Mar 29 '20 at 07:45
  • The link to your repo does not seem to work, which is a fine example of why you should put everything needed to reproduce the issue in the question itself, or in other words produce a [mcve]. From the screenshot it looks like you just get empty results, so double check your data and the search term. Remember that `LIKE` is *case sensitive*. – Ilja Everilä Mar 29 '20 at 08:00
  • This did it for me IIja, case sensativity in my search term. Thanks so much! – nvs0000 Mar 29 '20 at 08:01
  • Found this link which recommends using ILIKE to solve case sensitivity. [https://stackoverflow.com/a/19475095/7480366 ] – nvs0000 Mar 29 '20 at 08:18

2 Answers2

0

The problem here is that you are not binding your wildcard string literals correctly to your query string. You should be using a prepared statement here. Assuming you are using psycopg2, you may try:

import psycopg2

searchBook = request.form['searchBook']
sql = """SELECT *
         FROM books_table
         WHERE isbn LIKE %s OR title LIKE %s OR year::text LIKE %s"""
param = "%" + searchBook + "%"
found = db.execute(sql, (searchBook, searchBook, searchBook,))
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Note that they are already using a "prepared statement", just with SQLA named paramstyle that is converted to the underlying DB-API driver's paramstyle. – Ilja Everilä Mar 29 '20 at 06:21
  • @IljaEverilä I have never used Python before. I'm being honest. – Tim Biegeleisen Mar 29 '20 at 06:25
  • Did not mean to be pushy or rude, just wanted to point out that SQLA does handle the situation in this case. I quoted prepared statements because psycopg2 does not really use prepared statements internally, but handles escaping and interpolating variables in the Python driver. – Ilja Everilä Mar 29 '20 at 06:32
0

You're using an f-string in an attempt to use the variable searchBookVariableOnApplication_py, but not interpolating it within the f-string.

This:

{"lookingFor": f"\"%searchBookVariableOnApplication_py%\""}

Should be this:

{"lookingFor": f"\"%{searchBookVariableOnApplication_py}%\""}
OregonTrail
  • 8,594
  • 7
  • 43
  • 58