[sqlalchemy] iteration over mapper
Hi all, Probably this is a stupid question, :-[ but I don't understand how to iterate an object mapper to get fields value. --- user = session.query(User).select(id=1) for j in user.c: print j.name logname id password for j in user.c: print j.value 'Column' object has no attribute 'value' jo --~--~-~--~~~---~--~~ 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: iteration over mapper
Hi all, Probably this is a stupid question, :-[ but I don't understand how to iterate an object mapper to get fields value. --- user = session.query(User).select(id=1) for j in user.c: print j.name logname id password these are the column-names that your query-result has. It is an iterable. so u do: for u in user: print u.name instead of this: for j in user.c: print j.value 'Column' object has no attribute 'value' --~--~-~--~~~---~--~~ 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: iteration over mapper
Jose Soares wrote: Hi all, Probably this is a stupid question, :-[ but I don't understand how to iterate an object mapper to get fields value. --- user = session.query(User).select(id=1) for j in user.c: print j.name logname id password for j in user.c: print j.value 'Column' object has no attribute 'value' The fields are attributes of the 'user' object itself, so the values are at user.logname, user.id and user.password. To get an attribute whose name is stored in a variable, you can use 'getattr': for col in user.c: value = getattr(user, col.name) print col.name, value Hope that helps, Simon --~--~-~--~~~---~--~~ 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] date format
Hi all, I would like to display my dates with format '%d/%m/%Y' instead of ISO format. qry = session.query(Nazione).select(Nazione.c.codice=='201') qry[0].data_inizio print qry[0].data_inizio 2006-01-14 Is there a way to set it in SA without using a customer function ? jo --~--~-~--~~~---~--~~ 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: date format
On Wed, 2007-31-01 at 17:50 -0400, Jose Soares wrote: Hi all, I would like to display my dates with format '%d/%m/%Y' instead of ISO format. qry = session.query(Nazione).select(Nazione.c.codice=='201') qry[0].data_inizio print qry[0].data_inizio 2006-01-14 Is there a way to set it in SA without using a customer function ? Something like: '/'.join(s.split('-').reverse()) where 's' is the string you want to reformat, would do it. jo -- Guy Hulbert [EMAIL PROTECTED] (preferred) work: (416) 391-2051 (no voicemail) cell: (416) 738-6257 (voicemail) --~--~-~--~~~---~--~~ 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: date format
On Wed, 2007-31-01 at 12:17 -0500, Guy Hulbert wrote: I would like to display my dates with format '%d/%m/%Y' instead of ISO format. qry = session.query(Nazione).select(Nazione.c.codice=='201') qry[0].data_inizio print qry[0].data_inizio 2006-01-14 Is there a way to set it in SA without using a customer function ? Something like: '/'.join(s.split('-').reverse()) where 's' is the string you want to reformat, would do it. s = '2006-01-14' l = s.split('-') s = '/'.join(l) does work (I haven't been using python long enough to get it into a line :-( ... i'll shut up now ... -- --gh --~--~-~--~~~---~--~~ 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: polymorphic mapping with more than 2 level of inheritance
sorry, there was one more quoting fix that was needed. passes with PG on 2274. IMO even some more. see ansisql.py, format_column(), any sqlalchemy.sql._ColumnClause falls into the literal case, and is not quoted (try with zz.py, the py_a.dataC). Also see schema.py, the copy() and the make_proxy() are not consistent on case_sensitive arg. u could make the copy() more generic and use it on both places though. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- zz.py Description: application/python
[sqlalchemy] sorting mapped class by joined subquery result (error)
I have a mapped class.. lets call it Data with a few properties Data.id (primary key), Data.a, Data.b, Data.c I want to query a few of these objects out.. but they need to be sorted by some arbitrary data arbitrary_data=select ( [Data.c.id, OtherClass.c.somedata], and_()).alias('somedata') ok.. now query the data: dat=Data.select( and_(.), from_obj=[ datas.join(arbitrary_data,arbitrary_data.c.id==datas.c.id) ] , order_by=[asc(arbitrary_data.c.somedata)]) Now, the generated sql is in the form (with query.py deciding it needs to nest the query): select datas.id as datas_id, datas.a as datas_a etc. from (select datas.id as datas_id, arbitrary_data.somedata as arbitrary_data_somedata from datas join (my arbitrary_data table query with where clause ) as arbitrary_data where . order by arbitrary_data.somedata ) as tbl_row_count join datas on ... order by arbitrary_data.somedata The last line is the problem.. The from clause renames the column to arbitrary_data_somedata but the order by clause uses the inner form with a . still. The error: missing FROM-clause entry for table arbitrary_data (because that table only exists on the inner aliased table) Anyhow, if I rename the sort on the outer query to use the underscore manually, the query returns the correct results in the correct order. I believe the faulty behavior starts at line 455 in orm/query.py (trunk).I'm not sure if it is the Aliasizer that is not converting the column. Anyhow, I need this to work so I don't have to write my great big huge dynamic query out by hand so I'll be digging into the sqlalchemy code for a second. Is there is quick easy fix though? Thanks -Dennis --~--~-~--~~~---~--~~ 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: any particular reason for creating unused lists?
another things i noted: - using value.lower() == value instead of value.islower() - ansisql.py: in _requires_quotes(): this bool(len([x for x in str(value) if x not in self._legal_characters()])) should be same as bool( s.translate( 256*' ', self._legal_characters() ) ) and that table(256) can be a static/global. i know, speed will not budge... well, profiling some case (5x (simple A,B,C setup + 50x query)), 6% goes in visit_select, 3% of time goes in __generic_obj_format(), and 3% in getattr, and everything else gets less. just habit ...i dont like one liners with :, also makes it easy to tack on conditionals...feel free to submit a patch for all those if theyre really bothering you (i guarantee program speed /mem usage will not budge in any measurable way). On Jan 25, 4:30 pm, [EMAIL PROTECTED] wrote: there are several places of such unused lists being made. i pick a random occurence, in this case InstrumentedAttribute: def _adapt_list(self, data): if self.typecallable is not None: t = self.typecallable() if data is not None: [t.append(x) for x in data] return t else: return data why not just for x in data: t.append(x) --~--~-~--~~~---~--~~ 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: polymorphic mapping with more than 2 level of inheritance
what are there like 10 .py attached scripts that ive to look at now ? ive completely lost track. post tickets for the important ones. On Jan 31, 12:42 pm, svilen [EMAIL PROTECTED] wrote: sorry, there was one more quoting fix that was needed. passes with PG on 2274. IMO even some more. see ansisql.py, format_column(), any sqlalchemy.sql._ColumnClause falls into the literal case, and is not quoted (try with zz.py, the py_a.dataC). Also see schema.py, the copy() and the make_proxy() are not consistent on case_sensitive arg. u could make the copy() more generic and use it on both places though. zz.py 5KDownload --~--~-~--~~~---~--~~ 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: any particular reason for creating unused lists?
On Jan 31, 1:28 pm, svilen [EMAIL PROTECTED] wrote: another things i noted: - using value.lower() == value instead of value.islower() - ansisql.py: in _requires_quotes(): this bool(len([x for x in str(value) if x not in self._legal_characters()])) should be same as bool( s.translate( 256*' ', self._legal_characters() ) ) and that table(256) can be a static/global. oh no those are great, and should be fixed. the only ones i dont want to use are map() and reduce() since guido has said that list comprehensions should be used instead. i tend to not memorize all the little tweaky functions like these (note that I am frequently coming out against humane interfaces since i am more of a thinker and less of a memorizer). one reason you wont see too much performance gain with those in particuilar is because the quoting system caches all the decisions it makes about identifiers. --~--~-~--~~~---~--~~ 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: sorting mapped class by joined subquery result (error)
quick easy fix is to use result set mapping instead, or feed a full select() statement into query.select(), which will skip the whole compilation step. i would like a small test case attached for this one so i can play with it, though. for example i dont see why its deciding to use the nesting feature of the compilation in the first place. On Jan 31, 1:12 pm, Dennis [EMAIL PROTECTED] wrote: I have a mapped class.. lets call it Data with a few properties Data.id (primary key), Data.a, Data.b, Data.c I want to query a few of these objects out.. but they need to be sorted by some arbitrary data arbitrary_data=select ( [Data.c.id, OtherClass.c.somedata], and_()).alias('somedata') ok.. now query the data: dat=Data.select( and_(.), from_obj=[ datas.join(arbitrary_data,arbitrary_data.c.id==datas.c.id) ] , order_by=[asc(arbitrary_data.c.somedata)]) Now, the generated sql is in the form (with query.py deciding it needs to nest the query): select datas.id as datas_id, datas.a as datas_a etc. from (select datas.id as datas_id, arbitrary_data.somedata as arbitrary_data_somedata from datas join (my arbitrary_data table query with where clause ) as arbitrary_data where . order by arbitrary_data.somedata ) as tbl_row_count join datas on ... order by arbitrary_data.somedata The last line is the problem.. The from clause renames the column to arbitrary_data_somedata but the order by clause uses the inner form with a . still. The error: missing FROM-clause entry for table arbitrary_data (because that table only exists on the inner aliased table) Anyhow, if I rename the sort on the outer query to use the underscore manually, the query returns the correct results in the correct order. I believe the faulty behavior starts at line 455 in orm/query.py (trunk).I'm not sure if it is the Aliasizer that is not converting the column. Anyhow, I need this to work so I don't have to write my great big huge dynamic query out by hand so I'll be digging into the sqlalchemy code for a second. Is there is quick easy fix though? Thanks -Dennis --~--~-~--~~~---~--~~ 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: sorting mapped class by joined subquery result (error)
Sorry if this posts twice... I didn't get a Message has been sent page last time.. I posted a bug with a test case here: http://www.sqlalchemy.org/trac/ticket/449 Thanks Dennis --~--~-~--~~~---~--~~ 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: sorting mapped class by joined subquery result (error)
I've created a bug with an attached test: http://www.sqlalchemy.org/trac/ticket/449 Thanks! -Dennis --~--~-~--~~~---~--~~ 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: custom sql.
hi, i've got it this far now. but it doesnt like the q.address.lat/lon since it makes an alias in the join. how to i get at that alias? sajoin = q.join_to('address') sorder = ABS( + str(q.address.lat) + - %f) + ABS( + str(q.address.lon) + - %f) sorder %= (lat,lon) return q.select( sajoin ,order_by=asc(sorder) ,limit=800 ) On 1/31/07, Brent Pedersen [EMAIL PROTECTED] wrote: hi, how can put custom sql in a query? specifically in this code? the problem is with the order_by. i want to pass it some custom sql: q = session.query(Student) sajoin = q.join_to('address') return q.select( sajoin, order_by=(ABS(address.lat - %s) + ABS(address.lon - %s)) % (lat,lon) ,limit=800 ) that doesnt work . this does: order_by=asc((address.c.lat - lat) + (address.c.lon - lon)) but it's not quite what i need--the manhattan distance and i'd like the results to be in my mapped objects so i can still do student.address.lat, etc. i've scoured the docs, but maybe i've missed something. any pointers? thanks. -brent --~--~-~--~~~---~--~~ 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: sorting mapped class by joined subquery result (error)
Just a quick note, I tried out your suggestion to pass in a select statement. That does indeed work. There is an issue though, I tried using the contains_eager('myproperty') as noted in the docs and that only worked in combination with eagerload('myproperty'). I think the reason is that in the docs, the mapper is defined with lazy=False. That was a little confusing, but perhaps it is supposed to be that way. I would have thought that contains_eager implies eagerload. -Dennis On Jan 31, 12:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: quick easy fix is to use result set mapping instead, or feed a full select() statement into query.select(), which will skip the whole compilation step. i would like a small test case attached for this one so i can play with it, though. for example i dont see why its deciding to use the nesting feature of the compilation in the first place. On Jan 31, 1:12 pm, Dennis [EMAIL PROTECTED] wrote: I have a mapped class.. lets call it Data with a few properties Data.id (primary key), Data.a, Data.b, Data.c I want to query a few of these objects out.. but they need to be sorted by some arbitrary data arbitrary_data=select ( [Data.c.id, OtherClass.c.somedata], and_()).alias('somedata') ok.. now query the data: dat=Data.select( and_(.), from_obj=[ datas.join(arbitrary_data,arbitrary_data.c.id==datas.c.id) ] , order_by=[asc(arbitrary_data.c.somedata)]) Now, the generated sql is in the form (with query.py deciding it needs to nest the query): select datas.id as datas_id, datas.a as datas_a etc. from (select datas.id as datas_id, arbitrary_data.somedata as arbitrary_data_somedata from datas join (my arbitrary_data table query with where clause ) as arbitrary_data where . order by arbitrary_data.somedata ) as tbl_row_count join datas on ... order by arbitrary_data.somedata The last line is the problem.. The from clause renames the column to arbitrary_data_somedata but the order by clause uses the inner form with a . still. The error: missing FROM-clause entry for table arbitrary_data (because that table only exists on the inner aliased table) Anyhow, if I rename the sort on the outer query to use the underscore manually, the query returns the correct results in the correct order. I believe the faulty behavior starts at line 455 in orm/query.py (trunk).I'm not sure if it is the Aliasizer that is not converting the column. Anyhow, I need this to work so I don't have to write my great big huge dynamic query out by hand so I'll be digging into the sqlalchemy code for a second. Is there is quick easy fix though? Thanks -Dennis --~--~-~--~~~---~--~~ 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: sorting mapped class by joined subquery result (error)
On Jan 31, 4:57 pm, Dennis [EMAIL PROTECTED] wrote: little confusing, but perhaps it is supposed to be that way. I would have thought that contains_eager implies eagerload. great idea. i threw that into rev 2284. --~--~-~--~~~---~--~~ 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: any particular reason for creating unused lists?
another things i noted: - using value.lower() == value instead of value.islower() - ansisql.py: in _requires_quotes(): this bool(len([x for x in str(value) if x not in self._legal_characters()])) should be same as bool( s.translate( 256*' ', self._legal_characters() ) ) and that table(256) can be a static/global. oh no those are great, and should be fixed. the only ones i dont want to use are map() and reduce() since guido has said that list comprehensions should be used instead. i tend to not memorize all the little tweaky functions like these (note that I am frequently coming out against humane interfaces since i am more of a thinker and less of a memorizer). one reason you wont see too much performance gain with those in particuilar is because the quoting system caches all the decisions it makes about identifiers. hmmm. i run 300 times a query, and it gets something like #calls 47026__generic_obj_format called from: ansisql.py:929(format_label)(10530) ansisql.py:935(format_table)(20273) ansisql.py:942(format_column)(16223) which is a lot. Only getattr, isinstance etc are more. Anyway, my attempts at brainless (non-logic) optimisation show this: - fixing a little __generic_obj_format(): ~1% gain - replace OrderedSet( sets.Set+OrderedDict) with one over set: ~3% - little fix of _process_froms() and append_column(): 0.5% This is 1 setup + many times a relatively simple query. -- as of trunk: 2017068 function calls (193 primitive calls) Ordered by: internal time List reduced from 1631 to 15 due to restriction 15 %ncalls tottime filename:lineno(function) 4% 8101.030ansisql.py:343(visit_select) 3%1343680.780:0(getattr) 3% 170330.730ansisql.py:208(visit_column) 3% 470260.690ansisql.py:892(__generic_obj_format) 2% 441250.590util.py:178(__setitem__) 2%1354200.570:0(isinstance) 2% 122730.510sql.py:1514(append_column) 1% 121800.380interfaces.py:66(_get_context_strategy) 1% 8330.370base.py:553(__init__) 1% 162230.370ansisql.py:933(format_column) 1% 117830.330sql.py:1223(__init__) 1% 344600.320sql.py:1263(accept_visitor) 1% 741350.320:0(has_key) 1% 452610.280:0(lower) 1% 208560.260sets.py:519(add) ++ with my changes: 1910888 function calls (1895502 primitive calls) Ordered by: internal time List reduced from 1631 to 15 due to restriction 15 %ncalls tottime filename:lineno(function) 5% 8101.090 ansisql.py:343(visit_select) 4%1343520.870:0(getattr) 3% 170330.770ansisql.py:208(visit_column) 3% 470260.670ansisql.py:892(__generic_obj_format) 3%1321180.640:0(isinstance) 2% 121800.490interfaces.py:66(_get_context_strategy) 2% 162230.420ansisql.py:933(format_column) 1% 122730.380sql.py:1514(append_column) 1% 139590.370sql.py:1551(_process_froms) 1% 344600.300sql.py:1263(accept_visitor) 1% 8330.290base.py:553(__init__) 1% 105300.290ansisql.py:203(visit_label) 1% 202730.280ansisql.py:926(format_table) 1% 426670.260:0(get) 1% 306320.260oset.py:8(add) === definitely, not that big gain (in 3 hours). btw, for the record, comparative time of these (x,y are strings): 9 '%s.%s' % (x,y) 12 str(x)+'.'+str(y) 16 '.'join( (str(x),str(y))) 5 x+'.'+y 9 '.'join( x,y) same but x 3 14 '%s.%s.%s.%s.%s.%s' % (x,y , x,y, x,y) 40 str(x)+'.'+str(y)+... 34 '.'join( (str(x),str(y)...)) 19 x+'.'+y+,,, 15 '.'join( x,y,...) func calls _are_ expensive... ciao svil --~--~-~--~~~---~--~~ 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: custom sql.
use func.abs(q.address.lat) for the ABS function. On Jan 31, 2007, at 4:24 PM, Brent Pedersen wrote: hi, i've got it this far now. but it doesnt like the q.address.lat/lon since it makes an alias in the join. how to i get at that alias? sajoin = q.join_to('address') sorder = ABS( + str(q.address.lat) + - %f) + ABS( + str(q.address.lon) + - %f) sorder %= (lat,lon) return q.select( sajoin ,order_by=asc(sorder) ,limit=800 ) On 1/31/07, Brent Pedersen [EMAIL PROTECTED] wrote: hi, how can put custom sql in a query? specifically in this code? the problem is with the order_by. i want to pass it some custom sql: q = session.query(Student) sajoin = q.join_to('address') return q.select( sajoin, order_by=(ABS(address.lat - %s) + ABS(address.lon - % s)) % (lat,lon) ,limit=800 ) that doesnt work . this does: order_by=asc((address.c.lat - lat) + (address.c.lon - lon)) but it's not quite what i need--the manhattan distance and i'd like the results to be in my mapped objects so i can still do student.address.lat, etc. i've scoured the docs, but maybe i've missed something. any pointers? thanks. -brent --~--~-~--~~~---~--~~ 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: custom sql.
ah. that makes sense. here's my working query. returns results in order of distance to a point (lat,lon): q = session.query(Student) j = q.join_to('address') return q.select( j,order_by=asc(func.abs(address.c.lat -lat) + func.abs(address.c.lon-lon)) ,limit=500 ) thanks for the response. ...and sqlalchemy. -brent On 1/31/07, Michael Bayer [EMAIL PROTECTED] wrote: use func.abs(q.address.lat) for the ABS function. On Jan 31, 2007, at 4:24 PM, Brent Pedersen wrote: hi, i've got it this far now. but it doesnt like the q.address.lat/lon since it makes an alias in the join. how to i get at that alias? sajoin = q.join_to('address') sorder = ABS( + str(q.address.lat) + - %f) + ABS( + str(q.address.lon) + - %f) sorder %= (lat,lon) return q.select( sajoin ,order_by=asc(sorder) ,limit=800 ) On 1/31/07, Brent Pedersen [EMAIL PROTECTED] wrote: hi, how can put custom sql in a query? specifically in this code? the problem is with the order_by. i want to pass it some custom sql: q = session.query(Student) sajoin = q.join_to('address') return q.select( sajoin, order_by=(ABS(address.lat - %s) + ABS(address.lon - % s)) % (lat,lon) ,limit=800 ) that doesnt work . this does: order_by=asc((address.c.lat - lat) + (address.c.lon - lon)) but it's not quite what i need--the manhattan distance and i'd like the results to be in my mapped objects so i can still do student.address.lat, etc. i've scoured the docs, but maybe i've missed something. any pointers? thanks. -brent --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---