FreeTDS 将 MS SQL money 类型转换为 Python float，而不是 Decimal

在 Linux 中使用 Python 连接到 MS SQL Server 数据库时，通过 pyodbc 和 FreeTDS 驱动程序进行连接。在从 MSSQL 返回 money 字段时，将其表示为 float，而不是 Python Decimal。

2、解决方案

方法一：

def convert_money_to_decimal(value):
    """
    Converts a money value to a Python Decimal.

    Args:
        value: The money value to convert.

    Returns:
        The converted Python Decimal.
    """

    # Check if the value is None
    if value is None:
        return None

    # Convert the value to a string
    value_str = str(value)

    # Check if the value is negative
    negative = False
    if value_str[0] == '-':
        negative = True
        value_str = value_str[1:]

    # Split the value into dollars and cents
    dollars, cents = value_str.split('.')

    # Convert the dollars and cents to integers
    dollars = int(dollars)
    cents = int(cents)

    # Create a Python Decimal from the dollars and cents
    decimal = dollars + (cents / 100)

    # Apply the negative sign if necessary
    if negative:
        decimal = -decimal

    # Return the Python Decimal
    return decimal

在代码中使用 convert_money_to_decimal() 方法将 money 值转换为 Python Decimal：

import pyodbc

# Connect to the database
connection = pyodbc.connect('DRIVER={FreeTDS};SERVER=localhost;DATABASE=AdventureWorks2019;UID=sa;PWD=password')

# Create a cursor
cursor = connection.cursor()

# Execute a query that returns a money value
cursor.execute('SELECT TOP 1 [Product].[ListPrice] FROM [Production].[Product]')

# Get the money value from the first row of the result set
money_value = cursor.fetchone()[0]

# Convert the money value to a Python Decimal
decimal_value = convert_money_to_decimal(money_value)

# Print the Python Decimal
print(decimal_value)

方法二：

import decimal

def convert_money_to_decimal(value):
    """
    Converts a money value to a Python Decimal.

    Args:
        value: The money value to convert.

    Returns:
        The converted Python Decimal.
    """

    # Convert the value to a string
    value_str = str(value)

    # Check if the value is negative
    negative = False
    if value_str[0] == '-':
        negative = True
        value_str = value_str[1:]

    # Split the value into dollars and cents
    dollars, cents = value_str.split('.')

    # Convert the dollars and cents to integers
    dollars = int(dollars)
    cents = int(cents)

    # Create a Python Decimal from the dollars and cents
    decimal = decimal.Decimal(f'{dollars}.{cents}')

    # Apply the negative sign if necessary
    if negative:
        decimal = -decimal

    # Return the Python Decimal
    return decimal

在代码中使用 convert_money_to_decimal() 方法将 money 值转换为 Python Decimal：

import decimal
import pyodbc

connection = pyodbc.connect('ドライバー={FreeTDS}；サーバー=localhost;データベース=AdventureWorks2019；UID=sa;PWD=パスワード')
cursor = connection.cursor()
cursor.execute('SELECT TOP 1 [Product].[ListPrice] FROM [Production].[Product]')
money_value = cursor.fetchone()[0]
decimal_value = convert_money_to_decimal(money_value)
print(decimal_value)

通过以上方法，可以在 Linux 中使用 Python 连接到 MS SQL Server 数据库时，将 money 字段返回为 Python Decimal。