GeoPandas data types from PostGIS are always string

Question

I am using GeoPandas from_postgis function to read data from PostGIS. However, the data types of the GeoPandas columns are always object.

Data I store in PostGIS is mixture of text/char, bool, integer and geom (snip below).

I have no issue with geom and text but integer and bool data is not maintained over the process.

Python script I created reads 4 layers from the database, clip it within a polygon and export it as a geopackage.

conn_string = f'postgresql://postgres:abcs@15612:5432/wi_designsdb' #database string
db = create_engine(conn_string) #create engine from the string above
conn = db.connect() #connect to database
df= gpd.GeoDataFrame.from_postgis(f'select * from pia.pole',con=conn, geom_col='geometry')

df = df.clip(build_project)
df = df[columns_list]
df.to_file(merginmaps_output_folder + 'pole.gpkg')

Data types in DB:

Data types in GeoDataFrame:

Data types in QGIS:

Answer 1

Underlying problem is still unknown (I suspect most likely the null values) but I created a function to read data type from the database and save it as schema. I used that schema for fiona to control how the file is written.

def table_data_type(table, schema='pia', geom='Linestring', dbname='wi_designsdb'):
table_schema = {'geometry': geom, 'properties': {}}
conn = psycopg2.connect("host= dbname= user=postgres password=")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cur.execute(f"""SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = '{schema}' AND table_name = '{table}'""")

for row in cur:
    table_schema['properties'].setdefault(row['column_name'], row['data_type'])
    print("column: {col}, type: {col_type}".format(
        col = row['column_name'], col_type = row['data_type']))
    
return table_schema

Another function for matching postgresql datatypes with fiona datatypes

        df_schema = table_data_type(table= x, schema='pia', geom=df.geom_type, dbname='wi_designsdb')
    temp_dict = df_schema['properties'].copy()
    for dfp in temp_dict:
        if dfp not in df_columns_list:
            df_schema['properties'].pop(dfp)
        else:
            if df_schema['properties'][dfp] == 'text' or df_schema['properties'][dfp] == 'character varying':
                df_schema['properties'][dfp] = 'str'
            elif df_schema['properties'][dfp] == 'boolean':
                df_schema['properties'][dfp] = 'bool'
            elif df_schema['properties'][dfp] == 'integer':
                df_schema['properties'][dfp] = 'int'
    df_schema['properties'].pop('geometry')

and saving geopackage

df.to_file(output_folder + x + '.gpkg', schema= df_schema)