sqlobject.sqlbuilder module

sqlobject.sqlbuilder

author:

Ian Bicking <ianb@colorstudy.com>

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.

sqlobject.sqlbuilder.ALL(subquery)[source]
sqlobject.sqlbuilder.AND(*ops)[source]
sqlobject.sqlbuilder.ANY(subquery)[source]
classsqlobject.sqlbuilder.Alias(table, alias=None)[source]

Bases: SQLExpression

components()[source]
select(clause=None, clauseTables=None, orderBy=<class 'sqlobject.sqlbuilder.NoDefault'>, limit=None, lazyColumns=False, reversed=False, distinct=False, connection=None, join=None, forUpdate=False)[source]
classsqlobject.sqlbuilder.AliasField(tableName, fieldName, alias, aliasTable)[source]

Bases: Field

tablesUsedImmediate()[source]
classsqlobject.sqlbuilder.AliasSQLMeta(table, alias)[source]

Bases: object

classsqlobject.sqlbuilder.AliasTable(table, alias=None)[source]

Bases: Table

FieldClass

alias of AliasField

as_string=''
classsqlobject.sqlbuilder.CONCAT(*expressions)[source]

Bases: SQLExpression

sqlobject.sqlbuilder.CONTAINSSTRING(expr, pattern)[source]
sqlobject.sqlbuilder.CROSSJOIN(table1, table2)[source]
classsqlobject.sqlbuilder.ColumnAS(expr, name)[source]

Bases: SQLOp

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

classsqlobject.sqlbuilder.ConstantSpace[source]

Bases: object

classsqlobject.sqlbuilder.DESC(expr)[source]

Bases: SQLExpression

classsqlobject.sqlbuilder.Delete(table, where=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]

Bases: SQLExpression

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

sqlobject.sqlbuilder.ENDSWITH(expr, pattern)[source]
sqlobject.sqlbuilder.EXISTS(subquery)[source]
sqlobject.sqlbuilder.FULLJOIN(table1, table2)[source]
sqlobject.sqlbuilder.FULLJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.FULLJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.FULLJOINUsing(table1, table2, using_columns)[source]
sqlobject.sqlbuilder.FULLOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.FULLOUTERJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.FULLOUTERJOINUsing(table1, table2, using_columns)[source]
classsqlobject.sqlbuilder.Field(tableName, fieldName)[source]

Bases: SQLExpression

execute(executor)[source]
tablesUsedImmediate()[source]
sqlobject.sqlbuilder.IN(item, list)[source]
sqlobject.sqlbuilder.INNERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.INNERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.INNERJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.INNERJOINUsing(table1, table2, using_columns)[source]
classsqlobject.sqlbuilder.INSubquery(item, subquery)[source]

Bases: SQLExpression

components()[source]
op='IN'
sqlobject.sqlbuilder.ISNOTNULL(expr)[source]
sqlobject.sqlbuilder.ISNULL(expr)[source]
classsqlobject.sqlbuilder.ImportProxy(clsName, registry=None)[source]

Bases: SQLExpression

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

FieldClass

alias of ImportProxyField

classsqlobject.sqlbuilder.ImportProxyField(tableName, fieldName, original, soClass, column)[source]

Bases: SQLObjectField

tablesUsedImmediate()[source]
classsqlobject.sqlbuilder.Insert(table, valueList=None, values=None, template=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]

Bases: SQLExpression

sqlobject.sqlbuilder.JOIN(table1, table2)[source]
sqlobject.sqlbuilder.LEFTJOIN(table1, table2)[source]
sqlobject.sqlbuilder.LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.LEFTJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.LEFTJOINUsing(table1, table2, using_columns)[source]
sqlobject.sqlbuilder.LEFTOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.LEFTOUTERJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.LEFTOUTERJOINUsing(table1, table2, using_columns)[source]
classsqlobject.sqlbuilder.LIKE(expr, string, escape=None)[source]

Bases: SQLExpression

components()[source]
execute(executor)[source]
op='LIKE'
sqlobject.sqlbuilder.NATURALFULLJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALFULLOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALLEFTJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALLEFTOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALRIGHTJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NATURALRIGHTOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.NOT(op)[source]
sqlobject.sqlbuilder.NOTEXISTS(subquery)[source]
sqlobject.sqlbuilder.NOTIN(item, list)[source]
classsqlobject.sqlbuilder.NOTINSubquery(item, subquery)[source]

Bases: INSubquery

op='NOT IN'
classsqlobject.sqlbuilder.NoDefault[source]

Bases: object

sqlobject.sqlbuilder.OR(*ops)[source]
classsqlobject.sqlbuilder.Outer(table)[source]

Bases: object

classsqlobject.sqlbuilder.OuterField(tableName, fieldName, original, soClass, column)[source]

Bases: SQLObjectField

tablesUsedImmediate()[source]
classsqlobject.sqlbuilder.OuterTable(soClass)[source]

Bases: SQLObjectTable

FieldClass

alias of OuterField

sqlobject.sqlbuilder.RIGHTJOIN(table1, table2)[source]
sqlobject.sqlbuilder.RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.RIGHTJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.RIGHTJOINUsing(table1, table2, using_columns)[source]
sqlobject.sqlbuilder.RIGHTOUTERJOIN(table1, table2)[source]
sqlobject.sqlbuilder.RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]
sqlobject.sqlbuilder.RIGHTOUTERJOINOn(table1, table2, on_condition)[source]
sqlobject.sqlbuilder.RIGHTOUTERJOINUsing(table1, table2, using_columns)[source]
classsqlobject.sqlbuilder.RLIKE(expr, string, escape=None)[source]

Bases: LIKE

execute(executor)[source]
op='RLIKE'
op_db={'firebird': 'RLIKE', 'maxdb': 'RLIKE', 'mysql': 'RLIKE', 'postgres': '~', 'sqlite': 'REGEXP'}
classsqlobject.sqlbuilder.Replace(table, values, template=<class 'sqlobject.sqlbuilder.NoDefault'>, where=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]

Bases: Update

sqlName()[source]
sqlobject.sqlbuilder.SOME(subquery)[source]
classsqlobject.sqlbuilder.SQLCall(expr, args)[source]

Bases: SQLExpression

components()[source]
execute(executor)[source]
classsqlobject.sqlbuilder.SQLConstant(const)[source]

Bases: SQLExpression

execute(executor)[source]
sqlobject.sqlbuilder.SQLExprConverter(value, db)[source]
classsqlobject.sqlbuilder.SQLExpression[source]

Bases: object

components()[source]
contains(s)[source]
endswith(s)[source]
startswith(s)[source]
tablesUsed(db)[source]
tablesUsedImmediate()[source]
tablesUsedSet(db)[source]
classsqlobject.sqlbuilder.SQLJoin(table1, table2, op=',')[source]

Bases: SQLExpression

classsqlobject.sqlbuilder.SQLJoinConditional(table1, table2, op, on_condition=None, using_columns=None)[source]

Bases: SQLJoin

Conditional JOIN

classsqlobject.sqlbuilder.SQLJoinOn(table1, table2, op, on_condition)[source]

Bases: SQLJoinConditional

Conditional JOIN ON

classsqlobject.sqlbuilder.SQLJoinUsing(table1, table2, op, using_columns)[source]

Bases: SQLJoinConditional

Conditional JOIN USING

classsqlobject.sqlbuilder.SQLModulo(expr1, expr2)[source]

Bases: SQLOp

classsqlobject.sqlbuilder.SQLObjectField(tableName, fieldName, original, soClass, column)[source]

Bases: Field

contains(s)[source]
endswith(s)[source]
json_contains(value, path=None)
json_extract(key, path=None)
json_length(path=None)
startswith(s)[source]
classsqlobject.sqlbuilder.SQLObjectState(soObject, connection=None)[source]

Bases: object

classsqlobject.sqlbuilder.SQLObjectTable(soClass)[source]

Bases: Table

FieldClass

alias of SQLObjectField

classsqlobject.sqlbuilder.SQLObjectTableWithJoins(soClass)[source]

Bases: SQLObjectTable

classsqlobject.sqlbuilder.SQLOp(op, expr1, expr2)[source]

Bases: SQLExpression

components()[source]
execute(executor)[source]
classsqlobject.sqlbuilder.SQLPrefix(prefix, expr)[source]

Bases: SQLExpression

components()[source]
execute(executor)[source]
classsqlobject.sqlbuilder.SQLTrueClauseClass[source]

Bases: SQLExpression

execute(executor)[source]
sqlobject.sqlbuilder.STARTSWITH(expr, pattern)[source]
sqlobject.sqlbuilder.STRAIGHTJOIN(table1, table2)[source]
classsqlobject.sqlbuilder.Select(items=<class 'sqlobject.sqlbuilder.NoDefault'>, where=<class 'sqlobject.sqlbuilder.NoDefault'>, groupBy=<class 'sqlobject.sqlbuilder.NoDefault'>, having=<class 'sqlobject.sqlbuilder.NoDefault'>, orderBy=<class 'sqlobject.sqlbuilder.NoDefault'>, limit=<class 'sqlobject.sqlbuilder.NoDefault'>, join=<class 'sqlobject.sqlbuilder.NoDefault'>, lazyColumns=False, distinct=False, start=0, end=None, reversed=False, forUpdate=False, clause=<class 'sqlobject.sqlbuilder.NoDefault'>, staticTables=<class 'sqlobject.sqlbuilder.NoDefault'>, distinctOn=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]

Bases: SQLExpression

clone(**newOps)[source]
distinct()[source]
filter(filter_clause)[source]
lazyColumns(value)[source]
limit(limit)[source]
newClause(new_clause)[source]
newItems(items)[source]
orderBy(orderBy)[source]
reversed()[source]
unlimited()[source]
classsqlobject.sqlbuilder.Subquery(op, subquery)[source]

Bases: SQLExpression

classsqlobject.sqlbuilder.Table(tableName)[source]

Bases: SQLExpression

FieldClass

alias of Field

execute(executor)[source]
classsqlobject.sqlbuilder.TableSpace[source]

Bases: object

TableClass

alias of Table

classsqlobject.sqlbuilder.Union(*tables)[source]

Bases: SQLExpression

classsqlobject.sqlbuilder.Update(table, values, template=<class 'sqlobject.sqlbuilder.NoDefault'>, where=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]

Bases: SQLExpression

sqlName()[source]
exceptionsqlobject.sqlbuilder.VersionError[source]

Bases: Exception

sqlobject.sqlbuilder.dictToList(template, dict)[source]
sqlobject.sqlbuilder.execute(expr, executor)[source]
sqlobject.sqlbuilder.sqlIdentifier(obj)[source]
sqlobject.sqlbuilder.tablesUsedSet(obj, db)[source]