column_expression issue/question

[Eric Lemoine]

Hi

I use 0.8's column_expression. Like this:

-----
from sqlalchemy.types import UserDefinedType
from sqlalchemy.sql import func


class Geometry(UserDefinedType):

def column_expression(self, col):
return func.ST_AsBinary(col, type_=self)


from sqlalchemy import Table, Column, MetaData

lakes = Table('lake', MetaData(),
Column('geom', Geometry)
)

from sqlalchemy.sql import select
s = select([lakes])
print s
----

The final print statement returns this: "SELECT ST_AsBinary(lake.geom)
AS geom_1 FROM lake".

My issue is with the "geom_1" label being generated. My column name
being "geom" I'd expect the following to work:

s = select([lakes])
for row in conn.execute(s):
geom = row['geom']

but it won't work because "row" does not have a "geom" item.

Is there a solution to this issue?

Thanks,

--
Eric Lemoine

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex

Tel : 00 33 4 79 44 44 96
Mail : eric.l...@camptocamp.com
http://www.camptocamp.com

[Michael Bayer]

fixed in tip.

I'm a little uncertain that the fix might hit some other cases that aren't handled yet, if you get any errors about .name or .key with more complex expressions let me know.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Eric Lemoine]

On Sunday, September 2, 2012, Michael Bayer wrote:

fixed in tip.

Thanks.

You may want to correct one of the generated SQL strings in the doc (<http://docs.sqlalchemy.org/en/latest/core/types.html?highlight=column_expression#types-sql-value-processing>).

 

I'm a little uncertain that the fix might hit some other cases that aren't handled yet, if you get any errors about .name or .key with more complex expressions let me know.

Will do.

[Eric Lemoine]

Hi

I'm facing another column_expression issue. It is not related to the
previous issue, and I'm sure if SQLAlchemy can help me with this one.

----
from sqlalchemy.types import UserDefinedType
from sqlalchemy.sql import func


class Geometry(UserDefinedType):

def column_expression(self, col):
return func.ST_AsBinary(col, type_=self)


from sqlalchemy import Table, Column, MetaData

lakes = Table('lake', MetaData(),
Column('geom', Geometry)
)

from sqlalchemy.sql.expression import select, alias

s = select([lakes])

a = alias(s, 'name')
s = a.select()
print s
----


The print statement returns this:


SELECT ST_AsBinary(name.geom) AS geom FROM (SELECT
ST_AsBinary(lake.geom) AS geom FROM lake) AS name


Which is expected. But the execution of this statement fails in my
PostGIS database, because ST_AsBinary cannot receive a bytea value as
input. (the outer ST_AsBinary call receives what's returned from the
inner ST_AsBinary call). It would work if ST_AsBinary was idempotent,
but it's not in PostGIS 2
(<http://trac.osgeo.org/postgis/ticket/1869>).

I'm stuck on this one.

[Michael Bayer]

oh. yeah it's going to have to not do that. try out tip, it will only put the type expression on those columns being delivered to the result (i.e. the outermost).

[Eric Lemoine]

On Mon, Sep 3, 2012 at 4:29 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> oh. yeah it's going to have to not do that. try out tip, it will only put the type expression on those columns being delivered to the result (i.e. the outermost).

I didn't think you'd fix that one. You rock. Thanks.

[Michael Bayer]

On Sep 3, 2012, at 11:22 AM, Eric Lemoine wrote:

> On Mon, Sep 3, 2012 at 4:29 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> oh. yeah it's going to have to not do that. try out tip, it will only put the type expression on those columns being delivered to the result (i.e. the outermost).
>
> I didn't think you'd fix that one. You rock. Thanks.

yeah the whole system is based on the ability to know when a column is being rendered "for the result", i.e. the top level, so as these SQL functions are to augment the usual "process_result_value()" system it follows that these "in-SQL processing" functions only need to be at that level as well. It was a one line change.

just one of a zillion little details that took years to get right....

>
>
>
>
>
> --
> Eric Lemoine
>
> Camptocamp France SAS
> Savoie Technolac, BP 352
> 73377 Le Bourget du Lac, Cedex
>
> Tel : 00 33 4 79 44 44 96
> Mail : eric.l...@camptocamp.com
> http://www.camptocamp.com
>

[Eric Lemoine]

On Monday, September 3, 2012, Michael Bayer wrote:

On Sep 3, 2012, at 11:22 AM, Eric Lemoine wrote:

> On Mon, Sep 3, 2012 at 4:29 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> oh.  yeah it's going to have to not do that.     try out tip, it will only put the type expression on those columns being delivered to the result (i.e. the outermost).
>
> I didn't think you'd fix that one. You rock. Thanks.

yeah the whole system is based on the ability to know when a column is being rendered "for the result", i.e. the top level, so as these SQL functions are to augment the usual "process_result_value()" system it follows that these "in-SQL processing" functions only need to be at that level as well.    It was a one line change.

result_processor and column_expression are companions, and relate to the "result". That makes sense.