sqlalchemy

I ran into an interesting issue when using SQLAlchemy 0.7.7 with the Informix dialect. In a rather ugly database (which dates back to the late 1980s) there is a table called "xrefr" that contains two types of records: "supersede" and "cross". What those signify doesn't really matter for this issue so I'll skip any further explanation. But the really twisted part is that while a single field distinquishes between these two record types - it does not do so based on a consistent value.

You have a text file with four million records and you want to load this data into a table in an SQLite database. But some of these records are duplicates (based on certain fields) and the file is not ordered. Due to the size of the data loading the entire file into memory doesn't work very well. And due to the number of records doing a check-at-insert when loading the data is also prohibitively slow. But what does work pretty well is just to load all the data and then deduplicate it.

There are lots of examples of how to use SQLAlchemy to provide your Python application with a first-rate ORM. But most of these examples tend to model very trivial queries; yet the real power of SQLAlchemy, unlike many ORM solutions, is that it doesn't hide / bury the power of the RDBMS - and if you aren't going to use that power why bother with an RDBMS at all [Aren't NoSQL solutions the IT fad of the year? You could be so hip!].

A common action when synchronizing data between some source and a database is to check if such-and-such record already exists and needs to be updated or if a new record needs to be created. The SQLAlchemy's one() method [of the query object] provides an easy way to check to see if such-and-such record exists; but it doesn't return either an ORM object or None - if no record is found it raises an exception. This is surprising at first as x=do;if-not-x is possibly the most common of all Python constructs.

JSON doesn't provide a date or date-time construct; therefore every application is burdened with implementing a solution of it's own for receiving date-time information over the wire. On common issue receiving JSON and serializing that data into some type of database - but the database knows the value is a date-time and you want to be able to perform date-time like operations on the value (locally).

OpenGroupware Coils uses SQLAlchemy as it's ORM. One of the desired features was a Logic command that efficiently returns contacts with upcoming birthdays. In raw SQL this query would be very simple to write - but how to do it in SQLAlchemy? The answer: sql.expression.extract which will create an expression column equivalent to EXTRACT. With EXTRACT it is possible to compare to the year-of-day represented by a date. The Python code looks like:

In implementing the List method of the EntityAccessManager provided by the Contacts bundle in OpenGroupware Coils it seemed like it would be very efficient to allow the consumer to request what set of attributes it needed; for instance, if a WebDAV client's PROPFIND request didn't ask for a given property, why request the corresponding attribute in the query? Especially since the result set for PROPFIND queries are frequently very large [on the order of 20,000 records or so]. But how to pass a set of attributes as a parameter?