INSERT INTO через SQLAlchemy/MySQL не работает при выполнении с параметрами

У меня есть базовый API фляги с методом POST и другими. Он выполняет запросы к базе данных MySQL. Это соответствующая часть кода:

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.

Ожидается, что метод вставит книгу при запросе, например, через cURL:

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

Однако я получаю следующую трассировку:

 * 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)

Я попытался жестко закодировать свои значения в методе, и он работает, что я подтвердил запросом GET:

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')

Так что наверное у меня параметры не правильно написаны. Что я делаю не так?

🤔 А знаете ли вы, что...
В Python есть множество библиотек и фреймворков для разработки веб-приложений.


31
1

Ответ:

Решено

Похоже, ваши значения не попадают в части %s вашей строки, которые вызывают проблему.

Из документации кажется, что, возможно, вам следует изменить способ маркировки ваших переменных в вашем текстовом выражении, чтобы они были более напрямую связаны. Такая настройка может работать на основе того, что я прочитал:

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')

Я не проверял это, поэтому могут быть опечатки и т. д. Надеюсь, это поможет!