FastAPI提交表单至SQL Server遇HY104精度错误（AverageRate字段）

我用FastAPI提交包含用户、企业信息的表单到SQL Server数据库，用户信息能正常写入Users表，企业信息能写入Customers表，但Services表的AverageRate字段总是报HY104精度错误，没法插入数据。手动插入Services表数据是正常的，我试过在Python里转成Numeric、float、decimal类型，还是提交失败。

前端接收AverageRate的代码 #

<input type="number" name="service-rate-${service}" placeholder="Avg Rate (AUD)" min="0" step = "0.01">

后端Main.py相关代码 #

# FastAPI app instance
app = FastAPI()

# Allow CORS for frontend running on port 5500
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # Allow your frontend origin
    allow_credentials=True,
    allow_methods=["*"],  # Allow all HTTP methods (GET, POST, etc.)
    allow_headers=["*"],  # Allow all headers
)

# SQLAlchemy database URL
DATABASE_URL = f"mssql+pyodbc://BookingSystem"

# Create SQLAlchemy engine
engine = create_engine(DATABASE_URL)

# Create session maker
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
class Service(Base):
    __tablename__ = "services"

    ServiceID = Column(Integer, primary_key=True, index=True, autoincrement=True)
    CustomerID = Column(Integer, ForeignKey("Customers.CustomerID"), nullable=False)
    ServiceName = Column(String, nullable=False)
    AverageRate = Column(Numeric(10, 2))
    Amenities = Column(String, nullable=True)  # Comma-separated amenities

    # Relationship with Customer
    customer = relationship("Customer", back_populates="services")

@app.post("/register")
def register_business(data: dict, db: Session = Depends(get_db)):
    user_data = data.get("user", {})
    business_data = data.get("business", {})
    selected_services = data.get("services", [])  # List of selected services
    selected_amenities = data.get("amenities", [])  # List of selected amenities

    try:
        # Ensure required user and business data is provided
        if not user_data:
            raise HTTPException(status_code=400, detail="User data is missing")
        if not business_data:
            raise HTTPException(status_code=400, detail="Business data is missing")
        
        # Ensure BusinessTypeID is present
        if "BusinessTypeID" not in business_data:
            raise HTTPException(status_code=400, detail="BusinessTypeID is required")

        # **Create new user**
        new_user = User(
            FirstName=user_data["firstName"],
            LastName=user_data["lastName"],
            Username=user_data["username"],
            PasswordHash=user_data["password"]
            #PasswordHash=hash_password(user_data["password"])  # ✅ Hash password
        )
        db.add(new_user)
        db.commit()
        db.refresh(new_user)

        # **Create new business**
        new_business = Customer(
            BusinessName=business_data["BusinessName"],
            Location=business_data["Location"],
            Phone=business_data["Phone"],
            Email=business_data["Email"],
            ABN=business_data["ABN"],
            OwnerName=business_data["OwnerName"],
            BusinessTypeID=business_data["BusinessTypeID"]
        )
        db.add(new_business)
        db.commit()
        db.refresh(new_business)

        # **Map user to business**
        mapping = UserCustomerMapping(UserID=new_user.UserID, CustomerID=new_business.CustomerID)
        db.add(mapping)
        db.commit()

        # **Insert selected services**
        if not selected_services:
            raise HTTPException(status_code=400, detail="No services selected")

        for service in selected_services:
            # Ensure AverageRate is provided
            if "AverageRate" not in service:
                raise HTTPException(status_code=400, detail="AverageRate is required")

            try:
                average_rate_decimal = Decimal(service["AverageRate"])
            except Exception as e:
                raise HTTPException(status_code=400, detail=f"Invalid AverageRate format: {str(e)}")

            new_service = Service(
                CustomerID=new_business.CustomerID,
                ServiceName=service["ServiceName"],
                AverageRate=average_rate_decimal,  # Use the Decimal object
                Amenities=", ".join(selected_amenities)
            )
            db.add(new_service)

        db.commit() 

        return {"message": "Registration successful", "redirect": "login.html"}

    except KeyError as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=f"Missing required field: {e}")
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

错误情况 #

提交小数数值时触发HY104精度错误，手动执行SQL插入Services表数据则无异常。

解决方案 #

1. 强制Decimal精度与数据库匹配
   数据库中AverageRate定义为Numeric(10,2)，转换时需强制保留两位小数，避免因浮点数精度丢失导致的问题：

   from decimal import Decimal, ROUND_HALF_UP
   # 替换原有转换代码
   average_rate_decimal = Decimal(str(service["AverageRate"])).quantize(Decimal('0.00'), rounding=ROUND_HALF_UP)
   

   先转字符串再转Decimal，避免浮点数转Decimal时的精度偏差，同时用quantize确保数值符合数据库字段的小数位数要求。

2. 调整SQLAlchemy引擎参数
   创建引擎时添加decimal处理参数，确保SQLAlchemy与SQL Server的decimal类型交互兼容：

   engine = create_engine(DATABASE_URL, connect_args={"decimal_return_scale": 2})
   

3. 验证前端传入数据
   确认前端input传过来的数值没有多余小数位，可在前端提交前做一次格式化，确保数值最多保留两位小数。

4. 核对数据库字段定义
   再次确认SQL Server中Services表的AverageRate字段确实是NUMERIC(10,2)，没有被误配置为其他精度（如NUMERIC(5,1)），确保手动插入的SQL语句格式和代码提交的一致。

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