co3.accessors.sql module

Design proposal: variable backends

One particular feature not supported by the current type hierarchy is the possible use of different backends to implement a general interface like SQLAccessor. One could imagine, for instance, using sqlalchemy or sqlite to define the same methods laid out in a parent class blueprint. It’s not too difficult to imagine cases where both of these may be useful, but for now it is outside the development scope. Should it ever enter the scope, however, we might consider a simple backend argument on instantiation, keeping just the SQLAccessor exposed rather than a whole set of backend-specific types:

class SQLAlchemyAccessor(RelationalAccessor): # may also inherit from a dedicated interface parent
    def select(...):
        ...

class SQLiteAccessor(RelationalAccessor):
    def select(...):
        ...

class SQLAccessor(RelationalAccessor):
    backends = {
        'sqlalchemy': SQLAlchemyAccessor,
        'sqlite':     SQLteAccessor,
    }

    def __init__(self, backend: str):
        self.backend = self.backends.get(backend)

    def select(...):
        return self.backend.select(...)

For now, we can look at SQLAccessor (and equivalents in other type hierarchies, like SQLManagers) as being SQLAlchemyAccessors and not supporting any backend swapping. But in theory, to make the above change, we’d just need to rename it and wrap it up.

class co3.accessors.sql.RelationalAccessor

Bases: Accessor, Generic

static raw_select(self, connection, text)

select(connection, relation, attributes=None, where=None, distinct_on=None, order_by=None, limit=0, mappings=False, include_cols=False)

select_one(connection, relation, attributes=None, where=None, mappings=False, include_cols=False)

class co3.accessors.sql.SQLAccessor

Bases: RelationalAccessor[SQLTable]

raw_select(connection, sql, bind_params=None, mappings=False, include_cols=False)

static result_dicts(results, query_cols=None)

Parse SQLAlchemy results into Python dicts. Leverages mappings to associate full column name context.

If query_cols is provided, their implicit names will be used for the keys of the returned dictionaries. This information is not available under CursorResults and thus must be provided separately. This will yield results like the following:

[..., {'table1.col':<value>, 'table2.col':<value>, ...}, ...]

Instead of the automatic mapping names:

[..., {'col':<value>, 'col_1':<value>, ...}, ...]

which can make accessing certain results a little more intuitive.

select(connection, component, columns=None, where=None, distinct_on=None, order_by=None, limit=0, mappings=False, include_cols=False)

Perform a SELECT query against the provided table-like object (see check_table()).

Deprecated: String aliases

String aliases for tables are no longer supported. This method no longer checks against any specific schema table-maps or Composers. Instead, this should be done outside the Accessor.

Parameters:

• group_by – list of columns to group by; for now serves as a proxy for DISTINCT (no aggregation methods accepted)

• order_by – column to order results by (can use <col>.desc() to order by descending)

Returns:

Statement results, either as a list of 1) SQLAlchemy Mappings, or 2) converted dictionaries