如何将Python十进制转换为SQLite数字？

Question

我有一个程序可以读取 JSON 格式的财务数据并将其插入到 SQLite 数据库中。问题是当我将它插入 SQLite 数字列时，它似乎不喜欢 十进制 对象。

我发现这个问题之前回答过，但答案已经过时并且来自我的理解 SQLite 现在有一种货币数据类型称为数字。

现在作为一种解决方法，我将十进制值存储为文本，但是是否可以将其存储为数字？对于数据库插入和财务计算，我是否受困于将小数转换为字符串（反之亦然）的开销？

Answer 1

sqlite3 允许您注册一个适配器（在插入时透明地将 Decimals 转换为 TEXT）和一个转换器（以透明地转换 TEXT<获取时将 /code> 转换为 小数）。

以下是来自 文档的示例代码的轻微修改版本：

import sqlite3
import decimal
D=decimal.Decimal

def adapt_decimal(d):
    return str(d)

def convert_decimal(s):
    return D(s)

# Register the adapter
sqlite3.register_adapter(D, adapt_decimal)

# Register the converter
sqlite3.register_converter("decimal", convert_decimal)

d = D('4.12')

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(d decimal)")

cur.execute("insert into test(d) values (?)", (d,))
cur.execute("select d from test")
data=cur.fetchone()[0]
print(data)
print(type(data))

cur.close()
con.close()

产量

4.12
<class 'decimal.Decimal'>

Answer 2

SQLite 的列亲和性规则说：

如果列的声明类型包含任何字符串
“CHAR”、“CLOB”或“TEXT”则该列具有 TEXT 亲和性。注意
VARCHAR 类型包含字符串“CHAR”，因此被赋值
文本亲和力。

您可以将列声明为您喜欢的任何类型：

CREATE TABLE a_test(
  a_decimal DECTEXT NOT NULL  -- will be stored as TEXT
);

---

def adapt_decimal(d):
    return str(d)

def convert_decimal(s):
    return decimal.Decimal(s)

# Register the adapter
sqlite3.register_adapter(decimal.Decimal, adapt_decimal)

# Register the converter
sqlite3.register_converter("DECTEXT", convert_decimal)

con = sqlite3.connect("test.s3db", detect_types=sqlite3.PARSE_DECLTYPES)
C1 = con.cursor()

C1.execute("INSERT INTO a_test VALUES(?)", (decimal.Decimal("102.20"),))

不知道这是否是处理它的好方法 - 欢迎评论

Answer 3

我发现我必须对 unutbu 的方法进行一些小调整。对于值为“4.00”的修改示例，它从数据库中返回为“4”。我正在处理商品，不想将精度硬编码到数据库中（就像我只是乘以 100 并除以 100 时所做的那样）。因此，我对转换函数进行了如下调整：

def adapt_decimal(d):
    return '#'+str(d)

def convert_decimal(s):
    return D(s[1:])

这在美观上并不好，但确实挫败了 sqlite 将字段存储为整数的渴望，并失去了精度的跟踪。

Answer 4

至少您链接到的页面没有提及 currency 数据类型，并且 decimal(10,5) 示例数据类型 只需打开NUMERIC 亲和性。

如果是我的数据，我可能会存储整数个货币“单位”——便士，或十分之一便士，或百分之一便士，任何合适的——然后使用缩放因子< /em> 将整数输入转换为十进制，相当于从数据库读取数据时进行计算。搞砸整数就更难了。

Answer 5

我基本上遵循与其他人相同的方法，但有一点补充。问题是定义适配器和转换器会处理逐行访问十进制列的情况。但是，当您发出聚合函数时，例如对所有行的小数列求和，求和是使用实数（即浮点算术）完成的，并且返回的结果将是带有后续舍入误差的浮点数。

解决方案是创建一个定制的聚合函数，decimal_sum。由于sqlite3聚合函数受到它们可能返回的类型的限制，decimal_sum将返回十进制总和的字符串表示形式，这将是Decimal< /em> 类型（此代码也兼容 Python 3）：

import sqlite3
from decimal import Decimal


# DECTEXT columns will have TEXT affinity:
sqlite3.register_adapter(Decimal, lambda d: str(d))
sqlite3.register_converter("DECTEXT", lambda d: Decimal(d.decode('ascii')))


class DecimalSum:
    def __init__(self):
        self.sum = None

    def step(self, value):
        if value is None:
            return
        v = Decimal(value)
        if self.sum is None:
            self.sum = v
        else:
            self.sum += v

    def finalize(self):
        return None if self.sum is None else str(self.sum)


conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
conn.create_aggregate("decimal_sum", 1, DecimalSum)

cursor = conn.cursor()
cursor.execute("""
    create table test (
        amount DECTEXT not null
    )
""")

for _ in range(1000):
    cursor.execute("insert into test(amount) values(?)", (Decimal("12.01"),))
conn.commit()

# Uses floating point math:
cursor.execute("select sum(amount) from test")
row = cursor.fetchone()
print('Floating point sum:', row[0], type(row[0]))

# Uses decimal math but returns result as a string
# and so we do a final conversion from string to Decimal:
cursor.execute("select decimal_sum(amount) as `amount [dectext]` from test")
row = cursor.fetchone()
print('Decimal sum:', row[0], type(row[0]))

cursor.close()
conn.close()

打印：

Floating point sum: 12010.000000000178 <class 'float'>
Decimal sum: 12010.00 <class 'decimal.Decimal'>