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

sqlobject.sqlbuilder.AND(*ops)

sqlobject.sqlbuilder.ANY(subquery)

class sqlobject.sqlbuilder.Alias(table, alias=None)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()

class sqlobject.sqlbuilder.AliasField(tableName, fieldName, alias, aliasTable)

  Bases: `sqlobject.sqlbuilder.Field`

  tablesUsedImmediate()

class sqlobject.sqlbuilder.AliasTable(table, alias=None)

  Bases: `sqlobject.sqlbuilder.Table`

  FieldClass

    alias of `sqlobject.sqlbuilder.AliasField`

  as_string = ''

class sqlobject.sqlbuilder.CONCAT(*expressions)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

sqlobject.sqlbuilder.CONTAINSSTRING(expr, pattern)

sqlobject.sqlbuilder.CROSSJOIN(table1, table2)

class sqlobject.sqlbuilder.ColumnAS(expr, name)

  Bases: `sqlobject.sqlbuilder.SQLOp`

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

class sqlobject.sqlbuilder.ConstantSpace

  Bases: `object`

class sqlobject.sqlbuilder.DESC(expr)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

class sqlobject.sqlbuilder.Delete(table, where=<class 'sqlobject.sqlbuilder.NoDefault'>)

  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)

sqlobject.sqlbuilder.EXISTS(subquery)

sqlobject.sqlbuilder.FULLJOIN(table1, table2)

sqlobject.sqlbuilder.FULLJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.FULLJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.FULLJOINUsing(table1, table2, using_columns)

sqlobject.sqlbuilder.FULLOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.FULLOUTERJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.FULLOUTERJOINUsing(table1, table2, using_columns)

class sqlobject.sqlbuilder.Field(tableName, fieldName)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  execute(executor)
  tablesUsedImmediate()

sqlobject.sqlbuilder.IN(item, list)

sqlobject.sqlbuilder.INNERJOIN(table1, table2)

sqlobject.sqlbuilder.INNERJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.INNERJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.INNERJOINUsing(table1, table2, using_columns)

class sqlobject.sqlbuilder.INSubquery(item, subquery)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()
  op = 'IN'

sqlobject.sqlbuilder.ISNOTNULL(expr)

sqlobject.sqlbuilder.ISNULL(expr)

class sqlobject.sqlbuilder.ImportProxy(clsName, registry=None)

  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 `sqlobject.sqlbuilder.ImportProxyField`

class sqlobject.sqlbuilder.ImportProxyField(tableName, fieldName, original, soClass, column)

  Bases: `sqlobject.sqlbuilder.SQLObjectField`

  tablesUsedImmediate()

class sqlobject.sqlbuilder.Insert(table, valueList=None, values=None, template=<class 'sqlobject.sqlbuilder.NoDefault'>)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

sqlobject.sqlbuilder.JOIN(table1, table2)

sqlobject.sqlbuilder.LEFTJOIN(table1, table2)

sqlobject.sqlbuilder.LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.LEFTJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.LEFTJOINUsing(table1, table2, using_columns)

sqlobject.sqlbuilder.LEFTOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.LEFTOUTERJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.LEFTOUTERJOINUsing(table1, table2, using_columns)

class sqlobject.sqlbuilder.LIKE(expr, string, escape=None)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()
  execute(executor)
  op = 'LIKE'

sqlobject.sqlbuilder.NATURALFULLJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALFULLOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALLEFTJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALLEFTOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALRIGHTJOIN(table1, table2)

sqlobject.sqlbuilder.NATURALRIGHTOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.NOT(op)

sqlobject.sqlbuilder.NOTEXISTS(subquery)

sqlobject.sqlbuilder.NOTIN(item, list)

class sqlobject.sqlbuilder.NOTINSubquery(item, subquery)

  Bases: `sqlobject.sqlbuilder.INSubquery`

  op = 'NOT IN'

class sqlobject.sqlbuilder.NoDefault

  Bases: `object`

sqlobject.sqlbuilder.OR(*ops)

class sqlobject.sqlbuilder.Outer(table)

  Bases: `object`

class sqlobject.sqlbuilder.OuterField(tableName, fieldName, original, soClass, column)

  Bases: `sqlobject.sqlbuilder.SQLObjectField`

  tablesUsedImmediate()

class sqlobject.sqlbuilder.OuterTable(soClass)

  Bases: `sqlobject.sqlbuilder.SQLObjectTable`

  FieldClass

    alias of `sqlobject.sqlbuilder.OuterField`

sqlobject.sqlbuilder.RIGHTJOIN(table1, table2)

sqlobject.sqlbuilder.RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.RIGHTJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.RIGHTJOINUsing(table1, table2, using_columns)

sqlobject.sqlbuilder.RIGHTOUTERJOIN(table1, table2)

sqlobject.sqlbuilder.RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)

sqlobject.sqlbuilder.RIGHTOUTERJOINOn(table1, table2, on_condition)

sqlobject.sqlbuilder.RIGHTOUTERJOINUsing(table1, table2, using_columns)

class sqlobject.sqlbuilder.RLIKE(expr, string, escape=None)

  Bases: `sqlobject.sqlbuilder.LIKE`

  execute(executor)
  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'>)

  Bases: `sqlobject.sqlbuilder.Update`

  sqlName()

sqlobject.sqlbuilder.SOME(subquery)

class sqlobject.sqlbuilder.SQLCall(expr, args)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()
  execute(executor)

class sqlobject.sqlbuilder.SQLConstant(const)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  execute(executor)

sqlobject.sqlbuilder.SQLExprConverter(value, db)

class sqlobject.sqlbuilder.SQLExpression

  Bases: `object`

  components()
  contains(s)
  endswith(s)
  startswith(s)
  tablesUsed(db)
  tablesUsedImmediate()
  tablesUsedSet(db)

class sqlobject.sqlbuilder.SQLJoin(table1, table2, op=',')

  Bases: `sqlobject.sqlbuilder.SQLExpression`

class sqlobject.sqlbuilder.SQLJoinConditional(table1, table2, op, on_condition=None, using_columns=None)

  Bases: `sqlobject.sqlbuilder.SQLJoin`

  Conditional JOIN

class sqlobject.sqlbuilder.SQLJoinOn(table1, table2, op, on_condition)

  Bases: `sqlobject.sqlbuilder.SQLJoinConditional`

  Conditional JOIN ON

class sqlobject.sqlbuilder.SQLJoinUsing(table1, table2, op, using_columns)

  Bases: `sqlobject.sqlbuilder.SQLJoinConditional`

  Conditional JOIN USING

class sqlobject.sqlbuilder.SQLModulo(expr1, expr2)

  Bases: `sqlobject.sqlbuilder.SQLOp`

class sqlobject.sqlbuilder.SQLObjectField(tableName, fieldName, original, soClass, column)

  Bases: `sqlobject.sqlbuilder.Field`

  contains(s)
  endswith(s)
  json_contains(value, path=None)
  json_extract(key, path=None)
  json_length(path=None)
  startswith(s)

class sqlobject.sqlbuilder.SQLObjectState(soObject, connection=None)

  Bases: `object`

class sqlobject.sqlbuilder.SQLObjectTable(soClass)

  Bases: `sqlobject.sqlbuilder.Table`

  FieldClass

    alias of `sqlobject.sqlbuilder.SQLObjectField`

class sqlobject.sqlbuilder.SQLObjectTableWithJoins(soClass)

  Bases: `sqlobject.sqlbuilder.SQLObjectTable`

class sqlobject.sqlbuilder.SQLOp(op, expr1, expr2)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()
  execute(executor)

class sqlobject.sqlbuilder.SQLPrefix(prefix, expr)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  components()
  execute(executor)

class sqlobject.sqlbuilder.SQLTrueClauseClass

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  execute(executor)

sqlobject.sqlbuilder.STARTSWITH(expr, pattern)

sqlobject.sqlbuilder.STRAIGHTJOIN(table1, table2)

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'>)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  clone(**newOps)
  distinct()
  filter(filter_clause)
  lazyColumns(value)
  limit(limit)
  newClause(new_clause)
  newItems(items)
  orderBy(orderBy)
  reversed()
  unlimited()

class sqlobject.sqlbuilder.Subquery(op, subquery)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

class sqlobject.sqlbuilder.Table(tableName)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  FieldClass

    alias of `sqlobject.sqlbuilder.Field`

  execute(executor)

class sqlobject.sqlbuilder.TableSpace

  Bases: `object`

  TableClass

    alias of `sqlobject.sqlbuilder.Table`

class sqlobject.sqlbuilder.Union(*tables)

  Bases: `sqlobject.sqlbuilder.SQLExpression`

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

  Bases: `sqlobject.sqlbuilder.SQLExpression`

  sqlName()

exception sqlobject.sqlbuilder.VersionError

  Bases: `Exception`

sqlobject.sqlbuilder.dictToList(template, dict)

sqlobject.sqlbuilder.execute(expr, executor)

sqlobject.sqlbuilder.sqlIdentifier(obj)

sqlobject.sqlbuilder.tablesUsedSet(obj, db)