flask execute mysql query with where expression

Question

I use this function to run an SQL query:

@staticmethod
def find_users(query):
    search_job = query
    # search_formatted = ' & '.join(words)

    sql = ("""SELECT first_name,last_name, email, phone, image, diplome, latitude, longitude, description
    FROM users, jobs
    WHERE users.id_job = jobs.id
    AND jobs.description = ?
           """, [search_job])
    cursor = db.session.execute(sql)
                                # {'search_terms': search_job})

    # Returns a list of product tuples
    return cursor.fetchall()

But I get this error

2017-08-05 18:54:18,421 INFO sqlalchemy.engine.base.Engine (4L,) 2017-08-05 18:54:18,424 INFO sqlalchemy.engine.base.Engine COMMIT 127.0.0.1 - - [05/Aug/2017 18:54:18] "GET / HTTP/1.1" 200 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/img/markers_shadow.png HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.woff2 HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.woff HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.ttf HTTP/1.1" 404 - [2017-08-05 18:54:23,162] ERROR in app: Exception on /auth/search [GET] Traceback (most recent call last): File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app response = self.full_dispatch_request() File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request rv = self.dispatch_request() File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request return self.view_functionsrule.endpoint File "/home/alaoui/Documents/ProjetHandy/handy_2/app/auth/views.py", line 194, in search_handyman handymans = User.find_handymans(search_query) File "/home/alaoui/Documents/ProjetHandy/handy_2/app/models.py", line 88, in find_handymans cursor = db.session.execute(sql) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1101, in execute clause = expression._literal_as_text(clause) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 4238, in _literal_as_text "instead" % type(element) ArgumentError: SQL expression object or string expected, got object of type instead

Have you run a debugger and verified that search_job is correct? — Shawn Mehan

Shawn Mehan Shawn Mehan · Accepted Answer · 2017-08-05T16:37:38

If you look at the signature for execute you find:

execute(clause, params=None, mapper=None, bind=None, **kw)

and then, looking at the doc, you find:

Parameters:

clause – An executable statement (i.e. an Executable expression such as expression.select()) or string SQL statement to be executed.
params – Optional dictionary, or list of dictionaries, containing bound parameter values. If a single dictionary, single-row execution occurs; if a list of dictionaries, an “executemany” will be invoked. The keys in each dictionary must correspond to parameter names present in the statement.
mapper – Optional mapper() or mapped class, used to identify the appropriate bind. This argument takes precedence over clause when locating a bind. See Session.get_bind() for more details.
bind – Optional Engine to be used as the bind. If this engine is already involved in an ongoing transaction, that connection will be used. This argument takes precedence over mapper and clause when locating a bind.
**kw – Additional keyword arguments are sent to Session.get_bind() to allow extensibility of “bind” schemes.

Use a debugger to step through your code and look at what you are actually passing as params against that signature. You will find that your second param, [search_job] is not satisfying any of those expected params, and hence you get

ArgumentError: SQL expression object or string expected, got object of type instead

flask execute mysql query with where expression

1 Answers