使用Python Flask/SQL Workbench/Postman创建POST API报错：tuple无encode属性

问题描述 #

我在SQL Workbench中有一个名为gem的表，已成功建立数据库连接。但通过Postman添加新宝石数据时，出现错误：'tuple' object has no attribute 'encode'，作为新手无法定位问题，请求帮助。

用到的代码文件 #

导入内容 #

import flask
from flask import jsonify
from flask import request
from sql import create_connection
from sql import execute_read_query
import creds

数据库连接文件sql.py内容 #

import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection

def execute_read_query(connection, query):
    cursor = connection.cursor(dictionary=True)
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")

POST接口代码 #

@app.route('/api/gem', methods=['POST'])
def add_example():
    request_data = request.get_json()
    newid = request_data['id']
    newgemtype = request_data['gemtype']
    newgemcolor = request_data['gemcolor']
    newcarat = request_data['carat']
    newprice = request_data['price']
    myCreds = creds.Creds()
    conn = create_connection(myCreds.conString, myCreds.userName, myCreds.password, myCreds.dbName)
    sql = "INSERT INTO gem(id, gemtype, gemcolor, carat, price) VALUES(%s, %s, %s, %s, %s)", (newid, newgemtype, newgemcolor, newcarat, newprice)
    gem = execute_read_query(conn, sql)
    results = []
    gem.append({'id': newid, 'gemtype': newgemtype, 'gemcolor': newgemcolor, 'carat': newcarat, 'price': newprice})
    return 'add request successful'  

错误原因分析 #

1. SQL语句构造错误：直接用逗号拼接SQL字符串和参数元组，生成了tuple类型值，但execute_read_query期望接收纯SQL字符串，cursor.execute执行时传入tuple触发编码错误。
2. 函数用途错误：execute_read_query是为查询操作设计的，调用cursor.fetchall()获取结果，但插入操作不需要查询结果。
3. 未提交事务：MySQL默认不自动提交事务，插入后不提交数据不会写入数据库。
4. 结果处理错误：插入操作通过查询函数执行会返回None，调用gem.append会触发新的报错。

修复步骤 #

第一步：新增写入操作函数 #

在sql.py中添加专门执行插入/更新/删除的函数：

def execute_write_query(connection, query, params=None):
    cursor = connection.cursor()
    try:
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        connection.commit()  # 提交事务
        print("Query executed successfully")
        return cursor.lastrowid  # 返回插入数据的ID（可选）
    except Error as e:
        connection.rollback()  # 出错回滚事务
        print(f"The error '{e}' occurred")
        return None

第二步：修改POST接口代码 #

修正SQL构造、使用正确函数、完善事务和结果处理：

@app.route('/api/gem', methods=['POST'])
def add_example():
    request_data = request.get_json()
    
    # 校验必要参数
    required_fields = ['id', 'gemtype', 'gemcolor', 'carat', 'price']
    if not all(k in request_data for k in required_fields):
        return jsonify({"error": "缺少必要参数"}), 400
    
    newid = request_data['id']
    newgemtype = request_data['gemtype']
    newgemcolor = request_data['gemcolor']
    newcarat = request_data['carat']
    newprice = request_data['price']
    
    myCreds = creds.Creds()
    conn = create_connection(myCreds.conString, myCreds.userName, myCreds.password, myCreds.dbName)
    
    if not conn:
        return jsonify({"error": "数据库连接失败"}), 500
    
    sql = "INSERT INTO gem(id, gemtype, gemcolor, carat, price) VALUES(%s, %s, %s, %s, %s)"
    params = (newid, newgemtype, newgemcolor, newcarat, newprice)
    
    # 执行插入操作
    result_id = execute_write_query(conn, sql, params)
    
    conn.close()  # 关闭连接
    
    if result_id is not None:
        return jsonify({
            "message": "添加请求成功",
            "gem": {
                'id': newid,
                'gemtype': newgemtype,
                'gemcolor': newgemcolor,
                'carat': newcarat,
                'price': newprice
            }
        }), 201
    else:
        return jsonify({"error": "添加宝石数据失败"}), 500

第三步：额外优化建议 #

• 新增参数类型校验，比如carat和price需为数值类型，避免数据库报错。
• 用try-except包裹核心逻辑，增强代码容错性。
• 考虑使用数据库连接池，替代每次请求新建连接的方式，提升性能。

内容的提问来源于stack exchange，提问作者AmberH