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)[source]

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)[source]

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)[source]

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)[source]

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