1

I want to pass in parameters to a sql query when using GeoPandas from_postgis functionality with SQLAlchemy.

classmethod GeoDataFrame.from_postgis(sql, con, geom_col='geom', crs=None, index_col=None, coerce_float=True, params=None)

I have looked at a previous similar question and also here which suggests to use SQLAlchemy's textual SQL option. However, this requires access to con.execute which isn't included in the GeoDataFrame from_postgis option.

Could you suggest the best way to pass the parameters to SQL? If not directly in from_postgis, then how best to construct the full SQL string separately and passing it in as the first sql argument to from_postgis.

eamon1234
  • 1,555
  • 3
  • 19
  • 38

1 Answers1

4

For textual SQL, you can add parameters by using .bindparams:

query = text("select * from foo where bar = :a").bindparams(a=1)

For queries you construct in SQLAlchemy, bind parameters are automatically included:

foo = Table(...)  # you need to define foo
query = select(["*"]).select_from(foo).where(foo.c.bar == 1)

You can also directly pass parameters via the params parameter of from_postgis, if that's more natural:

df.from_postgis(text("select * from foo where bar = :a"), params={"a": 1})

Do not use str.format as the other answer suggests because it's vulnerable to SQL injection.

univerio
  • 19,548
  • 3
  • 66
  • 68
  • 1
    I found that geopandas / psychopg did not like the **:param_name** naming style in the sql text, but it did work with **%(param_name)s** style. – Nij Jan 16 '20 at 15:08
  • @Nij Note that in this case the colon style is handled directly by SQLAlchemy, regardless of database backend. If you're using psycopg2 directly then yes, you need to use the `pyformat` style. – univerio Jan 18 '20 at 03:02