flask sqlalchemy execute sql query error

Question

can you help me to solve this error plz:

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

model.py :

    @staticmethod
def find_users(query):
    search_job = str(query)

    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 = %s
           """, [search_job]
    cursor = db.session.execute(sql )

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

views.py :

    @auth.route('/search' )
def search_user():
    search_query = request.args.get('query')
    users = User.find_users(search_query)

    return render_template("home/search_results.html",
                           query=search_query,
                           users=users)

home.html :

 <form class="navbar-form navbar-left" action="{{url_for('auth.search_user') }}">
                        <div class="form-group">
                          <input type="text" class="form-control" name="query" placeholder="Search">
                          <input type="hidden" name="index" value="All">
                        </div>
                        <button type="submit" class="btn btn-default">Search</button>
                      </form>

tha's all the code i use to execute this request, do you have any idea. thank you.

NB : when i give a value in my modele fonction it work.

jobs.description = %s -with-> jobs.description = "value"

Try replacing [search_job] with search_job. Can you simply check? — Nabin
Unfortunately @vishesshell removed their answer, but the root cause to your problems is that you're passing a tuple to execute(). Instead pass the SQL string and the parameters as separate arguments. Read Using Textual SQL carefully. SQLAlchemy offers you a DB-API driver agnostic handling of named placeholders (instead of for example %s placeholders). You are on the right path in that you have not used string formatting for forming the query. — Ilja Everilä
Nabin: I tested it before putting this post, but it did not work. I do not know why vishesshell removed his answer, I did not test it :(. @IljaEverilä i will test it and I give you an answer, I am not at home at the moment, thank you. — Abdellah ALAOUI ISMAILI
That'd be me, I critiqued some aspects of the answer – a bit harshly. Namely the 1st example where values would've been inlined to the query string using string formatting, which is pretty much always the wrong approach. — Ilja Everilä
In addition to the previous link, you should also of course read the documentation on Session.execute() as well. — Ilja Everilä

Abdellah ALAOUI ISMAILI Abdellah ALAOUI ISMAILI · Accepted Answer · 2017-08-12T18:18:24

thas is my solution in models.py and it works :)

@staticmethod
def find_handymans(query):
    search_job = str(query)

    sql_query =text("SELECT first_name,last_name, email, user_type,  phone, image, diplome, latitude, longitude, description FROM users, jobs WHERE users.id_job = jobs.id AND jobs.description = :x ")
    cursor = db.session.execute(sql_query,{'x' : search_job})

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

flask sqlalchemy execute sql query error

1 Answers