Python sqlalchemy.schema.MetaData() Examples

def test_mysql_column_types(metadata):
    Table("simple_items", metadata, Column("id", mysql.INTEGER), Column("name", mysql.VARCHAR(255)))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('id', Integer),
    Column('name', String(255))
)
"""
    ) 

def __init__(self):
        ###
        # Private database engine and metadata attributes.
        #
        self._engine = None
        self._metadata = MetaData(schema=db_option('schema'))

        ###
        # Session Factory classes, later initialized in self.initialize() method.
        #
        # The self.Session corresponds to a session factory that doesn't expire ORM instances from memory
        #   after getting committed.
        #
        # The self.OnCommitExpiringSession corresponds to a session factory that expires ORM instances from
        #   memory after getting committed.
        #
        self.Session = None
        self.OnCommitExpiringSession = None

        ###
        # Declarative Base Model class.
        #
        self.BaseModel = declarative_base(
            cls=BaseModelMixin,
            metadata=MetaData(schema=db_option('schema'))) 

def provide_metadata(fn, *args, **kw):
    """Provide bound MetaData for a single test, dropping afterwards."""

    from . import config
    from . import engines
    from sqlalchemy import schema

    metadata = schema.MetaData(config.db)
    self = args[0]
    prev_meta = getattr(self, 'metadata', None)
    self.metadata = metadata
    try:
        return fn(*args, **kw)
    finally:
        engines.drop_all_tables(metadata, config.db)
        self.metadata = prev_meta 

def __init__(self):
        ###
        # Private database engine and metadata attributes.
        #
        self._engine = None
        self._metadata = MetaData(schema=db_option('schema'))

        ###
        # Session Factory classes, later initialized in self.initialize() method.
        #
        # The self.Session corresponds to a session factory that doesn't expire ORM instances from memory
        #   after getting committed.
        #
        # The self.OnCommitExpiringSession corresponds to a session factory that expires ORM instances from
        #   memory after getting committed.
        #
        self.Session = None
        self.OnCommitExpiringSession = None

        ###
        # Declarative Base Model class.
        #
        self.BaseModel = declarative_base(
            cls=BaseModelMixin,
            metadata=MetaData(schema=db_option('schema'))) 

def provide_metadata(fn, *args, **kw):
    """Provide bound MetaData for a single test, dropping afterwards."""

    from . import config
    from . import engines
    from sqlalchemy import schema

    metadata = schema.MetaData(config.db)
    self = args[0]
    prev_meta = getattr(self, 'metadata', None)
    self.metadata = metadata
    try:
        return fn(*args, **kw)
    finally:
        engines.drop_all_tables(metadata, config.db)
        self.metadata = prev_meta 

def codegen(args):

    # Use reflection to fill in the metadata
    engine = create_engine(args.url)

    metadata = MetaData(engine)
    tables = args.tables.split(",") if args.tables else None
    metadata.reflect(engine, args.schema, not args.noviews, tables)
    if db.session.bind.dialect.name == "sqlite":
        # dirty hack for sqlite
        engine.execute("""PRAGMA journal_mode = OFF""")

    # Write the generated model code to the specified file or standard output

    capture = StringIO()
    # outfile = io.open(args.outfile, 'w', encoding='utf-8') if args.outfile else capture # sys.stdout
    generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect, args.noclasses)
    generator.render(capture)
    generated = capture.getvalue()
    generated = fix_generated(generated)
    if args.outfile:
        outfile = io.open(args.outfile, "w", encoding="utf-8")
        outfile.write(generated)
    return generated 

def provide_metadata(fn, *args, **kw):
    """Provide bound MetaData for a single test, dropping afterwards."""

    from . import config
    from . import engines
    from sqlalchemy import schema

    metadata = schema.MetaData(config.db)
    self = args[0]
    prev_meta = getattr(self, 'metadata', None)
    self.metadata = metadata
    try:
        return fn(*args, **kw)
    finally:
        engines.drop_all_tables(metadata, config.db)
        self.metadata = prev_meta 

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 test_column_adaptation(metadata):
    Table("simple_items", metadata, Column("id", postgresql.BIGINT), Column("length", postgresql.DOUBLE_PRECISION))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import BigInteger, Column, Float, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('id', BigInteger),
    Column('length', Float)
)
"""
    ) 

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 metadata_fixture(ddl="function"):
    """Provide MetaData for a pytest fixture."""

    from sqlalchemy.testing import config
    from . import fixture_functions

    def decorate(fn):
        def run_ddl(self):
            from sqlalchemy import schema

            metadata = self.metadata = schema.MetaData()
            try:
                result = fn(self, metadata)
                metadata.create_all(config.db)
                # TODO:
                # somehow get a per-function dml erase fixture here
                yield result
            finally:
                metadata.drop_all(config.db)

        return fixture_functions.fixture(scope=ddl)(run_ddl)

    return decorate 

def test_noindexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noindexes=True)
        == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer),
    CheckConstraint('number > 2')
)
"""
    ) 

def test_noconstraints_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noconstraints=True)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer, index=True)
)
"""
    ) 

def test_no_classes(metadata):
    Table("simple_items", metadata, Column("id", INTEGER, primary_key=True))

    assert (
        generate_code(metadata, noclasses=True)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('id', Integer, primary_key=True)
)
"""
    ) 

def test_schema_table(metadata):
    Table("simple_items", metadata, Column("name", VARCHAR), schema="testschema")

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('name', String),
    schema='testschema'
)
"""
    ) 

def test_schema_boolean(metadata):
    Table(
        "simple_items", metadata, Column("bool1", INTEGER), CheckConstraint("testschema.simple_items.bool1 IN (0, 1)"), schema="testschema"
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('bool1', Boolean),
    schema='testschema'
)
"""
    ) 

def provide_metadata(fn, *args, **kw):
    """Provide bound MetaData for a single test, dropping afterwards."""

    from . import config
    from . import engines
    from sqlalchemy import schema

    metadata = schema.MetaData(config.db)
    self = args[0]
    prev_meta = getattr(self, 'metadata', None)
    self.metadata = metadata
    try:
        return fn(*args, **kw)
    finally:
        engines.drop_all_tables(metadata, config.db)
        self.metadata = prev_meta 

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 test_foreign_key_options(metadata):
    Table(
        "simple_items",
        metadata,
        Column(
            "name", VARCHAR, ForeignKey("simple_items.name", ondelete="CASCADE", onupdate="CASCADE", deferrable=True, initially="DEFERRED")
        ),
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('name', String, ForeignKey('simple_items.name', ondelete='CASCADE', \
onupdate='CASCADE', deferrable=True, initially='DEFERRED'))
)
"""
    ) 

def setUp(self):
        super(RetainSchemaTest, self).setUp()

        metadata = schema.MetaData()
        self.test_table = schema.Table(
            'test_table', metadata,
            schema.Column('x', types.Integer),
            schema.Column('y', types.Integer),
            mysql_engine='InnoDB'
        )

        def gen_schema(engine):
            metadata.create_all(engine, checkfirst=False)
        self._gen_schema = gen_schema 

def drop_all_objects(self, engine):
        """Drop all database objects.

        Drops all database objects remaining on the default schema of the
        given engine.

        Per-db implementations will also need to drop items specific to those
        systems, such as sequences, custom types (e.g. pg ENUM), etc.

        """

        with engine.begin() as conn:
            inspector = sqlalchemy.inspect(engine)
            metadata = schema.MetaData()
            tbs = []
            all_fks = []

            for table_name in inspector.get_table_names():
                fks = []
                for fk in inspector.get_foreign_keys(table_name):
                    # note that SQLite reflection does not have names
                    # for foreign keys until SQLAlchemy 1.0
                    if not fk['name']:
                        continue
                    fks.append(
                        schema.ForeignKeyConstraint((), (), name=fk['name'])
                        )
                table = schema.Table(table_name, metadata, *fks)
                tbs.append(table)
                all_fks.extend(fks)

            if self.supports_drop_fk:
                for fkc in all_fks:
                    conn.execute(schema.DropConstraint(fkc))

            for table in tbs:
                conn.execute(schema.DropTable(table))

            self.drop_additional_objects(conn) 

def setUp(self):
        super(DropAllObjectsTest, self).setUp()

        self.metadata = metadata = schema.MetaData()
        schema.Table(
            'a', metadata,
            schema.Column('id', types.Integer, primary_key=True),
            mysql_engine='InnoDB'
        )
        schema.Table(
            'b', metadata,
            schema.Column('id', types.Integer, primary_key=True),
            schema.Column('a_id', types.Integer, schema.ForeignKey('a.id')),
            mysql_engine='InnoDB'
        )
        schema.Table(
            'c', metadata,
            schema.Column('id', types.Integer, primary_key=True),
            schema.Column('b_id', types.Integer, schema.ForeignKey('b.id')),
            schema.Column(
                'd_id', types.Integer,
                schema.ForeignKey('d.id', use_alter=True, name='c_d_fk')),
            mysql_engine='InnoDB'
        )
        schema.Table(
            'd', metadata,
            schema.Column('id', types.Integer, primary_key=True),
            schema.Column('c_id', types.Integer, schema.ForeignKey('c.id')),
            mysql_engine='InnoDB'
        )

        metadata.create_all(self.engine, checkfirst=False)
        # will drop nothing if the test worked
        self.addCleanup(metadata.drop_all, self.engine, checkfirst=True) 

def get_tables(self, source_uri, target, engine):
        """Create tables given a :class:`sqlalchemy.engine.base.Engine`

        Parameters
        -----------
        source_uri : str
            Source table URI to run queries against.
        target : :class:`pandas.DataFrame` or str
            Target table to add features to. If a string, must point to
            a table location found in the database.
        engine : :class:`sqlalchemy.engine.base.Engine`
            Engine with the database dialect

        Returns
        -------
        (:class:`sqlalchemy.schema.Table`, :class:`sqlalchemy.schema.Table`)
            Source and Target table
        """
        if isinstance(target, str):
            target_uri = target
        else:
            # Load the dataframe to database and get its URI
            target_uri = self.load(
                df=target, **self._inspect_options(self.options)
            )
        # Create SQLAlchemy primitives
        metadata = MetaData(bind=engine)
        source_table = Table(source_uri, metadata, autoload=True)
        target_table = Table(target_uri, metadata, autoload=True)
        return source_table, target_table 

def metadata(self):
        kw = {}
        if (
            self.migration_context is not None
            and "target_metadata" in self.migration_context.opts
        ):
            mt = self.migration_context.opts["target_metadata"]
            if hasattr(mt, "naming_convention"):
                kw["naming_convention"] = mt.naming_convention
        return sa_schema.MetaData(**kw) 

def __init__(self, engine, schema=None, meta=None):
        self.connectable = engine
        if not meta:
            from sqlalchemy.schema import MetaData
            meta = MetaData(self.connectable, schema=schema)

        self.meta = meta 

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 _execute_create(self):
        # Inserting table into database, add to MetaData object
        self.table = self.table.tometadata(self.pd_sql.meta)
        self.table.create() 

def _patch_metadata():
    naming_convention = {
        'fk': ('fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s'
                '_%(referred_column_0_name)s'),
        'pk': 'pk_%(table_name)s',
        'ix': 'ix_%(table_name)s_%(column_0_name)s',
        'ck': 'ck_%(table_name)s_%(constraint_name)s',
        'uq': 'uq_%(table_name)s_%(column_0_name)s',
    }
    metadata = MetaData(naming_convention=naming_convention)
    base = declarative_base(cls=flask_sqlalchemy.Model, name='Model',
                            metaclass=flask_sqlalchemy._BoundDeclarativeMeta,
                            metadata=metadata)
    base.query = flask_sqlalchemy._QueryProperty(db)
    db.Model = base 

def _sql_backend(request, engine, **kwargs):

        meta = MetaData(engine)
        meta.reflect()
        meta.drop_all()
        # we enable foreign key checks for SQLITE
        if str(engine.url).startswith('sqlite://'):
            engine.connect().execute('pragma foreign_keys=ON')

        if not 'ondelete' in kwargs:
            kwargs['ondelete'] = 'CASCADE'
        backend = SqlBackend(engine=engine, **kwargs)
        backend.init_schema()
        backend.create_schema()

        def finalizer():
            backend.rollback()
            del backend.connection
            print("Dropping schema...")
            # we disable foreign key checks for SQLITE (as dropping tables with circular foreign keys won't work otherwise...)
            if str(engine.url).startswith('sqlite://'):
                engine.connect().execute('pragma foreign_keys=OFF')
            meta = MetaData(engine)
            meta.reflect()
            meta.drop_all()
            print("Done...")

        request.addfinalizer(finalizer)

        return backend 

def dump_db_schema(db_url, dst_file):
    engine = sqlalchemy.create_engine(db_url)
    created_tables_metadata = MetaData(bind=engine)
    created_tables_metadata.reflect()
    # Write out table schema as described in
    # https://docs.sqlalchemy.org/en/13/faq/
    # metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string
    schema = "".join([str(CreateTable(ti)) for ti in created_tables_metadata.sorted_tables])
    print("Writing database schema to %s" % dst_file)
    with open(dst_file, "w") as handle:
        handle.write(schema)