Re: [GeoAlchemy] Re: Table name concatenated with column in SQL statements, causes ProgrammingError

[Eric Lemoine]

On Fri, Jan 4, 2013 at 11:26 AM, Alex Sirotenko
<alex.si...@gmail.com> wrote:
> I have same error. In Google silent about this trouble, only that post. Does
> anyone know solution (without RAW queries)?
> Using GeoAlchemy-0.7.1, SQLAlchemy-0.8.0b2, postgis-1.5.3, PostgreSQL 9.1.7
>
> SQLAlchemy declaration:
> class NationalTerritories(Base):
> __tablename__ = 'terrclass_nationalterritories'
> id = Column(UUID, primary_key=True,
> server_default=text('uuid_generate_v1()'), info={'title': 'UUID'})
> title = Column(Unicode(50), unique=True, info={'title': _('Name')})
> geom = GeometryColumn(Polygon(2))
>
> GeometryDDL(NationalTerritories.__table__)
>
> On updating geom field have same error:
> ProgrammingError: (ProgrammingError) column "terrclass_nationalterritories"
> of relation "terrclass_nationalterritories" does not exist
> LINE 1: UPDATE terrclass_nationalterritories SET terrclass_nationalt...
>
> 'UPDATE terrclass_nationalterritories SET
> terrclass_nationalterritories.geom=GeomFromText(%(GeomFromText_1)s,
> %(GeomFromText_2)s) WHERE terrclass_nationalterritories.id =
> %(terrclass_nationalterritories_id)s' {'terrclass_nationalterritories_id':
> '0a8020b6-557e-11e2-a370-5404a6b5cc92', 'GeomFromText_2': 4326,
> 'GeomFromText_1': 'POLYGON ((28.1250000000000000 50.1768981220010701,
> 29.4653320312500000 48.0927571603273591, 36.4086914062500000
> 48.1807385073038361, 34.5629882812499929 50.4575040204205791,
> 31.0253906250000000 50.7781552746592482, 28.1250000000000000
> 50.1768981220010701))'}
>
> in pgAdmin this query have same error:
> UPDATE terrclass_nationalterritories SET
> terrclass_nationalterritories.geom=GeomFromText('POLYGON
> ((28.1250000000000000 50.1768981220010701, 29.4653320312500000
> 48.0927571603273591, 36.4086914062500000 48.1807385073038361,
> 34.5629882812499929 50.4575040204205791, 31.0253906250000000
> 50.7781552746592482, 28.1250000000000000 50.1768981220010701))', 4326) WHERE
> terrclass_nationalterritories.id = '0a8020b6-557e-11e2-a370-5404a6b5cc92'
> ERROR: column "terrclass_nationalterritories" of relation
> "terrclass_nationalterritories" does not exist
> LINE 1: UPDATE terrclass_nationalterritories SET terrclass_nationalt...
>
> but without tablename in SET ("terrclass_nationalterritories.geom" replace
> to "geom") works fine:
> UPDATE terrclass_nationalterritories SET geom=GeomFromText('POLYGON
> ((28.1250000000000000 50.1768981220010701, 29.4653320312500000
> 48.0927571603273591, 36.4086914062500000 48.1807385073038361,
> 34.5629882812499929 50.4575040204205791, 31.0253906250000000
> 50.7781552746592482, 28.1250000000000000 50.1768981220010701))', 4326) WHERE
> terrclass_nationalterritories.id = '0a8020b6-557e-11e2-a370-5404a6b5cc92'
> Query returned successfully: 1 row affected, 24 ms execution time.

Hi

I'm not able to reproduce with the following test-case:


from sqlalchemy import create_engine, MetaData, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from geoalchemy import GeometryColumn, Point, GeometryDDL


engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)
metadata = MetaData(engine)
session = sessionmaker(bind=engine)()
Base = declarative_base(metadata=metadata)


class Poi(Base):
__tablename__ = 'poi'
id = Column(Integer, primary_key=True)
name = Column(String)
geom = GeometryColumn(Point(2))

GeometryDDL(Poi.__table__)

Poi.__table__.drop(bind=engine, checkfirst=True)
Poi.__table__.create(bind=engine)

poi = Poi()
poi.geom = 'POINT(1 3)'

# INSERT
session.add(poi)
session.commit()

# UPDATE
poi.geom = 'POINT(4 1)'
session.commit()

Poi.__table__.drop(bind=engine)




The second session.commit() produces the following UPDATE statement:

UPDATE poi SET geom=ST_GeomFromText(%(ST_GeomFromText_1)s,
%(ST_GeomFromText_2)s) WHERE poi.id = %(poi_id)s


which works (geom in the SET clause isn't qualified with the table name).


Can you help me reproduce?


--
Eric

[Alex Sirotenko]

Hi.

After remove SQLAlchemy-0.8.0b2 and install SQLAlchemy-0.7.9 - error gone. Seems it trouble in work with SqlAlchemy 0.8.0b2...

Thanks for help. If I can help for testing with 0.8.2, you are welcome.

2013/1/9 Alex Sirotenko <alex.si...@gmail.com>

Hi.

I tried to run your test-case, changing only connection info to:
engine = create_engine('postgresql://tauser:tau...@127.0.0.1:5432/tradeassistant', echo=True)
and I got same error. Changing driver not affect.

In attachment: errortrace.txt - console output of script, pgside.txt - logs from postgres.

In this week I`ll try to setup another server, and check this problem on the clean and fresh system.

2013/1/8 Eric Lemoine <eric.l...@gmail.com>

--
Alexander Sirotenko

--
Alexander Sirotenko

[Eric Lemoine]

On Wed, Jan 9, 2013 at 3:58 PM, Alex Sirotenko <alex.si...@gmail.com> wrote:
> Hi.
>
> After remove SQLAlchemy-0.8.0b2 and install SQLAlchemy-0.7.9 - error gone.
> Seems it trouble in work with SqlAlchemy 0.8.0b2...
>
> Thanks for help. If I can help for testing with 0.8.2, you are welcome.

Yes, big deal. I need to test. Thanks for reporting the issue.


--
Eric

[Eric Lemoine]

Fixed in master.

<https://github.com/geoalchemy/geoalchemy/pull/27>
<https://github.com/geoalchemy/geoalchemy/commit/fc1a53922b0ad17f1159997239c1eea88893aa20>



--
Eric

[Alex Sirotenko]

Hi.

After applying patch, error gone (on 0.8.0b2). Thanks for fix.

2013/1/10 Eric Lemoine <eric.l...@gmail.com>

--
Alexander Sirotenko