sqlbuilder

Builds SQL expressions from normal Python expressions.

Disclaimer

This program is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.

Instructions

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 respectively (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.

See the bottom of this module for some examples, and run it (i.e. python sql.py) to see the results of those examples.


Attributes

a const

<sqlobject.sqlbuilder.ConstantSpace instance at 0x9578a6c>

a table

<sqlobject.sqlbuilder.TableSpace instance at 0x9578a4c>

a SQLTrueClause

1 = 1

a func

<sqlobject.sqlbuilder.ConstantSpace instance at 0x9578a6c>

a operatorMap

{'!=': <built-in function ne>,
 '*': <built-in function mul>,
 '+': <built-in function add>,
 '-': <built-in function sub>,
 '/': <built-in function div>,
 '<': <built-in function lt>,
 '<=': <built-in function le>,
 '=': <built-in function eq>,
 '>': <built-in function gt>,
 '>=': <built-in function ge>,
 'IN': <built-in function contains>,
 'IS': <built-in function eq>}

a safeSQLRE

<_sre.SRE_Pattern object at 0x9351598>

a __package__

'sqlobject'

Functions

f sqlIdentifier(obj) ...

f execute(expr, executor) ...

f SQLExprConverter(value, db) ...

f tablesUsedSet(obj, db) ...

f dictToList(template, dict) ...

f AND(*ops) ...

f OR(*ops) ...

f NOT(op) ...

f IN(item, list) ...

f NOTIN(item, list) ...

f STARTSWITH(expr, pattern) ...

f ENDSWITH(expr, pattern) ...

f CONTAINSSTRING(expr, pattern) ...

f ISNULL(expr) ...

f ISNOTNULL(expr) ...

f JOIN(table1, table2) ...

f INNERJOIN(table1, table2) ...

f CROSSJOIN(table1, table2) ...

f STRAIGHTJOIN(table1, table2) ...

f LEFTJOIN(table1, table2) ...

f LEFTOUTERJOIN(table1, table2) ...

f NATURALJOIN(table1, table2) ...

f NATURALLEFTJOIN(table1, table2) ...

f NATURALLEFTOUTERJOIN(table1, table2) ...

f RIGHTJOIN(table1, table2) ...

f RIGHTOUTERJOIN(table1, table2) ...

f NATURALRIGHTJOIN(table1, table2) ...

f NATURALRIGHTOUTERJOIN(table1, table2) ...

f FULLJOIN(table1, table2) ...

f FULLOUTERJOIN(table1, table2) ...

f NATURALFULLJOIN(table1, table2) ...

f NATURALFULLOUTERJOIN(table1, table2) ...

f INNERJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f FULLJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) ...

f INNERJOINOn(table1, table2, on_condition) ...

f LEFTJOINOn(table1, table2, on_condition) ...

f LEFTOUTERJOINOn(table1, table2, on_condition) ...

f RIGHTJOINOn(table1, table2, on_condition) ...

f RIGHTOUTERJOINOn(table1, table2, on_condition) ...

f FULLJOINOn(table1, table2, on_condition) ...

f FULLOUTERJOINOn(table1, table2, on_condition) ...

f INNERJOINUsing(table1, table2, using_columns) ...

f LEFTJOINUsing(table1, table2, using_columns) ...

f LEFTOUTERJOINUsing(table1, table2, using_columns) ...

f RIGHTJOINUsing(table1, table2, using_columns) ...

f RIGHTOUTERJOINUsing(table1, table2, using_columns) ...

f FULLJOINUsing(table1, table2, using_columns) ...

f FULLOUTERJOINUsing(table1, table2, using_columns) ...

f EXISTS(subquery) ...

f NOTEXISTS(subquery) ...

f SOME(subquery) ...

f ANY(subquery) ...

f ALL(subquery) ...

Classes

C VersionError(...) ...

This class contains 10 members.

C NoDefault(...) ...

C SQLObjectState(...) ...

This class contains 4 members.

C SQLExpression(...) ...

This class contains 36 members.

C SQLOp(...) ...

This class contains 39 members.

C SQLModulo(...) ...

This class contains 39 members.

C SQLCall(...) ...

This class contains 39 members.

C SQLPrefix(...) ...

This class contains 39 members.

C SQLConstant(...) ...

This class contains 39 members.

C SQLTrueClauseClass(...) ...

This class contains 38 members.

C Field(...) ...

This class contains 39 members.

C SQLObjectField(...) ...

This class contains 39 members.

C Table(...) ...

This class contains 40 members.

C SQLObjectTable(...) ...

This class contains 40 members.

C SQLObjectTableWithJoins(...) ...

This class contains 40 members.

C TableSpace(...) ...

This class contains 1 member.

C ConstantSpace(...) ...

C AliasField(...) ...

This class contains 39 members.

C AliasTable(...) ...

This class contains 41 members.

C Alias(...) ...

This class contains 38 members.

C Union(...) ...

This class contains 38 members.

C Select(...) ...

This class contains 48 members.

C Insert(...) ...

This class contains 38 members.

C Update(...) ...

This class contains 39 members.

C Delete(...) ...

To be safe, this will signal an error if there is no where clause, unless you pass in where=None to the constructor.

This class contains 38 members.

C Replace(...) ...

This class contains 39 members.

C DESC(...) ...

This class contains 38 members.

C ColumnAS(...) ...

Just like SQLOp('AS', expr, name) except without the parentheses

This class contains 39 members.

C SQLJoin(...) ...

This class contains 38 members.

C SQLJoinConditional(...) ...

Conditional JOIN

This class contains 38 members.

C SQLJoinOn(...) ...

Conditional JOIN ON

This class contains 38 members.

C SQLJoinUsing(...) ...

Conditional JOIN USING

This class contains 38 members.

C OuterField(...) ...

This class contains 39 members.

C OuterTable(...) ...

This class contains 40 members.

C Outer(...) ...

This class contains 1 member.

C LIKE(...) ...

This class contains 40 members.

C RLIKE(...) ...

This class contains 41 members.

C INSubquery(...) ...

This class contains 39 members.

C NOTINSubquery(...) ...

This class contains 39 members.

C Subquery(...) ...

This class contains 38 members.

C ImportProxyField(...) ...

This class contains 39 members.

C ImportProxy(...) ...

Class to be used in column definitions that rely on other tables that might not yet be in a classregistry.

This class contains 39 members.

See the source for more information.