如何修复错误：(psycopg2.errors.NotNullViolation)“id”列中的空值违反非空约束？

为什么会出现下面的错误？我知道db.session.add()方法但是我想使用原始 SQL 来学习。 UUID 不应该自动生成吗？也许我在 postgresql 文本中遗漏了一些内容或者模型中的 id 不正确？

error

Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, testuser, [email protected] /cdn-cgi/l/email-protection, pASsWorD, null, 2021-01-10 16:13:23.270353-08).

模型.py

from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy import DateTime
from sqlalchemy.sql import func

from .. import db

class Users(db.Model):
    __tablename__ = 'users'

    id = db.Column(
        UUID(as_uuid=True),
        primary_key=True,
        unique=True,
    )
    username = db.Column(
        db.String(120),
        unique=True,
        nullable=False
    )
    email = db.Column(
        db.String(120),
        unique=True,
        nullable=False
    )
    password = db.Column(
        db.String(120),
        nullable=False
    )
    updated_at = db.Column(
        DateTime(timezone=True),
        onupdate=func.now()
    )
    created_at = db.Column(
        DateTime(timezone=True),
        server_default=func.now()
    )

api.py

from flask import Blueprint, request, jsonify
from sqlalchemy import text, exc
from types import SimpleNamespace
from ...models.users import db, Users

bp = Blueprint('api', __name__, url_prefix='/api/v1')

@bp.route('/users', methods=['POST'])
def users():
    if request.method == 'POST':
        try:
            response = dict(request.get_json())
            values = SimpleNamespace(**response)

            if all(response.values()):
                sql_insert_one = text(f"INSERT INTO users(username, email, password) VALUES ('{values.username}', '{values.email}', '{values.password}');")
                db.session.execute(sql_insert_one)
                db.session.commit()
                message = f"successfully added new user: {values.username}"
            else:
                message = f"error adding new user, values submitted: {values}"

            return jsonify(response)
        except Exception as err:
            print(f'Error: {err}') 
            return jsonify(f'"error":"{err}"')

回答你的问题“UUID 不应该自动生成吗？”不。来自 postgresql 文档：“核心数据库不包含任何生成 UUID 的函数”ref https://www.postgresql.org/docs/9.1/datatype-uuid.html.

你需要自己生成UUID，尝试一下uuid https://docs.python.org/3/library/uuid.html.

或者你可以使用db.Integer代替db.UUIDSQLAlchemy 会将其设为一个序列，并自动生成id value.