Flask中SQL更新浮点数/小数失效，无法保留两位小数求助

Hey there! Let's figure out why your SQL table is rounding up the cash value after stock purchases—this is a super common issue when dealing with monetary data, and we’ll fix it step by step.

核心问题分析 #

The root cause almost always boils down to two key areas: your database field type for cash is set to an integer (which can’t store decimals), or your code is accidentally converting the calculated amount to an integer before updating the database.

1. 检查并修改数据库字段类型 #

First, let’s confirm the cash column in your users table. If it’s set to INT (integer) or another non-decimal type, it will automatically round any decimal values up or down when you save them.

Fix: Change the field to a decimal type #

Update your users table to use a type designed for monetary values, like DECIMAL(10,2):

ALTER TABLE users ALTER COLUMN cash TYPE DECIMAL(10,2);

• DECIMAL(10,2) means the number can have up to 10 total digits, with 2 of them after the decimal point—perfect for storing currency values accurately.

2. 修正代码中的金额计算逻辑 #

Looking at your code snippet, you’re using int(100 * price[i] * shares[i]) then dividing by 100 to get the total. While this works for display, it can introduce precision issues when updating the database, especially if you’re passing integer values instead of decimals.

Better approach: Use Python’s decimal module for precise calculations #

Monetary values shouldn’t use regular floats (since floats can have hidden precision errors, like 0.1 + 0.2 = 0.30000000000000004). Instead, use the decimal module to handle calculations accurately:

from decimal import Decimal, getcontext

# Set precision to 2 decimal places for currency
getcontext().prec = 2

# When calculating total price for a stock purchase
price_per_share = Decimal(str(price[i]))  # Convert to Decimal to avoid float errors
shares_bought = Decimal(str(shares[i]))
total_purchase_amount = price_per_share * shares_bought

Update the cash value in the database correctly #

When updating the user’s cash, pass the precise decimal value directly to your SQL query (avoid converting to int at any step):

# Example update query (adjust to match your actual purchase logic)
db.execute(
    "UPDATE users SET cash = cash - :amount WHERE id = :userid",
    amount=float(total_purchase_amount),  # Or pass Decimal if your DB driver supports it
    userid=user_id
)

3. 确保前端/模板显示正确 #

When displaying the cash value in your template, format it to always show two decimal places to keep consistency:

<!-- In your HTML template -->
Available Cash: {{ "%.2f" % cash }}

Quick recap #

• Fix the database column type to DECIMAL(10,2) to store decimals properly
• Use Python’s decimal module for all monetary calculations to avoid precision loss
• Never convert the final amount to an integer before updating the database

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