oslo_db.sqlalchemy.utils

class oslo_db.sqlalchemy.utils.DialectFunctionDispatcher

Bases: object

dispatch_for(expr)
classmethod dispatch_for_dialect(expr, multiple=False)

Provide dialect-specific functionality within distinct functions.

e.g.:

@dispatch_for_dialect("*")
def set_special_option(engine):
    pass

@set_special_option.dispatch_for("sqlite")
def set_sqlite_special_option(engine):
    return engine.execute("sqlite thing")

@set_special_option.dispatch_for("mysql+mysqldb")
def set_mysqldb_special_option(engine):
    return engine.execute("mysqldb thing")

After the above registration, the set_special_option() function is now a dispatcher, given a SQLAlchemy Engine, Connection, URL string, or sqlalchemy.engine.URL object:

eng = create_engine('...')
result = set_special_option(eng)

The filter system supports two modes, “multiple” and “single”. The default is “single”, and requires that one and only one function match for a given backend. In this mode, the function may also have a return value, which will be returned by the top level call.

“multiple” mode, on the other hand, does not support return arguments, but allows for any number of matching functions, where each function will be called:

# the initial call sets this up as a "multiple" dispatcher
@dispatch_for_dialect("*", multiple=True)
def set_options(engine):
    # set options that apply to *all* engines

@set_options.dispatch_for("postgresql")
def set_postgresql_options(engine):
    # set options that apply to all Postgresql engines

@set_options.dispatch_for("postgresql+psycopg2")
def set_postgresql_psycopg2_options(engine):
    # set options that apply only to "postgresql+psycopg2"

@set_options.dispatch_for("*+pyodbc")
def set_pyodbc_options(engine):
    # set options that apply to all pyodbc backends

Note that in both modes, any number of additional arguments can be accepted by member functions. For example, to populate a dictionary of options, it may be passed in:

@dispatch_for_dialect("*", multiple=True)
def set_engine_options(url, opts):
    pass

@set_engine_options.dispatch_for("mysql+mysqldb")
def _mysql_set_default_charset_to_utf8(url, opts):
    opts.setdefault('charset', 'utf-8')

@set_engine_options.dispatch_for("sqlite")
def _set_sqlite_in_memory_check_same_thread(url, opts):
    if url.database in (None, 'memory'):
        opts['check_same_thread'] = False

opts = {}
set_engine_options(url, opts)

The driver specifiers are of the form: <database | *>[+<driver | *>]. That is, database name or “*”, followed by an optional + sign with driver or “*”. Omitting the driver name implies all drivers for that database.

dispatch_on_drivername(drivername)

Return a sub-dispatcher for the given drivername.

This provides a means of calling a different function, such as the “*” function, for a given target object that normally refers to a sub-function.

class oslo_db.sqlalchemy.utils.DialectMultiFunctionDispatcher

Bases: oslo_db.sqlalchemy.utils.DialectFunctionDispatcher

class oslo_db.sqlalchemy.utils.DialectSingleFunctionDispatcher

Bases: oslo_db.sqlalchemy.utils.DialectFunctionDispatcher

class oslo_db.sqlalchemy.utils.InsertFromSelect(table, select)

Bases: sqlalchemy.sql.dml.UpdateBase

Form the base for INSERT INTO table (SELECT ... ) statement.

oslo_db.sqlalchemy.utils.add_index(migrate_engine, table_name, index_name, idx_columns)

Create an index for given columns.

Parameters:
  • migrate_engine – sqlalchemy engine
  • table_name – name of the table
  • index_name – name of the index
  • idx_columns – tuple with names of columns that will be indexed
oslo_db.sqlalchemy.utils.change_deleted_column_type_to_boolean(migrate_engine, table_name, **col_name_col_instance)
oslo_db.sqlalchemy.utils.change_deleted_column_type_to_id_type(migrate_engine, table_name, **col_name_col_instance)
oslo_db.sqlalchemy.utils.change_index_columns(migrate_engine, table_name, index_name, new_columns)

Change set of columns that are indexed by given index.

Parameters:
  • migrate_engine – sqlalchemy engine
  • table_name – name of the table
  • index_name – name of the index
  • new_columns – tuple with names of columns that will be indexed
oslo_db.sqlalchemy.utils.column_exists(engine, table_name, column)

Check if table has given column.

Parameters:
  • engine – sqlalchemy engine
  • table_name – name of the table
  • column – name of the colmn
oslo_db.sqlalchemy.utils.drop_index(migrate_engine, table_name, index_name)

Drop index with given name.

Parameters:
  • migrate_engine – sqlalchemy engine
  • table_name – name of the table
  • index_name – name of the index
oslo_db.sqlalchemy.utils.drop_old_duplicate_entries_from_table(migrate_engine, table_name, use_soft_delete, *uc_column_names)

Drop all old rows having the same values for columns in uc_columns.

This method drop (or mark ad deleted if use_soft_delete is True) old duplicate rows form table with name table_name.

Parameters:
  • migrate_engine – Sqlalchemy engine
  • table_name – Table with duplicates
  • use_soft_delete – If True - values will be marked as deleted, if False - values will be removed from table
  • uc_column_names – Unique constraint columns
oslo_db.sqlalchemy.utils.get_callable_name(function)
oslo_db.sqlalchemy.utils.get_connect_string(backend, database, user=None, passwd=None, host='localhost')

Get database connection

Try to get a connection with a very specific set of values, if we get these then we’ll run the tests, otherwise they are skipped

DEPRECATED: this function is deprecated and will be removed from oslo.db in a few releases. Please use the provisioning system for dealing with URLs and database provisioning.

oslo_db.sqlalchemy.utils.get_db_connection_info(conn_pieces)
oslo_db.sqlalchemy.utils.get_non_innodb_tables(connectable, skip_tables=('migrate_version', 'alembic_version'))

Get a list of tables which don’t use InnoDB storage engine.

Parameters:
  • connectable – a SQLAlchemy Engine or a Connection instance
  • skip_tables – a list of tables which might have a different storage engine
oslo_db.sqlalchemy.utils.get_table(engine, name)

Returns an sqlalchemy table dynamically from db.

Needed because the models don’t work for us in migrations as models will be far out of sync with the current data.

Warning

Do not use this method when creating ForeignKeys in database migrations because sqlalchemy needs the same MetaData object to hold information about the parent table and the reference table in the ForeignKey. This method uses a unique MetaData object per table object so it won’t work with ForeignKey creation.

oslo_db.sqlalchemy.utils.index_exists(migrate_engine, table_name, index_name)

Check if given index exists.

Parameters:
  • migrate_engine – sqlalchemy engine
  • table_name – name of the table
  • index_name – name of the index
oslo_db.sqlalchemy.utils.is_backend_avail(backend, database, user=None, passwd=None)

Return True if the given backend is available.

DEPRECATED: this function is deprecated and will be removed from oslo.db in a few releases. Please use the provisioning system to access databases based on backend availability.

oslo_db.sqlalchemy.utils.model_query(model, session, args=None, **kwargs)

Query helper for db.sqlalchemy api methods.

This accounts for deleted and project_id fields.

Parameters:
  • model (models.ModelBase) – Model to query. Must be a subclass of ModelBase.
  • session (sqlalchemy.orm.session.Session) – The session to use.
  • args (tuple) – Arguments to query. If None - model is used.

Keyword arguments:

Parameters:
  • project_id (iterable, model.__table__.columns.project_id.type, None type) – If present, allows filtering by project_id(s). Can be either a project_id value, or an iterable of project_id values, or None. If an iterable is passed, only rows whose project_id column value is on the project_id list will be returned. If None is passed, only rows which are not bound to any project, will be returned.
  • deleted (bool) – If present, allows filtering by deleted field. If True is passed, only deleted entries will be returned, if False - only existing entries.

Usage:

from oslo_db.sqlalchemy import utils


def get_instance_by_uuid(uuid):
    session = get_session()
    with session.begin()
        return (utils.model_query(models.Instance, session=session)
                     .filter(models.Instance.uuid == uuid)
                     .first())

def get_nodes_stat():
    data = (Node.id, Node.cpu, Node.ram, Node.hdd)

    session = get_session()
    with session.begin()
        return utils.model_query(Node, session=session, args=data).all()

Also you can create your own helper, based on utils.model_query(). For example, it can be useful if you plan to use project_id and deleted parameters from project’s context

from oslo_db.sqlalchemy import utils


def _model_query(context, model, session=None, args=None,
                 project_id=None, project_only=False,
                 read_deleted=None):

    # We suppose, that functions ``_get_project_id()`` and
    # ``_get_deleted()`` should handle passed parameters and
    # context object (for example, decide, if we need to restrict a user
    # to query his own entries by project_id or only allow admin to read
    # deleted entries). For return values, we expect to get
    # ``project_id`` and ``deleted``, which are suitable for the
    # ``model_query()`` signature.
    kwargs = {}
    if project_id is not None:
        kwargs['project_id'] = _get_project_id(context, project_id,
                                               project_only)
    if read_deleted is not None:
        kwargs['deleted'] = _get_deleted_dict(context, read_deleted)
    session = session or get_session()

    with session.begin():
        return utils.model_query(model, session=session,
                                 args=args, **kwargs)

def get_instance_by_uuid(context, uuid):
    return (_model_query(context, models.Instance, read_deleted='yes')
                  .filter(models.Instance.uuid == uuid)
                  .first())

def get_nodes_data(context, project_id, project_only='allow_none'):
    data = (Node.id, Node.cpu, Node.ram, Node.hdd)

    return (_model_query(context, Node, args=data, project_id=project_id,
                         project_only=project_only)
                  .all())
oslo_db.sqlalchemy.utils.paginate_query(query, model, limit, sort_keys, marker=None, sort_dir=None, sort_dirs=None)

Returns a query with sorting / pagination criteria added.

Pagination works by requiring a unique sort_key, specified by sort_keys. (If sort_keys is not unique, then we risk looping through values.) We use the last row in the previous page as the ‘marker’ for pagination. So we must return values that follow the passed marker in the order. With a single-valued sort_key, this would be easy: sort_key > X. With a compound-values sort_key, (k1, k2, k3) we must do this to repeat the lexicographical ordering: (k1 > X1) or (k1 == X1 && k2 > X2) or (k1 == X1 && k2 == X2 && k3 > X3)

We also have to cope with different sort_directions.

Typically, the id of the last row is used as the client-facing pagination marker, then the actual marker object must be fetched from the db and passed in to us as marker.

Parameters:
  • query – the query object to which we should add paging/sorting
  • model – the ORM model class
  • limit – maximum number of items to return
  • sort_keys – array of attributes by which results should be sorted
  • marker – the last item of the previous page; we returns the next results after this value.
  • sort_dir – direction in which results should be sorted (asc, desc)
  • sort_dirs – per-column array of sort_dirs, corresponding to sort_keys
Return type:

sqlalchemy.orm.query.Query

Returns:

The query with sorting/pagination added.

oslo_db.sqlalchemy.utils.sanitize_db_url(url)
oslo_db.sqlalchemy.utils.text(text, bind=None, bindparams=None, typemap=None, autocommit=None)

Construct a new TextClause clause, representing a textual SQL string directly.

E.g.:

fom sqlalchemy import text

t = text("SELECT * FROM users")
result = connection.execute(t)

The advantages text() provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally. The construct can also be provided with a .c collection of column elements, allowing it to be embedded in other SQL expression constructs as a subquery.

Bind parameters are specified by name, using the format :name. E.g.:

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

For SQL statements where a colon is required verbatim, as within an inline string, use a backslash to escape:

t = text("SELECT * FROM users WHERE name='\:username'")

The TextClause construct includes methods which can provide information about the bound parameters as well as the column values which would be returned from the textual statement, assuming it’s an executable SELECT type of statement. The TextClause.bindparams() method is used to provide bound parameter detail, and TextClause.columns() method allows specification of return columns including names and types:

t = text("SELECT * FROM users WHERE id=:user_id").\
        bindparams(user_id=7).\
        columns(id=Integer, name=String)

for id, name in connection.execute(t):
    print(id, name)

The text() construct is used internally in cases when a literal string is specified for part of a larger query, such as when a string is specified to the Select.where() method of Select. In those cases, the same bind parameter syntax is applied:

s = select([users.c.id, users.c.name]).where("id=:user_id")
result = connection.execute(s, user_id=12)

Using text() explicitly usually implies the construction of a full, standalone statement. As such, SQLAlchemy refers to it as an Executable object, and it supports the Executable.execution_options() method. For example, a text() construct that should be subject to “autocommit” can be set explicitly so using the :paramref:`.Connection.execution_options.autocommit` option:

t = text("EXEC my_procedural_thing()").\
        execution_options(autocommit=True)

Note that SQLAlchemy’s usual “autocommit” behavior applies to text() constructs implicitly - that is, statements which begin with a phrase such as INSERT, UPDATE, DELETE, or a variety of other phrases specific to certain backends, will be eligible for autocommit if no transaction is in progress.

Parameters:
  • text – the text of the SQL statement to be created. use :<param> to specify bind parameters; they will be compiled to their engine-specific format.
  • autocommit – Deprecated. Use .execution_options(autocommit=<True|False>) to set the autocommit option.
  • bind – an optional connection or engine to be used for this text query.
  • bindparams

    Deprecated. A list of bindparam() instances used to provide information about parameters embedded in the statement. This argument now invokes the TextClause.bindparams() method on the construct before returning it. E.g.:

    stmt = text("SELECT * FROM table WHERE id=:id",
              bindparams=[bindparam('id', value=5, type_=Integer)])
    

    Is equivalent to:

    stmt = text("SELECT * FROM table WHERE id=:id").\
              bindparams(bindparam('id', value=5, type_=Integer))
    

    Deprecated since version 0.9.0: the TextClause.bindparams() method supersedes the bindparams argument to text().

  • typemap

    Deprecated. A dictionary mapping the names of columns represented in the columns clause of a SELECT statement to type objects, which will be used to perform post-processing on columns within the result set. This parameter now invokes the TextClause.columns() method, which returns a TextAsFrom construct that gains a .c collection and can be embedded in other expressions. E.g.:

    stmt = text("SELECT * FROM table",
                  typemap={'id': Integer, 'name': String},
              )
    

    Is equivalent to:

    stmt = text("SELECT * FROM table").columns(id=Integer,
                                               name=String)
    

    Or alternatively:

    from sqlalchemy.sql import column
    stmt = text("SELECT * FROM table").columns(
                          column('id', Integer),
                          column('name', String)
                      )
    

    Deprecated since version 0.9.0: the TextClause.columns() method supersedes the typemap argument to text().

oslo_db.sqlalchemy.utils.visit_insert_from_select(element, compiler, **kw)

Form the INSERT INTO table (SELECT ... ) statement.

Previous topic

oslo_db.sqlalchemy.test_migrations

Next topic

How to contribute

This Page