[sqlalchemy] Splitting concatenated fields into separate values
I posted a recipe for splitting multiple fields (by byte range) out of single table columns at https://gist.github.com/1263055 . I have to interact with some legacy tables that aren't remotely closed to being 1NF. This lets me treat byte ranges without those columns as separate columns so I can filter on them. I know this is an awful thing to do, but this at least makes those tables usable until we can deploy their replacements. Anyway, I thought maybe someone else could use this and that I'd save them the effort. Kirk -- 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] Validation of new objects before committing
On Sep 28, 2011, at 9:45 AM, Michael Bayer wrote: Also, there are type-based validations, fine, string, numeric, integer, dates, including length of strings. If someone throws on postgresql.INET, not really, unless the contract of TypeEngine objects is extended such that they *all* get in-python validation functions. Which is really, really redundant, as DBAPIs do that most of the time. Very heavyhanded for very little use - we definitely don't want these validations turned on all the time as they'd kill performance unnecessarily. String length in particular, we have to deal with unicode conversions before checking length, some databases store unicode as number of chars others as number of encoded bytes, it's complicated, and entirely redundant vs. what the database already does. OK, you've convinced me. I hadn't taken those cases into consideration; they don't come up much in the stuff I'm working with. For the record, though (in case anyone ever Googles this and wonders what I was thinking), I never thought of this as a mandatory behavior but as something that would be there if you wanted to use it, like: from sqlalchemy import getvalidationerrors newobj = MyClass(column1='foo', column2='bar') for error in getvalidationerrors(newobj): [...] Anyway, thanks for the pointers to a workable ad-hoc approach. - Kirk -- 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.
[sqlalchemy] Validation of new objects before committing
Does SA natively support (or is there a module on PyPI that supports)
client-side validation of SQLAlchemy objects? For example, I have this
declarative class:
class ImportedPayment(Base):
__tablename__ = 'importedpayment'
__table_args = {'schema': 'public'}
paymentid = Column(Integer, primary_key=True)
externalid = Column(String(16), nullable=False)
line = Column(Integer, nullable=False)
invoicestatus = Column(String(32), nullable=False)
quantity = Column(Numeric(scale=2), nullable=False)
rate = Column(Numeric(scale=2), nullable=False)
I'm reading data from a directory full of spreadsheets and generating millions
of these objects. If one contains invalid data, it's OK to just log it and move
on. Unfortunately, that pretty much means that I have to commit after every
insertion so that I can catch any potential exceptions and this makes the whole
process take ages to run.
Now, I've already defined the error conditions I'm likely to encounter and that
I can easily handle: they're the constraints I defined in the class above. I'd
love for the objects I'm creating to validate themselves through a method call
like:
newobject = ImportedPayment(externalid='foo', line=None, [...])
try:
newobject.validate()
except ValueError as e:
print e.column, e.errmsg
else:
session.add(newobject)
yielding
'line', 'None in not-nullable column'
or similar. Granted, I could write these tests easily myself:
if externalid is None or len(externalid) 16:
return False
but I've already specified them once and I don't want to repeat myself as it's
a lot of extra typing and a lot harder to maintain (if I change invoicestatus
to a String(64), I have to update every module which manually validates that
data). Note that I'm not talking about higher-level checks like the
emailaddress Column contains a valid email address, but just the simple data
type checks that can be inferred from class definitions (strings of appropriate
length, not null, etc.).
Is there an easy way to do this? If not, why? And if the answer to that is
because you haven't written it yet, would anyone be interested in using it if
I were to create such a thing?
Kirk
--
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.
[sqlalchemy] Show SQL that would be executed to create a table - but don't do it?
Given a largish file with a lot of table definitions in it, and SQLAlchemy 0.5.7, how can I see what SQL would be generated to create one specific table on a PostgreSQL database without actually attempting to create the table? -- Kirk Strauser -- 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] Comparator factory generator for the substr() of columns
I'm writing SA classes against a very unnormalized legacy database. Some of
our columns comprise several distinct values, and I wanted to take this
opportunity to split those into separate properties. I also wanted to be able
to search on the values in those properties, and here's my solution to it.
from sqlalchemy import func
from sqlalchemy.orm.properties import ColumnProperty
def substrcomparatormaker(column, i, j=None):
Return a comparator that tests for the substring of 'column'
from 'i' to 'j', as specified with Python slice values. This
means setting the start column to i + 1 because Python is 0-based
and SQL is 1-based, and setting the length to j - i.
To search for columns where table.foo[0:3] has a certain value, use:
@comparable_using(substrcomparatormaker(foo, 0, 3)) # = substr(1, 3)
To search on table.foo[4:6], use:
@comparable_using(substrcomparatormaker(foo, 4, 6)) # = substr(5, 2)
class SubstrComparator(ColumnProperty.Comparator):
Subclass of Comparator that looks at a slice of a column
def __eq__(self, other):
Compare the substr of a column to the given value
if j is None:
return func.substr(column, i + 1) == other
else:
return func.substr(column, i + 1, j - i) == other
return SubstrComparator
Here's how I use it in production:
class Invoice(Base):
__tablename__ = 'invoice'
typeofinv = Column(String(10))
@comparable_using(substrcomparatormaker(typeofinv, 0, 3))
@property
def shiptype(self):
The shipment's type
return self.typeofinv[:3]
@comparable_using(substrcomparatormaker(typeofinv, 3, 6))
@property
def shiptariff(self):
The shipment's tariff
return self.typeofinv[3:6]
@comparable_using(substrcomparatormaker(typeofinv, 6))
@property
def shipmode(self):
The shipment's mode
return self.typeofinv[6:]
invoice = session.query(Invoice).filter(Invoice.invid==2380724)
invoice = invoice.filter(Invoice.shiptype=='DPS')
invoice = invoice.filter(Invoice.shiptariff=='DOM')
invoice = invoice.filter(Invoice.shipmode=='HHG')
The point of mangling the arguments to substrcomparatormaker is so they can
have the same start and end values as the Python slices that they're mapping
to. I'd rather write Python in Python than SQL in Python.
For bonus points, I guess I could've made a function that accepts (column, i,
j) and returns the entire property definition, but this seemed useful in the
general case. For example, I might've wanted something like:
@comparable_using(substrcomparatormaker(typeofinv, 0, 3))
@property
def shiptype(self):
The shipment's type
return {'FOO': 'Foo invoice', 'BAR': 'Bar invoice'}[self.typeofinv[:3]]
--
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Thursday 30 July 2009 04:26:20 pm Michael Bayer wrote:
you have to get the select() syntax right:
BillingInfo = relation('BillingInfo',
primaryjoin=and_(Invoice.pay2addrid==BillingInfo.pay2addrid,Invoice.custom
er==
select([Customer.customer]).where(Customer.xrscustid==BillingInfo.xrscustid
)))
Michael, a million thanks. Seriously. That did exactly what I needed, and
should handle a bunch of similar issues with other legacy tables. I appreciate
it!
--
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Friday 31 July 2009 08:30:52 am Kirk Strauser wrote:
On Thursday 30 July 2009 04:26:20 pm Michael Bayer wrote:
you have to get the select() syntax right:
BillingInfo = relation('BillingInfo',
primaryjoin=and_(Invoice.pay2addrid==BillingInfo.pay2addrid,Invoice.cust
om er==
select([Customer.customer]).where(Customer.xrscustid==BillingInfo.xrscust
id )))
Michael, a million thanks. Seriously. That did exactly what I needed, and
should handle a bunch of similar issues with other legacy tables. I
appreciate it!
Can you stand one more question on this? I tried to enable eager loading on
this table:
invoices = invoices.options(eagerload('BillingInfo'))
but ended up with an error:
sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT fro.xrscust.xrscust
FROM fro.xrscust, fro.bllginfo AS bllginfo_1
WHERE bllginfo_1.xrscustid = fro.xrscust.xrscustid' returned no FROM clauses
due to auto-correlation; specify
correlate(tables) to control correlation manually.
I don't really understand what it's asking for, and the docs for correlate()
only seem to explain how to configure it and not what I should want it
to be set to.
--
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Friday 31 July 2009 09:16:21 am Michael Bayer wrote: you likely want to call correlate(billing_table) on your select. rows inside the subquery want to correlate outwards to the parent billing table. Resulting in: sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|BillingInfo|bllginfo' has no property '_deannotate' I think I'm going to put this on pause and take another look after I get back from vacation and my neurotransmitters are replenished. :-) -- 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Friday 31 July 2009 10:19:29 am Michael Bayer wrote:
BillingInfo.__table__. BillingInfo is a python class, billing_table is
the Table object.
After all that, it turned out that yet *another* table needed to be linked in.
Here's what I finally ended up with:
class Invoice(Base):
BillingInfo = relation('BillingInfo',
primaryjoin=''.join(
and_(Invoice.pay2addrid==BillingInfo.pay2addrid,
Invoice.customer==Customer.customer,
Customer.xrscustid==BillingInfo.xrscustid,
Invoice.shipid==Ship.shipid,
or_(and_(Ship.typeship=='BOL',BillingInfo.typeship=='GBL'),
and_(Ship.typeship!='BOL',BillingInfo.typeship==Ship.typeship))
)
.split('\n')))
This works, always returns the right values, and is eagerload-friendly.
Thanks again for pointing me in the right direction! Even if this is quite a
bit of upfront work, I've already saved screenfuls of SQL by converting to SA.
--
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Thursday 30 July 2009 12:56:19 pm Michael Bayer wrote: at this point, invoices[0] is the invoice that is subject to the given filter criterion. With you so far. print invoices[0].BillingInfo.typeship now this part is very unusual and is something I haven't tested. Your foreign key is to only one column of a composite primary key - very strange. So SQLA probably sees this as many-to-one but the result in which the lazy load will incur is essentially random since many BillingInfo entries may have that same value. That's exactly it. I tried forcing an eager load, but the resulting left outer join yielded the same random results. I suppose that until we restructure the database, a workaround would be to split the query into two steps where I fetch the BillingInfo rows and loop through them manually, but I'm lazy and I'd much rather let SA do the work if I can teach it how. Its very likely that your linkage here is incorrect, and you in fact want to declare, at least within SQLAlchemy-land, a composite foreign key (using ForeignKeyConstraint) on Invoice that matches both invoice.xrscust and invoice.pay2addrid to both of the corresponding columns on BillingInfo. The explicit join condition on the relation() would then no longer be needed (the need to explicitly declare things SQLA should be figuring out may be considered a code smell here). But Invoice doesn't have both of those columns. It has a String reference to Customer, while BillingInfo has an Integer reference to Customer [1]. That's why I ended up with the cyclic relation, and wouldn't otherwise need to involve Customer at all. [1] Again, legacy. The numeric primary key was added *relatively* recently and new tables use it. At this exact moment, we have both String and Integer references to the Customer table. This whole mess is based on data that are copied from Visual FoxPro tables on an hourly basis, and all new code is written to query PostgreSQL instead of VFP. -- 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Fetching wrong values from a query involving composite primary keys
On Thursday 30 July 2009 01:31:21 pm Michael Bayer wrote:
oh, its the string. OK so dont do ForeignKeyConstraint, but definitely
add to the Invoice.BillingInfo relation() all the information needed
within the primaryjoin to select the correct row. seems like it would be
(pseudocode) invoice.pay2addrid=billing.pay2addrid AND
invoice.custinfo=select(customer.stringname).where(customer.id==billing.cus
tid). The subquery should work and I don't see another way to get around
that.
Going down that road, I tried:
class Invoice(Base):
__tablename__ = 'invoice'
__table_args__ = dict(schema='fro')
# Each invoice has a unique invid
invid = Column(Integer, primary_key=True)
# This is the name of the customer on this invoice
customer = Column('xrscust', String(10), ForeignKey('fro.xrscust.xrscust'))
# Some customers have multiple payment addresses, so point to the
# one used for this specific invoice
pay2addrid = Column(Integer, ForeignKey('fro.bllginfo.pay2addrid'))
BillingInfo = relation('BillingInfo',
primaryjoin=and_(Invoice.pay2addrid==BillingInfo.pay2addrid,Invoice.customer==select(Customer.customer).where(Customer.xrscustid==BillingInfo.xrscustid)))
but ended up with:
$ ./satest.py
Traceback (most recent call last):
File ./satest.py, line 67, in module
invoices = session.query(Invoice)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/session.py, line
895, in query
return self._query_cls(entities, self, **kwargs)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py, line
91, in __init__
self._set_entities(entities)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py, line
100, in _set_entities
self.__setup_aliasizers(self._entities)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py, line
114, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/util.py, line
492, in _entity_info
mapper = class_mapper(entity, compile)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/util.py, line
567, in class_mapper
mapper = mapper.compile()
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py, line
658, in compile
mapper._post_configure_properties()
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py, line
687, in _post_configure_properties
prop.init()
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/interfaces.py,
line 408, in init
self.do_init()
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/properties.py,
line 712, in do_init
self._process_dependent_arguments()
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/properties.py,
line 739, in _process_dependent_arguments
setattr(self, attr, getattr(self, attr)())
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/ext/declarative.py,
line 596, in return_cls
x = eval(arg, globals(), d)
File string, line 1, in module
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/sql/expression.py,
line 246, in select
s = Select(columns, whereclause=whereclause, from_obj=from_obj, **kwargs)
File /usr/local/lib/python2.6/dist-packages/sqlalchemy/sql/expression.py,
line 3239, in __init__
[_literal_as_column(c) for c in columns]
TypeError: 'Column' object is not iterable
Any idea where I might start digging into that?
--
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Merging output of several queries?
I'm developing a Django site that uses a lot of pre-existing SQLAlchemy tables to generate reports, etc. I'm trying to get my head around the best way to programmatically generate some fairly complex queries. For instance, our users may have several roles assigned to them, and the data they can see depends on the roles they have. What I planned to do was something along the lines of: results = [] for role in usersroles: query = session.query(DataTable) if role.field1value: query = query.filter_by(field1=field1value) if role.field2value: query = query.filter_by(field2=field2value) if role.field3value: query = query.filter_by(field3=field3value) results.extend(query.all()) That's really ugly for a few reasons, though. It's possible (and likely) that the same record might be returned by the results of several different queries, so I'd have to use a set or similar to weed out duplicates. Also, if the first role's query results in a million records and the second role's query gives the same million records, then I'm moving a whole lot of data for no good reason. Now, Django's built-in ORM supports using | to merge the output of several different queries, and it does this by actually generating generating the appropriate SQL. For example: models.Role.objects.filter(pk=1) [Role: foo] models.Role.objects.filter(pk=2) [Role: bar] models.Role.objects.filter(pk=2) | models.Role.objects.filter(pk=1) [Role: bar, Role: foo] print (models.Role.objects.filter(pk=1) | models.Role.objects.filter(pk=2)).query SELECT user_role.id, user_role.name FROM user_role WHERE user_role.id = 1 OR user_role.id = 2 ORDER BY user_role.name ASC Is there anything similar in SQLAlchemy? If not, is there an idiomatic approach for this? Thanks! -- 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] Best way to handle in()
I have mappers configured for main and child, and those tables are
linked on main.childid=child.childid. How can I use in() to get rows where
child.othercolumn is in a list of values? I'd like to do something like:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(ChildTable.c.othercolumn.in_('bar', 'baz', 'qux'))
Or, even better, some variant on:
foo = session.query(MainTable).filter_by(customer='CUSTNAME')
foo = foo.filter(othercolumn in ('bar', 'baz', 'qux'))
When I try to do that, though, I get SQL like:
SELECT main.value AS main_value, main.childid AS main_childid, child.childid
AS child_childid, child.othercolumn AS child_othercolumn
FROM testing.main, testing.child
WHERE child.othercolumn IN (%(child_othercolumn)s, %(child_othercolumn_1)s,
%(child_othercolumn_2)s) ORDER BY main.value
which is really doing a cartesian join and never enforcing
main.childid=child.childid. Is there another way I should be approaching
this?
--
Kirk Strauser
signature.asc
Description: This is a digitally signed message part.
[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: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] Queries on computed properties
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? 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'; I've tried to RTFM, but I'm really a beginner with it and don't even know what to search for yet. -- 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 -~--~~~~--~~--~--~---
