gws.base.database.provider

Base database provider.

Source code: gws.base.database.provider

Module Contents

class gws.base.database.provider.Config(*args, **kwargs)

Bases: gws.Config

Database provider

pool: dict | None

Connection pool options.

schemaCacheLifeTime: gws.Duration = '3600'

How long table structures read from the database are cached.

withPool: bool | None = False

Keep and reuse database connections in a pool.

class gws.base.database.provider.Object

Bases: gws.DatabaseProvider

Base database provider.

Manages the SQLAlchemy engine and the per-thread connection, reflects table structures and describes tables and columns, and runs plain SQL text.

Subclasses provide url, split_table_name, join_table_name and table_bounds, and extend describe_column for database-specific types.

SA_TO_ATTR

Attribute types for SQLAlchemy type names.

SA_TO_GEOM

Geometry types for database geometry type names.

saEngine: gws.lib.sa.Engine

SQLAlchemy engine.

saMetaMap: dict[str, gws.lib.sa.MetaData]

Reflected metadata, keyed by schema name.

UNKNOWN_ARRAY_TYPE

Attribute type for array columns of unknown item types.

UNKNOWN_TYPE

Attribute type for columns of unknown types.

activate()

Activation hook.

Called by Root.activate after the configuration has been loaded.

autocommit_connection()

Open a separate connection in autocommit mode, to be used as a context manager.

For statements that cannot run inside a transaction, like VACUUM. The connection is not shared with begin.

Returns:

A context manager that yields the connection.

Raises:

gws.Error – If a transaction of this provider is open in the current thread.

begin(nested=False)

Begin a transaction, to be used as a context manager.

The outermost call opens a connection and begins a transaction, which is committed on normal exit and rolled back on an exception. Inner calls in the same thread join the open transaction, or create a savepoint if nested is set:

with db.begin() as conn:
    conn.execute('INSERT ...')
    with db.begin() as conn2:  # same connection and transaction
        rows = conn2.fetch_all('SELECT ...')
    with db.begin(nested=True) as conn3:  # savepoint
        conn3.execute('UPDATE ...')
# committed, connection closed

Unlike SQLAlchemy Engine.begin, an inner call does not open a new connection. Each provider keeps its own connection per thread.

Parameters:

nested – Create a savepoint when joining an open transaction. An exception inside the block then rolls back to the savepoint only. Has no effect on the outermost call.

Returns:

A context manager that yields the connection.

column(table, column_name)

Return the SQLAlchemy Column object for a column.

Parameters:

• table – Table object or table name.

• column_name – Column name.

Returns:

The column object.

Raises:

sqlalchemy.exc.SQLAlchemyError – If the table or the column does not exist.

configure()

Configuration hook.

Called when the node is initialized. Raising an exception here aborts the creation of the node.

connect()

Deprecated, same as begin.

count(table)

Return the number of rows in a table.

Parameters:

table – Table object or table name.

Returns:

The row count, or 0 if the table does not exist.

create_engine(**kwargs)

Create a new SQLAlchemy Engine object for this provider.

Parameters:

**kwargs – Engine options, see engine_options.

Returns:

The engine.

describe(table)

Describe a table.

Parameters:

table – Table object or table name.

Returns:

The table description. The first geometry column becomes the primary geometry.

Raises:

sqlalchemy.exc.SQLAlchemyError – If the table does not exist.

describe_column(table, column_name)

Describe a column in a table.

Parameters:

• table – Table object or table name.

• column_name – Column name.

Returns:

The column description.

Raises:

sqlalchemy.exc.SQLAlchemyError – If the table or the column does not exist.

engine()

Return the SQLAlchemy Engine object for this provider, creating it if needed.

Returns:

The engine.

engine_options(**kwargs)

Add defaults to the SQLAlchemy engine options.

Parameters:

**kwargs – Engine options. Options passed here take precedence over the defaults.

Returns:

The options dict.

execute_text(sql, **kwargs)

Execute a textual statement in a transaction, see begin.

Parameters:

• sql – SQL statement with :name bind parameters.

• **kwargs – Bind parameters.

Returns:

The result.

Raises:

sqlalchemy.exc.SQLAlchemyError – On database errors.

has_column(table, column_name)

Check if a column exists.

Parameters:

• table – Table object or table name.

• column_name – Column name.

Returns:

True if the table and the column exist.

has_schema(schema)

Check if a schema exists.

Parameters:

schema_name – Schema name.

Returns:

True if the schema exists.

has_table(table_name: str)

Check if a table exists.

Parameters:

table_name – Table name, optionally schema-qualified.

Returns:

True if the table exists.

inspect_schema(schema, options=None)

Reflect the tables of a schema and keep the result.

A schema that has already been reflected is not reflected again, unless options.refresh is set. The reflected metadata is cached for the configured schema cache lifetime. If options are given, their cacheLifeTime is used instead; 0 means no caching.

Parameters:

• schema – Schema name.

• options – Inspection options.

schema_names()

Return the names of the schemas in the database.

Returns:

A list of schema names.

select_text(sql, **kwargs)

Execute a textual SELECT statement.

Parameters:

• sql – SQL statement with :name bind parameters.

• **kwargs – Bind parameters.

Returns:

A list of rows as dicts.

Raises:

sqlalchemy.exc.SQLAlchemyError – On database errors.

table(table, **kwargs)

Return the SQLAlchemy Table object for a table.

Parameters:

• table – Table object or table name.

• **kwargs – Not used by the base provider.

Returns:

The table object.

Raises:

sqlalchemy.exc.SQLAlchemyError – If the table does not exist.