Python sqlalchemy.inspect() Examples

def test_create_table_sqlite3_error(
        self, sqlite_database, mysql_database, mysql_credentials, mocker, caplog
    ):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeSQLiteCursor:
            def executescript(self, *args, **kwargs):
                raise sqlite3.Error("Unknown SQLite error")

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()
        mocker.patch.object(proc, "_sqlite_cur", FakeSQLiteCursor())
        caplog.set_level(logging.DEBUG)
        with pytest.raises(sqlite3.Error):
            proc._create_table(choice(mysql_tables)) 

def test_autoincrement_col(self):
        """test that 'autoincrement' is reflected according to sqla's policy.

        Don't mark this test as unsupported for any backend !

        (technically it fails with MySQL InnoDB since "id" comes before "id2")

        A backend is better off not returning "autoincrement" at all,
        instead of potentially returning "False" for an auto-incrementing
        primary key column.

        """

        meta = self.metadata
        insp = inspect(meta.bind)

        for tname, cname in [
            ('users', 'user_id'),
            ('email_addresses', 'address_id'),
            ('dingalings', 'dingaling_id'),
        ]:
            cols = insp.get_columns(tname)
            id_ = dict((c['name'], c) for c in cols)[cname]
            assert id_.get('autoincrement', True) 

def force_drop_names(*names):
    """Force the given table names to be dropped after test complete,
    isolating for foreign key cycles

    """
    from . import config
    from sqlalchemy import inspect

    @decorator
    def go(fn, *args, **kw):

        try:
            return fn(*args, **kw)
        finally:
            drop_all_tables(
                config.db, inspect(config.db), include_names=names)
    return go 

def object_as_dict(obj, relationships=False):
    """
    Converts an SQLAlchemy instance to a dictionary.

    :param relationships: If true, also include relationships in the output dict
    """
    properties = inspect(obj).mapper.all_orm_descriptors

    if not relationships:
        properties = {
            key: value
            for key, value in properties.items()
            if not hasattr(value, "prop")
            or not isinstance(value.prop, RelationshipProperty)
        }

    return {key: getattr(obj, key) for key, value in properties.items()} 

def aliased_attrs(cls, aliased_class: AliasedClass, obj: object, *attr_names: str):
        """ Wrap a whole list of dictionaries into aliased wrappers """
        # Prepare AliasedInsp: this is what adapt_to_entity() wants
        aliased_inspector = inspect(aliased_class)
        assert aliased_inspector.is_aliased_class, '`aliased_class` must be an alias!'

        # Convert every attribute
        for attr_name in attr_names:
            setattr(obj, attr_name,
                    # Wrap it with self
                    cls(getattr(obj, attr_name),
                        aliased_inspector)
                    )

        # Done
        return obj 

def _test_get_indexes(self, schema=None):
        meta = self.metadata
        users, addresses, dingalings = self.tables.users, \
            self.tables.email_addresses, self.tables.dingalings
        # The database may decide to create indexes for foreign keys, etc.
        # so there may be more indexes than expected.
        insp = inspect(meta.bind)
        indexes = insp.get_indexes('users', schema=schema)
        expected_indexes = [
            {'unique': False,
             'column_names': ['test1', 'test2'],
             'name': 'users_t_idx'},
            {'unique': False,
             'column_names': ['user_id', 'test2', 'test1'],
             'name': 'users_all_idx'}
        ]
        index_names = [d['name'] for d in indexes]
        for e_index in expected_indexes:
            assert e_index['name'] in index_names
            index = indexes[index_names.index(e_index['name'])]
            for key in e_index:
                eq_(e_index[key], index[key]) 

def sqlalchemy_to_dict(obj):
    """Convert SQLAlchemy object to dict.

    Args:
        query (class): SQLAlchemy Object.

    Returns:
        dict: Return a dict representation of an SQLAlchemy object.

    """
    fields = {}
    for field in [c.key for c in inspect(obj).mapper.column_attrs]:
        data = obj.__getattribute__(field)
        try:
            if isinstance(data, datetime):
                data = data.strftime('%Y-%m-%d %H:%M:%S')
            json.dumps(data)
            fields[field] = data
        except TypeError:
            fields[field] = None
    return fields 

def __init__(self, obj, visited_objects=None, relations=[]):
        self.visited_objects = visited_objects or []
        self.obj = obj
        self.version_parent = self.obj.version_parent
        self.parent_class = parent_class(self.obj.__class__)
        self.parent_mapper = sa.inspect(self.parent_class)
        self.session = sa.orm.object_session(self.obj)

        self.relations = list(relations)
        for path in relations:
            subpath = path.split('.')[0]
            if subpath not in self.parent_mapper.relationships:
                raise ReverterException(
                    "Could not initialize Reverter. Class '%s' does not have "
                    "relationship '%s'." % (
                        parent_class(self.obj.__class__).__name__,
                        subpath
                    )
                ) 

def test_create_table_unknown_mysql_connector_error(
        self, sqlite_database, mysql_database, mysql_credentials, mocker, caplog
    ):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeSQLiteCursor:
            def executescript(self, statement):
                raise mysql.connector.Error(
                    msg="Error Code: 2000. Unknown MySQL error",
                    errno=errorcode.CR_UNKNOWN_ERROR,
                )

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()
        mocker.patch.object(proc, "_sqlite_cur", FakeSQLiteCursor())
        caplog.set_level(logging.DEBUG)
        with pytest.raises(mysql.connector.Error):
            proc._create_table(choice(mysql_tables)) 

def changeset(self):
        """
        Return a dictionary of changed fields in this version with keys as
        field names and values as lists with first value as the old field value
        and second list value as the new value.
        """
        previous_version = self.previous
        data = {}

        for key in sa.inspect(self.__class__).columns.keys():
            if is_internal_column(self, key):
                continue
            if not previous_version:
                old = None
            else:
                old = getattr(previous_version, key)
            new = getattr(self, key)
            if old != new:
                data[key] = [old, new]

        manager = get_versioning_manager(self)
        manager.plugins.after_construct_changeset(self, data)
        return data 

def _test_get_table_names(self, schema=None, table_type='table',
                              order_by=None):
        meta = self.metadata
        users, addresses, dingalings = self.tables.users, \
            self.tables.email_addresses, self.tables.dingalings
        insp = inspect(meta.bind)

        if table_type == 'view':
            table_names = insp.get_view_names(schema)
            table_names.sort()
            answer = ['email_addresses_v', 'users_v']
            eq_(sorted(table_names), answer)
        else:
            table_names = insp.get_table_names(schema,
                                               order_by=order_by)
            if order_by == 'foreign_key':
                answer = ['users', 'email_addresses', 'dingalings']
                eq_(table_names, answer)
            else:
                answer = ['dingalings', 'email_addresses', 'users']
                eq_(sorted(table_names), answer) 

def option(obj_or_class, option_name):
    """
    Return the option value of given option for given versioned object or
    class.

    :param obj_or_class: SQLAlchemy declarative model object or class
    :param option_name: The name of an option to return
    """
    if isinstance(obj_or_class, AliasedClass):
        obj_or_class = sa.inspect(obj_or_class).mapper.class_
    cls = obj_or_class if isclass(obj_or_class) else obj_or_class.__class__
    if not hasattr(cls, '__versioned__'):
        cls = parent_class(cls)
    return get_versioning_manager(cls).option(
        cls, option_name
    ) 

def get_inherited_denormalized_columns(self, table):
        parent_models = list(versioned_parents(self.manager, self.model))
        mapper = sa.inspect(self.model)
        args = {}

        if parent_models and not (mapper.single or mapper.concrete):
            columns = [
                self.manager.option(self.model, 'operation_type_column_name'),
                self.manager.option(self.model, 'transaction_column_name')
            ]
            if self.manager.option(self.model, 'strategy') == 'validity':
                columns.append(
                    self.manager.option(
                        self.model,
                        'end_transaction_column_name'
                    )
                )

            for column in columns:
                args[column] = column_property(
                    table.c[column],
                    *[m.__table__.c[column] for m in parent_models]
                )
        return args 

def upgrade():
    conn = op.get_bind()

    insp = inspect(conn)
    fk_names = [fk['name'] for fk in insp.get_foreign_keys('host')]
    if ("fk_hypervisor_id_resource_id" not in fk_names and
            "fk_host_id_resource_id" in fk_names):
        # NOTE(sileht): we are already good, the BD have been created from
        # scratch after "a54c57ada3f5"
        return

    op.drop_constraint("fk_hypervisor_id_resource_id", "host",
                       type_="foreignkey")
    op.drop_constraint("fk_hypervisor_history_resource_history_revision",
                       "host_history", type_="foreignkey")
    op.create_foreign_key("fk_host_id_resource_id", "host", "resource",
                          ["id"], ["id"], ondelete="CASCADE")
    op.create_foreign_key("fk_host_history_resource_history_revision",
                          "host_history", "resource_history",
                          ["revision"], ["revision"], ondelete="CASCADE") 

def jsonify(self, attrs=None):
        d = dict(self)
        del d['revision']
        if 'metrics' not in sqlalchemy.inspect(self).unloaded:
            d['metrics'] = dict((m.name, six.text_type(m.id))
                                for m in self.metrics)

        if self.creator is None:
            d['created_by_user_id'] = d['created_by_project_id'] = None
        else:
            d['created_by_user_id'], _, d['created_by_project_id'] = (
                self.creator.partition(":")
            )

        if attrs:
            return {key: val for key, val in d.items() if key in attrs}
        else:
            return d 

def jsonify(self):
        d = {
            "id": self.id,
            "creator": self.creator,
            "name": self.name,
            "unit": self.unit,
        }
        unloaded = sqlalchemy.inspect(self).unloaded
        if 'resource' in unloaded:
            d['resource_id'] = self.resource_id
        else:
            d['resource'] = self.resource
        if 'archive_policy' in unloaded:
            d['archive_policy_name'] = self.archive_policy_name
        else:
            d['archive_policy'] = self.archive_policy

        if self.creator is None:
            d['created_by_user_id'] = d['created_by_project_id'] = None
        else:
            d['created_by_user_id'], _, d['created_by_project_id'] = (
                self.creator.partition(":")
            )

        return d 

def get_virtual_server(self, neutron_id):
        return inspect(self).session.\
            query(A10ScalingGroupMemberVirtualServer).\
            filter_by(member_id=self.id, neutron_id=neutron_id).\
            first() 

def test_transfer_table_data_exceptions(
        self,
        sqlite_database,
        mysql_database,
        mysql_credentials,
        mocker,
        caplog,
        exception,
    ):
        proc = MySQLtoSQLite(
            sqlite_file=sqlite_database,
            mysql_user=mysql_credentials.user,
            mysql_password=mysql_credentials.password,
            mysql_database=mysql_credentials.database,
            mysql_host=mysql_credentials.host,
            mysql_port=mysql_credentials.port,
        )

        class FakeMySQLCursor:
            def fetchall(self):
                raise exception

            def fetchmany(self, size=1):
                raise exception

        mysql_inspect = inspect(mysql_database.engine)
        mysql_tables = mysql_inspect.get_table_names()

        table_name = choice(mysql_tables)
        columns = [column["name"] for column in mysql_inspect.get_columns(table_name)]

        sql = 'INSERT OR IGNORE INTO "{table}" ({fields}) VALUES ({placeholders})'.format(
            table=table_name,
            fields=('"{}", ' * len(columns)).rstrip(" ,").format(*columns),
            placeholders=("?, " * len(columns)).rstrip(" ,"),
        )

        mocker.patch.object(proc, "_mysql_cur", FakeMySQLCursor())

        with pytest.raises((mysql.connector.Error, sqlite3.Error)):
            proc._transfer_table_data(table_name, sql) 

def _test_get_table_oid(self, table_name, schema=None):
        meta = self.metadata
        insp = inspect(meta.bind)
        oid = insp.get_table_oid(table_name, schema)
        self.assert_(isinstance(oid, int)) 

def primary_key_columns(cls):
        return inspect(cls).primary_key 

def test_transfer_specific_tables_only(
        self, cli_runner, sqlite_database, mysql_credentials, mysql_database
    ):
        mysql_engine = create_engine(
            "mysql+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
                user=mysql_credentials.user,
                password=mysql_credentials.password,
                host=mysql_credentials.host,
                port=mysql_credentials.port,
                database=mysql_credentials.database,
            )
        )
        mysql_inspect = inspect(mysql_engine)
        mysql_tables = mysql_inspect.get_table_names()

        if six.PY2:
            table_number = choice(xrange(1, len(mysql_tables)))
        else:
            table_number = choice(range(1, len(mysql_tables)))

        result = cli_runner.invoke(
            mysql2sqlite,
            [
                "-f",
                sqlite_database,
                "-d",
                mysql_credentials.database,
                "-t",
                " ".join(sample(mysql_tables, table_number)),
                "-u",
                mysql_credentials.user,
                "-p",
                mysql_credentials.password,
                "-h",
                mysql_credentials.host,
                "-P",
                mysql_credentials.port,
            ],
        )
        assert result.exit_code == 0 

def add_update(self, target):
        state_copy = copy(sa.inspect(target).committed_state)
        relationships = sa.inspect(target.__class__).relationships
        # Remove all ONETOMANY and MANYTOMANY relationships
        for rel_key, relationship in relationships.items():
            if relationship.direction.name in ['ONETOMANY', 'MANYTOMANY']:
                if rel_key in state_copy:
                    del state_copy[rel_key]

        if state_copy:
            self.add(Operation(target, Operation.UPDATE))

    # def add_delete(self, target):
    #     self.add(Operation(target, Operation.DELETE)) 

def clone_model(instance):
    """
    Clones an SQLAlchemy instance.
    """
    # Copy the attributes as a dictionary
    dict = object_as_dict(instance, relationships=True)
    # Find the primary key and remove the ID
    column_name = inspect(instance.__class__).primary_key[0].name
    del dict[column_name]
    # Create a new instance using this data
    new_instance = instance.__class__(**dict)
    return new_instance 

def instance_pk(instance):
    """
    Returns a tuple of (column_name, column_value) for the first primary key on
    this instance.
    """
    column_name = inspect(instance.__class__).primary_key[0].name
    return column_name, getattr(instance, column_name) 

def relationship_fields(model):
    """
    Returns a list of keys that each correspond to a relationship on this
    model.
    """
    return [rel.key for rel in inspect(model).relationships.values()] 

def one_to_many_subquery(self, obj):
        tx_column = option(obj, 'transaction_column_name')

        remote_alias = sa.orm.aliased(self.remote_cls)
        primary_keys = [
            getattr(remote_alias, column.name) for column
            in sa.inspect(remote_alias).mapper.columns
            if column.primary_key and column.name != tx_column
        ]

        return sa.exists(
            sa.select(
                [1]
            ).where(
                sa.and_(
                    getattr(remote_alias, tx_column) <=
                    getattr(obj, tx_column),
                    *[
                        getattr(remote_alias, pk.name) ==
                        getattr(self.remote_cls, pk.name)
                        for pk in primary_keys
                    ]
                )
            ).group_by(
                *primary_keys
            ).having(
                sa.func.max(getattr(remote_alias, tx_column)) ==
                getattr(self.remote_cls, tx_column)
            ).correlate(self.local_cls, self.remote_cls)
        ) 

def reflect_column(self, column):
        """
        Make a copy of parent table column and some alterations to it.

        :param column: SQLAlchemy Column object of parent table
        """
        # Make a copy of the column so that it does not point to wrong
        # table.
        column_copy = column.copy()
        # Remove unique constraints
        column_copy.unique = False
        # Remove onupdate triggers
        column_copy.onupdate = None
        if column_copy.autoincrement:
            column_copy.autoincrement = False
        if column_copy.name == self.option('transaction_column_name'):
            column_copy.nullable = False

        if not column_copy.primary_key:
            column_copy.nullable = True

        # Find the right column key
        if self.model is not None:
            for key, value in sa.inspect(self.model).columns.items():
                if value is column:
                    column_copy.key = key
        return column_copy 

def for_manager(self, manager, cls):
        strategy = manager.option(cls, 'strategy')
        operation_type_column = manager.option(
            cls,
            'operation_type_column_name'
        )
        excluded_columns = [
            c.name for c in sa.inspect(cls).columns
            if manager.is_excluded_column(cls, c)
        ]
        return self(
            update_validity_for_tables=(
                sa.inspect(cls).tables if strategy == 'validity' else []
            ),
            version_table_name_format=manager.option(cls, 'table_name'),
            operation_type_column_name=operation_type_column,
            transaction_column_name=manager.option(
                cls, 'transaction_column_name'
            ),
            end_transaction_column_name=manager.option(
                cls, 'end_transaction_column_name'
            ),
            use_property_mod_tracking=uses_property_mod_tracking(manager),
            excluded_columns=excluded_columns,
            table=cls.__table__
        ) 

def inheritance_args(self, cls, version_table, table):
        """
        Return mapper inheritance args for currently built history model.
        """
        args = {}

        if not sa.inspect(self.model).single:
            parent = find_closest_versioned_parent(
                self.manager, self.model
            )
            if parent:
                # The version classes do not contain foreign keys, hence we
                # need to map inheritance condition manually for classes that
                # use joined table inheritance
                if parent.__table__.name != table.name:
                    mapper = sa.inspect(self.model)

                    inherit_condition = adapt_columns(
                        mapper.inherit_condition
                    )
                    tx_column_name = self.manager.options[
                        'transaction_column_name'
                    ]
                    args['inherit_condition'] = sa.and_(
                        inherit_condition,
                        getattr(parent.__table__.c, tx_column_name) ==
                        getattr(cls.__table__.c, tx_column_name)
                    )
                    args['inherit_foreign_keys'] = [
                        version_table.c[column.key]
                        for column in sa.inspect(self.model).columns
                        if column.primary_key
                    ]

        args.update(copy_mapper_args(self.model))

        return args 

def delete_port(self, port):
        port = self.get_port(port)
        if port:
            inspect(self).session.delete(port)