Package gavo :: Package base :: Module sqlmunge

Module sqlmunge

Helpers for building SQL expressions.

Some of this code is concerned with SQL factories. These are functions with the signature:

       func(field, val, outPars) -> fragment

outPars is a dictionary that is used to transmit literal values into SQL. The result must be an SQL boolean expression for embedding into a WHERE clause (use None to signal no constraint). Field is the field for which the expression is being generated.

The factories currently are never called when val is a sequence; there's special hard-coded behaviour for that in getSQLFactory.

To enter values in outPars, use getSQLKey. Its docstring contains an example that shows how that would look like.

Functions

joinOperatorExpr(operator, operands)
filters empty operands and joins the rest using operator.

source code

getSQLKey(key, value, sqlPars)
adds value to sqlPars and returns a key for inclusion in a SQL query.

source code

registerSQLFactory(type, factory)
registers factory as an SQL factory for the type type (a string).

source code

getSQLForField(field, inPars, sqlPars)
returns an SQL fragment for a column-like thing.

source code

Variables
	plusInfinity = `inf`
	minusInfinity = `-inf`
	__package__ = `'gavo.base'`

Function Details

joinOperatorExpr(operator, operands)

source code

filters empty operands and joins the rest using operator.

The function returns an expression string or None for the empty expression.

getSQLKey(key, value, sqlPars)

source code

adds value to sqlPars and returns a key for inclusion in a SQL query.

This function is used to build parameter dictionaries for SQL queries, avoiding overwriting parameters with accidental name clashes.

As an extra service, if value is a list, it is turned into a set (rather than the default, which would be an array). We don't believe there's a great need to match against arrays. If you must match against arrays, use numpy arrays.

>>> sqlPars = {}
>>> getSQLKey("foo", 13, sqlPars)
'foo0'
>>> getSQLKey("foo", 14, sqlPars)
'foo1'
>>> getSQLKey("foo", 13, sqlPars)
'foo0'
>>> sqlPars["foo0"], sqlPars["foo1"]; sqlPars = {}
(13, 14)
>>> "WHERE foo<%%(%s)s OR foo>%%(%s)s"%(getSQLKey("foo", 1, sqlPars),
...   getSQLKey("foo", 15, sqlPars))
'WHERE foo<%(foo0)s OR foo>%(foo1)s'

getSQLForField(field, inPars, sqlPars)

source code

returns an SQL fragment for a column-like thing.

This will be empty if no input in inPars is present. If it is, (a) new key(s) will be left in sqlPars.

getSQLForField defines the default behaviour; in DBCore condDescs, it can be overridden using phrase makers.

inPars is supposed to be "typed"; we do not catch general parse errors here.