gavo.base.sqlsupport module

Basic support for communicating with the database server.

This is currently very postgres specific. If we really wanted to support some other database, this would need massive refactoring.

class gavo.base.sqlsupport.AdhocQuerier(connectionManager=None)

Bases: QuerierMixin

A simple interface to querying the database through pooled connections.

These are constructed using the connection getters (getTableConn (default), getAdminConn) and then serve as context managers, handing back the connection as you exit the controlled block.

Since they operate through pooled connections, no transaction management takes place. These are typically for read-only things.

You can use the query method and everything that’s in the QuerierMixin.

class gavo.base.sqlsupport.CustomConnectionPool(minconn, maxconn, profileName, autocommitted=True)

Bases: ThreadedConnectionPool

A threaded connection pool that returns connections made via profileName.

knownPools = []

classmethod serverRestarted()

class gavo.base.sqlsupport.DebugConnection

Bases: GAVOConnection

commit()

commit() – Commit all changes to database.

cursor(*args, **kwargs)

cursor(name=None, cursor_factory=extensions.cursor, withhold=False) – new cursor

Return a new cursor.

The cursor_factory argument can be used to create non-standard cursors by passing a class different from the default. Note that the new class should be a sub-class of extensions.cursor.

Return type:

extensions.cursor

getPID()

rollback()

rollback() – Roll back all changes done to database.

class gavo.base.sqlsupport.DebugCursor

Bases: cursor

execute(sql, args=None)

execute(query, vars=None) – Execute query with bound vars.

executemany(sql, args=[])

executemany(query, vars_list) – Execute many queries with bound vars.

exception gavo.base.sqlsupport.Error(msg: str = '', hint: Optional[str] = None)

Bases: Error

class gavo.base.sqlsupport.FloatableAdapter(val)

Bases: object

An adapter for things that do “float”, in particular numpy.float*

getquoted()

prepare(conn)

class gavo.base.sqlsupport.GAVOConnection

Bases: connection

A psycopg2 connection with some additional methods.

This derivation is also done so we can attach the getDBConnection arguments to the connection; it is used when recovering from a database restart.

configure(settings, cursor=None)

sets a number of postgres connection parameters.

settings is a list of (parameter, value) pairs, where value must be a python value that psycopg2 understands and that works for the parameter in question.

This returns a settings-list that restores the previous values when passed to configure()

execute(query, args={})

executes query in a cursor.

This returns the rowcount of the cursor used.

extensionFunctions = frozenset({'epoch_prop', 'healpix_nest', 'q3c_ang2ipix', 'smoc_union'})

getParameter(key, cursor=None)

returns the value of the postgres parameter key.

This returns unprocessed values, probably almost always as strings. Caveat emptor.

The main purpose of this function is to help the parameters connection manager, so users shouldn’t really mess with it.

parameters(settings, cursor=None)

executes a block with a certain set of parameters on a connection, resetting them to their original value again afterwards.

Of course, this only works as expected if you’re not sharing your connections to widely.

This rolls back the connection by itself on database errors; we couldn’t reset the parameters otherwise.

query(query, args={}, timeout=None, yieldDicts=False, caseFixer=None)

iterates over result tuples for query.

This is mainly for ad-hoc queries needing little metadata.

You can pass yieldDicts=True to get dictionaries instead of tuples. The dictionary keys are determined by what the database says the column titles are; thus, it’s usually lower-cased variants of what’s in the select-list. To fix this, you can pass in a caseFixer dict that gives a properly cased version of lowercase names.

Timeout is in seconds.

Warning: this is an iterator, so unless you iterate over the result, the query will not get executed. Hence, for non-select statements you will generally have to use conn.execute.

queryToDicts(query, args={}, timeout=None, caseFixer=None)

iterates over dictionary rows for query.

This is a thin wrapper around query(yieldDicts=True) provided for convenience and backwards compatibility.

savepoint()

sets up a section protected by a savepoint that will be released after use.

If an exception happens in the controlled section, the connection will be rolled back to the savepoint.

classmethod senseEnvironment(conn)

configures us depending on what is in the database.

The argument needs to be a connection to the database we will connect to. In practice, _initPsycopg calls this once during DaCHS startup.

class gavo.base.sqlsupport.IntableAdapter(val)

Bases: object

An adapter for things that do “int”, in particular numpy.int*

getquoted()

prepare(conn)

class gavo.base.sqlsupport.NULLAdapter(val)

Bases: object

An adapter for things that should end up as NULL in the DB.

getquoted()

prepare(conn)

class gavo.base.sqlsupport.NonBlockingQuery(conn, query, args={})

Bases: object

a query run in a pseudo-nonblocking way.

While psycopg2 can do proper async, that doesn’t play well with about everything else DaCHS is doing so far. So, here’s a quick way to allow long-running queries that users can still interrupt. The ugly secret is that it’s based on threads.

This should not be used within the server. We might want to port the async taprunner (which runs outside of the server) to using this, though.

To use it, construct it with conn, query and perhaps args and use it as a context manager.

Wait for its result attribute to become non-None; this will then be either a list of result rows or an Exception (which will also be raised when exiting the context manager).

To abort a running query, call abort().

abort()

aborts the current query and reaps the thread.

cleanup(timeout=None)

tries to reap the thread (i.e., join it).

If the thread hasn’t terminated within timeout seconds, a sqlsupport.Error is raised.

class gavo.base.sqlsupport.PostgresQueryMixin

Bases: object

is a mixin containing various useful queries that are postgres specific.

This mixin expects a parent that mixes is QuerierMixin (that, for now, also mixes in PostgresQueryMixin, so you won’t need to mix this in).

dropTable(tableName, cascade=False)

drops a table or view named by tableName.

This does not raise an error if no such relation exists.

* postgres specific *

foreignKeyExists(srcTableName, destTableName, srcColNames, destColNames, schema=None)

getACLFromRes(thingWithPrivileges)

returns a dict of (role, ACL) as it is defined in thingWithPrivileges.

thingWithPrivileges is something mixing in rscdef.common.PrivilegesMixin. (or has readProfiles and allProfiles attributes containing sequences of profile names).

getColumnsFromDB(tableName)

returns a sequence of (name, type) pairs of the columns this table has in the database.

If the table is not on disk, this will raise a NotFoundError.

* psycopg2 specific *

getForeignKeyName(srcTableName, destTableName, srcColNames, destColNames, schema=None)

returns True if there’s a foreign key constraint on srcTable’s srcColNames using destTableName’s destColNames.

Warning: names in XColNames that are not column names in the respective tables are ignored.

This raises a ValueError if the foreign keys do not exist.

getOIDForTable(tableName, schema=None)

returns the current oid of tableName.

tableName may be schema qualified. If it is not, public is assumed.

getPrimaryIndexName(tableName)

returns the name of the index corresponding to the primary key on (the unqualified) tableName.

getRowEstimate(tableName)

returns the size of the table in rows as estimated by the query planner.

This will raise a KeyError with tableName if the table isn’t known to postgres.

getSchemaPrivileges(schema)

returns (owner, readRoles, allRoles) for schema’s ACL.

getTablePrivileges(schema, tableName)

returns (owner, readRoles, allRoles) for the relation tableName and the schema.

* postgres specific *

getTableType(tableName, schema=None)

returns the type of the relation relationName.

If relationName does not exist, None is returned. Otherwise, it’s what is in the information schema for the table, which for postgres currently is one of BASE TABLE, VIEW, FOREIGN TABLE, MATERIALIZED VIEW, or LOCAL TEMPORARY.

The DaCHS-idiomatic way to see if a relation exists is getTableType() is not None.

You can pass in schema-qualified relation names, or the relation name and the schema separately.

* postgres specific *

hasIndex(tableName, indexName, schema=None)

returns True if table tablename has and index called indexName.

See _parseTableName on the meaning of the arguments.

parsePGACL(acl)

returns a dict roleName->acl for acl in postgres’ ACL serialization.

roleExists(role)

returns True if there role is known to the database.

schemaExists(schema)

returns True if the named schema exists in the database.

class gavo.base.sqlsupport.QuerierMixin

Bases: PostgresQueryMixin, StandardQueryMixin

is a mixin for “queriers”, i.e., objects that maintain a db connection.

The mixin assumes an attribute connection from the parent.

defaultProfile = None

enableAutocommit()

query(query, data={}, timeout=None)

wraps conn.query adding logic to re-establish lost connections.

Don’t use this method any more in new code. It contains wicked logic to tell DDL statements (that run without anyone pulling the results) from actual selects. That’s a bad API. Also note that the timeout is ignored for DDL statements.

We’ll drop this some time in 2023.

Use either connection.query or connection.execute in new code.

queryToDicts(*args, **kwargs)

wraps conn.queryToDicts for backwards compatilitiy.

class gavo.base.sqlsupport.SqlArrayAdapter(seq)

Bases: object

An adapter that formats python lists as SQL arrays

This makes, in the shameful tradition of VOTable, empty arrays equal to NULL.

getquoted()

prepare(conn)

class gavo.base.sqlsupport.SqlSetAdapter(seq)

Bases: object

is an adapter that formats python sequences as SQL sets.

– as opposed to psycopg2’s apparent default of building arrays out of them.

getquoted()

prepare(conn)

class gavo.base.sqlsupport.StandardQueryMixin

Bases: object

is a mixin containing various useful queries that should work against all SQL systems.

This mixin expects a parent that mixes is QuerierMixin (that, for now, also mixes in StandardQueryMixin, so you won’t need to mix this in).

The parent also needs to mix in something like PostgresQueryMixin (I might want to define an interface there once I’d like to support other databases).

setSchemaPrivileges(rd)

sets the privileges defined on rd to its schema.

This function will never touch the public schema.

setTablePrivileges(tableDef)

sets the privileges defined in tableDef for that table through querier.

class gavo.base.sqlsupport.UnmanagedQuerier(connection)

Bases: QuerierMixin

A simple interface to querying the database through a connection managed by someone else.

This is typically used as in:

with base.getTableConn() as conn:
        q = UnmanagedQuerier(conn)
        ...

This contains numerous methods abstracting DB functionality a bit. Documented ones include:

• schemaExists(schema)

• getColumnsFromDB(tableName)

• getTableType(tableName) – this will return None for non-existing tables, which is DaCHS’ official way to determine table existence.

gavo.base.sqlsupport.adapter

alias of IntableAdapter

gavo.base.sqlsupport.getAdminConn()

gavo.base.sqlsupport.getDBConnection(profile, debug=False, autocommitted=False)

returns an enhanced database connection through profile.

You will typically rather use the context managers for the standard profiles (getTableConnection and friends). Use this function if you want to keep your connection out of connection pools or if you want to use non-standard profiles.

profile will usually be a string naming a profile defined in GAVO_ROOT/etc.

gavo.base.sqlsupport.getDBMeta(key)

returns the value for key from within dc.metastore.

This always returns a unicode string. Type conversions are the client’s business.

If no value exists, this raises a KeyError.

gavo.base.sqlsupport.getPgVersion(digits=2)

returns the version number of the postgres server executing untrusted (ADQL) queries.

This is relatively expensive, as it will actually ask the server.

gavo.base.sqlsupport.getTableConn()

gavo.base.sqlsupport.getUntrustedConn()

gavo.base.sqlsupport.getWritableAdminConn()

gavo.base.sqlsupport.getWritableTableConn()

gavo.base.sqlsupport.getWritableUntrustedConn()

gavo.base.sqlsupport.initPsycopg()

does any DaCHS-specific database setup necessary.

This is executed on sqlsupport import unless we are in initdachs (or setting up the testbed); see the foot of this module for how this is done.

This needs to call the GAVOConnection.senseEnvironment.

gavo.base.sqlsupport.setDBMeta(conn, key, value)

adds/overwrites (key, value) in the dc.metastore table within conn.

conn must be an admin connection; this does not commit.

key must be a string, value something unicodeable.