4

I am having a very hard time using a LIKE statement in my Flask application to query my DB. I keep getting a syntax error.

My application is for searching for books and I need to be able to search all columns. I know I need to use LIKE but it seems that the single quotes from my variable are getting in the way. However, I am not 100% positive.

I know if I wanted to SELECT all from a table based on in id, the format would be like this:

id = request.form.get('id')
name = db.execute("SELECT first_name FROM users WHERE id=:id", {"id": current_id})

If I try this similar format with LIKE, such as:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()

I get this error:

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

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

From this error, I can see that single quotes are being added to my variable value (search) but I haven't been successful in stripping or replacing them. So I tried this format:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()

But that also gave me an error: AttributeError: 'list' object has no attribute 'keys'

I've been working on this for about four days and nothing I've tried has worked. I am at a loss. I know that I need to get those single quotes off, but I am so unsure how. I have a feeling that it's super easy, which is going to make me so sad but I am trying my best as a newbie.

Please let me know if you need any other information.

EDIT: Including tracebacks

Traceback for books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()

AttributeError
AttributeError: 'tuple' object has no attribute 'keys'

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 163, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1291, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1020, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1099, in _execute_clauseelement
keys = list(distilled_params[0].keys())
AttributeError: 'tuple' object has no attribute 'keys'
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object

Traceback for books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()

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

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

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
The above exception was the direct cause of the following exception:
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1277, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement
ret = self._execute_context(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context
self._handle_dbapi_exception(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
util.raise_(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
raise exception
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Raymond"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Raymond'%'
^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object
Improve this question
17
  • Please can you give the traceback for the last example? That looks like it should work to me Commented Jun 4, 2020 at 16:32
  • Please include the traceback in your question as an edit. It's no use to us in a word document Commented Jun 4, 2020 at 19:34
  • @roganjosh Very sorry! I have added the tracebacks as an Edit. Please let me know if anything else is needed. Commented Jun 4, 2020 at 20:33
  • No worries. Thanks for the edit; I'm current;y stumped on this one Commented Jun 4, 2020 at 20:38
  • Can you please try pip install sqlalchemy --upgrade in the cmd/powershell? Commented Jun 4, 2020 at 20:40

2 Answers 2

Reset to default
2

For parameterized queries you never need to have quotes inside the statement, they will be added by the db api. This should work:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE :search", {"search": '%' + search + '%'}).fetchall()

The % signs need to be part of the parameter, so that :search can finally be replaced with the quoted search string, as described here.

The error messages show what happens if the query already contains quotes: anohter set of qoutes will be added, breaking the query.

But note that this could also have problems as described here: backslashes in LIKE expresssions are treated as escape characters which can be used to treat wildcards into the passed string as their literal value, but if not used correctly can cause unexpected search results, or can enven cause an error (e.g. if the like pattern ends with a backslash).
If you don't intend to allow the user to insert wildcards in the search string, you'd need to escape them, or consider using the position() funciton instead: 

position(:search in author) > 0
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

But db.execute("SELECT * FROM books WHERE author LIKE %s" doesn't contain quotes around the placeholder, either?
But you db driver doesn't seem to use the "%s" param style, so that's why it doesn't work, but your example suggests it uses the :key param style.
I'm not the OP, so it's not my query. But I fully expect db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall() to work
And postgres/psycopg2 does use %s as the parameter
Sorry, my mistake. The orm layer (sqlalchemy) should unify that param style, so it doesn't really matter what the underlying driver uses.
|
0

format helps me in terms of this topic.

example:

query = "SELECT title FROM movies WHERE title LIKE '%{s}%'".format(s=Keyword) 

cursor.execute(query)

You can refer to this post.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.