Re: [sqlalchemy] Define a GenericFunction that passes all columns
This works perfectly, thank you very much! With this example I understood
much better how the compiles() function works.
I had to update your example to make it work in GeoAlchemy2 but everything
seems ok now. See https://github.com/geoalchemy/geoalchemy2/pull/258
Thanks again!
Le mer. 15 avr. 2020 à 16:59, Mike Bayer a
écrit :
> so the attached script includes what I was suggesting, which is that when
> this table or mapped class comes in, you get that into a ColumnElement
> right away, that way the function internals treat it like any other column;
> the code fails otherwise in current development SQLAlchemy versions that
> are more strict about passing the correct kinds of arguments to things.
> the element itself is:
>
> class TableRowThing(ColumnElement):
> def __init__(self, selectable):
> self.selectable = selectable
>
> @property
> def _from_objects(self):
> return [self.selectable]
>
>
> then to get the name, SQLCompiler doesn't have public API to get just this
> name, so usually when that happens the best approach is to get a string
> from the compiler that you know has what you need and you know how to find
> it, in this case, compile a column and pull the table name out:
>
> @compiles(TableRowThing)
> def _compile_table_row_thing(element, compiler, **kw):
> compiled = compiler.process(list(element.selectable.columns)[0], **kw)
>
> # 1. check for exact name of the selectable is here, use that.
> # this way if it has dots and spaces and anything else in it, we
> # can get it w/ correct quoting
> m = re.match(r"(.?%s.?)\." % element.selectable.name, compiled)
> if m:
> return m.group(1)
>
> # 2. just split on the dot, assume anonymized name
> return compiled.split(".")[0]
>
> a test suite is included in the attached along with a crazy table name
> test.
>
>
>
> On Wed, Apr 15, 2020, at 10:33 AM, Adrien Berchet wrote:
>
>
> Yes, the first query:
>
> select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
>
> returns only the geometry part of a GeoJson:
> {
> "type": "LineString",
> "coordinates": [[0, 0], [1, 1]]
> }
> while the query:
>
> select([func.ST_AsGeoJSON(Lake, 'geom')])
>
> returns a complete GeoJson with properties:
> {
> "type": "Feature",
> "geometry": {
> "type": "LineString",
> "coordinates": [[0, 0], [1, 1]]
> },
> "properties": {"id": 1}
> }
>
> Thanks for your help!
>
> Le mer. 15 avr. 2020 à 16:11, Mike Bayer a
> écrit :
>
>
> working on this now, just a quick question, is there an actual difference
> between
>
> select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
>
> and
>
> select([func.ST_AsGeoJSON(Lake, 'geom')])
>
> ?
>
> that is, users definitely need this goofy "Table" syntax, right?
>
>
>
> On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
>
> Ok, thank you for your advice, following it I tried the following (in
> geoalchemy2.functions.py).
>
> *class* ST_AsGeoJSON(functions.GenericFunction):
>
> *def* __init__(self, *args, **kwargs):
> args = list(args)
> self.feature_mode = False
> *for* idx, elem *in* enumerate(args):
> *try*:
> insp = inspect(elem)
> *if* hasattr(insp, "selectable"):
> args[idx] = insp.selectable
> self.feature_mode = True
> *except* Exception:
> *continue*
> functions.GenericFunction.__init__(self, *args, **kwargs)
>
> *def* _compile_ST_AsGeoJSON(cls):
> *def* _compile_geojson_feature(cls_name, element, compiler, **kw):
> *if* *not* element.feature_mode:
> *return* "{}({})".format(cls_name,
> compiler.process(element.clauses, **kw))
> *else*:
> clauses = list(element.clauses)
> table = compiler.process(clauses[0], asfrom=True,
> **kw).split(".")[-1] # This is quite dirty
> args = []
> *if* len(clauses) > 1:
> args = ", ".join([compiler.process(i, **kw) *for* i *in*
> clauses[1:]])
> *return* "{}({})".format(cls_name, ", ".join([table, args]))
>
> *def* _compile_geojson_default(element, compiler, **kw):
> *return* _compile_geojson_feature(cls, element, compiler, **kw)
>
> *def* _compile_geojson_sqlite(element, compiler, **kw):
Re: [sqlalchemy] Define a GenericFunction that passes all columns
Yes, the first query:
> select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
>
returns only the geometry part of a GeoJson:
{
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
}
while the query:
> select([func.ST_AsGeoJSON(Lake, 'geom')])
>
returns a complete GeoJson with properties:
{
"type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
},
"properties": {"id": 1}
}
Thanks for your help!
Le mer. 15 avr. 2020 à 16:11, Mike Bayer a
écrit :
> working on this now, just a quick question, is there an actual difference
> between
>
> select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
>
> and
>
> select([func.ST_AsGeoJSON(Lake, 'geom')])
>
> ?
>
> that is, users definitely need this goofy "Table" syntax, right?
>
>
>
> On Wed, Apr 15, 2020, at 7:15 AM, Adrien Berchet wrote:
>
> Ok, thank you for your advice, following it I tried the following (in
> geoalchemy2.functions.py).
>
> *class* ST_AsGeoJSON(functions.GenericFunction):
>
> *def* __init__(self, *args, **kwargs):
> args = list(args)
> self.feature_mode = False
> *for* idx, elem *in* enumerate(args):
> *try*:
> insp = inspect(elem)
> *if* hasattr(insp, "selectable"):
> args[idx] = insp.selectable
> self.feature_mode = True
> *except* Exception:
> *continue*
> functions.GenericFunction.__init__(self, *args, **kwargs)
>
> *def* _compile_ST_AsGeoJSON(cls):
> *def* _compile_geojson_feature(cls_name, element, compiler, **kw):
> *if* *not* element.feature_mode:
> *return* "{}({})".format(cls_name,
> compiler.process(element.clauses, **kw))
> *else*:
> clauses = list(element.clauses)
> table = compiler.process(clauses[0], asfrom=True,
> **kw).split(".")[-1] # This is quite dirty
> args = []
> *if* len(clauses) > 1:
> args = ", ".join([compiler.process(i, **kw) *for* i *in*
> clauses[1:]])
> *return* "{}({})".format(cls_name, ", ".join([table, args]))
>
> *def* _compile_geojson_default(element, compiler, **kw):
> *return* _compile_geojson_feature(cls, element, compiler, **kw)
>
> *def* _compile_geojson_sqlite(element, compiler, **kw):
> *return* _compile_geojson_feature(cls[3:], element, compiler, **kw)
>
> compiles(globals()[cls])(_compile_geojson_default)
> compiles(globals()[cls], "sqlite")(_compile_geojson_sqlite)
>
>
> _compile_ST_AsGeoJSON("ST_AsGeoJSON")
>
> I am not sure it is the right way to do it but using this it is possible to
> write the following queries:
>
>
>1. select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
>2. select([func.ST_AsGeoJSON(Lake, 'geom')])
>
> But it does not work for subqueries:
>
> sq = select([Lake, bindparam('dummy_val', 10).label('dummy_attr')]).alias()
> select([func.ST_AsGeoJSON(sq, 'geom')])
>
> because the generated query is:
>
> [SELECT ST_AsGeoJSON(lake) *AS* anon_1, %(ST_AsGeoJSON_2)s) *AS*
> "ST_AsGeoJSON_1"
>
> *FROM* (SELECT gis.lake.id *AS* id, gis.lake.geom *AS* geom, %(dummy_val)s
> *AS* dummy_attr
>
> *FROM* gis.lake) *AS* anon_1]
>
> [parameters: {'dummy_val': 10, 'ST_AsGeoJSON_2': 'geom'}]
>
> How can I get the alias of an aliased selectable?
>
>
>
> Le mar. 14 avr. 2020 à 18:33, Mike Bayer a
> écrit :
>
>
> OK so use the "t" form with the "geom" name sent as a string, it wants the
> whole row so this is a special Postgresql syntax.There are many ways to
> make it output this and it depends on the specifics of how this is being
> rendered. it may require a custom construct with a @compiles rule as I
> would assume it needs to respond to things like table alias names, subquery
> alias names, etc.
>
>
>
>
>
>
> On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
>
> This function is defined here:
>
> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
> And its C implementation is here:
>
> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
>
> Its first argument is a record and the second one (optional) is the name
> of the geometry column. If the second arg
Re: [sqlalchemy] Define a GenericFunction that passes all columns
Ok, thank you for your advice, following it I tried the following (in
geoalchemy2.functions.py).
class ST_AsGeoJSON(functions.GenericFunction):
>
> def __init__(self, *args, **kwargs):
> args = list(args)
> self.feature_mode = False
> for idx, elem in enumerate(args):
> try:
> insp = inspect(elem)
> if hasattr(insp, "selectable"):
> args[idx] = insp.selectable
> self.feature_mode = True
> except Exception:
> continue
> functions.GenericFunction.__init__(self, *args, **kwargs)
>
> def _compile_ST_AsGeoJSON(cls):
> def _compile_geojson_feature(cls_name, element, compiler, **kw):
> if not element.feature_mode:
> return "{}({})".format(cls_name,
> compiler.process(element.clauses, **kw))
> else:
> clauses = list(element.clauses)
> table = compiler.process(clauses[0], asfrom=True,
> **kw).split(".")[-1] # This is quite dirty
> args = []
> if len(clauses) > 1:
> args = ", ".join([compiler.process(i, **kw) for i in
> clauses[1:]])
> return "{}({})".format(cls_name, ", ".join([table, args]))
>
> def _compile_geojson_default(element, compiler, **kw):
> return _compile_geojson_feature(cls, element, compiler, **kw)
>
> def _compile_geojson_sqlite(element, compiler, **kw):
> return _compile_geojson_feature(cls[3:], element, compiler, **kw)
>
> compiles(globals()[cls])(_compile_geojson_default)
> compiles(globals()[cls], "sqlite")(_compile_geojson_sqlite)
>
>
> _compile_ST_AsGeoJSON("ST_AsGeoJSON")
>
> I am not sure it is the right way to do it but using this it is possible to
> write the following queries:
1. select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
2. select([func.ST_AsGeoJSON(Lake, 'geom')])
But it does not work for subqueries:
sq = select([Lake, bindparam('dummy_val', 10).label('dummy_attr')]).alias()
select([func.ST_AsGeoJSON(sq, 'geom')])
because the generated query is:
[SELECT ST_AsGeoJSON(lake) AS anon_1, %(ST_AsGeoJSON_2)s) AS
> "ST_AsGeoJSON_1"
FROM (SELECT gis.lake.id AS id, gis.lake.geom AS geom, %(dummy_val)s AS
> dummy_attr
FROM gis.lake) AS anon_1]
[parameters: {'dummy_val': 10, 'ST_AsGeoJSON_2': 'geom'}]
How can I get the alias of an aliased selectable?
Le mar. 14 avr. 2020 à 18:33, Mike Bayer a
écrit :
> OK so use the "t" form with the "geom" name sent as a string, it wants the
> whole row so this is a special Postgresql syntax.There are many ways to
> make it output this and it depends on the specifics of how this is being
> rendered. it may require a custom construct with a @compiles rule as I
> would assume it needs to respond to things like table alias names, subquery
> alias names, etc.
>
>
>
>
>
>
> On Tue, Apr 14, 2020, at 10:28 AM, Adrien Berchet wrote:
>
> This function is defined here:
>
> https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
> And its C implementation is here:
>
> https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
>
> Its first argument is a record and the second one (optional) is the name
> of the geometry column. If the second argument is not given, it iterates
> over all attributes of this record, pick the first geometry attribute to
> convert it to a GeoJSON Geometry and all over attributes are set as
> properties in the generated GeoJSON.
> The only two ways I found to use this function are the following:
>
>1. SELECT ST_AsGeoJson(t.*, 'geom') -- The 'geom' argument is
>optional here
>FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
>2. SELECT ST_AsGeoJson(t, 'geom') -- The 'geom' argument is optional
>here
>FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
>
> If we want to convert only a subset of columns into GeoJson properties we
> have to use a subquery to select this subset, it is not possible (as far as
> I can see) to pass the subset to the function. The only way I found to pass
> a subset is using the ROW() function but then the GeoJson properties have
> dummy names ('f1', 'f2', ...).
>
> Le mar. 14 avr. 2020 à 15:57, Mike Bayer a
> écrit :
>
>
> does this ST_AsGeoJSON function hardcode itself to look for column names
> "id" and "geom" ? it's not going to be any easier to get SQLAlchemy to
> render "t" than it is "t.*". it wants
Re: [sqlalchemy] Define a GenericFunction that passes all columns
This function is defined here:
https://github.com/postgis/postgis/blob/7f4426716f561187175d73bfff330343b25a7be9/postgis/postgis.sql.in#L4609
And its C implementation is here:
https://github.com/postgis/postgis/blob/b48fb3e2272568aa6310fc26aefc69010d4f37e3/postgis/lwgeom_out_geojson.c#L79
Its first argument is a record and the second one (optional) is the name of
the geometry column. If the second argument is not given, it iterates over
all attributes of this record, pick the first geometry attribute to convert
it to a GeoJSON Geometry and all over attributes are set as properties in
the generated GeoJSON.
The only two ways I found to use this function are the following:
1. SELECT ST_AsGeoJson(t.*, 'geom') -- The 'geom' argument is optional
here
FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
2. SELECT ST_AsGeoJson(t, 'geom') -- The 'geom' argument is optional
here
FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
If we want to convert only a subset of columns into GeoJson properties we
have to use a subquery to select this subset, it is not possible (as far as
I can see) to pass the subset to the function. The only way I found to pass
a subset is using the ROW() function but then the GeoJson properties have
dummy names ('f1', 'f2', ...).
Le mar. 14 avr. 2020 à 15:57, Mike Bayer a
écrit :
> does this ST_AsGeoJSON function hardcode itself to look for column names
> "id" and "geom" ? it's not going to be any easier to get SQLAlchemy to
> render "t" than it is "t.*". it wants to name columns.
>
>
>
> On Tue, Apr 14, 2020, at 9:45 AM, Adrien Berchet wrote:
>
> I just found that in fact it is possible to just pass the table name to
> ST_AsGeoJson, so the following query works:
>
> SELECT ST_AsGeoJSON(t)
> FROM t;
>
>
> I will try to use this writing in GeoAlchemy2, though I don't know yet how
> to translate it in SQLAlchemy.
>
> Le mar. 14 avr. 2020 à 14:23, Mike Bayer a
> écrit :
>
>
> and you can't say "SELECT t.d, t.geom" ? There really should be no
> difference between "t.*" and "t.id, t.geom".
>
>
>
> On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
>
> The "column names" issue is that when we use ROW(), like in the following
> query:
>
> SELECT ROW(t.id, t.geom)
> FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
>
> we obtain the following result:
>
> row
>
> (1,010100F03FF03F)
>
> in which the initial column names ('id' and 'geom') are lost. So when we
> give this result to the ST_AsGeoJson() function, it can not retrieve these
> names for the property names so it just replaces them by 'f1', 'f2', ...
>
> And I can't find any way to pass the names to the ROW() constructor:
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
>
>
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> On Mon, Apr 13, 2020, at 6:25 PM, Adrien Berchet wrote:
>
> Hello there
>
> I tried to integrate your POC in GeoAlchemy2 in the following PR:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> It is almost working but the ST_AsGeoJSON() function needs a record, not a
> list of columns. So the query should be like:
>
> SELECT ST_AsGeoJSON(t.*)
> FROM t;
>
> while the example you provided gives:
>
> SELECT ST_AsGeoJSON(t.id, t.geom)
> FROM t;
>
> which is not accepted by PostGIS (Error: the function
> st_asgeojson(integer, geometry) does not exist).
>
> I was able to make it work in the PR by adding a call to the ROW()
> function, which leads to the following query:
>
> SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
> FROM t;
>
> This query is properly executed by PostGIS but the column names are lost.
> For example I will get the following result:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"f1"*: 1}} => "f1" property should be name "id"
>
> instead of:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"id"*: 1}}
>
>
> Do you have any idea to overcome this issue? I did not find how to produce
> a "t.*" with SQLAlchemy...
>
>
>
> There should definitely be some way for this to work without doing "*".
> what if "t" didn't have the columns in that specific order? the "ROW"
> function seems like w
Re: [sqlalchemy] Define a GenericFunction that passes all columns
I just found that in fact it is possible to just pass the table name to
ST_AsGeoJson, so the following query works:
> SELECT ST_AsGeoJSON(t)
> FROM t;
>
I will try to use this writing in GeoAlchemy2, though I don't know yet how
to translate it in SQLAlchemy.
Le mar. 14 avr. 2020 à 14:23, Mike Bayer a
écrit :
> and you can't say "SELECT t.d, t.geom" ? There really should be no
> difference between "t.*" and "t.id, t.geom".
>
>
>
> On Tue, Apr 14, 2020, at 5:31 AM, Adrien Berchet wrote:
>
> The "column names" issue is that when we use ROW(), like in the following
> query:
>
> SELECT ROW(t.id, t.geom)
> FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
>
> we obtain the following result:
>
> row
>
> (1,010100F03FF03F)
>
> in which the initial column names ('id' and 'geom') are lost. So when we
> give this result to the ST_AsGeoJson() function, it can not retrieve these
> names for the property names so it just replaces them by 'f1', 'f2', ...
>
> And I can't find any way to pass the names to the ROW() constructor:
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
>
>
> Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> On Mon, Apr 13, 2020, at 6:25 PM, Adrien Berchet wrote:
>
> Hello there
>
> I tried to integrate your POC in GeoAlchemy2 in the following PR:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> It is almost working but the ST_AsGeoJSON() function needs a record, not a
> list of columns. So the query should be like:
>
> SELECT ST_AsGeoJSON(t.*)
> FROM t;
>
> while the example you provided gives:
>
> SELECT ST_AsGeoJSON(t.id, t.geom)
> FROM t;
>
> which is not accepted by PostGIS (Error: the function
> st_asgeojson(integer, geometry) does not exist).
>
> I was able to make it work in the PR by adding a call to the ROW()
> function, which leads to the following query:
>
> SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
> FROM t;
>
> This query is properly executed by PostGIS but the column names are lost.
> For example I will get the following result:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"f1"*: 1}} => "f1" property should be name "id"
>
> instead of:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"id"*: 1}}
>
>
> Do you have any idea to overcome this issue? I did not find how to produce
> a "t.*" with SQLAlchemy...
>
>
>
> There should definitely be some way for this to work without doing "*".
> what if "t" didn't have the columns in that specific order? the "ROW"
> function seems like what should be used but I don't understand the "column
> names" issue, is this regarding what goes into the JSON structure that the
> PG function returns? there should be a way to affect that at the SQL level.
>
>
>
>
>
>
> Thanks :-)
>
>
>
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> 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 is not legal as a SQL literal value
>
>
>
> by "return type" I think you
Re: [sqlalchemy] Define a GenericFunction that passes all columns
The "column names" issue is that when we use ROW(), like in the following
query:
> SELECT ROW(t.id, t.geom)
> FROM (SELECT 1 AS id, ST_GeomFromText('POINT( 1 1)') AS geom) AS t;
>
we obtain the following result:
> row
>
> (1,010100F03FF03F)
>
in which the initial column names ('id' and 'geom') are lost. So when we
give this result to the ST_AsGeoJson() function, it can not retrieve these
names for the property names so it just replaces them by 'f1', 'f2', ...
And I can't find any way to pass the names to the ROW() constructor:
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
Le mardi 14 avril 2020 00:47:28 UTC+2, Mike Bayer a écrit :
>
>
>
> On Mon, Apr 13, 2020, at 6:25 PM, Adrien Berchet wrote:
>
> Hello there
>
> I tried to integrate your POC in GeoAlchemy2 in the following PR:
> https://github.com/geoalchemy/geoalchemy2/pull/258
>
> It is almost working but the ST_AsGeoJSON() function needs a record, not a
> list of columns. So the query should be like:
>
> SELECT ST_AsGeoJSON(t.*)
> FROM t;
>
> while the example you provided gives:
>
> SELECT ST_AsGeoJSON(t.id, t.geom)
> FROM t;
>
> which is not accepted by PostGIS (Error: the function
> st_asgeojson(integer, geometry) does not exist).
>
> I was able to make it work in the PR by adding a call to the ROW()
> function, which leads to the following query:
>
> SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
> FROM t;
>
> This query is properly executed by PostGIS but the column names are lost.
> For example I will get the following result:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"f1"*: 1}} => "f1" property should be name "id"
>
> instead of:
>
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"id"*: 1}}
>
>
> Do you have any idea to overcome this issue? I did not find how to produce
> a "t.*" with SQLAlchemy...
>
>
>
> There should definitely be some way for this to work without doing "*".
> what if "t" didn't have the columns in that specific order? the "ROW"
> function seems like what should be used but I don't understand the "column
> names" issue, is this regarding what goes into the JSON structure that the
> PG function returns? there should be a way to affect that at the SQL level.
>
>
>
>
>
>
> Thanks :-)
>
>
>
> Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> 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 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
Re: [sqlalchemy] Define a GenericFunction that passes all columns
Hello there
I tried to integrate your POC in GeoAlchemy2 in the following PR:
https://github.com/geoalchemy/geoalchemy2/pull/258
It is almost working but the ST_AsGeoJSON() function needs a record, not a
list of columns. So the query should be like:
> SELECT ST_AsGeoJSON(t.*)
> FROM t;
>
> while the example you provided gives:
> SELECT ST_AsGeoJSON(t.id, t.geom)
> FROM t;
>
> which is not accepted by PostGIS (Error: the function
st_asgeojson(integer, geometry) does not exist).
I was able to make it work in the PR by adding a call to the ROW()
function, which leads to the following query:
> SELECT ST_AsGeoJSON(ROW(t.id, t.geom))
> FROM t;
>
> This query is properly executed by PostGIS but the column names are lost.
For example I will get the following result:
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"f1"*: 1}} => "f1" property should be name "id"
>
instead of:
> {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]},
> "properties": {*"id"*: 1}}
>
Do you have any idea to overcome this issue? I did not find how to produce
a "t.*" with SQLAlchemy...
Thanks :-)
Le lundi 20 janvier 2020 18:05:31 UTC+1, Mike Bayer a écrit :
>
>
>
> On Sun, Jan 19, 2020, at 12:23 PM, Stephan Hügel wrote:
>
>
>
> 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 ). 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 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f3718ff7-a252-41a9-8ac4-1ee2ee1e7f9f%40googlegroups.com
>
>
Re: [sqlalchemy] Memory management in sqlalchemy
I tried strong referencing the objects stored in the session using : def strong_reference_session(session): @event.listens_for(session, "pending_to_persistent") @event.listens_for(session, "deleted_to_persistent") @event.listens_for(session, "detached_to_persistent") @event.listens_for(session, "loaded_as_persistent") def strong_ref_object(sess, instance): if 'refs' not in sess.info: sess.info['refs'] = refs = set() else: refs = sess.info['refs'] refs.add(instance) as specified in the docs. Still, I have no way to get control over my object, and in the example I sent you I stay in the same scope, so session shouldn't lose reference to the object anyhow. Moreover, in this example, I am just requesting the object, I don't make any modifications on it, yet I can't flush, expire or expunge it, and finally, the session.close() don't free the memory. I don't get why in the simple example I sent you I can't flush my object. If i was to loop over this function, my memory would grow infinitely, and that's precisely what's happening when my script is running. -- 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/99190797-eeb3-4ca1-9fd4-adde124ae3b5%40googlegroups.com.
Re: [sqlalchemy] Memory management in sqlalchemy
I am using memory_profiler (https://pypi.org/project/memory-profiler/) to
measure the memory usage.
I reduced the code to just the request to see ifthe issue only comes from
here :
import gc
import sqlalchemy.orm.session as s
from MyDatabase.model.Table import Table
from memory_profiler import profile
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
HOST = 'myhost'
engine = create_engine(
'postgresql+psycopg2://postgres:myaccount@{}:5432'.format(HOST),
connect_args={'connect_timeout': 6000})
Session = sessionmaker(bind=engine)
session = Session() # type: Session
fp = open('memory_profiler_elem.log', 'w+')
@profile(stream=fp)
def calculate():
obj= session.query(Table).filter(Table.idTable == 1).scalar()
session.flush(obj)
session.expunge_all()
session.close()
s.close_all_sessions()
obj= None
gc.collect()
if __name__ == '__main__':
calculate()
Which returns in memory usage :
Line #Mem usageIncrement Line Contents
19140.8 MiB140.8 MiB @profile(stream=fp)
20 def calculate():
21154.2 MiB 13.4 MiB obj=
session.query(Table).filter(Table.idTable== 1).scalar()
22154.2 MiB 0.0 MiB session.flush(obj)
23154.2 MiB 0.0 MiB session.expunge_all()
24154.2 MiB 0.0 MiB session.close()
25154.2 MiB 0.0 MiB s.close_all_sessions()
26154.2 MiB 0.0 MiB obj= None
27154.2 MiB 0.0 MiB gc.collect()
For example, when I do a simple loop,the memory_profiler for this code :
from memory_profiler import profile
fp = open('memory_profiler_elem.log', 'w+')
@profile(stream=fp)
def calculate():
test = []
for i in range(100):
test.append(1)
test = None
if __name__ == '__main__':
calculate()
returns :
Line #Mem usageIncrement Line Contents
4 51.2 MiB 51.2 MiB @profile(stream=fp)
5 def calculate():
6 51.2 MiB 0.0 MiB test = []
7 58.9 MiB 0.0 MiB for i in range(100):
8 58.9 MiB 0.8 MiB test.append(1)
9 51.3 MiB 0.0 MiB test = None
So we see that the lib is correctly tracking memory usage.
I'll try to recreate the issue for you to test it, but my question really
is : Is there something I didn't get about memory usage in sqlalchemy,
meaning, is it normal for it to retain objects in memory, and if so, how
can we get past it ?
--
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/be105ac2-e0e0-4057-a0aa-fe071adf3e6d%40googlegroups.com.
[sqlalchemy] Memory management in sqlalchemy
Hello, I'm having troubles understanding how to deal with memory management with sqlalchemy. Here is my issue. I have a big script requesting data from a PostgreSQL DB. I process this data, and insert the generated data in my DB. On the beggining of the process, I request an object from my DB : Line #Mem usageIncrement Line Contents 223144.2 MiB144.2 MiB @profile(stream=fp) 224 def compute(): 226155.4 MiB 11.2 MiB obj= session.query(Table).filter(Table.idTable == some_id).scalar() As you can see, this object takes roughly 11Mb in my RAM. At the end of my script, I try to free the memory from this object, but nothing seems to work (other requests are done using the session.query() or engine.execute(), the issue is the same for them. This example is the most significant). 589161.4 MiB 0.0 MiB session.flush(obj) 590161.4 MiB 0.0 MiB session.expunge_all() 591161.4 MiB 0.0 MiB session.close() 592161.4 MiB 0.0 MiB s.close_all_sessions() 593161.4 MiB 0.0 MiB obj= None 594161.4 MiB 0.0 MiB gc.collect() I call this function several thousand times in the process, which makes the RAM increase infinitely until it breaks. I also tried expiring the object through the session, deleting its reference and garbage collecting it. How could I request this object, extract the information I need from it, and delete it from my memory, so that my process won't run out of memory ? -- 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/94afa0c9-7b4b-432c-8c8a-709ae366522f%40googlegroups.com.
Re: [sqlalchemy] declarative one to many relationship with composite primary key
On Wed, Nov 17, 2010 at 1:16 AM, Michael Bayer mike...@zzzcomputing.com wrote:
ForeignKeyConstraint needs to go into __table_args__ when using declarative.
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration
Thanks for the note. I have updated my test script to use
__table_args__ but the error remains the same (see script and ouput
below).
I then tried with the hybrid approach
(http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#using-a-hybrid-approach-with-table)
which works well.
Am I again doing something wrong with declarative ?
Thanks,
#
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = foo
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)
class Bar(Base):
__tablename__ = bar
__table_args__ = ( ForeignKeyConstraint(['one_id', 'two_id'],
['foo.one', 'foo.two']) )
id = Column(Integer, primary_key=True)
one_id = Column(Integer, nullable=False)
two_id = Column(Integer, nullable=False)
foo = relationship(Foo, backref = bars)
metadata = Base.metadata
engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
# create a configured Session class
Session = sessionmaker(bind=engine)
# create a Session
session = Session()
foo = Foo()
foo.one = 1
foo.two = 2
session.add(foo)
session.commit()
#
2010-11-17 14:56:01,309 INFO sqlalchemy.engine.base.Engine.0x...9690
PRAGMA table_info(foo)
2010-11-17 14:56:01,309 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690
PRAGMA table_info(bar)
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690
CREATE TABLE foo (
one INTEGER NOT NULL,
two INTEGER NOT NULL,
PRIMARY KEY (one, two)
)
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,310 INFO sqlalchemy.engine.base.Engine.0x...9690 COMMIT
2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690
CREATE TABLE bar (
id INTEGER NOT NULL,
one_id INTEGER NOT NULL,
two_id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690 ()
2010-11-17 14:56:01,311 INFO sqlalchemy.engine.base.Engine.0x...9690 COMMIT
Traceback (most recent call last):
File compositePrimaryKey_decl.py, line 39, in module
foo = Foo()
File string, line 4, in __init__
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
line 93, in initialize_instance
fn(self, instance, args, kwargs)
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 2357, in _event_on_init
instrumenting_mapper.compile()
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 805, in compile
mapper._post_configure_properties()
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 834, in _post_configure_properties
prop.init()
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/interfaces.py,
line 493, in init
self.do_init()
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/properties.py,
line 840, in do_init
self._determine_joins()
File
/home/virtualenvs/sqlalchemy/lib/python2.6/site-packages/sqlalchemy/orm/properties.py,
line 969, in _determine_joins
% self)
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Bar.foo. Specify a
'primaryjoin' expression. If this is a many-to-many relationship,
'secondaryjoin' is needed as well.
The script below works with the hybrid declarative approach:
#
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = foo
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)
bartable = Table(bar, Base.metadata,
Column(id, Integer, primary_key=True),
Column(one_id, Integer, nullable=False),
Column(two_id, Integer, nullable=False),
ForeignKeyConstraint(['one_id', 'two_id'], ['foo.one', 'foo.two']),
)
class Bar(Base):
__table__ = bartable
foo = relationship(Foo, backref = bars)
metadata = Base.metadata
engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
# create a configured Session class
Session = sessionmaker(bind=engine)
# create a Session
session = Session()
foo = Foo()
foo.one = 1
foo.two = 2
Re: [sqlalchemy] declarative one to many relationship with composite primary key
On Wed, Nov 17, 2010 at 4:58 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK its actually a huge SQLA bug that an error isn't raised for that, which is surprising to me, so I created and resolved #1972 in r67d8f4e2fcb9. __table_args__ is expected to be a tuple or dict, so now an error is raised if it's not. (x) isn't a tuple. Thanks for the quick reply, the patch and the syntax correction of my code. Regards, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] declarative one to many relationship with composite primary key
Hi list,
Sorry if this is trivial, I'm relatively new to sqlalchemy.
I'm trying to set a one to many relationship between class Foo and
class Bar (ie Foo should have a list of Bars). Foo has a composite
primary key.
#
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = foo
one = Column(Integer, primary_key=True)
two = Column(Integer, primary_key=True)
class Bar(Base):
__tablename__ = bar
id = Column(Integer, primary_key=True)
one_id = Column(Integer, nullable=False)
two_id = Column(Integer, nullable=False)
ForeignKeyConstraint([one_id, two_id], [foo.one, foo.two])
foo = relationship(Foo, backref = bars)
metadata = Base.metadata
engine = create_engine('sqlite:///:memory:', echo=True)
metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
# create a configured Session class
Session = sessionmaker(bind=engine)
# create a Session
session = Session()
foo = Foo()
foo.one = 1
foo.two = 2
session.add(foo)
session.commit()
#
I get the following message:
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Bar.foo. Specify a
'primaryjoin' expression.
I tried to change the relationship line to:
foo = relationship(Foo, backref = bars, primaryjoin=and_(one_id ==
Foo.one, two_id==Foo.two ) )
but then I get this message:
sqlalchemy.exc.ArgumentError: Could not determine relationship
direction for primaryjoin condition 'bar.one_id = foo.one AND
bar.two_id = foo.two', on relationship Bar.foo. Ensure that the
referencing Column objects have a ForeignKey present, or are otherwise
part of a ForeignKeyConstraint on their parent Table.
Thank you for your help!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
