I just wanted to give an update. I have found a solution that works
ok for now but I had to hack a bit. I ended up having to use
psycopg2's ability to override how python classes are converted
to/from a database. psycopg2 seems to have more complete support for
this then SA, so it let me convert to an exact string representation
for my type (ie. GeomFromText('POINT(1 1)') ). I combined this with
the GeoTypes library to represent geometry types and to read them back
from the OGC binary format.
The relevant code looks like this:
import sqlalchemy as sa
import psycopg2.extensions
import logging
pe = psycopg2.extensions
from GeoTypes import (OGGeoTypeFactory, WKBParser,
OGGeometry, OGPoint, OGPolygon, OGLineString)
class PostGisWKBFactory(object):
def __init__(self):
pass
def __call__(self, s=None):
factory = OGGeoTypeFactory()
parser = WKBParser(factory)
parser.parseGeometry(s)
return factory.getGeometry()
class GeometryType(sa.types.TypeEngine):
def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension
self.bfact = PostGisWKBFactory()
def __repr__(self):
return "%s:%s-%s(%s)" % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)
def get_col_spec(self):
return "GEOMETRY"
def convert_bind_param(self, value, engine):
# Could be used to make the type _conform_
#return pe.AsIs(str(value))
return value
def convert_result_value(self, value, engine):
geom_obj = self.bfact(binascii.a2b_hex(value))
geom_obj._srid = self.mSrid # set directly
return geom_obj
class GeometryPOINT(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, "POINT", 2)
class GeometryLINESTRING(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, "LINESTRING", 2)
Then I added the following method to the GeoTypes.Geometry class to
allow it to represent itself to psycopg2 correctly.
# Interface to allow psycopg2 to convert to database automatically
def getquoted(self):
return self.__str__()
def __conform__(self, proto):
# Return ourselves since we have a getquoted method
return self
I still don't see a way to handle this directly with SA, so if anyone
can tell me a way to let SA know exactly how I want the object's value
to appear in the generated SQL statement please let me know so I can
refine my code.
Thanks,
Allen
On 2/25/07, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
> [snip]
> > When I use this with my table and datamapper code, it looks like
> > everything is working fine but the generated SQL insert statement
> > fails with a exception:
> >
> > sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
> > invalid geometry
> > 'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
> > %(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
> > "GeomFromText('POINT(100 100)',-1)"}
> >
> > I know from using sql directly in pgadmin3 that this line works correctly:
> >
> > insert into gis_entity (id, name, pos) values (2, 'New entity',
> > GeomFromText('POINT(100 100)', -1));
> >
> > Does anyone see how this varies from the sql statement issued by SA?
>
> By looking at the postgres log I figured out what was causing the
> error, but I still don't know how to fix it.
>
> The problem is that SA considers "GeomFromText('POINT(100 100)', -1)"
> to be a string so it puts it in single quotes when creating the SQL
> command to execute. This causes problems because them postgres doesn't
> know it could be calling a method instead. I have tried returning an
> sqlalchemy.func object but this doesn't work either.
>
> Any ideas?
>
> -Allen
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
