score.sa.db

This module provides functions and classes for accessing databases conveniently using SQLAlchemy.

Quickstart

Add the SQLAlchemy url to your SCORE configuration:

[score.init]
modules =
    db

[db]
sqlalchemy.url = sqlite:///${here}/database.sqlite3

Access the configured sqlalchemy.engine.Engine as score.db.engine.

>>> connection = score.db.engine.connection()
>>> connection.execute('SELECT 1')

If you have also onfigured score.ctx, you can retrieve a connection with a transaction that has the same scope as the Context object itself: The transaction will be automatically committed at the end of the transaction on success (or rolled back, if the context is destroyed by an uncaught exception):

>>> with score.ctx.Context() as ctx:
>>>     ctx.db.execute('SELECT 1')

Configuration

score.sa.db.init(confdict, ctx=None)

Initializes this module acoording to our module initialization guidelines with the following configuration keys:

sqlalchemy.*

All configuration values under this key will be passed to engine_from_config(), which in turn calls sqlalchemy.create_engine() with these configuration values as keyword arguments. Usually the following is sufficient:

sqlalchemy.url = postgresql://dbuser@localhost/projname

destroyable False

Whether destructive operations may be performed on the database. This value prevents accidental deletion of important data on live servers.

Note that any application feature destroying data must consult this flag before proceeding!

ctx.member db

The name of the context member providing an sqlalchemy.engine.Connection. Can be the string None to indicate, that no context member should be registered.

This value is only relevant, if the optional score.ctx dependency was configured.

ctx.transaction True

Whether the context member providing the context-scoped database connection should wrap the connection in a transaction. The transaction will be committed at the end of the context object’s lifecycle (or rolled back, if the context was terminated with an uncaught exception).

This value is only relevant if ctx.member is not None.

Details

Enumerations

This module provides an Enum class that can be used to interface enumerations in the database. It sub-classes python’s built-in enum.Enum type and adds a function to extract an appropriate SQLAlchemy type:

from score.sa.db import Enum
import sqlalchemy as sa

class Status(Enum):
    ONLINE = 'online'
    OFFLINE = 'offline'

table = sa.Table('content', metadata,
    Column('status', Status.db_type(), nullable=False),
    ...

API

Configuration

score.sa.db.init(confdict, ctx=None)

Initializes this module acoording to our module initialization guidelines with the following configuration keys:

sqlalchemy.*

All configuration values under this key will be passed to engine_from_config(), which in turn calls sqlalchemy.create_engine() with these configuration values as keyword arguments. Usually the following is sufficient:

sqlalchemy.url = postgresql://dbuser@localhost/projname

destroyable False

Whether destructive operations may be performed on the database. This value prevents accidental deletion of important data on live servers.

Note that any application feature destroying data must consult this flag before proceeding!

ctx.member db

The name of the context member providing an sqlalchemy.engine.Connection. Can be the string None to indicate, that no context member should be registered.

This value is only relevant, if the optional score.ctx dependency was configured.

ctx.transaction True

Whether the context member providing the context-scoped database connection should wrap the connection in a transaction. The transaction will be committed at the end of the context object’s lifecycle (or rolled back, if the context was terminated with an uncaught exception).

This value is only relevant if ctx.member is not None.

class score.sa.db.ConfiguredSaDbModule(ctx, engine, destroyable, ctx_member, ctx_transaction)

This module’s configuration class.

engine

An SQLAlchemy Engine.

destroyable

Whether destructive operations may be performed on the database. This value will be consulted before any such operations are performed. Application developers are also advised to make use of this value appropriately.

destroy(connection=None)

Note

This function currently only works on postgresql and sqlite databases.

Drops everything in the database – tables, views, sequences, etc. This function will not execute if the database configuration was not explicitly set to be destroyable.

Helper Functions

score.sa.db.engine_from_config(config)

A wrapper around sqlalchemy.engine_from_config(), that converts certain configuration values. Currently, the following configurations are processed:

• sqlalchemy.echo (using score.init.parse_bool())
• sqlalchemy.echo_pool (using score.init.parse_bool())
• sqlalchemy.case_sensitive (using score.init.parse_bool())
• sqlalchemy.module (using score.init.parse_dotted_path())
• sqlalchemy.poolclass (using score.init.parse_dotted_path())
• sqlalchemy.pool (using score.init.parse_call())
• sqlalchemy.pool_size (converted to int)
• sqlalchemy.pool_recycle (converted to int)

Postgresql-Specific

score.sa.db.pg.destroy(connection, destroyable)

Drops everything in the database – tables, views, sequences, etc. For safety reasons, the destroyable flag of the database configuration must be passed as parameter.

score.sa.db.pg.list_sequences(connection)

Returns a list of the sequence names from the current database’s public schema.

score.sa.db.pg.list_tables(connection)

Returns a list of table names from the current database’s public schema.

score.sa.db.pg.list_views(connection)

Returns a list of view names from the current database’s public schema.

SQLite-Specific

score.sa.db.sqlite.destroy(connection, destroyable)

Drops everything in the database – tables, views, sequences, etc. For safety reasons, the destroyable flag of the database configuration must be passed as parameter.

score.sa.db.sqlite.list_tables(connection)

Returns a list of all table names.

score.sa.db.sqlite.list_triggers(connection)

Returns a list of all trigger names.

score.sa.db.sqlite.list_views(connection)

Returns a list of all view names.