INSERT INTO via SQLAlchemy/MySQL doesn't work when executed with parameters-CodePudding

I have a basic flask API with a POST method and others. It's running queries against a MySQL database. This is the relevant part of the code:

from flask import Flask, Response, request
from sqlalchemy.sql import text
from flask.views import MethodView
import json

class BooksAPI(MethodView):

    def post(self):
        sql = text("INSERT INTO books (title, year_written, author) VALUES (%s, %s, %s)")
        title = request.form['title']
        year_written = request.form['year']
        author = request.form['author']
        db.session.execute(sql, (title, year_written, author))
        db.session.commit()

        return Response(f'Successfully inserted book with title "{title}" by "{author}" of "{year_written}".', status=201, mimetype='application/json')

    # Omitting other methods

def route(app):
    view = BooksAPI.as_view('books_api')
    app.add_url_rule('/api/v1/book', 
        view_func=view, 
        methods=['POST',])

#  Omitting additional code for initializing and running the API.

The method is expected to insert a book when requesting for example via cURL:

curl -X POST 127.0.0.1:80/api/v1/book -d title="titleexample" -d author="authorexample" -d year=1234

However, I get the following traceback:

 * Serving Flask app 'app' (lazy loading)
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on all addresses.
   WARNING: This is a development server. Do not use it in a production deployment.
 * Running on http://172.17.0.2:80/ (Press CTRL C to quit)
[2022-05-11 12:08:43,126] ERROR in app: Exception on /api/v1/book [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s, %s, %s)' at line 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1518, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1516, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1502, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 84, in view
    return current_app.ensure_sync(self.dispatch_request)(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/flask/views.py", line 158, in dispatch_request
    return current_app.ensure_sync(meth)(*args, **kwargs)
  File "/app/./src/app.py", line 38, in post
    db.session.execute(sql, (title, year_written, author))
  File "<string>", line 2, in execute
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1696, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s, %s, %s)' at line 1")
[SQL: INSERT INTO books (title, year_written, author) VALUES (%%s, %%s, %%s)]
(Background on this error at: https://sqlalche.me/e/14/f405)

I tried hardcoding my values into the method and it works which I confirmed by a GET request:

class BooksAPI(MethodView):

    def post(self):
        sql = text("INSERT INTO books (title, year_written, author) VALUES ('titleexample', 1234, 'authorexample')")
        title = request.form['title']
        year_written = request.form['year']
        author = request.form['author']
        db.session.execute(sql)
        db.session.commit()

        return Response(f'Successfully inserted book with title "{title}" by "{author}" of "{year_written}".', status=201, mimetype='application/json')

So probably my parameters are not correctly written. What am I doing wrong?

CodePudding user response：

It looks like your values aren't making it into the %s portions of your string which is causing the problem.

From the documentation it seems that maybe you should change the way you label your variables in your text statement so that they're more directly connected. Setting it up like this may work based on what I've read:

def post(self):
    sql = text("INSERT INTO books (title, year_written, author) VALUES (:title, :year, :author)")
    title = request.form['title']
    year_written = request.form['year']
    author = request.form['author']
    db.session.execute(sql, {"title": title, "year": year_written, "author": author})
    db.session.commit()

    return Response(f'Successfully inserted book with title "{title}" by "{author}" of "{year_written}".', status=201, mimetype='application/json')

I've not tested this though, so there maybe typos, etc. Hope this helps!