programming error (psycopg2.ProgrammingError) can't adapt type 'Point'

[Mauro Mussin]

I have a postgres-postgis dB with a geometry:point field: if I insert the values directly 

INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003 54.097834)',4326));

no issue, but if I use this sequence (Python)

->import data in a dataframe: data contains lat and long columns;

-> trasform the lon,lat column in a shapely.geometry.point;

->create a geodataframe (gdf)

->connect to dB with psycopg2 and sqlalchemy

gdf.to_sql('<mytable>, conn,if_exists='append',index=False,schema=<myschema>)

I got this error:

--------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1223                     self.dialect.do_executemany(
-> 1224                         cursor, statement, parameters, context
   1225                     )

/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py in do_executemany(self, cursor, statement, parameters, context)
    751         else:
--> 752             cursor.executemany(statement, parameters)
    753 

ProgrammingError: can't adapt type 'Point'

Any hints?

[Mike Bayer]

On Mon, Jul 8, 2019, at 4:45 AM, Mauro Mussin wrote:

I have a postgres-postgis dB with a geometry:point field: if I insert the values directly 

INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003 54.097834)',4326));

no issue, but if I use this sequence (Python)

->import data in a dataframe: data contains lat and long columns;

-> trasform the lon,lat column in a shapely.geometry.point;

->create a geodataframe (gdf)

->connect to dB with psycopg2 and sqlalchemy

gdf.to_sql('<mytable>, conn,if_exists='append',index=False,schema=<myschema>)

I got this error:

Is this with geoalchemy?   Otherwise I don't know where you are getting the Point class from.  I'm not familiar with "gdf" however if it creates SQLAlchemy expressions, it needs to ensure that an insert construct refers to Table metadata that includes a Point-handling datatype, such as https://geoalchemy-2.readthedocs.io/en/latest/types.html#geoalchemy2.types.Geometry, which will produce the proper coercions from a "Point" object to the appropriate string/object representation for psycopg2 to accept.

Alternatively, psycopg2 also allows for native data handlers, again since I don't know what "gdf" is (googling was inconclusive), they might have recommendations to do it this way, not sure.    "gdf" is where you need to get assistance.   

--------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1223                     self.dialect.do_executemany(
-> 1224                         cursor, statement, parameters, context
   1225                     )

/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py in do_executemany(self, cursor, statement, parameters, context)
    751         else:
--> 752             cursor.executemany(statement, parameters)
    753 

ProgrammingError: can't adapt type 'Point'

Any hints?

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0a501873-7f27-45d1-93ec-c08aa0922a30%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.