Flask+Postman+MySQL多字段插入问题排查与优化建议

Hey there! Let's work through those multi-field insertion bugs you're hitting with Flask, Postman, and MySQL— I’ve got a step-by-step breakdown to fix each issue and optimize your code.

First, let's address the root cause that's triggering most of your errors: a misdefined database model. Your initial SQLAlchemy code sets name as an Integer primary key, which makes no sense if you're sending string usernames from Postman. That's almost certainly causing the "key name" errors. Let's fix that first, then tackle each specific bug.

1. Fix "Key name 'name' related errors" #

Step 1: Correct your Database Model/Table Structure #

Whether you use SQLAlchemy ORM or raw MySQL queries, your table needs to match the data you're sending. Here's the corrected setup:

Using SQLAlchemy (Recommended for Maintainability)

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash

app = Flask(__name__)
app.config['SECRET_KEY'] = 'thisissecret'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:1234@localhost/flask2018'
db = SQLAlchemy(app)

# Fixed Users model
class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # Auto-incrementing ID as primary key
    name = db.Column(db.String(80), unique=True, nullable=False)  # String username, unique to avoid duplicates
    password = db.Column(db.String(200), nullable=False)  # Longer field for hashed passwords

# Run this once to create the table (or use db.create_all() in your app)
with app.app_context():
    db.create_all()

@app.route('/users', methods=['POST'])
def create_user():
    data = request.get_json()
    
    # Validate required fields exist
    if not data or 'name' not in data or 'password' not in data:
        return jsonify({'error': 'Missing required fields: name or password'}), 400
    
    # Hash password before storing (never save plain text!)
    hashed_pw = generate_password_hash(data['password'], method='pbkdf2:sha256')
    
    new_user = Users(name=data['name'], password=hashed_pw)
    db.session.add(new_user)
    
    try:
        db.session.commit()
        return jsonify({'message': 'New user created!'}), 201
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 400

if __name__ == '__main__':
    app.run(debug=True, port=4022)

If You Prefer Raw MySQL Queries

First, ensure your users table is created with the right structure:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE,
    password VARCHAR(200) NOT NULL
);

Then fix your Flask code to reuse database connections (your original code creates multiple connections, causing inconsistencies):

from flask import Flask, jsonify, request
from flaskext.mysql import MySQL
from mysql.connector import errors
from werkzeug.security import generate_password_hash

app = Flask(__name__)
mysql = MySQL(app)

# MySQL Configs
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = '1234'
app.config['MYSQL_DATABASE_DB'] = 'flask2018'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

@app.route('/users', methods=['POST'])
def create_user():
    conn = mysql.connect()
    cur = conn.cursor()
    
    try:
        json_dict = request.get_json()
        if not json_dict or 'name' not in json_dict or 'password' not in json_dict:
            return jsonify({'error': 'Missing required fields: name or password'}), 400
        
        name = json_dict["name"]
        hashed_pw = generate_password_hash(json_dict["password"], method='pbkdf2:sha256')
        
        query = 'INSERT INTO users(name,password) VALUES(%s,%s)'
        cur.execute(query, (name, hashed_pw))
        conn.commit()
        return jsonify({'message': 'New user created successfully!'}), 201
    
    except errors.IntegrityError as e:
        conn.rollback()
        if "Duplicate entry" in str(e):
            return jsonify({'error': 'Username already exists!'}), 409
        else:
            return jsonify({'error': str(e)}), 400
    
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 400
    
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True, port=4022)

2. Fix "Duplicate Entry" Errors #

This happens because your name field is set to unique (a good practice for usernames). We added specific error handling for this in the code above— it catches the integrity error and returns a friendly "Username already exists" message instead of a generic crash.

3. Fix "401 Unauthorized" Errors #

Your initial code imports JWT libraries but doesn't implement authentication. If you want to secure your endpoint:

1. Add a token generation endpoint (e.g., /login to issue JWT tokens)
2. Add an authentication decorator to protect /users

Here's a quick implementation of the decorator:

import jwt
import datetime
from functools import wraps

def token_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        token = None
        # Expect token in header: Authorization: Bearer <your-token>
        if 'Authorization' in request.headers:
            token = request.headers['Authorization'].split(' ')[1]
        
        if not token:
            return jsonify({'message': 'Authentication token missing!'}), 401
        
        try:
            # Decode token (match the algorithm used when generating the token)
            data = jwt.decode(token, app.config['SECRET_KEY'], algorithms=["HS256"])
            # Optional: Fetch the user from the database to pass to the endpoint
            # current_user = Users.query.filter_by(id=data['user_id']).first()
        except:
            return jsonify({'message': 'Invalid authentication token!'}), 401
        
        return f(*args, **kwargs)
    return decorated

Then add the decorator to your /users endpoint:

@app.route('/users', methods=['POST'])
@token_required
def create_user():
    # Your endpoint code here

If you don't need authentication yet, just remove the JWT-related imports and code to eliminate 401 errors.

• Stick to One Database Tool: Don't mix SQLAlchemy and raw MySQL queries— SQLAlchemy's ORM will save you from connection bugs and make your code cleaner.
• Validate Input: Use libraries like marshmallow to enforce data types and rules for incoming requests.
• Use Environment Variables: Store database credentials and secret keys in environment variables instead of hardcoding them (use python-dotenv for this).

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