Namespaces/introspection: collecting sql strings for validation

[Peter Otten]

Martin Drautzburg wrote:

> I would like to validate sql strings, which are spread all over the
> code, i.e. I run ("prepare") them against a database to see if it happy
> with the statements. Spelling errors in sql have been a major pain for
> me.
> 
> The statements will not be assembled from smaller pieces, but they will
> not neccessarily be defined at module level. I could live with class
> level, but method level would be best. And I definitely don't want to
> parse the source file, but I am willing to mark the strings so it is
> easier to tell sql from non-sql.
> 
> So the code will look like this
> 
> class Foo:(...):
>         def aQuery(...):
>                 stmt = """
>                         -- checkSql
>                         select 1 from dual
>                 """
>                 executeQuery()
> 
> at the end of the file I would like to write something like
> if (...):
>         validateSql()
> 
> The validateSql() is the problem. It would be imported from elsewhere.
> and has to do some serious magic. It would have to lookup its caller's
> module, find all the classes (and methods, if we go that far) extract
> the constants, check if any of them are an SQL statement and validate
> it.
> 
> The problem is the first part: how can I lookup the callers module and
> the classobjs defined in there? Or finding any constant strings in the
> caller's module would also be just fine. Or is there a completely
> different way to do such a thing?

Since all strings are constants you could just tokenize the source code:

def strings(filename):
    with open(filename, "rU") as instream:
        for t in tokenize.generate_tokens(instream.readline):
            if t[0] == token.STRING:
                yield eval(t[1])

def validateSQL(filename=None):
    if filename is None:
        # by default operate on the calling module
        filename = sys._getframe(1).f_globals["__file__"]
    for s in strings(filename):
        print "validating", repr(s)

Another option would be to mark SQL statements similar to gettext by
enclosing them in a function call

sql = SQL("select * from...")

and then defining SQL() as either a no-op in production or an actual
validation while you are debugging. Even simpler and safer would be to
always validate once:

def SQL(sql, checked=set()):
    if sql in checked:
        return True
    if not valid_sql(sql): raise ValueError
    checked.add(sql)
    return sql

Peter