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.
- class sqlobject.sqlbuilder.AliasField(tableName, fieldName, alias, aliasTable)[source]¶
Bases:
sqlobject.sqlbuilder.Field
- class sqlobject.sqlbuilder.AliasTable(table, alias=None)[source]¶
Bases:
sqlobject.sqlbuilder.Table
- FieldClass¶
alias of
sqlobject.sqlbuilder.AliasField
- as_string = ''¶
- class sqlobject.sqlbuilder.ColumnAS(expr, name)[source]¶
Bases:
sqlobject.sqlbuilder.SQLOp
Just like SQLOp(‘AS’, expr, name) except without the parentheses
- 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.FULLJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- sqlobject.sqlbuilder.FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- sqlobject.sqlbuilder.INNERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- class sqlobject.sqlbuilder.INSubquery(item, subquery)[source]¶
Bases:
sqlobject.sqlbuilder.SQLExpression
- op = 'IN'¶
- 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¶
- class sqlobject.sqlbuilder.ImportProxyField(tableName, fieldName, original, soClass, column)[source]¶
- class sqlobject.sqlbuilder.Insert(table, valueList=None, values=None, template=<class 'sqlobject.sqlbuilder.NoDefault'>)[source]¶
- sqlobject.sqlbuilder.LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- sqlobject.sqlbuilder.LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- class sqlobject.sqlbuilder.LIKE(expr, string, escape=None)[source]¶
Bases:
sqlobject.sqlbuilder.SQLExpression
- op = 'LIKE'¶
- class sqlobject.sqlbuilder.NOTINSubquery(item, subquery)[source]¶
Bases:
sqlobject.sqlbuilder.INSubquery
- op = 'NOT IN'¶
- class sqlobject.sqlbuilder.OuterTable(soClass)[source]¶
Bases:
sqlobject.sqlbuilder.SQLObjectTable
- FieldClass¶
alias of
sqlobject.sqlbuilder.OuterField
- sqlobject.sqlbuilder.RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- sqlobject.sqlbuilder.RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None)[source]¶
- class sqlobject.sqlbuilder.RLIKE(expr, string, escape=None)[source]¶
Bases:
sqlobject.sqlbuilder.LIKE
- 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
- 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
- json_contains(value, path=None)¶
- json_extract(key, path=None)¶
- json_length(path=None)¶
- class sqlobject.sqlbuilder.SQLObjectTable(soClass)[source]¶
Bases:
sqlobject.sqlbuilder.Table
- FieldClass¶
alias of
sqlobject.sqlbuilder.SQLObjectField
- 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]¶
- class sqlobject.sqlbuilder.Table(tableName)[source]¶
Bases:
sqlobject.sqlbuilder.SQLExpression
- FieldClass¶
alias of
sqlobject.sqlbuilder.Field
- class sqlobject.sqlbuilder.TableSpace[source]¶
Bases:
object
- TableClass¶
alias of
sqlobject.sqlbuilder.Table