Pandasql: Python int too large to convert to SQLite INTEGER

Question

I'm getting the following 'Python int too large to convert to SQLite INTEGER' error when I run my code. I'm a beginner with psql.

Code:

import pandas as pd
import numpy  as np
import pandasql as psql
from pandasql import sqldf

T900_file = r'K:\myfile.xlsx'
df1 = pd.read_excel(T900_file)

T1000 = psql.sqldf("""Select Date
                            ,UP_Cust_Num
                            ,UP_Cust_Name
                            ,sum(Utilized_FVO)  as FVO
                            ,avg(UP_Generation) as UP_Gen
                      from df1
                      Group by Date, UP_Cust_Num, UP_Cust_Name""")

Error has to be with aggregation. how do i resolve the error? Any help would be greatly appreciated.

Answer 1

This seems to be a problem with the datatype in df1. If you run

df1.info()

you will probably see that some numeric values are type Int64; if that is the case then you can cast those values to a smaller integers (providing the values fit in the new data type). In order to cast to smaller integers you may need to do something like this:

df1["Utilized_FVO"] = df["Utilized_FVO"].astype(np.int8)
df1["UP_Generation"] = df["UP_Generation"].astype(np.int8)