After some thought on what I need in a Python ORM (multiple primary keys, complex joins, case statements etc.), and after having built these libraries for other un-named languages, I decided to start at the bottom. What seems to plague many ORM systems is the syntactic confusion and string-manipulation required to build the SQL Statements. If you want to do a Left Outer Join, support nested functions, and a nested conditional clause, you'd be building more of a string library than an ORM. Let's not even mention syntactical differences between databases, data types, and such.
My solution is sqlstring. A single-purpose library: to create SQL statement objects. These objects (such as sqlstring.Select), represent complex SQL Statements, but as Python objects. The benefit is that you can, at run-time, "build" the statement pythonically, without getting bogged down in String Manipulation. The theory is that once in use, things that were complex (string magic) become simpler, and allow the program to worry about higher-level issues. An Example: >>> import sqlstring >>> model = sqlstring.TableFactory() >>> print model.person SELECT person.* FROM [person] person >>> person_smith = model.person \ ** (model.person.first_name == "Smith") >>> print person_smith SELECT person.* FROM [person] person WHERE person.first_name = 'Smith' >>> print person_smith("party_id", "first_name") \ < < model.address ** (\ model.address.party_id == model.person.party_id) SELECT party_id party_id, first_name first_name, address.* FROM [person] person LEFT OUTER JOIN [address] address ON address.party_id = person.party_id WHERE person.first_name = 'Smith' Things can get much more interesting than this, including nested sub-selects (anywhere), conditional groups (and/or groups nested using & and |) and even CASE statements. Some of this stuff has been around for a while (using "magic" objects to build where clauses, etc.). But I'm trying to take it all the way--to a legit Select statement. While still in the early stages, it does work with a great many sql statements, as seen in the test suite. Currently supported are CASE statements, Nested conditional clauses, nested queries and most join types. At this point, I'm interested in getting feedback from the community on several fronts: 1. The Operator Overload model. I've chosen to overload Python's operators to give a short-hand syntax to most of the things you'd want to do with a select statement. The rest are accessable via methods. Currently ** is the "where" operator, // is the "in" operator, % the "like" operator and ^ aliases columns. Other overloads are as you'd expect- + / - * == all result in Expression Objects that dish out the right SQL string. The question is, is the "leap" in syntax to confusing? Is there a cleaner way to do this? (Functions for example) 2. How to best add further sql function support? Adding magic callable objects to columns came to mind, but this has it's own set of issues. I'm leaning towards a magic object in the sqlstring module. For example: sqlstring.F.substring(0, 4, person.first_name) would result in: substring(0, 4, person.first_name). the F object could be put in the local scope for short-hand. 3. I'm undecided on how best to handle database specific overwrites. I want this to be as easy as possible. I'm thinking about subclassing Expressions with a naming scheme on the Sub-Class (such as CaseExpression_oracle). Then the __init__ factory could dish out the right version of the object based on the the requestor. This brings up lots of questions, such as how to support multiple types of databases at the same time. Eventually the library should include all of the basic SQL Statements, including UPDATE, INSERT and CREATE statements. This is mostly for completeness, though. SELECT statements tend to be the most complex. The library can be downloaded at http://betur.net/download.php Any other thoughts or comments are very much appreciated -- http://mail.python.org/mailman/listinfo/python-list