How to convert Python decimal to SQLite numeric?

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 se开发者_JAVA技巧em to like the decimal object.

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 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).

The following is a lightly modified version of the example code from the docs:

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()

yields

4.12
<class 'decimal.Decimal'>

The column affinity rules for SQLite say:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

You can declare columns to be any type you like:

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"),))

Don't know if this is a good way to handle it or not - comments welcome

I found that I had to make a small tweak to unutbu's approach. With a modified example with a value '4.00', it comes back out of the database as '4'. I'm dealing with commodities and don't want to hardcode the precision into the database (like I'd be doing if I just multiplied and divided by 100). So I tweaked the conversion functions as follows:

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

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

which isn't aesthetically great but does defeat sqlite's eagerness to store the field as an integer and lose track of the precision.

At least the page you linked to didn't mention a currency data type, and the decimal(10,5) example datatype simply turns on the NUMERIC affinity.

If it were my data, I'd probably store an integer number of currency "units" -- pennies, or tenths of a penny, or hundredths of a penny, whatever is appropriate -- and then use a scaling factor to turn the integer input into the decimal equivalent to compute with when reading data from the database. It's harder to screw up with integers.

I am basically following the same approach as others but with one addition. The problem is that defining an adapter and converter takes care of the case of when you are accessing decimal columns row by row. But when you issue an aggregate function, such as summing a decimal column across all rows, the summation is done using real (i.e. floating point arithmetic) and the returned result will be a float with subsequent rounding errors.

The solution is to create a custom-tailored aggregate function, decimal_sum. Since sqlite3 aggregate functions are constrained in the types that they may return, decimal_sum will return a string representation of the decimal sum, which will be case to a Decimal type (this code is also Python 3-compatible):

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()

Prints:

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