Python sqlalchemy.sql.schema.MetaData() Examples

The following are 22 code examples of sqlalchemy.sql.schema.MetaData(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy.sql.schema , or try the search function .
Example #1
Source File: metadata.py    From gamification-engine with MIT License 6 votes vote down vote up 
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)
Example #2
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 6 votes vote down vote up 
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,)])
Example #3
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 6 votes vote down vote up 
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)
Example #4
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 6 votes vote down vote up 
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)
Example #5
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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)
Example #6
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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)
Example #7
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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)
Example #8
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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"))
Example #9
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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()
        )
Example #10
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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)
Example #11
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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,
            ),
        )
Example #12
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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)
Example #13
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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)
Example #14
Source File: test_sqlalchemy_athena.py    From PyAthenaJDBC with MIT License 5 votes vote down vote up 
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,
            ),
        )
Example #15
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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)
Example #16
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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)
Example #17
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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"))
Example #18
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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()
        )
Example #19
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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)
Example #20
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 5 votes vote down vote up 
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)
Example #21
Source File: test_sqlalchemy_athena.py    From PyAthena with MIT License 4 votes vote down vote up 
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),
                )
            ],
        )
Example #22
Source File: sql.py    From kotori with GNU Affero General Public License v3.0 4 votes vote down vote up 
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