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]¶

class sqlobject.sqlbuilder.Alias(table, alias=None)[source]¶

Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.AliasField(tableName, fieldName, alias, aliasTable)[source]¶

Bases: sqlobject.sqlbuilder.Field

tablesUsedImmediate()[source]¶

class sqlobject.sqlbuilder.AliasSQLMeta(table, alias)[source]¶

class sqlobject.sqlbuilder.AliasTable(table, alias=None)[source]¶

Bases: sqlobject.sqlbuilder.Table

FieldClass¶: alias of AliasField

as_string = ''¶

class sqlobject.sqlbuilder.CONCAT(*expressions)[source]¶: Bases: sqlobject.sqlbuilder.SQLExpression

sqlobject.sqlbuilder.CONTAINSSTRING(expr, pattern)[source]¶

sqlobject.sqlbuilder.CROSSJOIN(table1, table2)[source]¶

class sqlobject.sqlbuilder.ColumnAS(expr, name)[source]¶

Bases: sqlobject.sqlbuilder.SQLOp

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

class sqlobject.sqlbuilder.ConstantSpace[source]¶

class sqlobject.sqlbuilder.DESC(expr)[source]¶: Bases: sqlobject.sqlbuilder.SQLExpression

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

Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.Field(tableName, fieldName)[source]¶

Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.INSubquery(item, subquery)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

components()[source]¶

op = 'IN'¶

sqlobject.sqlbuilder.ISNOTNULL(expr)[source]¶

sqlobject.sqlbuilder.ISNULL(expr)[source]¶

class sqlobject.sqlbuilder.ImportProxy(clsName, registry=None)[source]¶

Bases: sqlobject.sqlbuilder.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

class sqlobject.sqlbuilder.ImportProxyField(tableName, fieldName, original, soClass, column)[source]¶

Bases: sqlobject.sqlbuilder.SQLObjectField

tablesUsedImmediate()[source]¶

class sqlobject.sqlbuilder.Insert(table, valueList=None, values=None, template=<class sqlobject.sqlbuilder.NoDefault>)[source]¶: Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.LIKE(expr, string, escape=None)[source]¶

Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.NOTINSubquery(item, subquery)[source]¶

Bases: sqlobject.sqlbuilder.INSubquery

op = 'NOT IN'¶

class sqlobject.sqlbuilder.NoDefault[source]¶

sqlobject.sqlbuilder.OR(*ops)[source]¶

class sqlobject.sqlbuilder.Outer(table)[source]¶

class sqlobject.sqlbuilder.OuterField(tableName, fieldName, original, soClass, column)[source]¶

Bases: sqlobject.sqlbuilder.SQLObjectField

tablesUsedImmediate()[source]¶

class sqlobject.sqlbuilder.OuterTable(soClass)[source]¶

Bases: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.RLIKE(expr, string, escape=None)[source]¶

Bases: sqlobject.sqlbuilder.LIKE

execute(executor)[source]¶

op = 'RLIKE'¶

op_db = {'firebird': 'RLIKE', 'maxdb': 'RLIKE', 'mysql': 'RLIKE', 'postgres': '~', 'sqlite': 'REGEXP'}¶

class sqlobject.sqlbuilder.Replace(table, values, template=<class sqlobject.sqlbuilder.NoDefault>, where=<class sqlobject.sqlbuilder.NoDefault>)[source]¶

Bases: sqlobject.sqlbuilder.Update

sqlName()[source]¶

sqlobject.sqlbuilder.SOME(subquery)[source]¶

class sqlobject.sqlbuilder.SQLCall(expr, args)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

components()[source]¶

execute(executor)[source]¶

class sqlobject.sqlbuilder.SQLConstant(const)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

execute(executor)[source]¶

sqlobject.sqlbuilder.SQLExprConverter(value, db)[source]¶

class sqlobject.sqlbuilder.SQLExpression[source]¶

components()[source]¶

contains(s)[source]¶

endswith(s)[source]¶

startswith(s)[source]¶

tablesUsed(db)[source]¶

tablesUsedImmediate()[source]¶

tablesUsedSet(db)[source]¶

class sqlobject.sqlbuilder.SQLJoin(table1, table2, op=', ')[source]¶: Bases: sqlobject.sqlbuilder.SQLExpression

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

Bases: sqlobject.sqlbuilder.SQLJoin

Conditional JOIN

class sqlobject.sqlbuilder.SQLJoinOn(table1, table2, op, on_condition)[source]¶

Bases: sqlobject.sqlbuilder.SQLJoinConditional

Conditional JOIN ON

class sqlobject.sqlbuilder.SQLJoinUsing(table1, table2, op, using_columns)[source]¶

Bases: sqlobject.sqlbuilder.SQLJoinConditional

Conditional JOIN USING

class sqlobject.sqlbuilder.SQLModulo(expr1, expr2)[source]¶: Bases: sqlobject.sqlbuilder.SQLOp

class sqlobject.sqlbuilder.SQLObjectField(tableName, fieldName, original, soClass, column)[source]¶

Bases: sqlobject.sqlbuilder.Field

contains(s)[source]¶

endswith(s)[source]¶

json_contains(value, path=None)¶

json_extract(key, path=None)¶

json_length(path=None)¶

startswith(s)[source]¶

class sqlobject.sqlbuilder.SQLObjectState(soObject, connection=None)[source]¶: Bases: object

class sqlobject.sqlbuilder.SQLObjectTable(soClass)[source]¶

Bases: sqlobject.sqlbuilder.Table

FieldClass¶: alias of SQLObjectField

class sqlobject.sqlbuilder.SQLObjectTableWithJoins(soClass)[source]¶: Bases: sqlobject.sqlbuilder.SQLObjectTable

class sqlobject.sqlbuilder.SQLOp(op, expr1, expr2)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

components()[source]¶

execute(executor)[source]¶

class sqlobject.sqlbuilder.SQLPrefix(prefix, expr)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

components()[source]¶

execute(executor)[source]¶

class sqlobject.sqlbuilder.SQLTrueClauseClass[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

execute(executor)[source]¶

sqlobject.sqlbuilder.STARTSWITH(expr, pattern)[source]¶

sqlobject.sqlbuilder.STRAIGHTJOIN(table1, table2)[source]¶

class sqlobject.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: sqlobject.sqlbuilder.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]¶

class sqlobject.sqlbuilder.Subquery(op, subquery)[source]¶: Bases: sqlobject.sqlbuilder.SQLExpression

class sqlobject.sqlbuilder.Table(tableName)[source]¶

Bases: sqlobject.sqlbuilder.SQLExpression

FieldClass¶: alias of Field

execute(executor)[source]¶

class sqlobject.sqlbuilder.TableSpace[source]¶

TableClass¶: alias of Table

class sqlobject.sqlbuilder.Union(*tables)[source]¶: Bases: sqlobject.sqlbuilder.SQLExpression

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

Bases: sqlobject.sqlbuilder.SQLExpression

sqlName()[source]¶

exception sqlobject.sqlbuilder.VersionError[source]¶: Bases: exceptions.Exception

sqlobject.sqlbuilder.dictToList(template, dict)[source]¶

sqlobject.sqlbuilder.execute(expr, executor)[source]¶

sqlobject.sqlbuilder.sqlIdentifier(obj)[source]¶

sqlobject.sqlbuilder.tablesUsedSet(obj, db)[source]¶

Table of Contents

This Page

sqlobject.sqlbuilder module¶