gavo.grammars.odbcgrammar module

A Grammar feeding from an odbc connection.

class gavo.grammars.odbcgrammar.ODBCGrammar(parent, **kwargs)

Bases: gavo.grammars.common.Grammar

A grammar that feeds from a remote database.

This works as a sort of poor man’s foreign data wrapper: you pull data from a remote database now and then, mogrifying it into whatever format you want locally.

This expects files containing pyodbc connection strings as sources, so you’ll normally just have one source. Having the credentials externally helps keeping RDs using this safe for public version control.

An example for an ODBC connection string:

DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass

See also http://www.connectionstrings.com/

This will only work if pyodbc (debian: python3-pyodbc) is installed. Additionally, you will have to install the odbc driver corresponding to your source database (e.g., odbc-postgresql).

attrSeq = [<gavo.base.attrdef.UnicodeAttribute object>, <gavo.base.parsecontext.IdAttribute object>, <gavo.base.complexattrs.StructAttribute object>, <gavo.base.complexattrs.StructAttribute object>, <gavo.base.parsecontext.OriginalAttribute object>, <gavo.base.complexattrs.PropertyAttribute object>, <gavo.base.attrdef.UnicodeAttribute object>, <gavo.rscdef.common.RDAttribute object>, <gavo.base.complexattrs.StructListAttribute object>, <gavo.base.complexattrs.StructAttribute object>]

clearProperty(name)

completeElement(ctx)

completedCallbacks = []

getFullId()

getProperty(name, default=<Undefined>)

hasProperty(name)

managedAttrs = {'enc': <gavo.base.attrdef.UnicodeAttribute object>, 'id': <gavo.base.parsecontext.IdAttribute object>, 'ignoreOn': <gavo.base.complexattrs.StructAttribute object>, 'makeQuery': <gavo.base.complexattrs.StructAttribute object>, 'original': <gavo.base.parsecontext.OriginalAttribute object>, 'properties': <gavo.base.complexattrs.PropertyAttribute object>, 'property': <gavo.base.complexattrs.PropertyAttribute object>, 'query': <gavo.base.attrdef.UnicodeAttribute object>, 'rd': <gavo.rscdef.common.RDAttribute object>, 'rowfilter': <gavo.base.complexattrs.StructListAttribute object>, 'rowfilters': <gavo.base.complexattrs.StructListAttribute object>, 'sourceFields': <gavo.base.complexattrs.StructAttribute object>}

name_ = 'odbcGrammar'

property rd

rowIterator

alias of gavo.grammars.odbcgrammar.ODBCIterator

setProperty(name, value)

validate()

class gavo.grammars.odbcgrammar.ODBCIterator(grammar, sourceToken, sourceRow=None)

Bases: gavo.grammars.common.RowIterator

class gavo.grammars.odbcgrammar.QueryGenerator(parent, **kwargs)

Bases: gavo.rscdef.procdef.ProcApp

A generator of ODBC queries.

This is is mainly useful when doing ODBC queries to incrementally havest some external resource.

The current ODBC iterator will be available as self.

The procedures also see a function escapeSQL(val) that returns val as a SQL literal (actually, it’s psycopg2’s adapt at the moment).

This is intended to be used somewhat like this with a monotonously increasing column insertion_time:

<makeQuery>
        <code>
                # find to until when we have data locally
                try:
                        with base.getTableConn() as conn:
                                localMax = next(conn.query(
                                        "SELECT MAX(insertion_time) FROM \schema.main"))[0]

                        fragment = " WHERE insertion_time>{}".format(
                                sqlEscape(localMax))
                except base.DBError as msg:
                        base.ui.notifyWarning(f"{msg} while harvesting: full re-harvest")
                        fragment = ""

                return f"SELECT * FROM remote_table{fragment}"
        </code>
</makeQuery>

additionalNamesForProcs = {'escapeSQL': <built-in function adapt>}

attrSeq = [<gavo.base.complexattrs.StructListAttribute object>, <gavo.base.attrdef.UnicodeAttribute object>, <gavo.base.attrdef.UnicodeAttribute object>, <gavo.base.attrdef.UnicodeAttribute object>, <gavo.base.parsecontext.IdAttribute object>, <gavo.base.attrdef.UnicodeAttribute object>, <gavo.base.parsecontext.OriginalAttribute object>, <gavo.base.parsecontext.ReferenceAttribute object>, <gavo.base.complexattrs.StructListAttribute object>, <gavo.base.attrdef.EnumeratedUnicodeAttribute object>]

completedCallbacks = []

formalArgs = 'self'

managedAttrs = {'bind': <gavo.base.complexattrs.StructListAttribute object>, 'bindings': <gavo.base.complexattrs.StructListAttribute object>, 'code': <gavo.base.attrdef.UnicodeAttribute object>, 'deprecated': <gavo.base.attrdef.UnicodeAttribute object>, 'doc': <gavo.base.attrdef.UnicodeAttribute object>, 'id': <gavo.base.parsecontext.IdAttribute object>, 'name': <gavo.base.attrdef.UnicodeAttribute object>, 'original': <gavo.base.parsecontext.OriginalAttribute object>, 'procDef': <gavo.base.parsecontext.ReferenceAttribute object>, 'setup': <gavo.base.complexattrs.StructListAttribute object>, 'setups': <gavo.base.complexattrs.StructListAttribute object>, 'type': <gavo.base.attrdef.EnumeratedUnicodeAttribute object>}

name_ = 'makeQuery'

requiredType = 'makeQuery'