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 [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
-~----------~----~----~----~------~----~------~--~---
