Package gavo :: Package base :: Module sqlmunge
[frames] | no frames]

Module sqlmunge

source code

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.