testtypes.py

SQLAlchemy. 经典的Python ORM框架。学习必看。

class DateTest(TestBase, AssertsExecutionResults):
    def setUpAll(self):
        global users_with_date, insert_data

        db = testing.db
        if testing.against('oracle'):
            import sqlalchemy.databases.oracle as oracle
            insert_data =  [
                    [7, 'jack',
                     datetime.datetime(2005, 11, 10, 0, 0),
                     datetime.date(2005,11,10),
                     datetime.datetime(2005, 11, 10, 0, 0, 0, 29384)],
                    [8, 'roy',
                     datetime.datetime(2005, 11, 10, 11, 52, 35),
                     datetime.date(2005,10,10),
                     datetime.datetime(2006, 5, 10, 15, 32, 47, 6754)],
                    [9, 'foo',
                     datetime.datetime(2006, 11, 10, 11, 52, 35),
                     datetime.date(1970,4,1),
                     datetime.datetime(2004, 9, 18, 4, 0, 52, 1043)],
                    [10, 'colber', None, None, None]
             ]
            fnames = ['user_id', 'user_name', 'user_datetime',
                      'user_date', 'user_time']

            collist = [Column('user_id', INT, primary_key=True),
                       Column('user_name', VARCHAR(20)),
                       Column('user_datetime', DateTime),
                       Column('user_date', Date),
                       Column('user_time', TIMESTAMP)]
        else:
            datetime_micro = 54839
            time_micro = 999

            # Missing or poor microsecond support:
            if testing.against('mssql', 'mysql', 'firebird'):
                datetime_micro, time_micro = 0, 0
            # No microseconds for TIME
            elif testing.against('maxdb'):
                time_micro = 0

            insert_data =  [
                [7, 'jack',
                 datetime.datetime(2005, 11, 10, 0, 0),
                 datetime.date(2005, 11, 10),
                 datetime.time(12, 20, 2)],
                [8, 'roy',
                 datetime.datetime(2005, 11, 10, 11, 52, 35),
                 datetime.date(2005, 10, 10),
                 datetime.time(0, 0, 0)],
                [9, 'foo',
                 datetime.datetime(2005, 11, 10, 11, 52, 35, datetime_micro),
                 datetime.date(1970, 4, 1),
                 datetime.time(23, 59, 59, time_micro)],
                [10, 'colber', None, None, None]
            ]
            fnames = ['user_id', 'user_name', 'user_datetime',
                      'user_date', 'user_time']

            collist = [Column('user_id', INT, primary_key=True),
                       Column('user_name', VARCHAR(20)),
                       Column('user_datetime', DateTime(timezone=False)),
                       Column('user_date', Date),
                       Column('user_time', Time)]

        users_with_date = Table('query_users_with_date',
                                MetaData(testing.db), *collist)
        users_with_date.create()
        insert_dicts = [dict(zip(fnames, d)) for d in insert_data]

        for idict in insert_dicts:
            users_with_date.insert().execute(**idict)

    def tearDownAll(self):
        users_with_date.drop()

    def testdate(self):
        global insert_data

        l = map(list, users_with_date.select().execute().fetchall())
        self.assert_(l == insert_data,
                     'DateTest mismatch: got:%s expected:%s' % (l, insert_data))

    def testtextdate(self):
        x = testing.db.text(
            "select user_datetime from query_users_with_date",
            typemap={'user_datetime':DateTime}).execute().fetchall()

        print repr(x)
        self.assert_(isinstance(x[0][0], datetime.datetime))

        x = testing.db.text(
            "select * from query_users_with_date where user_datetime=:somedate",
            bindparams=[bindparam('somedate', type_=types.DateTime)]).execute(
            somedate=datetime.datetime(2005, 11, 10, 11, 52, 35)).fetchall()
        print repr(x)

    def testdate2(self):
        meta = MetaData(testing.db)
        t = Table('testdate', meta,
                  Column('id', Integer,
                         Sequence('datetest_id_seq', optional=True),
                         primary_key=True),
                Column('adate', Date), Column('adatetime', DateTime))
        t.create(checkfirst=True)
        try:
            d1 = datetime.date(2007, 10, 30)
            t.insert().execute(adate=d1, adatetime=d1)
            d2 = datetime.datetime(2007, 10, 30)
            t.insert().execute(adate=d2, adatetime=d2)

            x = t.select().execute().fetchall()[0]
            self.assert_(x.adate.__class__ == datetime.date)
            self.assert_(x.adatetime.__class__ == datetime.datetime)

            t.delete().execute()

            # test mismatched date/datetime
            t.insert().execute(adate=d2, adatetime=d2)
            self.assertEquals(select([t.c.adate, t.c.adatetime], t.c.adate==d1).execute().fetchall(), [(d1, d2)])
            self.assertEquals(select([t.c.adate, t.c.adatetime], t.c.adate==d1).execute().fetchall(), [(d1, d2)])

        finally:
            t.drop(checkfirst=True)

class StringTest(TestBase, AssertsExecutionResults):
    def test_nolen_string_deprecated(self):
        metadata = MetaData(testing.db)
        foo =Table('foo', metadata,
            Column('one', String))

        # no warning
        select([func.count("*")], bind=testing.db).execute()

        try:
            # warning during CREATE
            foo.create()
            assert False
        except exceptions.SADeprecationWarning, e:
            assert "Using String type with no length" in str(e)
            assert re.search(r'\bone\b', str(e))

        bar = Table('bar', metadata, Column('one', String(40)))

        try:
            # no warning
            bar.create()

            # no warning for non-lengthed string
            select([func.count("*")], from_obj=bar).execute()
        finally:
            bar.drop()

def _missing_decimal():
    """Python implementation supports decimals"""
    try:
        import decimal
        return False
    except ImportError:
        return True

class NumericTest(TestBase, AssertsExecutionResults):
    def setUpAll(self):
        global numeric_table, metadata
        metadata = MetaData(testing.db)
        numeric_table = Table('numeric_table', metadata,
            Column('id', Integer, Sequence('numeric_id_seq', optional=True), primary_key=True),
            Column('numericcol', Numeric(asdecimal=False)),
            Column('floatcol', Float),
            Column('ncasdec', Numeric),
            Column('fcasdec', Float(asdecimal=True))
        )
        metadata.create_all()

    def tearDownAll(self):
        metadata.drop_all()

    def tearDown(self):
        numeric_table.delete().execute()

    @testing.fails_if(_missing_decimal)
    def test_decimal(self):
        from decimal import Decimal
        numeric_table.insert().execute(
            numericcol=3.5, floatcol=5.6, ncasdec=12.4, fcasdec=15.75)
        numeric_table.insert().execute(
            numericcol=Decimal("3.5"), floatcol=Decimal("5.6"),
            ncasdec=Decimal("12.4"), fcasdec=Decimal("15.75"))

        l = numeric_table.select().execute().fetchall()
        print l
        rounded = [
            (l[0][0], l[0][1], round(l[0][2], 5), l[0][3], l[0][4]),
            (l[1][0], l[1][1], round(l[1][2], 5), l[1][3], l[1][4]),
        ]
        assert rounded == [
            (1, 3.5, 5.6, Decimal("12.4"), Decimal("15.75")),
            (2, 3.5, 5.6, Decimal("12.4"), Decimal("15.75")),
        ]

    @testing.emits_warning('True Decimal types not available')
    def test_decimal_fallback(self):
        from sqlalchemy.util import Decimal  # could be Decimal or float

        numeric_table.insert().execute(ncasdec=12.4, fcasdec=15.75)
        numeric_table.insert().execute(ncasdec=Decimal("12.4"),
                                       fcasdec=Decimal("15.75"))

        for row in numeric_table.select().execute().fetchall():
            assert isinstance(row['ncasdec'], util.decimal_type)
            assert isinstance(row['fcasdec'], util.decimal_type)


class IntervalTest(TestBase, AssertsExecutionResults):
    def setUpAll(self):
        global interval_table, metadata
        metadata = MetaData(testing.db)
        interval_table = Table("intervaltable", metadata,
            Column("id", Integer, Sequence('interval_id_seq', optional=True), primary_key=True),
            Column("interval", Interval),
            )
        metadata.create_all()

    def tearDown(self):
        interval_table.delete().execute()

    def tearDownAll(self):
        metadata.drop_all()

    def test_roundtrip(self):
        delta = datetime.datetime(2006, 10, 5) - datetime.datetime(2005, 8, 17)
        interval_table.insert().execute(interval=delta)
        assert interval_table.select().execute().fetchone()['interval'] == delta

    def test_null(self):
        interval_table.insert().execute(id=1, inverval=None)
        assert interval_table.select().execute().fetchone()['interval'] is None

class BooleanTest(TestBase, AssertsExecutionResults):
    def setUpAll(self):
        global bool_table
        metadata = MetaData(testing.db)
        bool_table = Table('booltest', metadata,
            Column('id', Integer, primary_key=True),
            Column('value', Boolean))
        bool_table.create()
    def tearDownAll(self):
        bool_table.drop()
    def testbasic(self):
        bool_table.insert().execute(id=1, value=True)
        bool_table.insert().execute(id=2, value=False)
        bool_table.insert().execute(id=3, value=True)
        bool_table.insert().execute(id=4, value=True)
        bool_table.insert().execute(id=5, value=True)

        res = bool_table.select(bool_table.c.value==True).execute().fetchall()
        print res
        assert(res==[(1, True),(3, True),(4, True),(5, True)])

        res2 = bool_table.select(bool_table.c.value==False).execute().fetchall()
        print res2
        assert(res2==[(2, False)])

if __name__ == "__main__":
    testenv.main()