[sqlalchemy] datetime interval to a number..
hi all, I've been trying to work out some SQLalchemy code to query two datetime columns and choose the one closest in time. I have some code that does this, but it requires using PostgreSQL and looks a little messy: query = query.order_by(ABS(EXTRACT(EPOCH FROM (header.utdatetime - :utdatetime_x.params(utdatetime_x= self.header.utdatetime) I've been working on a piece of code that is all SQLalchemy, but I keep getting an error that I can't use the absolute value function on an interval and casting the interval to Numeric isn't working either: query = query.order_by(func.abs(cast(Header.utdatetime - self.header.utdatetime, Numeric())).asc()) any help would be greatly appreciated. -- 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.
Re: [sqlalchemy] datetime interval to a number..
haha you guys make it so easy for us and yet we still can't get. Thanks so
much for your help! I think that'll do me just fine.
On Thu, Jan 27, 2011 at 3:57 PM, Michael Bayer mike...@zzzcomputing.comwrote:
you can emit that exact SQL using func.abs() in conjunction with the
extract() function, which is a standalone SQLA construct.
from sqlalchemy import func, extract
func.abs(extract('epoch', header.udatetime - self.header.udatetime))
On Jan 27, 2011, at 4:21 PM, Petra Clementson wrote:
hi all,
I've been trying to work out some SQLalchemy code to query two datetime
columns and choose the one closest in time. I have some code that does this,
but it requires using PostgreSQL and looks a little messy:
query = query.order_by(ABS(EXTRACT(EPOCH FROM (header.utdatetime -
:utdatetime_x.params(utdatetime_x= self.header.utdatetime)
I've been working on a piece of code that is all SQLalchemy, but I keep
getting an error that I can't use the absolute value function on an interval
and casting the interval to Numeric isn't working either:
query = query.order_by(func.abs(cast(Header.utdatetime -
self.header.utdatetime, Numeric())).asc())
any help would be greatly appreciated.
--
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
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.
Re: [sqlalchemy] combining join with alias
On Sat, Jan 8, 2011 at 7:59 PM, Petra Clementson petraclement...@gmail.comwrote: On Sat, Jan 8, 2011 at 10:38 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 8, 2011, at 3:23 PM, Petra Clementson wrote: I want to do a self join on combined but it wont let me. Essensially, I want to join two different tables, and create aliases so I can compare one column and make sure that each item in the column is uniqe. If there are duplicates, I want my piece of code to show the duplicates. Combining join with alias seems like the best way to do this because when using other methods, if I change the duplicates to a unique name, the name that used to be a duplicate still prints. Note that the Header table has a foreign key reference to DiskFile and the sql version used was 0.6.5. This is the portion of code where I am getting an error: combined = join(DiskFile, Header) combined_alias = aliased(combined) ERROR: Traceback (most recent call last): File duplicatedl.py, line 32, in module combined_1 = aliased(combined) File /opt/sqlalchemy/lib/python2.5/site-packages/sqlalchemy/orm/ util.py, line 304, in __init__ self.__target = self.__mapper.class_ AttributeError: 'SQLCompiler' object has no attribute 'class_' This could perhaps be improved in the interface; aliased() applies to mapped classes and mappers. join() is a Selectable expression. To alias a selectable, use its alias() method to generate an alias object: join(A, B).alias() I'll think about having orm.aliased() detect a selectable and return selectable.alias() in 0.7. Thanks for your prompt reply! This looks like something I haven't tried but would probably work. I'll have to try it on Monday and let you know how it goes. -- Okay, so I implemented join(A, B).alias(), but I can't seem to access the columns of the aliased table. Here is my code: session = sessionfactory() combined_1 = join(DiskFile, Header).alias() combined_2 = join(DiskFile, Header).alias() query = session.query(header).select_from(combined_1, combined_2).filter(DiskFile.canonical == True).filter(Header.datalab != 'none').filter(combined_1.datalab == combined_2.datalab) And I get: AttributeError: 'Alias' object has no attribute 'datalab' Any ideas? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] combining join with alias
I want to do a self join on combined but it wont let me. Essensially, I want to join two different tables, and create aliases so I can compare one column and make sure that each item in the column is uniqe. If there are duplicates, I want my piece of code to show the duplicates. Combining join with alias seems like the best way to do this because when using other methods, if I change the duplicates to a unique name, the name that used to be a duplicate still prints. Note that the Header table has a foreign key reference to DiskFile and the sql version used was 0.6.5. This is the portion of code where I am getting an error: combined = join(DiskFile, Header) combined_alias = aliased(combined) ERROR: Traceback (most recent call last): File duplicatedl.py, line 32, in module combined_1 = aliased(combined) File /opt/sqlalchemy/lib/python2.5/site-packages/sqlalchemy/orm/ util.py, line 304, in __init__ self.__target = self.__mapper.class_ AttributeError: 'SQLCompiler' object has no attribute 'class_' Please help! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] combining join with alias
On Sat, Jan 8, 2011 at 10:38 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 8, 2011, at 3:23 PM, Petra Clementson wrote: I want to do a self join on combined but it wont let me. Essensially, I want to join two different tables, and create aliases so I can compare one column and make sure that each item in the column is uniqe. If there are duplicates, I want my piece of code to show the duplicates. Combining join with alias seems like the best way to do this because when using other methods, if I change the duplicates to a unique name, the name that used to be a duplicate still prints. Note that the Header table has a foreign key reference to DiskFile and the sql version used was 0.6.5. This is the portion of code where I am getting an error: combined = join(DiskFile, Header) combined_alias = aliased(combined) ERROR: Traceback (most recent call last): File duplicatedl.py, line 32, in module combined_1 = aliased(combined) File /opt/sqlalchemy/lib/python2.5/site-packages/sqlalchemy/orm/ util.py, line 304, in __init__ self.__target = self.__mapper.class_ AttributeError: 'SQLCompiler' object has no attribute 'class_' This could perhaps be improved in the interface; aliased() applies to mapped classes and mappers. join() is a Selectable expression. To alias a selectable, use its alias() method to generate an alias object: join(A, B).alias() I'll think about having orm.aliased() detect a selectable and return selectable.alias() in 0.7. Thanks for your prompt reply! This looks like something I haven't tried but would probably work. I'll have to try it on Monday and let you know how it goes. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
