# `sqlbuilder`

The sqlbuilder module is accessible via the `sqlobject` module.

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 0xa647e2c>

#### a `table`

    <sqlobject.sqlbuilder.TableSpace instance at 0xa647e0c>

#### a `SQLTrueClause`

    1 = 1

#### a `func`

    <sqlobject.sqlbuilder.ConstantSpace instance at 0xa647e2c>

#### 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 0xa404b10>

#### 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.