sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near

Question

[Postgresql] I want to execute a SELECT query with an inserted variable to query from URL command to search some data in my database, when I'm passing variable to SELECT query everything is ok, but with it gives me an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "0380795272"

LINE 1: SELECT * FROM books WHERE title LIKE '%'0380795272'%'

^

routes.py:

@app.route("/search", methods=['GET']) 
def search():
    form = SearchForm()
    search_field = request.args.get('query')
    db_books = db1.execute("SELECT * FROM books WHERE title LIKE '%:search%'", {"search": search_field}).fetchall() //this is the line where I have an error
    if db_books:
        return render_template("index.html", title="index", db_books=db_books, form=form, search_field=search_field)
    else:
        flash('There is no such book', 'danger')
        return render_template("index.html", db_books=db_books, form=form)

models.py:

class Books(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    isbn = db.Column(db.String(100))
    title = db.Column(db.String(100))
    author = db.Column(db.String(100))
    year = db.Column(db.String(20))

    def __repr__(self):
        return f"Books('{self.isbn}', '{self.title}', '{self.author}')"

score 4 · Accepted Answer · answered May 05 '20 at 11:17

The database rejects the query because of the extra quote around the search term:

SELECT * FROM books WHERE title LIKE '%'0380795272'%'

The quotes are added because the bound parameter :search gets quoted automatically:

db1.execute("SELECT * FROM books WHERE title LIKE '%:search%'", {"search": search_field})

The solution is to add the "%" wildcards to the search term, rather than having them inside the query

db1.execute("SELECT * FROM books WHERE title LIKE :search",
            {"search": '%{}%'.format(search_field)})

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near

1 Answers1