Python sqlalchemy.schema.CreateTable() Examples

def test_default_reflection_3(self):
        db = testing.db
        table = """CREATE TABLE r_defaults (
            data VARCHAR(40) DEFAULT 'my_default',
            val INTEGER NOT NULL DEFAULT 0
            )"""
        try:
            exec_sql(db, table)
            m1 = MetaData(db)
            t1 = Table("r_defaults", m1, autoload=True)
            exec_sql(db, "DROP TABLE r_defaults")
            t1.create()
            m2 = MetaData(db)
            t2 = Table("r_defaults", m2, autoload=True)
            self.assert_compile(
                CreateTable(t2),
                "CREATE TABLE r_defaults (data VARCHAR(40) "
                "DEFAULT 'my_default', val INTEGER DEFAULT 0 "
                "NOT NULL)",
            )
        finally:
            exec_sql(db, "DROP TABLE r_defaults") 

def test_on_conflict_clause_check_constraint(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column("id", Integer),
            Column("x", Integer),
            CheckConstraint("id > x", sqlite_on_conflict="FAIL"),
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE n (id INTEGER, x INTEGER, "
            "CHECK (id > x) ON CONFLICT FAIL)",
            dialect=sqlite.dialect(),
        ) 

def test_create_table_simple(self, compiler):

        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String))

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_table_with_identity(self, compiler):

        table = Table(
            't1',
            MetaData(),
            Column('id', Integer, primary_key=True, redshift_identity=[1, 2]),
            Column('name', String),
        )

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER IDENTITY(1,2) NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_table_with_diststyle(self, compiler):

        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_diststyle="EVEN")

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"DISTSTYLE EVEN\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_on_conflict_clause_primary_key_constraint(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column("id", Integer),
            Column("x", String(30)),
            PrimaryKeyConstraint("id", "x", sqlite_on_conflict="FAIL"),
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE n ("
            "id INTEGER NOT NULL, "
            "x VARCHAR(30) NOT NULL, "
            "PRIMARY KEY (id, x) ON CONFLICT FAIL)",
            dialect=sqlite.dialect(),
        ) 

def print_create_table(tables):
    app.config.from_object('config.default')
    database.init_app(app)

    engine = database.session.get_bind()

    for class_name in tables:
        cls = get_class(class_name)

        for c in cls.__table__.columns:
            if not isinstance(c.type, Enum):
                continue
            t = c.type
            sql = str(CreateEnumType(t).compile(engine))
            click.echo(sql.strip() + ';')

        for index in cls.__table__.indexes:
            sql = str(CreateIndex(index).compile(engine))
            click.echo(sql.strip() + ';')

        sql = str(CreateTable(cls.__table__).compile(engine))
        click.echo(sql.strip() + ';') 

def test_create_table_with_distkey(self, compiler):

        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_distkey="id")

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"DISTKEY (id)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_sqlite_autoincrement_constraint(self):
        table = Table(
            "autoinctable",
            MetaData(),
            Column("id", Integer, primary_key=True),
            Column("x", Integer, default=None),
            UniqueConstraint("x"),
            sqlite_autoincrement=True,
        )
        self.assert_compile(
            schema.CreateTable(table),
            "CREATE TABLE autoinctable (id INTEGER NOT "
            "NULL PRIMARY KEY AUTOINCREMENT, x "
            "INTEGER, UNIQUE (x))",
            dialect=sqlite.dialect(),
        ) 

def create_table(request, sa_table, database, loop, create_entries):
    async def f(rows):
        create_expr = CreateTable(sa_table)
        async with database.acquire() as conn:
            await conn.execute(create_expr)
            values = create_entries(rows)
            query1 = sa_table.insert().values(values)
            await conn.execute(query1)
            await conn.execute('commit;')
        return sa_table

    yield f

    async def fin():
        drop_expr = DropTable(sa_table)
        async with database.acquire() as conn:
            await conn.execute(drop_expr)
            await conn.execute('commit;')

    loop.run_until_complete(fin()) 

def test_sqlite_autoincrement_int_affinity(self):
        class MyInteger(sqltypes.TypeDecorator):
            impl = Integer

        table = Table(
            "autoinctable",
            MetaData(),
            Column("id", MyInteger, primary_key=True),
            sqlite_autoincrement=True,
        )
        self.assert_compile(
            schema.CreateTable(table),
            "CREATE TABLE autoinctable (id INTEGER NOT "
            "NULL PRIMARY KEY AUTOINCREMENT)",
            dialect=sqlite.dialect(),
        ) 

def test_create_table_compress(self):
        m = MetaData()
        tbl1 = Table(
            "testtbl1", m, Column("data", Integer), oracle_compress=True
        )
        tbl2 = Table(
            "testtbl2", m, Column("data", Integer), oracle_compress="OLTP"
        )

        self.assert_compile(
            schema.CreateTable(tbl1),
            "CREATE TABLE testtbl1 (data INTEGER) COMPRESS",
        )
        self.assert_compile(
            schema.CreateTable(tbl2),
            "CREATE TABLE testtbl2 (data INTEGER) " "COMPRESS FOR OLTP",
        ) 

def test_table_options(self):
        m = MetaData()

        t = Table(
            "foo",
            m,
            Column("x", Integer),
            prefixes=["GLOBAL TEMPORARY"],
            oracle_on_commit="PRESERVE ROWS",
        )

        self.assert_compile(
            schema.CreateTable(t),
            "CREATE GLOBAL TEMPORARY TABLE "
            "foo (x INTEGER) ON COMMIT PRESERVE ROWS",
        ) 

def create_table(self, table):
        table.dispatch.before_create(
            table, self.connection, checkfirst=False, _ddl_runner=self
        )
        self._exec(schema.CreateTable(table))
        table.dispatch.after_create(
            table, self.connection, checkfirst=False, _ddl_runner=self
        )
        for index in table.indexes:
            self._exec(schema.CreateIndex(index))

        with_comment = (
            sqla_compat._dialect_supports_comments(self.dialect)
            and not self.dialect.inline_comments
        )
        comment = sqla_compat._comment_attribute(table)
        if comment and with_comment:
            self.create_table_comment(table)

        for column in table.columns:
            comment = sqla_compat._comment_attribute(column)
            if comment and with_comment:
                self.create_column_comment(column) 

def test_create_table_with_sortkey(self, compiler):

        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_sortkey="id")

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"SORTKEY (id)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_table_with_multiple_sortkeys(self, compiler):

        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_sortkey=["id", "name"])

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"SORTKEY (id, name)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_table_all_together(self, compiler):
        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_diststyle="KEY",
                      redshift_distkey="id",
                      redshift_sortkey=["id", "name"])

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"DISTSTYLE KEY DISTKEY (id) SORTKEY (id, name)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_column_with_distkey(self, compiler):
        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True,
                             redshift_distkey=True),
                      Column('name', String)
                      )

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER DISTKEY NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_create_column_with_encoding(self, compiler):
        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True,
                             redshift_encode="LZO"),
                      Column('name', String)
                      )

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER ENCODE LZO NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_on_conflict_clause_primary_key_constraint_from_column(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column(
                "x",
                String(30),
                sqlite_on_conflict_primary_key="FAIL",
                primary_key=True,
            ),
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE n (x VARCHAR(30) NOT NULL, "
            "PRIMARY KEY (x) ON CONFLICT FAIL)",
            dialect=sqlite.dialect(),
        ) 

def test_money(self):
        """Exercise type specification for money types."""

        columns = [
            (mssql.MSMoney, [], {}, "MONEY"),
            (mssql.MSSmallMoney, [], {}, "SMALLMONEY"),
        ]
        metadata = MetaData()
        table_args = ["test_mssql_money", metadata]
        for index, spec in enumerate(columns):
            type_, args, kw, res = spec
            table_args.append(
                Column("c%s" % index, type_(*args, **kw), nullable=None)
            )
        money_table = Table(*table_args)
        dialect = mssql.dialect()
        gen = dialect.ddl_compiler(dialect, schema.CreateTable(money_table))
        for col in money_table.c:
            index = int(col.name[1:])
            testing.eq_(
                gen.get_column_specification(col),
                "%s %s" % (col.name, columns[index][3]),
            )
            self.assert_(repr(col)) 

def test_on_conflict_clause_primary_key(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column(
                "id",
                Integer,
                primary_key=True,
                sqlite_on_conflict_primary_key="FAIL",
            ),
            sqlite_autoincrement=True,
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE n (id INTEGER NOT NULL "
            "PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT)",
            dialect=sqlite.dialect(),
        ) 

def test_on_conflict_clause_unique_constraint(self):

        meta = MetaData()
        t = Table(
            "n",
            meta,
            Column("id", Integer),
            Column("x", String(30)),
            UniqueConstraint("id", "x", sqlite_on_conflict="FAIL"),
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE n (id INTEGER, x VARCHAR(30), "
            "UNIQUE (id, x) ON CONFLICT FAIL)",
            dialect=sqlite.dialect(),
        ) 

def test_create_table_with_schema_type_schema_translate(self):
        e1 = Enum("x", "y", "z", name="somename")
        e2 = Enum("x", "y", "z", name="somename", schema="someschema")
        schema_translate_map = {None: "foo", "someschema": "bar"}

        table = Table(
            "some_table", MetaData(), Column("q", e1), Column("p", e2)
        )
        from sqlalchemy.schema import CreateTable

        self.assert_compile(
            CreateTable(table),
            "CREATE TABLE foo.some_table (q foo.somename, p bar.somename)",
            schema_translate_map=schema_translate_map,
            render_schema_translate=True,
        ) 

def test_exclude_constraint_copy_where_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("room", Integer, primary_key=True))
        cons = ExcludeConstraint(
            (tbl.c.room, "="), where=tbl.c.room > 5, using="foobar"
        )
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING foobar "
            "(room WITH =) WHERE (testtbl.room > 5)",
        )

        m2 = MetaData()
        tbl2 = tbl.to_metadata(m2)
        self.assert_compile(
            schema.CreateTable(tbl2),
            "CREATE TABLE testtbl (room SERIAL NOT NULL, "
            "PRIMARY KEY (room), "
            "EXCLUDE USING foobar "
            "(room WITH =) WHERE (testtbl.room > 5))",
        ) 

def test_create_table_with_oids(self):
        m = MetaData()
        tbl = Table(
            "atable", m, Column("id", Integer), postgresql_with_oids=True
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE atable (id INTEGER) WITH OIDS",
        )

        tbl2 = Table(
            "anothertable",
            m,
            Column("id", Integer),
            postgresql_with_oids=False,
        )
        self.assert_compile(
            schema.CreateTable(tbl2),
            "CREATE TABLE anothertable (id INTEGER) WITHOUT OIDS",
        ) 

def test_sqlalchemy_compilation():
    engine = create_engine('impala://localhost')
    metadata = MetaData(engine)
    # TODO: add other types to this table (e.g., functional.all_types)
    mytable = Table("mytable",
                    metadata,
                    Column('col1', STRING),
                    Column('col2', TINYINT),
                    Column('col3', INT),
                    Column('col4', DOUBLE),
                    impala_partition_by='HASH PARTITIONS 16',
                    impala_stored_as='KUDU',
                    impala_table_properties={
                        'kudu.table_name': 'my_kudu_table',
                        'kudu.master_addresses': 'kudu-master.example.com:7051'
                    })
    observed = str(CreateTable(mytable, bind=engine))
    expected = ('\nCREATE TABLE mytable (\n\tcol1 STRING, \n\tcol2 TINYINT, '
                '\n\tcol3 INT, \n\tcol4 DOUBLE\n)'
                '\nPARTITION BY HASH PARTITIONS 16\nSTORED AS KUDU\n'
                "TBLPROPERTIES ('kudu.table_name' = 'my_kudu_table', "
                "'kudu.master_addresses' = 'kudu-master.example.com:7051')\n\n")
    assert expected == observed 

def test_create_table_with_unicode_sortkey(self, compiler):
        table = Table('t1',
                      MetaData(),
                      Column('id', Integer, primary_key=True),
                      Column('name', String),
                      redshift_sortkey=u"id")

        create_table = CreateTable(table)
        actual = compiler.process(create_table)
        expected = (
            u"\nCREATE TABLE t1 ("
            u"\n\tid INTEGER NOT NULL, "
            u"\n\tname VARCHAR, "
            u"\n\tPRIMARY KEY (id)\n) "
            u"SORTKEY (id)\n\n"
        )
        assert expected == actual, self._compare_strings(expected, actual) 

def test_column_computed_persisted_false(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column("x", Integer),
            Column("y", Integer, Computed("x + 2", persisted=False)),
        )
        assert_raises_message(
            exc.CompileError,
            "PostrgreSQL computed columns do not support 'virtual'",
            schema.CreateTable(t).compile,
            dialect=postgresql.dialect(),
        ) 

def test_create_table_with_tablespace_quoted(self):
        # testing quoting of tablespace name
        m = MetaData()
        tbl = Table(
            "anothertable",
            m,
            Column("id", Integer),
            postgresql_tablespace="table",
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            'CREATE TABLE anothertable (id INTEGER) TABLESPACE "table"',
        )