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