[sqlalchemy] Mapping MySQL geometry type.

[J. Cliff Dyer]

I'm trying to create a custom geometry type for MySQL, but I'm running
up against my limited knowledge of SQLAlchemy.


My goal is to be able to pass in a geos.Point object on INSERT and
UPDATE, convert it to WKT representation ('POINT (1.0000000000000000
2.0000000000000000)'), and pass that to the database, using MySQL's
GeomFromText() function.  

When I get it out of the database, I would like to be able to have my
SELECT query ask for AsText(column) rather than just column, and then on
the python side, pass that through geos.from_str() to get a geos.Point
object back.

I'm basing my work on the code here:
http://trac.bycycle.org/browser/Core/trunk/bycycle/core/model/data/sqltypes.py?rev=1180

I'm pretty sure I can handle everything on the python side using
bind_processor() and result_processor(), but I'm not sure how to pass my
WKT object through GeomFromText() on the way into the database.  

I tried writing a method like the following on my Geometry(TypeEngine)
class:

  
    def bind_processor(self, dialect):
        """Convert from Python type to database type."""

        def process(value):
            if value is None:
                return None
            else:
                if isinstance(value, geos.base.GEOSGeometry):
                    return sqlalchemy.func.GeomFromText(value.wkt)
                else:
                    return sqlalchemy.func.GeomFromText(value)
        return process

But I get the following error:

Traceback (most recent call last):
  File "test_columns.py", line 36, in test_constructor
    'coordinates': geos.Point(-78.930210, 35.981076),
  File "/var/lib/python-support/python2.6/sqlalchemy/sql/expression.py",
line 1087, in execute
    return e.execute_clauseelement(self, multiparams, params)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 1219, in execute_clauseelement
    return connection.execute_clauseelement(elem, multiparams, params)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 895, in execute_clauseelement
    return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 907, in _execute_compiled
    self.__execute_raw(context)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 916, in __execute_raw
    self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 960, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor)
  File "/var/lib/python-support/python2.6/sqlalchemy/engine/base.py",
line 942, in _handle_dbapi_exception
    raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) (1416, 'Cannot get geometry object
from data you send to the GEOMETRY field') u'INSERT INTO address (id,
street, coordinates) VALUES (%s, %s, %s)' [2, '2346 Huron St.',
<sqlalchemy.sql.expression._Function at 0x16f5bd0; GeomFromText>]

I suspect this is a simple thing, but I just keep missing it.

Thanks,
Cliff




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~----------~----~----~----~------~----~------~--~---