SqlAlchemy Programming Error when using raw SQL Like operator

Question

I am trying to run a SQL query in my Flask application to search a database. However, I get an error when I run the query:

db.execute("SELECT * FROM books WHERE author LIKE '%:author%' ", {"author":query})

The resulting error is this (passed 'Tom' in my input):


sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Tom"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Tom'%' 
                                                 ^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(author)s%%' ]
[parameters: {'author': 'Tom'}]
(Background on this error at: http://sqlalche.me/e/f405)

I've tested the query with a hard coded value and it works fine. I would like to know what is causing the error and how to fix it.

score 2 · Accepted Answer · answered Jun 14 '20 at 23:15

You need string concatenation. Many SQL databases support concat() (and other have an equivalent function or operator, such as standard operator ||):

db.execute("SELECT * FROM books WHERE author LIKE CONCAT('%', :author, '%')", {"author":query})

Another option is to concatenate '%'s around your parameter in your application first, and then pass it to the query.

SqlAlchemy Programming Error when using raw SQL Like operator

1 Answers1