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

[Anonymous]

У меня есть базовый API-интерфейс Flask с методом 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 "", 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')

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

Подробнее здесь: https://stackoverflow.com/questions/722 ... parameters