I am trying to cast datatypes based on a dataframe from a dictionary but not yet successful, tried astype but not working. any advise is appreciated, thanks in advance, Code:
schema_name = 'schema_name'
table_name = 'table_name'
sql_string = f"""select column_name,
data_type as col_type
from information_schema.columns
where table_catalog = 'db_name'
and table_schema = '{schema_name}'
and table_name = '{table_name}';"""
df = pd.read_sql(sql=sql_string, con=conn)
types_dict = df.to_dict(orient='records')
type_conversion = {
"bigint": "int64",
"boolean": "bool",
"character varying": "str",
"double precision": "float64",
"integer": "int32",
"numeric": "float64",
"timestamp without time zone": "datetime64"
}
for d in types_dict:
d['cast_type'] = type_conversion[d['col_type']]
insert_df[tname].astype({: <new-value-from-dictionary})