Up to now, I used to query geospatial tables with psycopg2 and then convert the geometries to my needs using, e.g. Shapely.

Per the psycopg2 documentation, you never format your query string yourself. Instead, you let the .execute() method of your cursor doing the actual job.

import psycopg2
sql_query = ("SELECT * FROM table WHERE id = %(id)s;")

with psycopg2.connect(**params) as conn:
  with conn.cursor() as curs:
    curs.execute(sql_query, {'id': id})
    res = curs.fetchall()

Now, I'd like to use .read_postgis() of GeoPandas to make an equivalent clean and secure call to my database.

But this method is a little bit skinny regarding the query string:

GeoPandas' query string

Is there a way to dynamically pass a dictionary of parameters to a call of GeoPandas ' .read_postgis() method? Maybe relying on the same mechanism that psycopg2 is using?

For the moment, my workaround is to take advantage from the psycopg2 way of doing by "mogrifying" the execution:

psycopg2 mogrify

as such:

import geopandas as gpd
gdf = gpd.read_postgis(
        cursRemote.mogrify(sql_query, {'id': id}), # <- passing this guy instead of my raw query
        connector, geom_col='mygeometrycolumn'

I would be glad to know if it exists a direct GeoPandas' way?

  • 1
    I use a SqlAlchemy (ORM) selectable, and pass arguments to the query with a dictionnary (docs.sqlalchemy.org/en/13/core/…) and read_sql or read_postgis accept the SqlAlchemy selectable as query. – J. Monticolo Jan 21 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.