Python sqlalchemy.sql.schema.Table() Examples

def create_table(self, T_dst_exists, T):
        with self.dst_engine.connect() as conn:
            if not T_dst_exists:
                self.logger.info(" --> Creating table '{0}'".format(T.name))
                try:
                    T.create(conn)
                    return True
                except Exception as e:
                    self.logger.error(
                        "Failed to create table '{0}'\n\n{1}".format(
                            T.name, e))
                    raise
            else:
                self.logger.warning(
                    "Table '{0}' already exists - not creating table, " +
                    "reflecting to get new changes instead..".format(T.name))
                self.tgt_insp.reflecttable(T, None)
                return True
                # We need to Upsert the data... 

def check_multiple_autoincrement_issue(self, auto_inc_count, pk_count, T):
        if pk_count > 1:
            # Sometimes we can't detect the 'autoincrement' attr on columns
            # (For instance on SQL Server...)
            for c in T.columns:
                if c.primary_key:
                    c.autoincrement = False
            # and engine == MySQL.innoDB...
            if auto_inc_count > 0:
                # print the verbose warning
                self.logger.warning("""
                ****************************************************************
                **** Table '{0}' contains a composite primary key,
                **** with an auto-increment attribute tagged on 1 of the columns.
                *****************************************************************
                ********* --We are dropping the auto-increment field-- **********
                *****************************************************************
                ** (why? MySQL -> InnoDB Engine does not support this.
                ** Try MyISAM for support - understand that Oracle does not allow
                ** auto-increment fields, but uses sequences to create unique
                ** composite PKs")
                *****************************************************************
                """.format(T.name)) 

def transform_table(self, T):
        ################################
        # Run Table Transformations
        ################################
        """ This will update the table 'T' in-place
        (i.e. change the table's name)
        """
        if not self.schema_transformer.transform_table(T):
            self.logger.info(
                " ---> Table ({0}) is scheduled to be deleted " +
                "according to table transformations...".format(T.name))
            # Clean up FKs and Indexes on this table...
            del self.indexes[T.name]
            del self.fks[T.name]
            self.deleted_table_count += 1
            self.deleted_columns += map(lambda c: T.name +
                                       "." + c.name, T.columns)
            self.deleted_column_count += len(T.columns)
            return None
        return True 

def test_to_sql(self, engine, conn):
        table_name = "to_sql_{0}".format(str(uuid.uuid4()).replace("-", ""))
        df = pd.DataFrame({"a": [1, 2, 3, 4, 5]})
        df.to_sql(
            table_name,
            engine,
            schema=SCHEMA,
            index=False,
            if_exists="replace",
            # Supported by Pandas version 0.24.0 or later.
            # method="multi",
        )

        table = Table(table_name, MetaData(bind=engine), autoload=True)
        rows = table.select().execute().fetchall()
        self.assertEqual(sorted(rows), [(1,), (2,), (3,), (4,), (5,)]) 

def test_reflect_table_include_columns(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine))
        version = float(
            re.search(r"^([\d]+\.[\d]+)\..+", sqlalchemy.__version__).group(1)
        )
        if version <= 1.2:
            engine.dialect.reflecttable(
                conn, one_row_complex, include_columns=["col_int"], exclude_columns=[]
            )
        else:
            # https://docs.sqlalchemy.org/en/13/changelog/changelog_13.html#
            # change-64ac776996da1a5c3e3460b4c0f0b257
            engine.dialect.reflecttable(
                conn,
                one_row_complex,
                include_columns=["col_int"],
                exclude_columns=[],
                resolve_fks=True,
            )
        self.assertEqual(len(one_row_complex.c), 1)
        self.assertIsNotNone(one_row_complex.c.col_int)
        self.assertRaises(AttributeError, lambda: one_row_complex.c.col_tinyint) 

def test_reflect_table_include_columns(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine))
        version = float(
            re.search(r"^([\d]+\.[\d]+)\..+", sqlalchemy.__version__).group(1)
        )
        if version <= 1.2:
            engine.dialect.reflecttable(
                conn, one_row_complex, include_columns=["col_int"], exclude_columns=[],
            )
        else:
            engine.dialect.reflecttable(
                conn,
                one_row_complex,
                include_columns=["col_int"],
                exclude_columns=[],
                resolve_fks=True,
            )
        self.assertEqual(len(one_row_complex.c), 1)
        self.assertIsNotNone(one_row_complex.c.col_int)
        self.assertRaises(AttributeError, lambda: one_row_complex.c.col_tinyint) 

def _set_table(self, table: Column, column: Table) -> None:
        self.impl._set_table(table, column) 

def test_reserved_words(self, engine, conn):
        """Presto uses double quotes, not backticks"""
        fake_table = Table(
            "select", MetaData(bind=engine), Column("current_timestamp", STRINGTYPE)
        )
        query = str(fake_table.select(fake_table.c.current_timestamp == "a"))
        self.assertIn('"select"', query)
        self.assertIn('"current_timestamp"', query)
        self.assertNotIn("`select`", query)
        self.assertNotIn("`current_timestamp`", query) 

def test_reflect_select(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine), autoload=True)
        self.assertEqual(len(one_row_complex.c), 15)
        self.assertIsInstance(one_row_complex.c.col_string, Column)
        rows = one_row_complex.select().execute().fetchall()
        self.assertEqual(len(rows), 1)
        self.assertEqual(
            list(rows[0]),
            [
                True,
                127,
                32767,
                2147483647,
                9223372036854775807,
                0.5,
                0.25,
                "a string",
                datetime(2017, 1, 1, 0, 0, 0),
                date(2017, 1, 2),
                b"123",
                "1, 2",
                "{1=2, 3=4}",
                "{a=1, b=2}",
                Decimal("0.100000"),
            ],
        )
        self.assertIsInstance(one_row_complex.c.col_boolean.type, BOOLEAN)
        self.assertIsInstance(one_row_complex.c.col_tinyint.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_smallint.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_int.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_bigint.type, BIGINT)
        self.assertIsInstance(one_row_complex.c.col_float.type, FLOAT)
        self.assertIsInstance(one_row_complex.c.col_double.type, FLOAT)
        self.assertIsInstance(one_row_complex.c.col_string.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_timestamp.type, TIMESTAMP)
        self.assertIsInstance(one_row_complex.c.col_date.type, DATE)
        self.assertIsInstance(one_row_complex.c.col_binary.type, BINARY)
        self.assertIsInstance(one_row_complex.c.col_array.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_map.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_struct.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_decimal.type, DECIMAL) 

def test_char_length(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine), autoload=True)
        result = (
            sqlalchemy.select(
                [sqlalchemy.func.char_length(one_row_complex.c.col_string)]
            )
            .execute()
            .scalar()
        )
        self.assertEqual(result, len("a string")) 

def test_unicode(self, engine, conn):
        unicode_str = "密林"
        one_row = Table("one_row", MetaData(bind=engine))
        returned_str = sqlalchemy.select(
            [expression.bindparam("あまぞん", unicode_str)], from_obj=one_row,
        ).scalar()
        self.assertEqual(returned_str, unicode_str) 

def test_reflect_table_with_schema(self, engine, conn):
        one_row = Table("one_row", MetaData(bind=engine), schema=SCHEMA, autoload=True)
        self.assertEqual(len(one_row.c), 1)
        self.assertIsNotNone(one_row.c.number_of_rows) 

def test_reflect_table(self, engine, conn):
        one_row = Table("one_row", MetaData(bind=engine), autoload=True)
        self.assertEqual(len(one_row.c), 1)
        self.assertIsNotNone(one_row.c.number_of_rows) 

def test_reflect_no_such_table(self, engine, conn):
        self.assertRaises(
            NoSuchTableError,
            lambda: Table("this_does_not_exist", MetaData(bind=engine), autoload=True),
        )
        self.assertRaises(
            NoSuchTableError,
            lambda: Table(
                "this_does_not_exist",
                MetaData(bind=engine),
                schema="also_does_not_exist",
                autoload=True,
            ),
        ) 

def test_reserved_words(self, engine, conn):
        """Presto uses double quotes, not backticks"""
        fake_table = Table(
            "select", MetaData(bind=engine), Column("current_timestamp", STRINGTYPE)
        )
        query = str(fake_table.select(fake_table.c.current_timestamp == "a"))
        self.assertIn('"select"', query)
        self.assertIn('"current_timestamp"', query)
        self.assertNotIn("`select`", query)
        self.assertNotIn("`current_timestamp`", query) 

def test_reflect_select(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine), autoload=True)
        self.assertEqual(len(one_row_complex.c), 15)
        self.assertIsInstance(one_row_complex.c.col_string, Column)
        rows = one_row_complex.select().execute().fetchall()
        self.assertEqual(len(rows), 1)
        self.assertEqual(
            list(rows[0]),
            [
                True,
                127,
                32767,
                2147483647,
                9223372036854775807,
                0.5,
                0.25,
                "a string",
                datetime(2017, 1, 1, 0, 0, 0),
                date(2017, 1, 2),
                b"123",
                "[1, 2]",
                "{1=2, 3=4}",
                "{a=1, b=2}",
                Decimal("0.1"),
            ],
        )
        self.assertIsInstance(one_row_complex.c.col_boolean.type, BOOLEAN)
        self.assertIsInstance(one_row_complex.c.col_tinyint.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_smallint.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_int.type, INTEGER)
        self.assertIsInstance(one_row_complex.c.col_bigint.type, BIGINT)
        self.assertIsInstance(one_row_complex.c.col_float.type, FLOAT)
        self.assertIsInstance(one_row_complex.c.col_double.type, FLOAT)
        self.assertIsInstance(one_row_complex.c.col_string.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_timestamp.type, TIMESTAMP)
        self.assertIsInstance(one_row_complex.c.col_date.type, DATE)
        self.assertIsInstance(one_row_complex.c.col_binary.type, BINARY)
        self.assertIsInstance(one_row_complex.c.col_array.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_map.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_struct.type, type(STRINGTYPE))
        self.assertIsInstance(one_row_complex.c.col_decimal.type, DECIMAL) 

def test_char_length(self, engine, conn):
        one_row_complex = Table("one_row_complex", MetaData(bind=engine), autoload=True)
        result = (
            sqlalchemy.select(
                [sqlalchemy.func.char_length(one_row_complex.c.col_string)]
            )
            .execute()
            .scalar()
        )
        self.assertEqual(result, len("a string")) 

def test_unicode(self, engine, conn):
        unicode_str = "密林"
        one_row = Table("one_row", MetaData(bind=engine))
        returned_str = sqlalchemy.select(
            [expression.bindparam("あまぞん", unicode_str)], from_obj=one_row,
        ).scalar()
        self.assertEqual(returned_str, unicode_str) 

def test_reflect_table_with_schema(self, engine, conn):
        one_row = Table("one_row", MetaData(bind=engine), schema=SCHEMA, autoload=True)
        self.assertEqual(len(one_row.c), 1)
        self.assertIsNotNone(one_row.c.number_of_rows) 

def test_reflect_table(self, engine, conn):
        one_row = Table("one_row", MetaData(bind=engine), autoload=True)
        self.assertEqual(len(one_row.c), 1)
        self.assertIsNotNone(one_row.c.number_of_rows) 

def test_reflect_no_such_table(self, engine, conn):
        self.assertRaises(
            NoSuchTableError,
            lambda: Table("this_does_not_exist", MetaData(bind=engine), autoload=True),
        )
        self.assertRaises(
            NoSuchTableError,
            lambda: Table(
                "this_does_not_exist",
                MetaData(bind=engine),
                schema="also_does_not_exist",
                autoload=True,
            ),
        ) 

def setUp(self):
        from gengine.app.cache import clear_all_caches
        clear_all_caches()
        self.db = db.db()
        dsn = self.db.dsn()
        self.engine =  create_engine(
            "postgresql://%(user)s@%(host)s:%(port)s/%(database)s" % {
                "user": dsn["user"],
                "host": dsn["host"],
                "port": dsn["port"],
                "database": dsn["database"],
            }
        )
        init_session(override_session=scoped_session(get_sessionmaker(bind=self.engine)), replace=True)
        from gengine.metadata import Base
        Base.metadata.bind = self.engine

        Base.metadata.drop_all(self.engine)
        self.engine.execute("DROP SCHEMA IF EXISTS public CASCADE")
        self.engine.execute("CREATE SCHEMA IF NOT EXISTS public")

        from alembic.config import Config
        from alembic import command

        alembic_cfg = Config(attributes={
            'engine': self.engine,
            'schema': 'public'
        })
        script_location = os.path.join(
            os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))),
            'app/alembic'
        )
        alembic_cfg.set_main_option("script_location", script_location)

        from gengine.app import model

        tables = [t for name, t in model.__dict__.items() if isinstance(t, Table)]
        Base.metadata.create_all(self.engine, tables=tables)

        command.stamp(alembic_cfg, "head") 

def __init__(self, config):
        ApplicationSession.__init__(self, config)
        self.count = 0
        self.engine = None


        metadata = MetaData()
        self.telemetry = Table("telemetry", metadata,
            Column("id", Integer(), primary_key=True),
            Column("MSG_ID", Integer()),
            Column("V_FC", Integer()),
            Column("V_CAP", Integer()),
            Column("A_ENG", Integer()),
            Column("A_CAP", Integer()),
            Column("T_O2_In", Integer()),
            Column("T_O2_Out", Integer()),
            Column("T_FC_H2O_Out", Integer()),
            Column("Water_In", Integer()),
            Column("Water_Out", Integer()),
            Column("Master_SW", Integer()),
            Column("CAP_Down_SW", Integer()),
            Column("Drive_SW", Integer()),
            Column("FC_state", Integer()),
            Column("Mosfet_state", Integer()),
            Column("Safety_state", Integer()),
            Column("Air_Pump_load", Numeric()),
            Column("Mosfet_load", Integer()),
            Column("Water_Pump_load", Integer()),
            Column("Fan_load", Integer()),
            Column("Acc_X", Integer()),
            Column("Acc_Y", Integer()),
            Column("Acc_Z", Integer()),
            Column("AUX", Numeric()),
            Column("GPS_X", Integer()),
            Column("GPS_Y", Integer()),
            Column("GPS_Z", Integer()),
            Column("GPS_Speed", Integer()),
            Column("V_Safety", Integer()),
            Column("H2_Level", Integer()),
            Column("O2_calc", Numeric()),
            Column("lat", Numeric()),
            Column("lng", Numeric()),
            )


#        metadata = MetaData()
#        self.telemetry = Table("telemetry", metadata,
#            Column("id", Integer(), primary_key=True),
#            Column("mma_x", Integer()),
#            Column("mma_y", Integer()),
#            Column("temp", Numeric()),
#	    Column("lat", Numeric()),
#	    Column("lng", Numeric()),
#        )

    #@inlineCallbacks 

def test_to_sql(self, engine, conn):
        # TODO Add binary column (After dropping support for Python 2.7)
        table_name = "to_sql_{0}".format(str(uuid.uuid4()).replace("-", ""))
        df = pd.DataFrame(
            {
                "col_int": np.int32([1]),
                "col_bigint": np.int64([12345]),
                "col_float": np.float32([1.0]),
                "col_double": np.float64([1.2345]),
                "col_string": ["a"],
                "col_boolean": np.bool_([True]),
                "col_timestamp": [datetime(2020, 1, 1, 0, 0, 0)],
                "col_date": [date(2020, 12, 31)],
            }
        )
        # Explicitly specify column order
        df = df[
            [
                "col_int",
                "col_bigint",
                "col_float",
                "col_double",
                "col_string",
                "col_boolean",
                "col_timestamp",
                "col_date",
            ]
        ]
        df.to_sql(
            table_name,
            engine,
            schema=SCHEMA,
            index=False,
            if_exists="replace",
            method="multi",
        )

        table = Table(table_name, MetaData(bind=engine), autoload=True)
        self.assertEqual(
            table.select().execute().fetchall(),
            [
                (
                    1,
                    12345,
                    1.0,
                    1.2345,
                    "a",
                    True,
                    datetime(2020, 1, 1, 0, 0, 0),
                    date(2020, 12, 31),
                )
            ],
        ) 

def setUp(self):
        from gengine.app.cache import clear_all_caches
        clear_all_caches()

        if os.path.exists("/tmp/test_pgdata"):
            shutil.rmtree("/tmp/test_pgdata")

        self.db = testDB.db()
        dsn = self.db.dsn()
        self.engine =  create_engine(
            "postgresql://%(user)s@%(host)s:%(port)s/%(database)s" % {
                "user": dsn["user"],
                "host": dsn["host"],
                "port": dsn["port"],
                "database": dsn["database"],
            }
        )
        init_session(override_session=scoped_session(get_sessionmaker(bind=self.engine)), replace=True)
        from gengine.metadata import Base
        Base.metadata.bind = self.engine

        Base.metadata.drop_all(self.engine)
        self.engine.execute("DROP SCHEMA IF EXISTS public CASCADE")
        self.engine.execute("CREATE SCHEMA IF NOT EXISTS public")

        from alembic.config import Config
        from alembic import command

        alembic_cfg = Config(attributes={
            'engine': self.engine,
            'schema': 'public'
        })
        script_location = os.path.join(
            os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))),
            'app/alembic'
        )
        alembic_cfg.set_main_option("script_location", script_location)

        from gengine.app import model

        tables = [t for name, t in model.__dict__.items() if isinstance(t, Table)]
        Base.metadata.create_all(self.engine, tables=tables)

        command.stamp(alembic_cfg, "head")