On Sunday, 19 January 2020 16:13:40 UTC, Mike Bayer wrote: > > > > On Sun, Jan 19, 2020, at 11:10 AM, Mike Bayer wrote: > > > > On Sun, Jan 19, 2020, at 10:54 AM, Stephan Hügel wrote: > > I'm trying to define a GenericFunction that calls a PostGIS 3.0 function ( > ST_AsGeoJson <https://postgis.net/docs/ST_AsGeoJSON.html>). The latest > version can be called in two different ways: > > SELECT ST_AsGeoJSON(t.geom) FROM foo as t WHERE t.id = 1; > SELECT ST_AsGeoJSON(t.*) FROM foo as t WHERE t.id = 1; > > where the first example returns a GeoJSON Geometry, and the second returns > a GeoJSON Feature. I'm only interested in making use of the second type / > am happy to define the two variants separately. > > I should be able to subclass GenericFunction in order to do this, but I'm > missing two details: > > 1. What should the return type of my subclass be? > 2. How can I specify that I want to pass the record / row / all columns, > as opposed to just the geom column to the underlying function? Ordinarily, > attempting something like > > sess.query(func.MyGeojson(Foo)).all() > > Will give me an error: > > Object <class 'models.Foo'> is not legal as a SQL literal value > > > > by "return type" I think you're referring to the "type" attribute, so in > SQL if you were to make a database column that stores the result of the > ST_AsGeoJSON function, a quick google shows it as "text", so for SQLAlchemy > you could make the return type String, VARCHAR, TEXT, etc. > > > OK well looking at https://postgis.net/docs/ST_AsGeoJSON.html it says > "Return the geometry as a GeoJSON "geometry" object, or the row as a > GeoJSON "feature" object", even though the signatures say "text". OK so > I see you want to change the type based on the arguments. It's safe to do > that in your constructor as well; "if this_looks_like_a_row(args): > self.type = Feature else: self.type=Geometry". > > > > > for the column expansion you override the constructor and inspect the > object(s) given and do what you want with them. SQLAlchemy has some > built-in systems for "how to expand an ORM model or Selectable into Table > columns" but only in 1.4 is there a new system for this that is organized > and predictable, so for the moment you'd want to roll it yourself: > > from sqlalchemy import inspect > > class MyFunction(GenericFunction): > def __init__(self, *args, **kw): > pass_args = [] > for arg in args: > insp = inspect(arg) > if hasattr(insp, "selectable"): > pass_args.extend(selectable.c.) > else: > pass_args.append(arg) > super(MyFunction, self).__init__(*pass_args, **kw) > > also note there's been a lot of call for Geo support lately (not sure if > you were the person that filed an issue re: SQL Server) but we are really > looking for help to get geoalchemy2 up to date and supporting more backends. > > > > > > > -- > 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 sqlal...@googlegroups.com <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > Thanks for the quick response Mike!
OK so far I've got: https://gist.github.com/urschrei/782989d0b3bee8e4208eb2bea945b65b. Requires PostGIS 3.0.x as an extension, and requires GeoAlchemy2. On line 15, I fixed what I assume was a typo for selectable in your original suggestion. I still get the expected Object <class '__main__.Hardinfra'> is not legal as a SQL literal value error though. I don't follow your suggestion about "if this_looks_like_a_row(args): self.type = Feature else: self.type=Geometry" possibly because I'm not well-versed in SQLA's internals – is there an easy way to distinguish between a row and a column? That still leaves the issue of the "Geometry" vs "Feature" distinction in self.types: Geoalchemy2 defines a "Geometry" type, but not a "Feature" type, because "Feature" is only a part of the GeoJSON spec, but I doubt that either of these things are causing the problem. I've also tried executing the SQL query on my db to make sure it's working and both SELECT ST_AsGeoJSON(t.*) FROM hardinfra as t WHERE t.id = 1; SELECT ST_AsGeoJSON(t.geom) FROM hardinfra as t WHERE t.id = 1; work, returning a GeoJSON Feature and Geometry respectively. I didn't open the issue about SQL Server, but I use GeoAlchemy regularly, so if there are intro issues available I'm happy to help out when I can. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/795a8d14-17da-4c47-a917-78f355e88951%40googlegroups.com.
