[sqlalchemy] Mapper and Relation Question
Hello,
I have a question regarding using a mapper to add a relation to a
mapped class using instance attributes (not contained in the mapped
classes table def) on the mapped class control the select statement
for the join.
For example:
my Calendar class has a relation to CalendarEvent
Calendar.map_table(calendar_table,
properties={'events':relation(CalendarEvent,backref='calendar',collection_class
= CalendarEventList)})
I would like to be able to control the where clause of the join the
mapper uses when the property is accessed with data from my Calendar
class that is not in the table definition, such as the time slice the
Calendar represents.
So something like:
Calendar.map_table(calendar_table,
properties={'events':relation(CalendarEvent,primaryjoin=and_(Calendar.c.id==CalendarEvent.c.calendar_id,
calendar.CalendarEvent.c.starttime >= ???{need
help}???)),backref='calendar',collection_class = CalendarEventList)})
so in a use case:
c = Calendar() [Assume fill from db]
c.event_list_start = datetime.now()
c.event_list_end = datetime.now() + timedelta(days=1)
and the mapper would have:
Calendar.map_table(calendar_table,properties={'events':relation(CalendarEvent,
primaryjoin=and_(
Calendar.c.space_id==calendar.CalendarEvent.c.calendar_id,
CalendarEvent.c.starttime >= Calendar.event_list_start,
CalendarEvent.c.endtime <= Calendar.event_list_end,
)),backref='calendar',collection_class = CalendarEventList)})
I am not sure if SQLalchemy supports this or if I am looking at this
the wrong way.
Thanks in advance,
Rob
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Complicated Mapper with Count Function
Dear all.
I have to make a complicated mapper, and I need a little help.
We have a list of products. These products have stock, each individual
stock item has an entry in the stockproduct table. So to get the total
stock we need to count the items in the stock database. We can filter
them by a particular stock.
So I made a function to create a mapper to do just that. But there are
two problems:
- It's not working well, because if the count function equals 0 (no
stock) the product does not appear in the results.
- I have the feeling this can be better optimized, but I can't see it
(maybe put it in a join or subquery?)
The function
def productStockMapper(stockList):
or_list = or_()
for stock in stockList:
or_list.append(stockproduct_table.c.stock_id==stock.id)
s = select([product_table,
func.count(stockproduct_table.c.id).label('stock')],
and_(
stockproduct_table.c.product_id==product_table.c.id,
or_list),
group_by=[c for c in product_table.c]).alias('count_select')
return mapper(Product, s, non_primary=True)
The tables:
product_table = Table('products', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False)
Column('code', Unicode(255), unique=True, nullable=False))
stockproduct_table = Table('stockproducts', metadata,
Column('id', Integer, primary_key=True),
Column('stock_id', Integer, ForeignKey("stocks.id"), nullable=False),
Column('product_id', Integer, ForeignKey("products.id"),
nullable=False))
stock_table = Table('stocks', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False))
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Spatial data (PostGIS/OGC) with SqlAlchemy
[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 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Spatial data (PostGIS/OGC) with SqlAlchemy
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:
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(
[sqlalchemy] Re: how to force a clean refresh of a lazy loaded attribute
On Feb 25, 2007, at 9:03 AM, Manlio Perillo wrote: > > Michael Bayer ha scritto: >> >> On Feb 23, 2007, at 1:56 PM, Manlio Perillo wrote: >> >>> Hi again. >>> >>> I have an object attribute loaded via lazy loader. >>> This object is loaded in a transaction. >>> >>> Then, in another transaction, I ussue an update statement (via >>> the sql >>> module, not using the orm), that updates the table of the main >>> object's >>> attribute. >>> >>> The problem, now, is that I want to reload this attribute. >>> >>> I have tried, in a separate transaction: >>> sess.update(obj) >>> sess.expire(obj) >>> >>> One problem here is that the entire object is reloaded, and I do not >>> want this. >> >> i think if you say delattr(obj, attributename), it will do a lazyload >> on the next run. >> > > Sorry, I have tested only sess.expire(obj). > > delattr(obj, attributename) does not works. > OK, again, if you want the actual object that is *in* the lazy loaded collection to be reloaded, you have to issue an expire() or refresh() on at least that object. if the object is already in the session, the lazy load may still fire off but will only reload the instance that is already in the session. so you might want do instead do this (assuming its a lazy load collection - modify accordingly for a scalar attribute): for o in myinstance.somecollection: sess.expire(o) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Spatial data (PostGIS/OGC) with SqlAlchemy
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Spatial data (PostGIS/OGC) with SqlAlchemy
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-users@lists.sourceforge.net/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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to force a clean refresh of a lazy loaded attribute
Michael Bayer ha scritto: > > On Feb 23, 2007, at 1:56 PM, Manlio Perillo wrote: > >> Hi again. >> >> I have an object attribute loaded via lazy loader. >> This object is loaded in a transaction. >> >> Then, in another transaction, I ussue an update statement (via the sql >> module, not using the orm), that updates the table of the main >> object's >> attribute. >> >> The problem, now, is that I want to reload this attribute. >> >> I have tried, in a separate transaction: >> sess.update(obj) >> sess.expire(obj) >> >> One problem here is that the entire object is reloaded, and I do not >> want this. > > i think if you say delattr(obj, attributename), it will do a lazyload > on the next run. > Sorry, I have tested only sess.expire(obj). delattr(obj, attributename) does not works. Regards Manlio Perillo --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to force a clean refresh of a lazy loaded attribute
Michael Bayer ha scritto: > > On Feb 23, 2007, at 1:56 PM, Manlio Perillo wrote: > >> Hi again. >> >> I have an object attribute loaded via lazy loader. >> This object is loaded in a transaction. >> >> Then, in another transaction, I ussue an update statement (via the sql >> module, not using the orm), that updates the table of the main >> object's >> attribute. >> >> The problem, now, is that I want to reload this attribute. >> >> I have tried, in a separate transaction: >> sess.update(obj) >> sess.expire(obj) >> >> One problem here is that the entire object is reloaded, and I do not >> want this. > > i think if you say delattr(obj, attributename), it will do a lazyload > on the next run. > Ok, it needs the latest version in trunk but now works. >> The other problem (maybe a bug?) is that the orm issue another >> query to >> reload the data from the database, but in the object I still find the >> old values! >> >> I have submitted a ticket for the last problem (#492). > > "refresh-expire" cascade had not yet been implemented, just added it > in the trunk. > Thanks! Regards Manlio Perillo --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
