Python sqlalchemy.schema.Column() Examples

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 spaxel_factory(classname, clean=None):
    ''' class factory for the spaxelprop tables '''

    if clean:
        classname = 'Clean{0}'.format(classname)
    tablename = classname.lower()

    params = {'__tablename__': tablename, '__table_args__': {'autoload': True, 'schema': 'mangadapdb'}}
    if clean:
        params.update({'pk': Column(Integer, primary_key=True)})

    def newrepr(self):
        return '<{2} (pk={0}, file={1})'.format(self.pk, self.file_pk, classname)

    try:
        newclass = type(classname, (Base, SpaxelAtts,), params)
        newclass.__repr__ = newrepr
    except Exception as e:
        newclass = None

    return newclass


# create the (clean)spaxel models from the DAP datamodel 

def matchIndex(self, name=None):

        # Get index of correct column
        incols = [x for x in self.cols if x in self.collist]
        if not any(incols):
            return None
        elif len(incols) == 1:
            idx = self.cols.index(incols[0])
        else:
            if not name:
                print('Multiple columns found.  Column name must be specified!')
                return None
            elif name in self.collist:
                idx = self.cols.index(name)
            else:
                return None

        return idx 

def create_column(self, cname, remote_type, foreign_key, properties):

        def wrapper(cls):
            return SA_Column(
                cname.attribute_name,
                remote_type,
                nullable=self.nullable,
                unique=self.unique,
                index=self.index,
                primary_key=self.primary_key,
                info=dict(label=self.label, foreign_key=foreign_key))

        properties[(anyblok_column_prefix +
                    cname.attribute_name)] = declared_attr(wrapper)
        properties['loaded_columns'].append(cname.attribute_name)
        properties['hybrid_property_columns'].append(cname.attribute_name)
        properties[cname.attribute_name] = hybrid_property(
            self.wrap_getter_column(cname.attribute_name),
            super(Many2One, self).wrap_setter_column(cname.attribute_name),
            expr=self.wrap_expr_column(cname.attribute_name)) 

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 _attr_name_from_column_obj(self, column_obj):
        # type: (Column) -> String
        prop = self._mapper.get_property_by_column(column_obj)
        assert isinstance(prop, MapperProperty) and hasattr(prop, 'key')
        attr_name = prop.key
        if attr_name != column_obj.name:
            raise NotImplementedError(
                'The property key (model attribute name) {!r} is not equal '
                'to the corresponding database column name {!r}. SQLAlchemy '
                'supports such cases but n6\'s Auth DB and Audit Log do not. '
                'If you insist that such cases should be supported, you need '
                'to implement all needed stuff (in particular, to make Audit '
                'Log messages include information that attribute x refers '
                'to column y...). But do we really need that?! Let\'s keep '
                'things simple!'.format(attr_name, column_obj.name))
        return attr_name 

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_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_timestamp(metadata):
    Table("simple", metadata, Column("id", INTEGER, primary_key=True), Column("timestamp", mysql.TIMESTAMP))

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

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
    __tablename__ = 'simple'

    id = Column(Integer, primary_key=True)
    timestamp = Column(TIMESTAMP)
"""
    ) 

def test_pascal_multiple_underscore(metadata):
    Table("customer_API__Preference", metadata, Column("id", INTEGER, primary_key=True))

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

Base = declarative_base()
metadata = Base.metadata


class CustomerAPIPreference(Base):
    __tablename__ = 'customer_API__Preference'

    id = Column(Integer, primary_key=True)
"""
    ) 

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 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_table_kwargs(metadata):
    Table("simple_items", metadata, Column("id", INTEGER, primary_key=True), schema="testschema")

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

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'
    __table_args__ = {'schema': 'testschema'}

    id = Column(Integer, primary_key=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_pk_default(metadata):
    Table("simple_items", metadata, Column("id", INTEGER, primary_key=True, server_default=text("uuid_generate_v4()")))

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

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True, server_default=text("uuid_generate_v4()"))
"""
    ) 

def test_server_default_double_quotes(metadata):
    Table("simple", metadata, Column("id", INTEGER, primary_key=True, server_default=text('nextval("foo")')))

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

Base = declarative_base()
metadata = Base.metadata


class Simple(Base):
    __tablename__ = 'simple'

    id = Column(Integer, primary_key=True, server_default=text("nextval(\\"foo\\")"))
"""
    ) 

def test_underscore(metadata):
    Table("customer_api_preference", metadata, Column("id", INTEGER, primary_key=True))

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

Base = declarative_base()
metadata = Base.metadata


class CustomerApiPreference(Base):
    __tablename__ = 'customer_api_preference'

    id = Column(Integer, primary_key=True)
"""
    ) 

def test_pascal_underscore(metadata):
    Table("customer_API_Preference", metadata, Column("id", INTEGER, primary_key=True))

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

Base = declarative_base()
metadata = Base.metadata


class CustomerAPIPreference(Base):
    __tablename__ = 'customer_API_Preference'

    id = Column(Integer, primary_key=True)
"""
    ) 

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 test_server_default_multiline(metadata):
    Table(
        "simple_items",
        metadata,
        Column(
            "id",
            INTEGER,
            primary_key=True,
            server_default=text(
                """\
/*Comment*/
/*Next line*/
something()"""
            ),
        ),
    )

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

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True, server_default=text(\"""\\
/*Comment*/
/*Next line*/
something()\"""))
"""
    ) 

def _normalize_columns(self, cols, table_name=False):
        """Given: column objects or names; return col names and
        (maybe) a table"""
        colnames = []
        table = None
        for col in cols:
            if isinstance(col, schema.Column):
                if col.table is not None and table is None:
                    table = col.table
                if table_name:
                    col = '.'.join((col.table.name, col.name))
                else:
                    col = col.name
            colnames.append(col)
        return colnames, table 

def test_invalid_attribute_names(metadata):
    Table(
        "simple-items",
        metadata,
        Column("id-test", INTEGER, primary_key=True),
        Column("4test", INTEGER),
        Column("_4test", INTEGER),
        Column("def", INTEGER),
    )

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

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple-items'

    id_test = Column('id-test', Integer, primary_key=True)
    _4test = Column('4test', Integer)
    _4test1 = Column('_4test', Integer)
    _def = Column('def', Integer)
"""
    ) 

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 test_contains_override_raises(self):
        for col in [
            Column("x", String),
            Column("x", Integer),
            Column("x", DateTime),
        ]:
            assert_raises_message(
                NotImplementedError,
                "Operator 'contains' is not supported on this expression",
                lambda: "foo" in col,
            ) 

def test_column_proxy(self):
        t = Table("t", MetaData(), Column("foo", self._add_override_factory()))
        with testing.expect_deprecated(
            "The SelectBase.c and SelectBase.columns attributes "
            "are deprecated"
        ):
            proxied = t.select().c.foo
        self._assert_add_override(proxied)
        self._assert_and_override(proxied) 

def test_override_builtin(self):
        c1 = Column("foo", self._add_override_factory())
        self._assert_add_override(c1) 

def test_foreign_key_schema(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("other_item_id", INTEGER),
        ForeignKeyConstraint(["other_item_id"], ["otherschema.other_items.id"]),
    )
    Table("other_items", metadata, Column("id", INTEGER, primary_key=True), schema="otherschema")

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

Base = declarative_base()
metadata = Base.metadata


class OtherItem(Base):
    __tablename__ = 'other_items'
    __table_args__ = {'schema': 'otherschema'}

    id = Column(Integer, primary_key=True)


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    other_item_id = Column(ForeignKey('otherschema.other_items.id'))

    other_item = relationship('OtherItem')
"""
    ) 

def table(self):
        """ Generate an appropriate table representation to mirror the
        fields known for this table. """
        if self._table is None:
            self._table = Table(self.table_name, self.meta)
            id_col = Column('_id', Unicode(42), primary_key=True)
            self._table.append_column(id_col)
            json_col = Column('_json', Unicode())
            self._table.append_column(json_col)
            self._fields_columns(self._table)
        return self._table 

def create_sa_proxies(self):

        # create the table and mapper
        metadata = schema.MetaData()
        user_table = schema.Table(
            'user',
            metadata,
            schema.Column('id', types.Integer, primary_key=True),
            schema.Column('first_name', types.Unicode(25)),
            schema.Column('last_name', types.Unicode(25))
        )

        class User(object):
            pass
        orm.mapper(User, user_table)

        # create the session
        engine = create_engine('sqlite:///:memory:')
        metadata.bind = engine
        metadata.create_all()
        session = orm.sessionmaker(bind=engine)()

        # add some dummy data
        user_table.insert().execute([
            {'first_name': 'Jonathan', 'last_name': 'LaCour'},
            {'first_name': 'Yoann', 'last_name': 'Roman'}
        ])

        # get the SA objects
        self.sa_object = session.query(User).first()
        select = user_table.select()
        self.result_proxy = select.execute()
        self.row_proxy = select.execute().fetchone()