Python sqlalchemy.Table() Examples

def get_type_of_fields(fields, table):
        """
        Return data types of `fields` that are in `table`. If a given
        parameter is empty return primary key.

        :param fields: list - list of fields that need to be returned
        :param table: sa.Table - the current table
        :return: list - list of the tuples `(field_name, fields_type)`
        """

        if not fields:
            fields = table.primary_key

        actual_fields = [
            field for field in table.c.items() if field[0] in fields
        ]

        data_type_fields = {
            name: FIELD_TYPES.get(type(field_type.type), rc.TEXT_FIELD.value)
            for name, field_type in actual_fields
        }

        return data_type_fields 

def importyaml(connection,metadata,sourcePath,language='en'):
    print "Importing dogma attribute categories"
    dgmAttributeCategories = Table('dgmAttributeCategories',metadata)
    
    print "opening Yaml"
        
    trans = connection.begin()
    with open(os.path.join(sourcePath,'fsd','dogmaAttributeCategories.yaml'),'r') as yamlstream:
        print "importing"
        dogmaAttributeCategories=load(yamlstream,Loader=SafeLoader)
        print "Yaml Processed into memory"
        for dogmaAttributeCategoryID in dogmaAttributeCategories:
          attribute = dogmaAttributeCategories[dogmaAttributeCategoryID]
          connection.execute(dgmAttributeCategories.insert(),
                             categoryID=dogmaAttributeCategoryID,
                             categoryName=attribute['name'],
                             categoryDescription=attribute['description']
                )
    trans.commit() 

def importyaml(connection,metadata,sourcePath,language='en'):
    invGroups = Table('invGroups',metadata)
    trnTranslations = Table('trnTranslations',metadata)
    print "Importing Groups"
    print "opening Yaml"
    with open(os.path.join(sourcePath,'fsd','groupIDs.yaml'),'r') as yamlstream:
        trans = connection.begin()
        groupids=load(yamlstream,Loader=SafeLoader)
        print "Yaml Processed into memory"
        for groupid in groupids:
            connection.execute(invGroups.insert(),
                            groupID=groupid,
                            categoryID=groupids[groupid].get('categoryID',0),
                            groupName=groupids[groupid].get('name',{}).get(language,'').decode('utf-8'),
                            iconID=groupids[groupid].get('iconID'),
                            useBasePrice=groupids[groupid].get('useBasePrice'),
                            anchored=groupids[groupid].get('anchored',0),
                            anchorable=groupids[groupid].get('anchorable',0),
                            fittableNonSingleton=groupids[groupid].get('fittableNonSingleton',0),
                            published=groupids[groupid].get('published',0))
            if (groupids[groupid].has_key('name')):
                for lang in groupids[groupid]['name']:
                    connection.execute(trnTranslations.insert(),tcID=7,keyID=groupid,languageID=lang,text=groupids[groupid]['name'][lang].decode('utf-8'));
    trans.commit() 

def importyaml(connection,metadata,sourcePath):
    eveGraphics = Table('eveGraphics',metadata)
    print "Importing Graphics"
    print "opening Yaml"
    with open(os.path.join(sourcePath,'fsd','graphicIDs.yaml'),'r') as yamlstream:
        print "importing"
        trans = connection.begin()
        graphics=load(yamlstream,Loader=SafeLoader)
        print "Yaml Processed into memory"
        for graphic in graphics:
            connection.execute(eveGraphics.insert(),
                            graphicID=graphic,
                            sofFactionName=graphics[graphic].get('sofFactionName',''),
                            graphicFile=graphics[graphic].get('graphicFile',''),
                            sofHullName=graphics[graphic].get('sofHullName',''),
                            sofRaceName=graphics[graphic].get('sofRaceName',''),
                            description=graphics[graphic].get('description',''))
    trans.commit() 

def importyaml(connection,metadata,sourcePath):

    print "Importing BSD Tables"

    files=glob.glob(os.path.join(sourcePath,'bsd','*.yaml'))
    for file in files:
        head, tail = os.path.split(file)
        tablename=tail.split('.')[0]
        print tablename
        tablevar = Table(tablename,metadata)
        print "Importing {}".format(file)
        print "Opening Yaml"
        trans = connection.begin()
        with open(file,'r') as yamlstream:
            rows=load(yamlstream,Loader=SafeLoader)
            print "Yaml Processed into memory"
            for row in rows:
                connection.execute(tablevar.insert().values(row))
        trans.commit() 

def generate_config(entities, base_url, template_name=None,
                    template_folder=None, desc=None, extra_context=None):
    template_name = template_name or 'config.j2'
    desc = desc or 'aiohttp_admin'

    if all(isinstance(ent[2], sa.Table) for ent in entities):
        build_entity = table_entity
    else:
        build_entity = trafaret_entity

    context = {
        "admin_description": desc,
        "base_url": base_url if base_url.endswith("/") else base_url + '/',
        "entities": [build_entity(n, pk, s) for n, pk, s in entities],
        "extra_context": extra_context,
    }

    tf = gather_template_folders(template_folder)
    loader = jinja2.FileSystemLoader(tf)
    env = jinja2.Environment(loader=loader)
    template = env.get_template(template_name)

    text = template.render(context)
    return text 

def _create_table_setup(self):
        from sqlalchemy import Table, Column, PrimaryKeyConstraint

        column_names_and_types = \
            self._get_column_names_and_types(self._sqlalchemy_type)

        columns = [Column(name, typ, index=is_index)
                   for name, typ, is_index in column_names_and_types]

        if self.keys is not None:
            if not is_list_like(self.keys):
                keys = [self.keys]
            else:
                keys = self.keys
            pkc = PrimaryKeyConstraint(*keys, name=self.name + '_pk')
            columns.append(pkc)

        schema = self.schema or self.pd_sql.meta.schema

        # At this point, attach to new metadata, only attach to self.meta
        # once table is created.
        from sqlalchemy.schema import MetaData
        meta = MetaData(self.pd_sql, schema=schema)

        return Table(self.name, meta, *columns, schema=schema) 

def get_type_for_inputs(table):
        """
        Return information about table's fields in dictionary type.

        :param table: sa.Table - the current table
        :return: list - list of the dictionaries
        """
        return [
            dict(
                type=INPUT_TYPES.get(
                    type(field_type.type), rc.TEXT_INPUT.value
                ),
                name=name,
                isPrimaryKey=(name in table.primary_key),
                props=None,
            ) for name, field_type in table.c.items()
        ] 

def schemas(self):
        """
        object: An object with attributes corresponding to the names of the schemas
            in this database.
        """
        from lazy_object_proxy import Proxy

        def get_schemas():
            if not getattr(self, '_schemas', None):
                assert getattr(self, '_sqlalchemy_metadata', None) is not None, (
                    "`{class_name}` instances do not provide the required sqlalchemy metadata "
                    "for schema exploration.".format(class_name=self.__class__.__name__)
                )
                self._schemas = Schemas(self._sqlalchemy_metadata)
            return self._schemas
        return Proxy(get_schemas)


# Extend Table to support returning pandas description of table 

def _show_create_table(self, connection, table, charset=None,
                           full_name=None):
        """Run SHOW CREATE TABLE for a ``Table``."""

        if full_name is None:
            full_name = self.identifier_preparer.format_table(table)
        st = "SHOW CREATE TABLE %s" % full_name

        rp = None
        try:
            rp = connection.execution_options(
                skip_user_error_events=True).execute(st)
        except exc.DBAPIError as e:
            if self._extract_error_code(e.orig) == 1146:
                raise exc.NoSuchTableError(full_name)
            else:
                raise
        row = self._compat_first(rp, charset=charset)
        if not row:
            raise exc.NoSuchTableError(full_name)
        return row[1].strip()

        return sql 

def _describe_table(self, connection, table, charset=None,
                        full_name=None):
        """Run DESCRIBE for a ``Table`` and return processed rows."""

        if full_name is None:
            full_name = self.identifier_preparer.format_table(table)
        st = "DESCRIBE %s" % full_name

        rp, rows = None, None
        try:
            try:
                rp = connection.execution_options(
                    skip_user_error_events=True).execute(st)
            except exc.DBAPIError as e:
                if self._extract_error_code(e.orig) == 1146:
                    raise exc.NoSuchTableError(full_name)
                else:
                    raise
            rows = self._compat_fetchall(rp, charset=charset)
        finally:
            if rp:
                rp.close()
        return rows 

def setup(srid=None):

    if package_extent_table is None:
        define_spatial_tables(srid)
        log.debug('Spatial tables defined in memory')

    if model.package_table.exists():
        if not Table('geometry_columns',meta.metadata).exists() or \
            not Table('spatial_ref_sys',meta.metadata).exists():
            raise Exception('The spatial extension is enabled, but PostGIS ' + \
                    'has not been set up in the database. ' + \
                    'Please refer to the "Setting up PostGIS" section in the README.')


        if not package_extent_table.exists():
            try:
                package_extent_table.create()
            except Exception,e:
                # Make sure the table does not remain incorrectly created
                # (eg without geom column or constraints)
                if package_extent_table.exists():
                    Session.execute('DROP TABLE package_extent')
                    Session.commit()

                raise e

            log.debug('Spatial tables created')
        else:
            log.debug('Spatial tables already exist')
            # Future migrations go here 

def _make_endpoint_groups(new_groups, new_endpoints):
    """Create endpoint groups and their corresponding endpoints."""
    md = sa.MetaData()
    engine = op.get_bind()
    sa.Table('vpn_endpoint_groups', md, autoload=True, autoload_with=engine)
    op.bulk_insert(md.tables['vpn_endpoint_groups'], new_groups)
    sa.Table('vpn_endpoints', md, autoload=True, autoload_with=engine)
    op.bulk_insert(md.tables['vpn_endpoints'], new_endpoints) 

def upgrade(migrate_engine):
    meta.bind = migrate_engine

    RECORD_TYPES = ['A', 'AAAA', 'CNAME', 'MX', 'SRV', 'TXT', 'SPF', 'NS',
                    'PTR', 'SSHFP', 'SOA', 'NAPTR', 'CAA']

    records_table = Table('recordsets', meta, autoload=True)
    records_table.columns.type.alter(name='type', type=Enum(*RECORD_TYPES)) 

def upgrade(migrate_engine):
    meta.bind = migrate_engine

    RECORD_TYPES = ['A', 'AAAA', 'CNAME', 'MX', 'SRV', 'TXT', 'SPF', 'NS',
                    'PTR', 'SSHFP', 'SOA', 'NAPTR']

    records_table = Table('recordsets', meta, autoload=True)
    records_table.columns.type.alter(name='type', type=Enum(*RECORD_TYPES)) 

def _duplicate_service_status(self):
        engine = session.get_engine('storage:sqlalchemy')
        metadata = MetaData(bind=engine)
        status = Table('service_statuses', metadata, autoload=True)
        service_select = (select([func.count()])
                          .select_from(status)
                          .group_by('service_name', 'hostname')
                          )
        service_counts = engine.execute(service_select).fetchall()
        duplicated_services = [i for i in service_counts if i[0] > 1]
        if duplicated_services:
            return upgradecheck.Result(upgradecheck.Code.FAILURE,
                                       _('Duplicated services found in '
                                         'service_statuses table.'))
        return upgradecheck.Result(upgradecheck.Code.SUCCESS) 

def setup_spatial_table(package_extent_class, db_srid=None):

    if legacy_geoalchemy:

        package_extent_table = Table(
            'package_extent', meta.metadata,
            Column('package_id', types.UnicodeText, primary_key=True),
            GeometryExtensionColumn('the_geom', Geometry(2, srid=db_srid))
        )

        meta.mapper(
            package_extent_class,
            package_extent_table,
            properties={'the_geom':
                        GeometryColumn(package_extent_table.c.the_geom,
                                       comparator=PGComparator)}
        )

        GeometryDDL(package_extent_table)
    else:

        # PostGIS 1.5 requires management=True when defining the Geometry
        # field
        management = (postgis_version()[:1] == '1')

        package_extent_table = Table(
            'package_extent', meta.metadata,
            Column('package_id', types.UnicodeText, primary_key=True),
            Column('the_geom', Geometry('GEOMETRY', srid=db_srid,
                                        management=management)),
        )

        meta.mapper(package_extent_class, package_extent_table)

    return package_extent_table 

def after_create(self, target, connection, **kw):
        """Called after CREATE statements are emitted.

        :param target: the :class:`.MetaData` or :class:`.Table`
         object which is the target of the event.
        :param connection: the :class:`.Connection` where the
         CREATE statement or statements have been emitted.
        :param \**kw: additional keyword arguments relevant
         to the event.  The contents of this dictionary
         may vary across releases, and include the
         list of tables being generated for a metadata-level
         event, the checkfirst flag, and other
         elements used by internal events.

        """ 

def upgrade():
    bind = op.get_bind()
    md = sa.MetaData()
    sa.Table('operating_status', md, autoload=True, autoload_with=bind)
    op.bulk_insert(md.tables['operating_status'], [{'name': 'DRAINING'}]) 

def make_models(Base):

    users_hobbies = sa.Table('users_hobbies', Base.metadata,
        sa.Column('user_id', sa.Integer, sa.ForeignKey('user.user_id')),
        sa.Column('hobby_id', sa.Integer, sa.ForeignKey('hobby.id')),
    )

    class User(Base):
        __tablename__ = 'user'
        id = sa.Column('user_id', sa.Integer, primary_key=True)
        addresses = sa.orm.relationship('Address', backref='user')
        hobbies = sa.orm.relationship('Hobby', secondary=users_hobbies, backref='users')

    class Address(Base):
        __tablename__ = 'address'
        id = sa.Column(sa.Integer, primary_key=True)
        user_id = sa.Column(sa.Integer, sa.ForeignKey('user.user_id'))

    class Hobby(Base):
        __tablename__ = 'hobby'
        id = sa.Column(sa.Integer, primary_key=True)

    return Bunch(
        User=User,
        Address=Address,
        Hobby=Hobby,
    ) 

def init_alembic(engine, schema_version):
	"""
	Creates the alembic_version table and sets the value of the table according
	to the specified schema version.

	:param engine: The engine used to connect to the database.
	:type engine: :py:class:`sqlalchemy.engine.Engine`
	:param int schema_version: The MetaData schema_version to set the alembic version to.
	"""
	pattern = re.compile(r'[a-f0-9]{10,16}_schema_v\d+\.py')
	alembic_revision = None
	alembic_directory = find.data_directory('alembic')
	if not alembic_directory:
		raise errors.KingPhisherDatabaseError('cannot find the alembic data directory')
	alembic_versions_files = os.listdir(os.path.join(alembic_directory, 'versions'))
	for file in alembic_versions_files:
		if not pattern.match(file):
			continue
		if not file.endswith('_schema_v' + str(schema_version) + '.py'):
			continue
		alembic_revision = file.split('_', 1)[0]
		break
	if not alembic_revision:
		raise errors.KingPhisherDatabaseError("cannot find current alembic version for schema version {0}".format(schema_version))

	alembic_metadata = sqlalchemy.MetaData(engine)
	alembic_table = sqlalchemy.Table(
		'alembic_version',
		alembic_metadata,
		sqlalchemy.Column(
			'version_num',
			sqlalchemy.String,
			primary_key=True,
			nullable=False
		)
	)
	alembic_metadata.create_all()
	alembic_version_entry = alembic_table.insert().values(version_num=alembic_revision)
	engine.connect().execute(alembic_version_entry)
	logger.info("alembic_version table initialized to {0}".format(alembic_revision)) 

def sync_trigger(conn, table_name):
    """
    Synchronizes versioning trigger for given table with given connection.

    ::


        sync_trigger(conn, 'my_table')


    :param conn: SQLAlchemy connection object
    :param table_name: Name of the table to synchronize versioning trigger for

    .. versionadded: 1.1.0
    """
    meta = sa.MetaData()
    version_table = sa.Table(
        table_name,
        meta,
        autoload=True,
        autoload_with=conn
    )
    parent_table = sa.Table(
        table_name[0:-len('_version')],
        meta,
        autoload=True,
        autoload_with=conn
    )
    excluded_columns = (
        set(c.name for c in parent_table.c) -
        set(c.name for c in version_table.c if not c.name.endswith('_mod'))
    )
    drop_trigger(conn, parent_table.name)
    create_trigger(conn, table=parent_table, excluded_columns=excluded_columns) 

def _get_selectable(self, engine_or_conn, columns=None):
        import sqlalchemy as sa
        from sqlalchemy import sql
        from sqlalchemy.exc import NoSuchTableError

        # process table_name
        if self._selectable is not None:
            selectable = self._selectable
        else:
            if isinstance(self._table_or_sql, sa.Table):
                selectable = self._table_or_sql
                self._table_or_sql = selectable.name
            else:
                m = sa.MetaData()
                try:
                    selectable = sa.Table(self._table_or_sql, m, autoload=True,
                                          autoload_with=engine_or_conn, schema=self._schema)
                except NoSuchTableError:
                    temp_table_name = 'temp_' + binascii.b2a_hex(uuid.uuid4().bytes).decode()
                    if columns:
                        selectable = sql.text(self._table_or_sql).columns(*[sql.column(c) for c in columns])
                    else:
                        selectable = sql.select(
                            '*', from_obj=sql.text('(%s) AS %s' % (self._table_or_sql, temp_table_name)))
                    self._selectable = selectable
        return selectable 

def bind(self):
        """Return the current "bind".

        In online mode, this is an instance of
        :class:`sqlalchemy.engine.Connection`, and is suitable
        for ad-hoc execution of any kind of usage described
        in :ref:`sqlexpression_toplevel` as well as
        for usage with the :meth:`sqlalchemy.schema.Table.create`
        and :meth:`sqlalchemy.schema.MetaData.create_all` methods
        of :class:`~sqlalchemy.schema.Table`,
        :class:`~sqlalchemy.schema.MetaData`.

        Note that when "standard output" mode is enabled,
        this bind will be a "mock" connection handler that cannot
        return results and is only appropriate for a very limited
        subset of commands.

        """
        return self.connection 

def _include_sqlalchemy(obj):
    for module in sqlalchemy, sqlalchemy.orm:
        for key in module.__all__:
            if not hasattr(obj, key):
                setattr(obj, key, getattr(module, key))
    # Note: obj.Table does not attempt to be a SQLAlchemy Table class.
    obj.Table = _make_table(obj)
    obj.relationship = _wrap_with_default_query_class(obj.relationship)
    obj.relation = _wrap_with_default_query_class(obj.relation)
    obj.dynamic_loader = _wrap_with_default_query_class(obj.dynamic_loader)
    obj.event = event 

def _make_table(db):
    def _make_table(*args, **kwargs):
        if len(args) > 1 and isinstance(args[1], db.Column):
            args = (args[0], db.metadata) + args[1:]
        info = kwargs.pop('info', None) or {}
        info.setdefault('bind_key', None)
        kwargs['info'] = info
        return sqlalchemy.Table(*args, **kwargs)
    return _make_table 

def after_parent_attach(self, target, parent):
        """Called after a :class:`.SchemaItem` is associated with
        a parent :class:`.SchemaItem`.

        :param target: the target object
        :param parent: the parent to which the target is being attached.

        :func:`.event.listen` also accepts a modifier for this event:

        :param propagate=False: When True, the listener function will
         be established for any copies made of the target object,
         i.e. those copies that are generated when
         :meth:`.Table.tometadata` is used.

        """ 

def before_parent_attach(self, target, parent):
        """Called before a :class:`.SchemaItem` is associated with
        a parent :class:`.SchemaItem`.

        :param target: the target object
        :param parent: the parent to which the target is being attached.

        :func:`.event.listen` also accepts a modifier for this event:

        :param propagate=False: When True, the listener function will
         be established for any copies made of the target object,
         i.e. those copies that are generated when
         :meth:`.Table.tometadata` is used.

        """ 

def after_drop(self, target, connection, **kw):
        """Called after DROP statements are emitted.

        :param target: the :class:`.MetaData` or :class:`.Table`
         object which is the target of the event.
        :param connection: the :class:`.Connection` where the
         DROP statement or statements have been emitted.
        :param \**kw: additional keyword arguments relevant
         to the event.  The contents of this dictionary
         may vary across releases, and include the
         list of tables being generated for a metadata-level
         event, the checkfirst flag, and other
         elements used by internal events.

        """ 

def before_create(self, target, connection, **kw):
        """Called before CREATE statements are emitted.

        :param target: the :class:`.MetaData` or :class:`.Table`
         object which is the target of the event.
        :param connection: the :class:`.Connection` where the
         CREATE statement or statements will be emitted.
        :param \**kw: additional keyword arguments relevant
         to the event.  The contents of this dictionary
         may vary across releases, and include the
         list of tables being generated for a metadata-level
         event, the checkfirst flag, and other
         elements used by internal events.

        """