[sqlalchemy] Re: Queries on computed properties
On May 2, 2007, at 8:32 PM, Michael Bayer wrote:
oh duh, i forgot about the new thing Gaetan came up with, try this
too:
mapper(Invoice, invoice_table, properties={
'customer':column_property(func.substr(invoice_table.c.invnum, 1,
4).label('customer'))
})
That's so very, *very* close. It works perfectly for that particular
column, but a slightly more complex function causes breakage. The
only problem is that it generates SQL that PostgreSQL 8.2 isn't quite
happy with. Given the following code:
rdy2bill_table = Table('rdy2bill', metadata,
Column('invid', Integer, primary_key=True),
Column('invnum', String),
Column('pprresp', String, key='responsible'),
Column('xmlvars', String),
)
mapper(ReadyToBill, rdy2bill_table, properties={
'customer' : column_property(func.substr
(rdy2bill_table.c.invnum, 1,
4).label('customer')),
'groupcode': column_property(func.substring
(rdy2bill_table.c.xmlvars, M.XRSGRPCD(.*)/M.XRSGRPCD).label
('groupcode')),
})
inv = session.query(ReadyToBill).get_by(invid=1000346504,
groupcode='BILLGSCB')
I get a query like:
'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS
customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS
rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum, rdy2bill.invid
AS rdy2bill_invid, substring(rdy2bill.xmlvars, %(substring)s) AS
groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid = %(rdy2bill_invid)
s) AND (substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = %
(literal)s) ORDER BY rdy2bill.invid \n LIMIT 1' {'substring':
'M.XRSGRPCD(.*)/M.XRSGRPCD', 'substr': 1, 'literal': 'BILLGSCB',
'rdy2bill_invid': 1000346504, 'substr_1': 4}
The killer part is the (substring(rdy2bill.xmlvars, %(substring)s)
AS groupcode = in the WHERE clause. PostgreSQL apparently doesn't
want that predicate to be named. Can that be disabled?
--
Kirk Strauser
--~--~-~--~~~---~--~~
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: Queries on computed properties
On May 3, 2007, at 5:29 PM, Kirk Strauser wrote: The killer part is the (substring(rdy2bill.xmlvars, %(substring)s) AS groupcode = in the WHERE clause. PostgreSQL apparently doesn't want that predicate to be named. Can that be disabled? -- not really (well yes, you can take the label off, but then you dont get it in your columns clause, so that will break). mapping to the select statement that includes the column is the more general solution here (also allows the function to be called once instead of twice). --~--~-~--~~~---~--~~ 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: Queries on computed properties
no wait, scratch my last email for a bit. try rev 2601.
On May 3, 2007, at 5:29 PM, Kirk Strauser wrote:
On May 2, 2007, at 8:32 PM, Michael Bayer wrote:
oh duh, i forgot about the new thing Gaetan came up with, try this
too:
mapper(Invoice, invoice_table, properties={
'customer':column_property(func.substr(invoice_table.c.invnum, 1,
4).label('customer'))
})
That's so very, *very* close. It works perfectly for that
particular column, but a slightly more complex function causes
breakage. The only problem is that it generates SQL that
PostgreSQL 8.2 isn't quite happy with. Given the following code:
rdy2bill_table = Table('rdy2bill', metadata,
Column('invid', Integer, primary_key=True),
Column('invnum', String),
Column('pprresp', String, key='responsible'),
Column('xmlvars', String),
)
mapper(ReadyToBill, rdy2bill_table, properties={
'customer' : column_property(func.substr
(rdy2bill_table.c.invnum, 1,
4).label('customer')),
'groupcode': column_property(func.substring
(rdy2bill_table.c.xmlvars, M.XRSGRPCD(.*)/M.XRSGRPCD).label
('groupcode')),
})
inv = session.query(ReadyToBill).get_by(invid=1000346504,
groupcode='BILLGSCB')
I get a query like:
'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS
customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS
rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum,
rdy2bill.invid AS rdy2bill_invid, substring(rdy2bill.xmlvars, %
(substring)s) AS groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid
= %(rdy2bill_invid)s) AND (substring(rdy2bill.xmlvars, %(substring)
s) AS groupcode = %(literal)s) ORDER BY rdy2bill.invid \n LIMIT
1' {'substring': 'M.XRSGRPCD(.*)/M.XRSGRPCD', 'substr': 1,
'literal': 'BILLGSCB', 'rdy2bill_invid': 1000346504, 'substr_1': 4}
The killer part is the (substring(rdy2bill.xmlvars, %(substring)s)
AS groupcode = in the WHERE clause. PostgreSQL apparently
doesn't want that predicate to be named. Can that be disabled?
--
Kirk Strauser
--~--~-~--~~~---~--~~
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: Queries on computed properties
On May 3, 2007, at 5:18 PM, Michael Bayer wrote: not really (well yes, you can take the label off, but then you dont get it in your columns clause, so that will break). mapping to the select statement that includes the column is the more general solution here (also allows the function to be called once instead of twice). Fair enough. But then, is there something else I can do to get it to emit PostgreSQL-compatible SQL? -- Kirk Strauser --~--~-~--~~~---~--~~ 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: Queries on computed properties
On May 3, 2007, at 5:31 PM, Michael Bayer wrote: no wait, scratch my last email for a bit. try rev 2601. Perfect! That was exactly what it needed. I have to say that after using SQLAlchemy for about a week, I'm really excited about this. -- Kirk Strauser --~--~-~--~~~---~--~~ 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: Queries on computed properties
On May 2, 1:54 pm, Kirk Strauser [EMAIL PROTECTED] wrote:
I'm new to SQLAlchemy and not sure exactly how to explain this in its
terminology, so please bear with me.
We moving to replace an in-house developed ORM with SQLAlchemy because it
works better with the software we want to use. One problem I have is that
we're working with some unnormalized tables that store multiple values in
certain columns. For example, a column invnum might be a varchar(20),
where the first four characters are a customer ID string, and the rest are
the the string representation of an integer invoice number. That is,
customer Foo, Inc. has an invoice 123456, and that is stored
as FOOI123456. Yes, this is unpretty, but we're working on it.
In the mean time, it's easy enough to create a property that returns the
customer ID, ala:
class Invoice(object):
def _getcustomer(self):
return self.invnum[:4]
customer = property(_getcustomer)
However, I also need to be able to search by that calculated value, ideally
with something like:
session.query(Invoice).get_by(customer='FOOI')
Is this even remotely possible?
with the basic mapping youre using, remotely possible yes, but not
your idealization. youd have to go with:
session.query(Invoice).get(func.substr(Invoice.c.invnum, 1, 4)=='FOO')
Our in-house ORM knew enough about our
table structure that it would generate SQL like:
select * from invoice where substr(invnum,1,4) = 'FOOI';
So here is the more fun part. you may be able to create a mapper to
this:
s = select([invoice_table, func.substr(invoice_table.c.invnum, 1,
4).label('customer')])
mapper(Invoice, s)
and then, youd probably get the querying functionality youre looking
for. plus youd even have a 'customer' attribute on your instance with
the right value. however, 'customer' wouldnt respond in memory when
you change the value of 'invnum', youd have to still use properties to
do that.
i say probably because i havent tried using get_by() with a function-
based column, would be curious to know if it works (or if not maybe i
can make it work).
--~--~-~--~~~---~--~~
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: Queries on computed properties
oh duh, i forgot about the new thing Gaetan came up with, try this
too:
mapper(Invoice, invoice_table, properties={
'customer':column_property(func.substr(invoice_table.c.invnum, 1,
4).label('customer'))
})
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
