Python sqlalchemy.dialects.mysql.VARCHAR Examples

def test_metadata_column(metadata):
    Table("simple", metadata, Column("id", INTEGER, primary_key=True), Column("metadata", VARCHAR))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
    __tablename__ = 'simple'

    id = Column(Integer, primary_key=True)
    metadata_ = Column('metadata', String)
"""
    ) 

def downgrade(op, tables, tester):
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f("fk_messages_media_type_id_mediatype"), "messages", type_="foreignkey")
    op.drop_index("messages_uuid", table_name="messages")
    op.drop_index("messages_severity", table_name="messages")
    op.drop_index("messages_media_type_id", table_name="messages")
    op.alter_column("messages", "uuid", existing_type=mysql.VARCHAR(length=36), nullable=True)
    op.drop_column("messages", "severity")
    op.drop_column("messages", "media_type_id")
    # ### end Alembic commands ###

    op.execute(
        tables.mediatype.delete().where(
            tables.mediatype.c.name == op.inline_literal("text/markdown")
        )
    ) 

def test_column_datatype_to_string(self):
        test_cases = (
            (DATE(), "DATE"),
            (VARCHAR(length=255), "VARCHAR(255)"),
            (
                VARCHAR(length=255, charset="latin1", collation="utf8mb4_general_ci"),
                "VARCHAR(255)",
            ),
            (NVARCHAR(length=128), "NATIONAL VARCHAR(128)"),
            (TEXT(), "TEXT"),
        )

        for original, expected in test_cases:
            actual = MySQLEngineSpec.column_datatype_to_string(
                original, mysql.dialect()
            )
            self.assertEqual(actual, expected) 

def upgrade():
    with op.batch_alter_table('capsule', schema=None) as batch_op:
        batch_op.create_unique_constraint('uniq_capsule0uuid', ['uuid'])
        batch_op.drop_column('message')

    with op.batch_alter_table('container_actions', schema=None) as batch_op:
        batch_op.create_foreign_key(
            None, 'container', ['container_uuid'], ['uuid'])

    with op.batch_alter_table('pci_device', schema=None) as batch_op:
        batch_op.create_foreign_key(
            None, 'compute_node', ['compute_node_uuid'], ['uuid'])

    with op.batch_alter_table('volume_mapping', schema=None) as batch_op:
        batch_op.alter_column('container_uuid',
                              existing_type=mysql.VARCHAR(length=36),
                              nullable=True)
        batch_op.create_foreign_key(
            None, 'container', ['container_uuid'], ['uuid']) 

def test_render_variant(self):
        from sqlalchemy import VARCHAR, CHAR

        self.autogen_context.opts["user_module_prefix"] = None

        type_ = (
            String(5)
            .with_variant(VARCHAR(10), "mysql")
            .with_variant(CHAR(15), "oracle")
        )

        # the new Black formatting will help a lot with this
        eq_ignore_whitespace(
            autogenerate.render._repr_type(type_, self.autogen_context),
            "sa.String(length=5)."
            "with_variant(sa.VARCHAR(length=10), 'mysql')."
            "with_variant(sa.CHAR(length=15), 'oracle')",
        ) 

def test_render_add_index_cast(self):
        m = MetaData()
        t = Table(
            "test",
            m,
            Column("id", Integer, primary_key=True),
            Column("code", String(255)),
        )
        idx = Index("test_lower_code_idx", cast(t.c.code, String))
        op_obj = ops.CreateIndexOp.from_index(idx)

        eq_ignore_whitespace(
            autogenerate.render_op_text(self.autogen_context, op_obj),
            "op.create_index('test_lower_code_idx', 'test', "
            "[sa.text(!U'CAST(code AS VARCHAR)')], unique=False)",
        ) 

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 upgrade():
    op.create_table(
        'gmailauthcredentials',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.Column('deleted_at', sa.DateTime(), nullable=True),
        sa.Column('gmailaccount_id', sa.Integer(), nullable=False),
        sa.Column('refresh_token_id', sa.Integer(), nullable=False),
        sa.Column('scopes', mysql.VARCHAR(length=512), nullable=False),
        sa.Column('g_id_token', mysql.VARCHAR(length=1024), nullable=False),
        sa.Column('client_id', mysql.VARCHAR(length=256), nullable=False),
        sa.Column('client_secret', mysql.VARCHAR(length=256), nullable=False),
        sa.Column('is_valid', sa.Boolean(),
                  nullable=False, server_default=sa.sql.expression.true()),
        sa.ForeignKeyConstraint(
            ['gmailaccount_id'], [u'gmailaccount.id'], ondelete='CASCADE'
        ),
        sa.ForeignKeyConstraint(
            ['refresh_token_id'], [u'secret.id'], ondelete='CASCADE'
        ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('refresh_token_id'),
    ) 

def test_indexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("id", INTEGER), Column("number", INTEGER), Column("text", VARCHAR))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))
    simple_items.indexes.add(Index("idx_text_number", simple_items.c.text, simple_items.c.number, unique=True))
    simple_items.indexes.add(Index("idx_text", simple_items.c.text, unique=True))

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

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('id', Integer),
    Column('number', Integer, index=True),
    Column('text', String, unique=True),
    Index('idx_text_number', 'text', 'number', unique=True)
)
"""
    ) 

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 test_enum_detection(metadata):
    Table("simple_items", metadata, Column("enum", VARCHAR(255)), CheckConstraint(r"simple_items.enum IN ('A', '\'B', 'C')"))

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

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('enum', Enum('A', "\\\\'B", 'C'))
)
"""
    ) 

def downgrade():
    op.add_column(
        'accounts',
        sa.Column('re_fee', mysql.VARCHAR(length=254), nullable=True)
    )
    op.drop_column('accounts', 're_other_fee')
    op.drop_column('accounts', 're_late_fee')
    bind = op.get_bind()
    session = Session(bind=bind)
    for acct in session.query(Account).all():
        acct.re_interest_charge = '^(interest charge|purchase finance charge)'
        acct.re_interest_paid = '^interest paid'
        acct.re_payment = '^(online payment|' \
                          'internet payment|online pymt|payment)'
        acct.re_fee = '^(late fee|past due fee)'
    session.commit() 

def upgrade():
    op.create_table(
        'phonenumber',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.Column('deleted_at', sa.DateTime(), nullable=True),
        sa.Column('contact_id', sa.Integer(), nullable=False),
        sa.Column('type', mysql.VARCHAR(length=64), nullable=True),
        sa.Column('number', mysql.VARCHAR(length=64), nullable=False),
        sa.ForeignKeyConstraint(
            ['contact_id'], [u'contact.id'], ondelete='CASCADE'
        ),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_phonenumber_created_at',
                    'phonenumber', ['created_at'], unique=False)
    op.create_index('ix_phonenumber_updated_at',
                    'phonenumber', ['updated_at'], unique=False)
    op.create_index('ix_phonenumber_contact_id',
                    'phonenumber', ['contact_id'], unique=False)
    op.create_index('ix_phonenumber_deleted_at',
                    'phonenumber', ['deleted_at'], unique=False) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('schedules',
    sa.Column('created_at', mysql.DATETIME(), nullable=False),
    sa.Column('updated_at', mysql.DATETIME(), nullable=False),
    sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
    sa.Column('cron', mysql.VARCHAR(length=255), nullable=True),
    sa.Column('pipeline_id', mysql.INTEGER(display_width=11), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['pipeline_id'], [u'pipelines.id'], name=u'schedules_ibfk_1'),
    sa.PrimaryKeyConstraint('id'),
    mysql_default_charset=u'utf8',
    mysql_engine=u'InnoDB'
    )
    # ### end Alembic commands ### 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('match', 'veto_mappool',
               existing_type=sa.String(length=500),
               type_=mysql.VARCHAR(length=160),
               existing_nullable=True)
    ### end Alembic commands ### 

def with_variant(self, type_, dialect_name):
        r"""Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type\_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        """
        return Variant(self, {dialect_name: to_instance(type_)}) 

def test_ad_hoc_types(self):
        """test storage of bind processors, result processors
        in dialect-wide registry."""

        from sqlalchemy.dialects import mysql, postgresql, sqlite
        from sqlalchemy import types

        eng = engines.testing_engine()
        for args in (
            (types.Integer,),
            (types.String,),
            (types.PickleType,),
            (types.Enum, "a", "b", "c"),
            (sqlite.DATETIME,),
            (postgresql.ENUM, "a", "b", "c"),
            (types.Interval,),
            (postgresql.INTERVAL,),
            (mysql.VARCHAR,),
        ):

            @profile_memory()
            def go():
                type_ = args[0](*args[1:])
                bp = type_._cached_bind_processor(eng.dialect)
                rp = type_._cached_result_processor(eng.dialect, 0)
                bp, rp  # strong reference

            go()

        assert not eng.dialect._type_memos 

def test_get_datatype_mysql(self):
        """Tests related to datatype mapping for MySQL"""
        self.assertEqual("TINY", MySQLEngineSpec.get_datatype(1))
        self.assertEqual("VARCHAR", MySQLEngineSpec.get_datatype(15)) 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: to_instance(type_)}) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('general_settings',
                    sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
                    sa.Column('name', mysql.VARCHAR(length=255), nullable=True),
                    sa.Column('value', sa.Text(), nullable=True),
                    sa.Column('created_at', mysql.DATETIME(), nullable=False),
                    sa.Column('updated_at', mysql.DATETIME(), nullable=False),
                    sa.PrimaryKeyConstraint('id'),
                    mysql_default_charset=u'utf8',
                    mysql_engine=u'InnoDB'
                    )
    # ### end Alembic commands ### 

def upgrade():
    op.alter_column('data_account_index_audit', 'account_id',
               existing_type=mysql.VARCHAR(length=64),
               nullable=True) 

def downgrade():
    op.alter_column('data_account_index_audit', 'account_id',
               existing_type=mysql.VARCHAR(length=64),
               nullable=False) 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`~sqlalchemy.types.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: type_}) 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: to_instance(type_)}) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('match', 'veto_mappool',
               existing_type=mysql.VARCHAR(length=160),
               type_=sa.String(length=500),
               existing_nullable=True)
    ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('avatar', mysql.VARCHAR(length=128), nullable=True))
    # ### end Alembic commands ### 

def downgrade():
    op.add_column('account', sa.Column('email_address',
                                       mysql.VARCHAR(length=191),
                                       nullable=True))
    op.add_column('contact', sa.Column('email_address',
                                       mysql.VARCHAR(length=191),
                                       nullable=True))
    op.create_index('ix_account_email_address', 'account', ['email_address'],
                    unique=False)
    op.create_index('ix_contact_email_address', 'contact', ['email_address'],
                    unique=False)
    from inbox.ignition import main_engine
    engine = main_engine(pool_size=1, max_overflow=0)
    from inbox.models.session import session_scope
    from sqlalchemy.ext.declarative import declarative_base
    Base = declarative_base()
    Base.metadata.reflect(engine)

    class Account(Base):
        __table__ = Base.metadata.tables['account']

    class Contact(Base):
        __table__ = Base.metadata.tables['contact']

    with session_scope(versioned=False) \
            as db_session:
        for acct in db_session.query(Account):
            acct.email_address = acct._raw_address
        db_session.commit()
        for contact in db_session.query(Account):
            contact.email_address = contact._raw_address
        db_session.commit()

    op.drop_index('ix_account__raw_address', table_name='account')
    op.drop_index('ix_account__canonicalized_address', table_name='account')
    op.drop_column('account', '_raw_address')
    op.drop_column('account', '_canonicalized_address') 

def downgrade():
    op.alter_column('message', 'g_msgid', type_=mysql.VARCHAR(40))
    op.alter_column('message', 'g_thrid', type_=mysql.VARCHAR(40))

    op.drop_index('ix_message_g_thrid', table_name='message')
    op.drop_index('ix_message_g_msgid', table_name='message') 

def downgrade():
    op.alter_column('account', 'email_address', type_=mysql.VARCHAR(254))
    op.alter_column('contact', 'email_address', type_=mysql.VARCHAR(191)) 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: to_instance(type_)})