gavo.base.sqlmunge module

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.

gavo.base.sqlmunge.getSQLForField(field, inPars, sqlPars)

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.

gavo.base.sqlmunge.getSQLKey(key, value, sqlPars)

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. key usually a string matching the identifier pattern or a QuotedName (the latter are going to be horribly mogrified)

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'
>>> getSQLKey(utils.QuotedName("-x-"), "x", sqlPars)
'id2dx2d0'

gavo.base.sqlmunge.joinOperatorExpr(operator, operands)

filters empty operands and joins the rest using operator.

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

gavo.base.sqlmunge.registerSQLFactory(type, factory)

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