CODEWITHSUNDEEP
pythonpandasdecimalsqldatatypesread-sql
wawawa
wawawa

Reputation: 3355

Pandas `read_sql_query` with decimal data type

I'm trying to use read_sql_query() to read a query from MySQL database, one of the field in the database, its type is decimal(28,5), I want to use dtype= parameter to have full control of the datatypes, but seems like pandas can't recognise Decimal type:

from decimal import *

dtypes = {
        'id': 'Int64',
        'date': 'datetime64',
        'value': Decimal
    }

df = pd.read_sql_query(sql_query, mysql_engine, dtype=dtypes, coerce_float=False)

This will give me an error TypeError: dtype '<class 'decimal.Decimal'>' not understood

So if I don't specify the type for field value:

dtypes = {
        'id': 'Int64',
        'date': 'datetime64'
    }

df = pd.read_sql_query(sql_query, mysql_engine, dtype=dtypes, coerce_float=False)

This works fine, if I print out the data type print(type(df['value'][0])), this will show me <class 'decimal.Decimal'>, but is there a way that I can specify the type of field value to be decimal in the Python code? Thanks.

Upvotes: 1

Views: 823

Answers (0)

Related Questions