So, as I stated in a previous entry, I’ve been using SQLAlchemy for more than three years now. If you know anything about me, you’d find it amazing that I have contributed little more than a patch here or there. Why is that? I think that’s because every time I try to find something that I need SQLAlchemy to do, it already does it. I have spent so little time trying to make SQLAlchemy do what I want it to do, and so _much_ time getting work done with it.
Here is a good example. One o
f my clients is a
company developing sports management software by the name of MVP. Although I work on their next-gen stuff, I was called on by them to promote the students on the older system to the next grade over in the summer time. Their database system had well over 40 tables, requiring no less then 10 of them to have modification. I fired up sa, reflected the changes, wrote the changes in python, with simple loops around what records I needed to change, an it was done. In one hour’s time I had a happy client, and a school system with a functioning system. Two things about this are amazing. 1. At the time, I had practically zilch in the Postgres experience department. 2. I only needed to learn the schema of the tables I was modifying, and I could do this with introspection. The fact that I was able to do this task in less than an hour was only made possible by my knowledge of SQLAlchemy’s table-based architecture. I did not need to know the nuances of Postgres’ SQL language (I was more familiar with MySQL at the time), or a in-depth knowledge of the database schema. I was able to pick and prod until the job was done, and it was painless.
I really like the fact that SQLAlchemy’s approach to making an ORM is layered. I can drop to whatever layer I need to to meet the requirements of my project. SQL, table objects, and mapped objects all have their place in the grand scheme of things, and I have used them all to varying degrees. What SQLAlchemy achieves with this layering is the ability to adapt to existing projects, schemas, etc, and make considerably useful software, in a short amount of time. By figuring out the nuances of different SQL dialects, it frees me up to focus on the task at hand, and provide products to my clients that work in a broad range of situations.
But this post is supposed to be about TurboGears, right? For me, SQLAlchemy is more than a tool I use for TurboGears projects, or even web applications, it’s something I can use any time I have to access a relational database. It is the ultimate base for writing tools that help me get my job done. That job might be providing a system to allow schools to schedule matches against each other, or enabling scientists to access their data directly using objects at a python prompt, instead of assembling arcane sql strings to gather data. SQLAlchemy is such a good basis in fact, that it makes building tools to help do my job even easier.
I have had the opportunity to contribute to sqlalchemy-migrate. If you don’t know anything about the project, consider this: You have a production database you cannot break. You have 5 minutes downtime to modify the schema, update records, etc. You need to be able to back out the changes and bring the system back up if everything breaks. Migrate lets you do all of this, and provides a versioning system to ensure that such a process moves smoothly. I have used migrate with postgres, found some rough edges and fixed them. SA’s table mapping makes this possible, migrate just adds a layer to make versioning and some table creation processes easier. I only hope that someday some of the migrate code makes it’s way back into the SA codebase.
I do lots of testing. I made numerous schema changes today to one of my databases at www.nrel.gov in fact. I test the database schema for
matching against my definitions with some yet-to-be-released software. There are about 3000 tests. With tables numbering in the fifty-semod, I needed something to make data entry easier for my tests. I wrote bootalchemy to do this. You pass it some YAML, and the models module, and it performs all the entries for you. It does a bit of introspection to determine dates, and it has reference pointing so you can inter-connect your objects within your yaml (using & and * like my old friend C). This has vastly decreased the amount of time it takes me to create new test data for new tests. Again, this is possible only with the framework that SA provides me.
Lately I have been interested in providing a broader base of scientists at NREL access to their data using python as a medium. Scientists (especially physicists) are really good at conceptualizing data. They use crazy tools like Matlab, and R and all sorts of proprietary tools to manipulate their data. They aren’t afraid of a command prompt. My idea is to give them something like Sage, but with direct access to their data as mapped objects. I also want to be able to show up at a scientist’s desk with laptop in tow, connect to one of their existing databases, and spit out a admin-style web interface based on TG in a few minutes. These notions have driven me to contribute sqlautocode. At this point sqlautocode will spew out a page (or 7) of python code that provides you with Declarative Objects and an interactive prompt. sqlautocde works as a library, so you can use saautocode’s output to directly in memory without generating any code at all. All this is possible with SA, and I don’t know of anything else that can do all of this.
I have focused on the technical here, but beyone that is a great team of individuals like Mike Bayer and Jason Kirtland who put in long hours and answer questions promptly on the mailing list. I often wonder how these guys get any sleep. The thing that excites me most about SA is that it will soon release version 0.6, which to me means that these guys have 4 more versions of increased functionality before they consider it “done.” So, as you can see, there is more to SA than just the ORM it provides. It’s framework gives you freedom to expand your horizons and get your job done, by focusing it’s task on the challenges that relational databases all have, so you don’t have to. This is what makes SA a killer feature of TG.
The third segment in this series is: TG’s Killer Feature: The Admin (Yes, We do, and it rocks)