Python schema.MetaData方法代码示例

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
def init_declarative_base(override_base=None):
    global Base
    if Base:
        return
    if override_base:
        Base = override_base
    else:
        convention = {
            "ix": 'ix_%(column_0_label)s',
            "uq": "uq_%(table_name)s_%(column_0_name)s",
            "ck": "ck_%(table_name)s_%(constraint_name)s",
            "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
            "pk": "pk_%(table_name)s"
        }
        metadata = MetaData(naming_convention=convention)
        Base = declarative_base(metadata = metadata) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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,)]) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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,
            ),
        ) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
def test_has_table(self, engine, conn):
        self.assertTrue(Table("one_row", MetaData(bind=engine)).exists())
        self.assertFalse(
            Table("this_table_does_not_exist", MetaData(bind=engine)).exists()
        ) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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")) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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) 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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 

# 需要导入模块: from sqlalchemy.sql import schema [as 别名]
# 或者: from sqlalchemy.sql.schema import MetaData [as 别名]
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),
                )
            ],
        )