Package install :: Package MoSTBioDat :: Package DataBase :: Package Query :: Package SQLBuilder :: Module sqlbuilder
[hide private]
[frames] | no frames]

Module sqlbuilder

source code


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., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
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
-- particularly left joins, among others.  You may wish to only use
this to generate ``WHERE`` clauses.

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

Classes [hide private]
  VersionError
  NoDefault
  SQLExpression
  SQLOp
  SQLCall
  SQLPrefix
  SQLConstant
  SQLTrueClauseClass
  Field
  SQLObjectField
  UnicodeField
  Table
  SQLObjectTable
  TableSpace
  ConstantSpace
  AliasField
  AliasTable
  Alias
  Select
  Insert
  Update
  Delete
To be safe, this will signal an error if there is no where clause, unless you pass in where=None to the constructor.
  Replace
  DESC
  _LikeQuoted
  SQLJoin
  SQLJoinConditional
Conditional JOIN
  SQLJoinOn
Conditional JOIN ON
  SQLJoinUsing
Conditional JOIN USING
  OuterField
  OuterTable
  Outer
  LIKE
  RLIKE
  INSubquery
  NOTINSubquery
  Subquery
Functions [hide private]
 
sqlIdentifier(obj) source code
 
execute(expr, executor) source code
 
SQLExprConverter(value, db) source code
 
tablesUsedDict(obj) source code
 
dictToList(template, dict) source code
 
AND(*ops) source code
 
OR(*ops) source code
 
NOT(op) source code
 
_IN(item, list) source code
 
IN(item, list) source code
 
NOTIN(item, list) source code
 
STARTSWITH(expr, pattern) source code
 
ENDSWITH(expr, pattern) source code
 
CONTAINSSTRING(expr, pattern) source code
 
ISNULL(expr) source code
 
ISNOTNULL(expr) source code
 
_quote_percent(s, db) source code
 
JOIN(table1, table2) source code
 
INNERJOIN(table1, table2) source code
 
CROSSJOIN(table1, table2) source code
 
STRAIGHTJOIN(table1, table2) source code
 
LEFTJOIN(table1, table2) source code
 
LEFTOUTERJOIN(table1, table2) source code
 
NATURALJOIN(table1, table2) source code
 
NATURALLEFTJOIN(table1, table2) source code
 
NATURALLEFTOUTERJOIN(table1, table2) source code
 
RIGHTJOIN(table1, table2) source code
 
RIGHTOUTERJOIN(table1, table2) source code
 
NATURALRIGHTJOIN(table1, table2) source code
 
NATURALRIGHTOUTERJOIN(table1, table2) source code
 
FULLJOIN(table1, table2) source code
 
FULLOUTERJOIN(table1, table2) source code
 
NATURALFULLJOIN(table1, table2) source code
 
NATURALFULLOUTERJOIN(table1, table2) source code
 
INNERJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
FULLJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None) source code
 
INNERJOINOn(table1, table2, on_condition) source code
 
LEFTJOINOn(table1, table2, on_condition) source code
 
LEFTOUTERJOINOn(table1, table2, on_condition) source code
 
RIGHTJOINOn(table1, table2, on_condition) source code
 
RIGHTOUTERJOINOn(table1, table2, on_condition) source code
 
FULLJOINOn(table1, table2, on_condition) source code
 
FULLOUTERJOINOn(table1, table2, on_condition) source code
 
INNERJOINUsing(table1, table2, using_columns) source code
 
LEFTJOINUsing(table1, table2, using_columns) source code
 
LEFTOUTERJOINUsing(table1, table2, using_columns) source code
 
RIGHTJOINUsing(table1, table2, using_columns) source code
 
RIGHTOUTERJOINUsing(table1, table2, using_columns) source code
 
FULLJOINUsing(table1, table2, using_columns) source code
 
FULLOUTERJOINUsing(table1, table2, using_columns) source code
 
EXISTS(subquery) source code
 
NOTEXISTS(subquery) source code
 
SOME(subquery) source code
 
ANY(subquery) source code
 
ALL(subquery) source code
Variables [hide private]
  safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
  operatorMap = {'!=': <built-in function ne>, '*': <built-in fu...
  SQLTrueClause = 1 = 1
  table = TableSpace()
  const = ConstantSpace()
  func = ConstantSpace()
  False = False
  True = True
Variables Details [hide private]

operatorMap

Value:
{'!=': <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>,
...