如何将Python小数转换为SQLite数值？

Question

我有一个程序，它可以读取以JSON格式存储的财务数据，并把这些数据插入到SQLite数据库中。现在遇到的问题是，当我把数据插入到SQLite的数字列时，它似乎不喜欢decimal对象。

我发现之前有人问过这个问题并得到了回答，但那个答案已经过时了。根据我的理解，SQLite现在有一种新的数据类型叫做numeric，专门用于存储货币。

目前，我的解决办法是把小数值存储为文本，但我想知道是否可以把它存储为数字？我是不是只能忍受在数据库插入和财务计算时，把小数转换成字符串和再转换回来带来的麻烦？

Answer 1

我发现我需要对unutbu的方法做一点小调整。在一个修改过的例子中，值是'4.00'，但从数据库里取出来时却变成了'4'。我在处理商品，不想把精度硬编码到数据库里（如果我只是简单地乘以100再除以100，就会这样做）。所以我对转换函数做了如下调整：

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

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

虽然这样看起来不太美观，但确实避免了sqlite把字段存储为整数，从而丢失了精度。

Answer 2

SQLite的列亲和性规则是这样的：

如果你定义的列类型里包含“CHAR”、“CLOB”或“TEXT”这些字眼，那么这个列就会被认为是TEXT类型。注意，VARCHAR这个类型里包含了“CHAR”，所以它也会被认为是TEXT类型。

你可以把列定义成你想要的任何类型：

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

sqlite3 让你可以注册一个适配器，这样在插入数据时就可以自动把 Decimals 类型转换成 TEXT 类型。同时，它也允许你注册一个转换器，这样在获取数据时就能自动把 TEXT 类型转换回 Decimals 类型。

下面是从 文档 中稍微修改过的示例代码：

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'>