Yesterday I felt like fooling around, so I made a module to create SQL statements from Python expressions. This requires Python 2.1, which added the feature of arbitrary comparison overriding (instead of just cmp overriding).
You can find it at: http://207.228.250.44/software/webware/SQLBuilder.py Here's the doc string and then an example: SQLBuilder.py 7 Oct 2001, Ian Bicking <[EMAIL PROTECTED]> Builds SQL expressions from normal Python expressions. Python 2.1 or higher IS REQUIRED. To begin a SQL expression, you must use some sort of SQL object -- a field, table, or SQL statement (SELECT, INSERT, etc.) You can then use normal operators, with the exception of: and, or, not, and in. You can use the AND, OR, NOT, and IN functions instead, or you can also use &, |, and ~ for and, or, and not (however -- the precidence for these operators doesn't work as you would want, so you must use many parenthesis). To create a sql field, table, or constant/function, use the namespaces table, const, and func. For instance, table.address refers to the "address" table, and table.address.state refers to the "state" field in the address table. const.NULL is the NULL SQL constant, and func.NOW() is the NOW() function call (const and func are actually identicle, but the two names are provided for clarity). Once you create this object, expressions formed with it will produce SQL statements. The sqlRepr(obj) function gets the SQL representation of these objects, as well as the proper SQL representation of basic Python types (None==NULL). There are a number of DB-specific SQL features that this does not implement. There are a bunch of normal ANSI features also not present -- particularly left joins, among others. You may wish to only use this to generate WHERE clauses. EXAMPLES ======== First the Python expression, then the generated SQL expression. These are all concocted without much thought, I should probably dig through my real SQL statements to find good examples. (table.address.name == "Ian Bicking") & (table.address.zip > 30000) ((address.name = 'Ian Bicking') AND (address.zip > 30000)) ## Note the "Ian Bicking" or 30000 could of course be variables with ## those values. & has annoying precedence, hence all the ()'s in ## the Python expression Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}, ]) INSERT INTO address (address, name) VALUES ('3049 N. 18th St.', 'BOB'), ('409 S. 10th St.', 'TIM') Select([table.user.name], where=AND(table.user.state == table.states.abbrev)) SELECT user.name FROM user, states WHERE (user.state = states.abbrev) ## Note that the FROM portion is automatically generated -- Ian Bicking Colorstudy Web Design [EMAIL PROTECTED] http://www.colorstudy.com 4769 N Talman Ave, Chicago, IL 60625 / (773) 275-7241 _______________________________________________ Webware-discuss mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/webware-discuss
