I have a table in sql I'm looking to read into a pandas dataframe. I can read the table in but all column dtypes are being read in as objects. When I write the table to a csv then re-read it back in using read_csv, the correct data types are assumed. Obviously this intermediate step is inefficient and I just want to be able to read the data directly from sql with the correct data types assumed.
I have 650 columns in the df so obviously manually specifying the data types is not possible.
So it turns out all the data types in the database are defined as varchar.
It seems read_sql reads the schema and assumes data types based off this. What's strange is then I couldn't convert those data types using infer_objects().
The only way to do it was to write to a csv then read than csv using pd.read_csv().
Pandas not assuming dtypes when using read_sql?