Python Table.select方法代码示例

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_reflect(self):
        t1.insert().execute({u'méil':2, u'\u6e2c\u8a66':7})
        t2.insert().execute({'a':2, 'b':2})
        t3.insert().execute({u'\u6e2c\u8a66_id': 2,
                             u'unitable1_\u6e2c\u8a66': 7,
                             u'Unitéble2_b': 2,
                             u'\u6e2c\u8a66_self': 2})

        meta = MetaData(unicode_bind)
        tt1 = Table(t1.name, meta, autoload=True)
        tt2 = Table(t2.name, meta, autoload=True)
        tt3 = Table(t3.name, meta, autoload=True)

        tt1.insert().execute({u'méil':1, u'\u6e2c\u8a66':5})
        tt2.insert().execute({u'méil':1, u'\u6e2c\u8a66':1})
        tt3.insert().execute({u'\u6e2c\u8a66_id': 1,
                              u'unitable1_\u6e2c\u8a66': 5,
                              u'Unitéble2_b': 1,
                              u'\u6e2c\u8a66_self': 1})

        self.assert_(tt1.select(order_by=desc(u'méil')).execute().fetchall() ==
                     [(2, 7), (1, 5)])
        self.assert_(tt2.select(order_by=desc(u'méil')).execute().fetchall() ==
                     [(2, 2), (1, 1)])
        self.assert_(tt3.select(order_by=desc(u'\u6e2c\u8a66_id')).
                     execute().fetchall() ==
                     [(2, 7, 2, 2), (1, 5, 1, 1)])
        meta.drop_all()
        metadata.create_all()

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_limit_offset_for_update(self):
        metadata = self.metadata
        # oracle can't actually do the ROWNUM thing with FOR UPDATE
        # very well.

        t = Table(
            "t1",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", Integer),
        )
        metadata.create_all()

        t.insert().execute(
            {"id": 1, "data": 1},
            {"id": 2, "data": 7},
            {"id": 3, "data": 12},
            {"id": 4, "data": 15},
            {"id": 5, "data": 32},
        )

        # here, we can't use ORDER BY.
        eq_(
            t.select().with_for_update().limit(2).execute().fetchall(),
            [(1, 1), (2, 7)],
        )

        # here, its impossible.  But we'd prefer it to raise ORA-02014
        # instead of issuing a syntax error.
        assert_raises_message(
            exc.DatabaseError,
            "ORA-02014",
            t.select().with_for_update().limit(2).offset(3).execute,
        )

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_reflect(self):
        t1.insert().execute({u('méil'): 2, ue('\u6e2c\u8a66'): 7})
        t2.insert().execute({u('a'): 2, u('b'): 2})
        t3.insert().execute({ue('\u6e2c\u8a66_id'): 2,
                             ue('unitable1_\u6e2c\u8a66'): 7,
                             u('Unitéble2_b'): 2,
                             ue('\u6e2c\u8a66_self'): 2})

        meta = MetaData(testing.db)
        tt1 = Table(t1.name, meta, autoload=True)
        tt2 = Table(t2.name, meta, autoload=True)
        tt3 = Table(t3.name, meta, autoload=True)

        tt1.insert().execute({u('méil'): 1, ue('\u6e2c\u8a66'): 5})
        tt2.insert().execute({u('méil'): 1, ue('\u6e2c\u8a66'): 1})
        tt3.insert().execute({ue('\u6e2c\u8a66_id'): 1,
                              ue('unitable1_\u6e2c\u8a66'): 5,
                              u('Unitéble2_b'): 1,
                              ue('\u6e2c\u8a66_self'): 1})

        self.assert_(tt1.select(order_by=desc(u('méil'))).execute().fetchall() ==
                     [(2, 7), (1, 5)])
        self.assert_(tt2.select(order_by=desc(u('méil'))).execute().fetchall() ==
                     [(2, 2), (1, 1)])
        self.assert_(tt3.select(order_by=desc(ue('\u6e2c\u8a66_id'))).
                     execute().fetchall() ==
                     [(2, 7, 2, 2), (1, 5, 1, 1)])

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_int_default_none_on_insert_reflected(self):
        metadata = self.metadata
        Table('x', metadata,
                Column('y', Integer,
                        server_default='5', primary_key=True),
                Column('data', String(10)),
                implicit_returning=False
                )
        metadata.create_all()

        m2 = MetaData(metadata.bind)
        t2 = Table('x', m2, autoload=True, implicit_returning=False)

        r = t2.insert().execute(data='data')
        eq_(r.inserted_primary_key, [None])
        if testing.against('sqlite'):
            eq_(
                t2.select().execute().fetchall(),
                [(1, 'data')]
            )
        else:
            eq_(
                t2.select().execute().fetchall(),
                [(5, 'data')]
            )

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_fixed_char(self):
        m = MetaData(testing.db)
        t = Table('t1', m,
                  Column('id', Integer, primary_key=True),
                  Column('data', CHAR(30), nullable=False))

        t.create()
        try:
            t.insert().execute(
                dict(id=1, data="value 1"),
                dict(id=2, data="value 2"),
                dict(id=3, data="value 3")
            )

            eq_(
                t.select().where(t.c.data == 'value 2').execute().fetchall(),
                [(2, 'value 2                       ')]
            )

            m2 = MetaData(testing.db)
            t2 = Table('t1', m2, autoload=True)
            assert type(t2.c.data.type) is CHAR
            eq_(
                t2.select().where(t2.c.data == 'value 2').execute().fetchall(),
                [(2, 'value 2                       ')]
            )

        finally:
            t.drop()

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_reflect(self):
        t1.insert().execute({u("méil"): 2, ue("\u6e2c\u8a66"): 7})
        t2.insert().execute({u("a"): 2, u("b"): 2})
        t3.insert().execute(
            {ue("\u6e2c\u8a66_id"): 2, ue("unitable1_\u6e2c\u8a66"): 7, u("Unitéble2_b"): 2, ue("\u6e2c\u8a66_self"): 2}
        )

        meta = MetaData(unicode_bind)
        tt1 = Table(t1.name, meta, autoload=True)
        tt2 = Table(t2.name, meta, autoload=True)
        tt3 = Table(t3.name, meta, autoload=True)

        tt1.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 5})
        tt2.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 1})
        tt3.insert().execute(
            {ue("\u6e2c\u8a66_id"): 1, ue("unitable1_\u6e2c\u8a66"): 5, u("Unitéble2_b"): 1, ue("\u6e2c\u8a66_self"): 1}
        )

        self.assert_(tt1.select(order_by=desc(u("méil"))).execute().fetchall() == [(2, 7), (1, 5)])
        self.assert_(tt2.select(order_by=desc(u("méil"))).execute().fetchall() == [(2, 2), (1, 1)])
        self.assert_(
            tt3.select(order_by=desc(ue("\u6e2c\u8a66_id"))).execute().fetchall() == [(2, 7, 2, 2), (1, 5, 1, 1)]
        )
        meta.drop_all()
        metadata.create_all()

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_numerics(self):
        m = self.metadata
        t1 = Table(
            "t1",
            m,
            Column("intcol", Integer),
            Column("numericcol", Numeric(precision=9, scale=2)),
            Column("floatcol1", Float()),
            Column("floatcol2", FLOAT()),
            Column("doubleprec", oracle.DOUBLE_PRECISION),
            Column("numbercol1", oracle.NUMBER(9)),
            Column("numbercol2", oracle.NUMBER(9, 3)),
            Column("numbercol3", oracle.NUMBER),
        )
        t1.create()
        t1.insert().execute(
            intcol=1,
            numericcol=5.2,
            floatcol1=6.5,
            floatcol2=8.5,
            doubleprec=9.5,
            numbercol1=12,
            numbercol2=14.85,
            numbercol3=15.76,
        )

        m2 = MetaData(testing.db)
        t2 = Table("t1", m2, autoload=True)

        for row in (
            t1.select().execute().first(),
            t2.select().execute().first(),
        ):
            for i, (val, type_) in enumerate(
                (
                    (1, int),
                    (decimal.Decimal("5.2"), decimal.Decimal),
                    (6.5, float),
                    (8.5, float),
                    (9.5, float),
                    (12, int),
                    (decimal.Decimal("14.85"), decimal.Decimal),
                    (15.76, float),
                )
            ):
                eq_(row[i], val)
                assert isinstance(row[i], type_), "%r is not %r" % (
                    row[i],
                    type_,
                )

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def _run_test(self, *arg, **kw):
        metadata = self.metadata
        implicit_returning = kw.pop("implicit_returning", True)
        kw["primary_key"] = True
        if kw.get("autoincrement", True):
            kw["test_needs_autoincrement"] = True
        t = Table(
            "x",
            metadata,
            Column("y", self.MyInteger, *arg, **kw),
            Column("data", Integer),
            implicit_returning=implicit_returning,
        )

        t.create()
        r = t.insert().values(data=5).execute()

        # we don't pre-fetch 'server_default'.
        if "server_default" in kw and (not testing.db.dialect.implicit_returning or not implicit_returning):
            eq_(r.inserted_primary_key, [None])
        else:
            eq_(r.inserted_primary_key, ["INT_1"])
        r.close()

        eq_(t.select().execute().first(), ("INT_1", 5))

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_reflect_nvarchar(self):
        metadata = self.metadata
        Table(
            "tnv",
            metadata,
            Column("nv_data", sqltypes.NVARCHAR(255)),
            Column("c_data", sqltypes.NCHAR(20)),
        )
        metadata.create_all()
        m2 = MetaData(testing.db)
        t2 = Table("tnv", m2, autoload=True)
        assert isinstance(t2.c.nv_data.type, sqltypes.NVARCHAR)
        assert isinstance(t2.c.c_data.type, sqltypes.NCHAR)

        if testing.against("oracle+cx_oracle"):
            assert isinstance(
                t2.c.nv_data.type.dialect_impl(testing.db.dialect),
                cx_oracle._OracleUnicodeStringNCHAR,
            )

            assert isinstance(
                t2.c.c_data.type.dialect_impl(testing.db.dialect),
                cx_oracle._OracleUnicodeStringNCHAR,
            )

        data = u("m’a réveillé.")
        with testing.db.connect() as conn:
            conn.execute(t2.insert(), dict(nv_data=data, c_data=data))
            nv_data, c_data = conn.execute(t2.select()).first()
            eq_(nv_data, data)
            eq_(c_data, data + (" " * 7))  # char is space padded
            assert isinstance(nv_data, util.text_type)
            assert isinstance(c_data, util.text_type)

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def _run_test(self, *arg, **kw):
        metadata = self.metadata
        implicit_returning = kw.pop('implicit_returning', True)
        kw['primary_key'] = True
        if kw.get('autoincrement', True):
            kw['test_needs_autoincrement'] = True
        t = Table('x', metadata,
            Column('y', self.MyInteger, *arg, **kw),
            Column('data', Integer),
            implicit_returning=implicit_returning
        )

        t.create()
        r = t.insert().values(data=5).execute()

        # we don't pre-fetch 'server_default'.
        if 'server_default' in kw and (not
                    testing.db.dialect.implicit_returning or
                    not implicit_returning):
            eq_(r.inserted_primary_key, [None])
        else:
            eq_(r.inserted_primary_key, ['INT_1'])
        r.close()

        eq_(
            t.select().execute().first(),
            ('INT_1', 5)
        )

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_string_default_none_on_insert(self):
        """Test that without implicit returning, we return None for
        a string server default.

        That is, we don't want to attempt to pre-execute "server_default"
        generically - the user should use a Python side-default for a case
        like this.   Testing that all backends do the same thing here.

        """

        metadata = self.metadata
        t = Table(
            'x', metadata,
            Column(
                'y', String(10), server_default='key_one', primary_key=True),
            Column('data', String(10)),
            implicit_returning=False
        )
        metadata.create_all()
        r = t.insert().execute(data='data')
        eq_(r.inserted_primary_key, [None])
        eq_(
            t.select().execute().fetchall(),
            [('key_one', 'data')]
        )

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def define_tables(cls, metadata):
        foo = Table('foo', metadata,
                    Column('a', String(30), primary_key=1),
                    Column('b', String(30), nullable=0))

        cls.tables.bar = foo.select(foo.c.b == 'bar').alias('bar')
        cls.tables.baz = foo.select(foo.c.b == 'baz').alias('baz')

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_column_accessor_shadow(self):
        shadowed = Table(
            'test_shadowed', self.metadata,
            Column('shadow_id', INT, primary_key=True),
            Column('shadow_name', VARCHAR(20)),
            Column('parent', VARCHAR(20)),
            Column('row', VARCHAR(40)),
            Column('_parent', VARCHAR(20)),
            Column('_row', VARCHAR(20)),
        )
        self.metadata.create_all()
        shadowed.insert().execute(
            shadow_id=1, shadow_name='The Shadow', parent='The Light',
            row='Without light there is no shadow',
            _parent='Hidden parent', _row='Hidden row')
        r = shadowed.select(shadowed.c.shadow_id == 1).execute().first()

        eq_(r.shadow_id, 1)
        eq_(r['shadow_id'], 1)
        eq_(r[shadowed.c.shadow_id], 1)

        eq_(r.shadow_name, 'The Shadow')
        eq_(r['shadow_name'], 'The Shadow')
        eq_(r[shadowed.c.shadow_name], 'The Shadow')

        eq_(r.parent, 'The Light')
        eq_(r['parent'], 'The Light')
        eq_(r[shadowed.c.parent], 'The Light')

        eq_(r.row, 'Without light there is no shadow')
        eq_(r['row'], 'Without light there is no shadow')
        eq_(r[shadowed.c.row], 'Without light there is no shadow')

        eq_(r['_parent'], 'Hidden parent')
        eq_(r['_row'], 'Hidden row')

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
 def _dont_test_reflect_all_types_schema(self):
     types_table = Table('all_types', MetaData(testing.db),
                         Column('owner', String(30), primary_key=True),
                         Column('type_name', String(30), primary_key=True),
                         autoload=True, oracle_resolve_synonyms=True)
     for row in types_table.select().execute().fetchall():
         [row[k] for k in row.keys()]

# 需要导入模块: from sqlalchemy.testing.schema import Table [as 别名]
# 或者: from sqlalchemy.testing.schema.Table import select [as 别名]
    def test_column_label_overlap_fallback(self):
        content = Table(
            'content', self.metadata,
            Column('type', String(30)),
        )
        bar = Table(
            'bar', self.metadata,
            Column('content_type', String(30))
        )
        self.metadata.create_all(testing.db)
        testing.db.execute(content.insert().values(type="t1"))

        row = testing.db.execute(content.select(use_labels=True)).first()
        in_(content.c.type, row)
        not_in_(bar.c.content_type, row)
        in_(sql.column('content_type'), row)

        row = testing.db.execute(
            select([content.c.type.label("content_type")])).first()
        in_(content.c.type, row)

        not_in_(bar.c.content_type, row)

        in_(sql.column('content_type'), row)

        row = testing.db.execute(select([func.now().label("content_type")])). \
            first()
        not_in_(content.c.type, row)

        not_in_(bar.c.content_type, row)

        in_(sql.column('content_type'), row)