'Must have equal len keys and value when setting with an iterable' error message

Question

I have a postgres spatial table that I want to convert to a geopandas GeoDataFrame. In order to do so I wrote the following piece of code:

#'pg_tab_clip' is the name of the postgres spatial table
#'b' is an integer
#'con' is the connection to postgres

    select = etl.fromdb(con, "SELECT ST_AsText(geom) FROM {0} WHERE buffer = {1}".format(pg_tab_clip, b))
    temp = select[1:len(select)]
    buffer_polygs = [shapely.wkt.loads(t[0]) for t in temp]

    buffer_gdf = gpd.GeoDataFrame()
    for i, bp in enumerate(buffer_polygs):
        buffer_gdf.loc[i, 'geometry'] = bp

When I run the script I get the following error:

Traceback (most recent call last):
  File "....py", line 417, in <module>
    main(working_folder, alr_path, pg_tab_culv, N, margin, pg_con)
  File "....py", line 390, in main
    loop_tiles(N, margin, con, cursor, alr_path, srid, pg_tab_buf, pg_tab_tile1, pg_tab_clip, buf)
  File "....py", line 274, in loop_tiles
    process_raster(con, pg_tab_clip, tile2_tif, srid, i, j, margin, culv_count, buf)
  File "....py", line 143, in process_raster
    buffer_gdf.loc[i, 'geometry'] = bp
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 190, in __setitem__
    self._setitem_with_indexer(indexer, value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 366, in _setitem_with_indexer
    self._setitem_with_indexer(new_indexer, value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py", line 611, in _setitem_with_indexer
    raise ValueError('Must have equal len keys and value '
ValueError: Must have equal len keys and value when setting with an iterable

I don't know what's causing this problem. The strangest of all is that the script doesn't actually fail every time I run it but only sometimes (???). Also, is there perhaps a more elegant/pythonic way to do what I am trying to do? Is the iterable 'buffer_polygs' actually unnecessary?

Answer 1

Try:

buffer_gdf = gpd.GeoDataFrame(geometry=buffer_polygs)

Another way using from_postgis copied from the answer to Geopandas PostGIS connection:

import geopandas as gpd
import psycopg2  # (if it is postgres/postgis)

con = psycopg2.connect(database="your database", user="user", password="password",
    host="your host")
sql = "select geom, x,y,z from your_table"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom' )