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'