如何将Python十进制转换为SQLite数字？ [英] How to convert Python decimal to SQLite numeric?

问题描述

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

I have a program that reads financial data in JSON and inserts it into an SQLite database. The problem is when I'm inserting it into SQLite numeric column and it doesn't seem to like the decimal object.

我发现了这个问题之前已回答，但答案已过时，据我了解，SQLite现在具有货币数据类型称为数字。

I've found this question answered before, but the answer is outdated and from what I understand SQLite now has a currency data type called numeric.

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

Right now as a workaround I'm storing decimal values as text, but is it possible to store it as numeric? Am I stuck with the overhead of converting decimals to strings and vice versa for database inserts and financial calculations?

推荐答案

sqlite3 允许您注册适配器（将十进制透明地转换为 TEXT 插入）和一个转换器（在提取时将 TEXT 透明地转换为 Decimals ）。

sqlite3 allows you to register an adapter (to transparently convert Decimals to TEXT when inserting) and a converter (to transparently convert TEXT into Decimals when fetching).

以下是文档：

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

这篇关于如何将Python十进制转换为SQLite数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！