执行数据库查询时出现“TypeError: not all arguments converted during string formatting”错误。

Question

我正在尝试使用MySQLdb查询我的数据库，但是当我发送带参数的查询时，我会收到“TypeError：not all arguments converted during string formatting”的错误。我认为这与查询中的“%s”有关。我该如何解决这个错误？

“Main.py”文件：

from flask import Flask, request
from app.config import DB
from app.items.items import ItemsAPI

app = Flask(__name__)
db = DB()

app.register_blueprint(ItemsAPI)

@app.route('/home')
def hello_world():
    return "Welcome to Omnimoda."

@app.route('/dbtest', methods=['GET'])
def hello_database():
    q_sql = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '%s'"
    a_sql = "omnimoda"
    test_request = db.query(q_sql, a_sql)
    result_request = test_request.fetchall()
    if (result_request is None):
        return "Database does not exist."
    else:
        return "Database exists."

Items.py:

from flask import Flask, request, jsonify, json, Blueprint
from app.config import DB

ItemsAPI = Blueprint('ItemsAPI', __name__)
db = DB()

@ItemsAPI.route('/items/listbycode', methods=['POST'])
def get_item_by_code():
    value = request.form['value']
    q_list_all = "SELECT * FROM item_info WHERE item_code = '%s'"
    print q_list_all
    a_list_all = (value)
    items_request = db.query(q_list_all, a_list_all)
    json_output = json.dumps(items_request.fetchall())
    return json_output

config.py:

from flask import Flask
import MySQLdb

class DB:
    conn = None

    def connect(self):
        self.conn = MySQLdb.connect(host="localhost", user="user", passwd="passwd", db="test_dbase")
        self.conn.autocommit(True)

    def query(self, sql, values):
        try:
            print values
            cursor = self.conn.cursor()
            cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            cursor.execute(sql)
        return cursor

追踪信息：

Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Library/Python/2.7/site-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/lanceguinto/Documents/Project_Omnimoda/API/app/items/items.py", line 26, in get_item_by_code
    items_request = db.query(q_list_all, a_list_all)
  File "/Users/lanceguinto/Documents/Project_Omnimoda/API/app/config.py", line 15, in query
    cursor.execute(sql, values)
  File "build/bdist.macosx-10.10-intel/egg/MySQLdb/cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

- zack_falcon

Answer 1

cursor.execute 期望其第二个参数为列表

q_list_all = "SELECT * FROM item_info WHERE item_code = '%s'"
print q_list_all
a_list_all = (value,) #the comma makes it a list not the parens
items_request = db.query(q_list_all, a_list_all)

当您向其传递字符串时

cur.execute(qry,"MyArg") # =becomes==> cur.execute(qry,["M","y","A","r","g"])

顺便提一下，你应该考虑只使用ORM（对象关系映射）库，比如SQLAlchemy。

- Joran Beasley

---

我得到了通常的“您的SQL语法有误”的提示。直到我去掉了%s周围的单引号，它才正常工作。谢谢！ - zack_falcon

逗号使其成为列表，而不是括号 - 这个语句对我很有帮助。 - Ganesh Kanna

Answer 2

(value) 不是元组，它只是一个字符串。而字符串是其字符的可迭代对象。因此，实际上就好像您将输入字段中的字符列表传递到仅有一个参数的SQL语句中一样。

请改用 (value,)。

- Daniel Roseman

---

啊，你比我快了4秒钟... :) +1 很棒的答案。 - Joran Beasley