Re: [sqlalchemy] Insert statement and DEFAULT keyword

[Mike Bayer]

oh, you're using insert.values() with multivalues. Sure, use a literal_column() 
for that, this is not a normal SQL thing. it won't scale to huge numbers 
because you will overflow the query buffer, unless you break them into batches.

I might prefer to use the psycopg2 batch mode helpers instead which makes the 
whole "VALUES" thing and the batching transparent, I would probably just pass 
the known defaults straight in assuming they are constants. 
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-fast-execution-helpers


On Tue, Apr 14, 2020, at 4:17 PM, Nicolas Caniart wrote:
> As you may have guessed the DEFAULT keyword is used to "even" the size of the 
> tuples in the VALUES clause and explicitly asks the RDBMS to replace itself 
> with the currently defined default value for the corresponding column.
> The RDBMS in question is Postgres (>=11).
> 
> The context is we want to insert, in batches, data that comes for a message 
> queue. Data comes in as (flat) JSON documents, but where some keys may be 
> omitted. We'd like to insert the batches without the need to know the server 
> defaults. We don't use the ORM layer, only the core layer (though I don't 
> think that would make any difference).
> 
> I attached an example that shows why using the DEFAULT keyword seems useful: 
> it avoids errors or data losses. A short comment explains what's wrong at the 
> top of each case. The example, to be run successfully, assumes a Postgres 
> server is running, accessible through its unix socket, and the current user 
> as access to a default database (generally one which name matches is login 
> name) and can create a table in the public schema. You can change the server 
> address if need be (see l. 48), change the schema (l. 42) or table name (l. 
> 36).
> 
> The important thing in the previous example was that we have tuples of 
> different sizes: a 3-tuple and a 2-tuple (when DEFAULT is omitted). Something 
> that, if fed as is to your SQL server, it will probably choke on: (1,'one', 
> 'I') and (100, 'one million'). If I do what you suggest (omit the roman 
> column) then I cannot pass an explicit value for that column when I have one.
> 
> A detail I forgot in the previous message is that the `roman` column in the 
> `numbers` table is *not nullable*.
> 
> CREATE TABLE number (
>  i INTEGER,
>  letters STRING,
>  roman STRING DEFAULT 'inexpressible' NOT NULL,
> ) ;
> 
> I mention this because I found old posts that mention in some case tuples may 
> be complete with NULL(s), but that is not what I observed and would not work 
> anyway in our context. You might argue why not drop the NOT NULL and make 
> NULL equivalent to 'inexpressible'. That would make perfect sense, I a 
> perfect world. But assume the database is ages old, hence has lots of quirks 
> I cannot get rid of (yet).
> 
> Of course, I may be missing something, probably obvious, that would explain 
> why nobody asked about this before. Hope it is a bit clearer.
> 
> Regards,
> Nicolas
> 
> Le mardi 14 avril 2020 18:35:48 UTC+2, Mike Bayer a écrit :
>> 
>> 
>> On Tue, Apr 14, 2020, at 10:41 AM, Nicolas Caniart wrote:
>>> Hi !
>>> 
>>> I've been looking into the documentation but could not find it.
>>> What is the proper way to insert a DEFAULT keywork in the tuples of the 
>>> VALUES clause in an INSERT statement ? As in
>>> 
>>> CREATE TABLE number (
>>>  i INTEGER,
>>>  letters STRING,
>>>  roman STRING DEFAULT '',
>>> ) ;
>>> 
>>> INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (100, 'one 
>>> million', DEFAULT) ; -- NOTE THE DEFAULT HERE
>> 
>> I'm not familiar with this keyword but to my knowledge there is no need for 
>> it on any backend I'm familiar with, you instead omit the "roman" column 
>> from the insert construct itself by only specifying values for the "i" and 
>> "letters" columns. The "roman" column will not be present and the SQL side 
>> default will fire off.
>> 
>> If you can share the reason that this special DEFAULT keyword is otherwise 
>> needed as well as what kind of database this is feel free, literal_column() 
>> would be the only way to go.
>> 
>>> 
>>> (yes this example is silly)
>>> 
>>> The best I came up with, is using the `literal_column('DEFAULT', 
>>> type_=...)` construct.
>>> 
>>> I wondered if there is a better way, cause I was worried about the keyword 
>>> possibly getting quoted at some point, though I am not 100% sure that could 
>>> happen.
>>> 
>>> Thanks in advance !
>>> 
>>> Regards,
>>> Nicolas.
>>> 

>>> --
>>> 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 

Re: [sqlalchemy] Insert statement and DEFAULT keyword

[Nicolas Caniart]

As you may have guessed the DEFAULT keyword is used to "even" the size of 
the tuples in the VALUES clause and explicitly asks the RDBMS to replace 
itself with the currently defined default value for the corresponding 
column.
The RDBMS in question is Postgres (>=11).

The context is we want to insert, in batches, data that comes for a message 
queue. Data comes in as (flat) JSON documents, but where some keys may be 
omitted. We'd like to insert the batches without the need to know the 
server defaults. We don't use the ORM layer, only the core layer (though I 
don't think that would make any difference).

I attached an example that shows why using the DEFAULT keyword seems 
useful: it avoids errors or data losses. A short comment explains what's 
wrong at the top of each case. The example, to be run successfully, assumes 
a Postgres server is running, accessible through its unix socket, and the 
current user as access to a default database (generally one which name 
matches is login name) and can create a table in the public schema. You can 
change the server address if need be (see l. 48), change the schema (l. 42) 
or table name (l. 36).

The important thing in the previous example was that we have tuples of 
different sizes: a 3-tuple and a 2-tuple (when DEFAULT is omitted). 
Something that, if fed as is to your SQL server, it will probably choke on: 
(1,'one', 
'I') and (100, 'one million'). If I do what you suggest (omit the roman 
column) then I cannot pass an explicit value for that column when I have 
one.

A detail I forgot in the previous message is that the `roman` column in the 
`numbers` table is *not nullable*.

CREATE TABLE number (
i INTEGER,
letters STRING,
roman STRING DEFAULT 'inexpressible' NOT NULL,
) ;

I mention this because I found old posts that mention in some case tuples 
may be complete with NULL(s), but that is not what I observed and would not 
work anyway in our context. You might argue why not drop the NOT NULL and 
make NULL equivalent to 'inexpressible'. That would make perfect sense, I a 
perfect world. But assume the database is ages old, hence has lots of 
quirks I cannot get rid of (yet).

Of course, I may be missing something, probably obvious, that would explain 
why nobody asked about this before. Hope it is a bit clearer.

Regards,
Nicolas

Le mardi 14 avril 2020 18:35:48 UTC+2, Mike Bayer a écrit :
>
>
>
> On Tue, Apr 14, 2020, at 10:41 AM, Nicolas Caniart wrote:
>
> Hi !
>
> I've been looking into the documentation but could not find it.
> What is the proper way to insert a DEFAULT keywork in the tuples of the 
> VALUES clause in an INSERT statement ? As in
>
> CREATE TABLE number (
> i INTEGER,
> letters STRING,
> roman STRING DEFAULT '',
> ) ;
>
> INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (100, 'one 
> million', DEFAULT) ; -- NOTE THE DEFAULT HERE
>
>
> I'm not familiar with this keyword but to my knowledge there is no need 
> for it on any backend I'm familiar with, you instead omit the "roman" 
> column from the insert construct itself by only specifying values for the 
> "i" and "letters" columns.The "roman" column will not be present and 
> the SQL side default will fire off.
>
> If you can share the reason that this special DEFAULT keyword is otherwise 
> needed as well as what kind of database this is feel free, literal_column() 
> would be the only way to go.
>
>
> (yes this example is silly)
>
> The best I came up with, is using the `literal_column('DEFAULT', 
> type_=...)` construct.
>
> I wondered if there is a better way, cause I was worried about the keyword 
> possibly getting quoted at some point, though I am not 100% sure that could 
> happen.
>
> Thanks in advance !
>
> Regards,
> Nicolas.
>
>
> --
> 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/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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 

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jonathan Vanasco]

This departs a bit from the example, because you are caching the youngest 
and oldest ids onto the Parent object.  is that necessary for your usage?

> Now my question is: how can I introduce a set/list of all children on the 
parent?

The line you commented out from the example was either:

children = relationship("Child")

children = relationship("Child", back_populates="parent")


both of those lines create an iterable list of all the Child objects on the 
`children`

There are a handful of ways you could structure this.  It really depends on 
your data model and usage patterns.

Off the top of my head, the simplest way to accomplish this would be to add 
a "parent_id" column on the child table, and then create a relationship for 
"children" that correlates the `Parent.id` to `Child.parent_id`.  

That change might not work with your data model if a Child can have 
multiple parents. 



-- 
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/ecfe0528-e141-44f4-a39e-eff4e1a3fe6d%40googlegroups.com.

Re: [sqlalchemy] Insert statement and DEFAULT keyword

[Mike Bayer]

On Tue, Apr 14, 2020, at 10:41 AM, Nicolas Caniart wrote:
> Hi !
> 
> I've been looking into the documentation but could not find it.
> What is the proper way to insert a DEFAULT keywork in the tuples of the 
> VALUES clause in an INSERT statement ? As in
> 
> CREATE TABLE number (
>  i INTEGER,
>  letters STRING,
>  roman STRING DEFAULT '',
> ) ;
> 
> INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (100, 'one 
> million', DEFAULT) ; -- NOTE THE DEFAULT HERE

I'm not familiar with this keyword but to my knowledge there is no need for it 
on any backend I'm familiar with, you instead omit the "roman" column from the 
insert construct itself by only specifying values for the "i" and "letters" 
columns. The "roman" column will not be present and the SQL side default will 
fire off.

If you can share the reason that this special DEFAULT keyword is otherwise 
needed as well as what kind of database this is feel free, literal_column() 
would be the only way to go.

> 
> (yes this example is silly)
> 
> The best I came up with, is using the `literal_column('DEFAULT', type_=...)` 
> construct.
> 
> I wondered if there is a better way, cause I was worried about the keyword 
> possibly getting quoted at some point, though I am not 100% sure that could 
> happen.
> 
> Thanks in advance !
> 
> Regards,
> Nicolas.
> 

> --
>  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/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com
>  
> .

-- 
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/aed920e2-9b42-4791-9d38-52bc6c2287b2%40www.fastmail.com.

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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 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...
>> 
>> 
>> 

[sqlalchemy] Insert statement and DEFAULT keyword

[Nicolas Caniart]

Hi !

I've been looking into the documentation but could not find it.
What is the proper way to insert a DEFAULT keywork in the tuples of the 
VALUES clause in an INSERT statement ? As in

CREATE TABLE number (
i INTEGER,
letters STRING,
roman STRING DEFAULT '',
) ;

INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (100, 'one 
million', DEFAULT) ; -- NOTE THE DEFAULT HERE

(yes this example is silly)

The best I came up with, is using the `literal_column('DEFAULT', type_=...)` 
construct.

I wondered if there is a better way, cause I was worried about the keyword 
possibly getting quoted at some point, though I am not 100% sure that could 
happen.

Thanks in advance !

Regards,
Nicolas.

-- 
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/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com.

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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 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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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 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 
>> ). 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:
>> 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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 ). 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 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Mike Bayer]

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 ). 
 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: 

Re: [sqlalchemy] Define a GenericFunction that passes all columns

[Adrien Berchet]

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 ). 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 = 

[sqlalchemy] Consolidate multiple one-to-one into a list

[Jens Troeger]

Hello,

Taking the relationship examples from the documentation 
, suppose I 
have the following:

class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)

oldest_child_id = Column(Integer, ForeignKey("child.id"))
oldest_child = relationship("Child", foreign_keys=oldest_child_id)

youngest_child_id = Column(Integer, ForeignKey("child.id"))
youngest_child = relationship("Child", foreign_keys=oldest_child_id)

# children = ... 

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)

For the sake of argument, we care for exactly two children per parent. Now 
my question is: how can I introduce a set/list of all children on the 
parent?

The naive approach would be something like

@property
def children(self):
return [self.oldest_child, self.youngest_child] # Or set(), or tuple().

In my particular case, the Child is actually a File table, and different 
other tables may have one or more Files associated. But it would be nice if 
these tables had a “files” property which is a consolidation of all their 
explicitly associated files.

Thank you!
Jens

-- 
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/b13c7494-ab23-44ba-a878-19fd7c0d1f63%40googlegroups.com.