CODEWITHSUNDEEP
pythonpandas
manoelpqueiroz
manoelpqueiroz

Reputation: 637

A way to apply converters to pandas.read_sql?

After learning I have to apply the converters argument (and not dtype) to have a pandas column of decimal.Decimal class, I tried doing it with a module I'm using to access SQLite databases. However, while this argument is implemented in pd.read_csv and is available as a kwarg in pd.read_excel, that is not the case with pd.read_sql:

In [1]: df = pd.read_sql('SELECT * FROM table', myconnection, converters={'somecol':decimal.Decimal})
Traceback (most recent call last):

  File "<ipython-input-13-abdc559366bb>", line 1, in <module>
    pd.read_sql('SELECT * FROM table', myconnection, converters={'somecol':decimal.Decimal})

TypeError: read_sql() got an unexpected keyword argument 'converters'

Is there any way to do the conversion natively? The documentation for the function indicates pd.read_sql doesn't allow for any kwargs at all.

If not possible, which would be the most recommended solution? I could do with df['somecol'] = df['somecol'].apply(decimal.Decimal), but I'm wondering if there is a less verbose way in this case.

Upvotes: 1

Views: 718

Answers (1)

manoelpqueiroz
manoelpqueiroz

Reputation: 637

The solution is found in SQLite3's own documentation, through the register_converter function and then calling an argument on the connection:

import sqlite3
from decimal import Decimal

def conv(i):
    # Converts a number input to decimal.
    return Decimal(str(i))

sqlite3.register_converter('REAL', conv)

con = sqlite3.connect('something.db', detect_types=sqlite3.PARSE_DECLTYPES)

This way all numbers stored as real in SQLite will be automatically converted to Decimal upon reading.

Obs: note that the string conversion before decimal in the conv function happens in order to preserve the state of the number (although calling conv(1.1+2.2) will result in badly-formed decimals anyway.

Upvotes: 2

Related Questions