OK, for the function execution, the syntax that works with release
0.3.5 is:
conn = db.connect()
trans = conn.begin()
conn.execute(func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2).select())
trans.commit()
conn.close()
notice it just adds a select(). in changeset 2364, you dont need
the select.
inserts with functions are done like this:
# without a connection
table.insert().execute(somecolumn = func.GeometryFunction(arg1, arg2,
arg3...))
# with a connection
conn.execute(table.insert(), somecolumn = func.GeometryFunction(arg1,
arg2, arg3...))
On Feb 27, 2007, at 10:42 AM, Michael Bayer wrote:
>
> these are all SA bugs/possible inaccuracies in API usage. you should
> be able to execute the function as I described below, you should be
> able to put the function into an INSERT statement as well. Ill test
> these features later today.
>
> On Feb 25, 2007, at 3:43 PM, Allen Bierbaum wrote:
>
>>
>> I tried your idea but it doesn't seem to be valid code. Here is the
>> code I tried:
>>
>> print "Trying with a transaction."
>> conn = db.connect()
>> trans = conn.begin()
>> conn.execute(sa.func.AddGeometryColumn
>> ('','gis_entity','fpos',-1,'LINESTRING',2))
>> trans.commit()
>> conn.close()
>>
>> and here is the exception I get on the execute line:
>>
>> Try with a transaction.
>> INFO:sqlalchemy.engine.base.Engine.0x..d0:BEGIN
>> Traceback (most recent call last):
>> File "gis_type_test.py", line 73, in ?
>> conn.execute(sa.func.AddGeometryColumn
>> ('','gis_entity','fpos',-1,'LINESTRING',2,
>> engine=db))
>> File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py",
>> line 258, in execute
>> return Connection.executors[type(object).__mro__[-2]](self,
>> object, *multiparams, **params)
>> KeyError: <class 'sqlalchemy.sql._CompareMixin'>
>>
>> I am now running into a new problem as well though. I can use my
>> previous hack to insert the column but I can't add data to it because
>> I have been unable to call "GeomFromText(..)" on the insert.
>>
>> I tried to get this working by defining my own type:
>> 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
>>
>> 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):
>> return 'GeomFromText(\'%s\',%s)'%(value, self.mSrid)
>>
>> def convert_result_value(self, value, engine):
>> # Not used yet
>> return value
>>
>> 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?
>> I have stared at it for 20 minutes and I don't see a difference.
>> Is there any way to see the raw SQL statement sent by SA to postgres?
>> I have turned up the debug output level to full but I still only can
>> see the format string and parameters used for making the sql
>> statement:
>>
>> INFO:sqlalchemy.engine.base.Engine.0x..50:INSERT INTO gis_entity (id,
>> name, pos) VALUES (%(mId)s, %(mName)s, %(mPos)s)
>> INFO:sqlalchemy.engine.base.Engine.0x..50:{'mName': 'New entity',
>> 'mId': 1L, 'mPos': "GeomFromText('POINT(100 100)',-1)"}
>>
>> Does anyone see what I am doing wrong here?
>>
>> Once I get this working, then I have to figure out how to get the
>> data
>> back out from postgis. What I want to end up with here is an SQL
>> statement like this:
>>
>> select id, name, AsText(pos) as pos from gis_entity;
>>
>> Note that pos is retrieved through a server side function ('AsText')
>> that unpacks the binary representation from the database into a
>> string
>> representation. Is there any way to do this with SA so the system
>> will always create queries in this form whenever it tries to retrieve
>> the value of pos?
>>
>> I don't know if this makes a solution easier, but I am using mappers
>> for all my tables. What I would really like to have is a mapper that
>> would just automatically know to wrap all references to "pos" in
>> INSERT calls with a call to the server-side function 'GeomFromText'
>> and correspondingly wrap all references to "pos" in SELECT calls with
>> a call to the server-side function 'AsText'. Is it possible to do
>> this at the mapper level? If it is, then that could greatly simplify
>> everything I am trying to do here.
>>
>> Thanks for you help.
>>
>> -Allen
>>
>> On 2/25/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>>>
>>> if the function youre calling needs a transaction commit, why dont
>>> you call an explicit transaction ?
>>>
>>> conn = engine.connect()
>>> trans = conn.begin()
>>> conn.execute(func.AddGeometryColumn
>>> ('','gis_entity','fpos',-1,'LINESTRING',2))
>>> trans.commit()
>>>
>>>
>>> On Feb 25, 2007, at 11:02 AM, Allen Bierbaum wrote:
>>>
>>>>
>>>> I have been pursuing this further on my own and one of the issues I
>>>> have run into is how to cleanly add a geometry column to a
>>>> database.
>>>> The posting referenced in the first e-mail [2] talks about doing
>>>> this
>>>> directly with psycopg2 cursor because the poster could not get it
>>>> working with SA. I gave it another try to see if I could get it
>>>> working and I think I have narrowed down the problem. That said, I
>>>> still don't fully understand how to fix it with SA only.
>>>>
>>>> Here is my code example:
>>>>
>>>> ----------------------------
>>>> db = sa.create_engine(dsn_str)
>>>>
>>>> # Option 1: Try using a function on the database (doesn't work)
>>>> print "Run with func"
>>>> db.func.AddGeometryColumn('','gis_entity','fpos',-1,'LINESTRING',
>>>> 2).execute()
>>>>
>>>> # Option 2: Try directly with database statement execution (doesn't
>>>> work)
>>>> print "Run on db"
>>>> r = db.execute("select
>>>> AddGeometryColumn('','gis_entity','cpos',-1,'LINESTRING',2)")
>>>> r.close()
>>>>
>>>> # Option 3: use psycopg to execute directly without autocomit?
>>>> (works)
>>>> print "Run with psycopg isolation level"
>>>> con = db.connect()
>>>> con.connection.connection.set_isolation_level
>>>> (psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>>>> con.execute("select
>>>> AddGeometryColumn('','gis_entity','p_pos',-1,'LINESTRING',2)")
>>>> con.close()
>>>> ------------------------
>>>>
>>>>
>>>> So option 3 works, but I don't fully understand why it works and
>>>> why I
>>>> can't use one of the other options. Option 1 is definitely my
>>>> preferred way to do this because it will make the calls look like
>>>> normal SA calls to a DB function.
>>>>
>>>> Can anyone tell my whey option 3 is working and if there is a
>>>> way to
>>>> do this directly with SA only?
>>>>
>>>> Thanks,
>>>> Allen
>>>>
>>>>
>>>> On 2/23/07, Allen <[EMAIL PROTECTED]> wrote:
>>>>>
>>>>> I would like to use SqlAlchemy with PostGIS to create, read,
>>>>> update,
>>>>> and query spatial data. I have search around a bit and found a
>>>>> few
>>>>> ideas of doing this [1][2] but I haven't seen a definitive best
>>>>> practice by any means. It looks like all the solutions I can find
>>>>> have a least some limitations.
>>>>>
>>>>> Is anyone here using SqlAlchemy with spatial databases and if so
>>>>> how
>>>>> are you doing it right now?
>>>>>
>>>>> Specifically:
>>>>> - How do you handle table specification with geometry types?
>>>>> [1] tries to do this but it is incomplete
>>>>>
>>>>> - Do you use custom types and if so how are you doing this?
>>>>> [2] has some custom type code but it seems to be tied to binary
>>>>> formats and I don't see how to create/retrieve the geometry in
>>>>> text
>>>>> format.
>>>>>
>>>>> - How are you handling the object mapping?
>>>>> For example is there a way to map a "POINT" geometry to a python
>>>>> Point
>>>>> class or tuple?
>>>>>
>>>>> - Do you have any general recommendations for how to use spatial
>>>>> data
>>>>> successfully with SqlAlchemy?
>>>>>
>>>>>
>>>>> Thanks,
>>>>> Allen
>>>>>
>>>>> [1] http://www.mail-archive.com/sqlalchemy-
>>>>> [EMAIL PROTECTED]/msg03371.html
>>>>> [2] http://bycycle.org/2007/01/29/using-postgis-with-sqlalchemy/
>>>>>
>>>>>
>>>>>>
>>>>>
>>>>
>>>>>
>>>
>>>
>>>>
>>>
>>
>>>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
