Pandas not assuming dtypes when using read_sql?
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().
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.