sqlobject.py

Python的一个ORM,现在很火

        person = self.Person.selectFirstBy(age=20, orderBy="-name")

        self.assertTrue(person)
        self.assertEquals(person.name, "John Joe")

        nobody = self.Person.selectFirstBy(age=1000, orderBy="name")

        self.assertEquals(nobody, None)

    def test_selectFirstBy_default_order(self):
        person = self.Person.selectFirstBy(age=20)

        self.assertTrue(person)
        self.assertEquals(person.name, "John Joe")

    def test_dummy_methods(self):
        person = self.Person.get(id=1)
        person.sync()
        person.syncUpdate()

    def test_col_name(self):
        class Person(self.SQLObject):
            foo = StringCol(dbName="name")
        person = Person.get(2)
        self.assertEquals(person.foo, "John Doe")

        class Person(self.SQLObject):
            foo = StringCol("name")
        person = Person.get(2)
        self.assertEquals(person.foo, "John Doe")

    def test_col_default(self):
        class Person(self.SQLObject):
            name = StringCol(default="Johny")
        person = Person()
        self.assertEquals(person.name, "Johny")

    def test_col_default_factory(self):
        class Person(self.SQLObject):
            name = StringCol(default=lambda: "Johny")
        person = Person()
        self.assertEquals(person.name, "Johny")

    def test_col_not_null(self):
        class Person(self.SQLObject):
            name = StringCol(notNull=True)
        person = Person.get(2)
        self.assertRaises(NoneError, setattr, person, "name", None)

    def test_string_col(self):
        class Person(self.SQLObject):
            name = StringCol()
        person = Person.get(2)
        self.assertEquals(person.name, "John Doe")

    def test_int_col(self):
        class Person(self.SQLObject):
            age = IntCol()
        person = Person.get(2)
        self.assertEquals(person.age, 20)

    def test_bool_col(self):
        class Person(self.SQLObject):
            age = BoolCol()
        person = Person.get(2)
        self.assertEquals(person.age, True)

    def test_float_col(self):
        class Person(self.SQLObject):
            age = FloatCol()
        person = Person.get(2)
        self.assertTrue(abs(person.age - 20.0) < 1e-6)

    def test_utcdatetime_col(self):
        class Person(self.SQLObject):
            ts = UtcDateTimeCol()
        person = Person.get(2)
        self.assertEquals(person.ts,
                          datetime.datetime(2007, 2, 5, 20, 53, 15,
                                            tzinfo=tzutc()))
    def test_date_col(self):
        class Person(self.SQLObject):
            ts = DateCol()
        person = Person.get(2)
        self.assertEquals(person.ts, datetime.date(2007, 2, 5))

    def test_interval_col(self):
        class Person(self.SQLObject):
            delta = IntervalCol()
        person = Person.get(2)
        self.assertEquals(person.delta, datetime.timedelta(42, 45296, 780000))

    def test_foreign_key(self):
        class Person(self.Person):
            address = ForeignKey(foreignKey="Address", dbName="address_id",
                                 notNull=True)

        class Address(self.SQLObject):
            city = StringCol()

        person = Person.get(2)

        self.assertEquals(person.addressID, 2)
        self.assertEquals(person.address.city, "Sao Carlos")

    def test_foreign_key_no_dbname(self):
        self.store.execute("CREATE TABLE another_person "
                           "(id INTEGER PRIMARY KEY, name TEXT, age INTEGER,"
                           " ts TIMESTAMP, address INTEGER)")
        self.store.execute("INSERT INTO another_person VALUES "
                           "(2, 'John Doe', 20, '2007-02-05 20:53:15', 2)")

        class AnotherPerson(self.Person):
            address = ForeignKey(foreignKey="Address", notNull=True)

        class Address(self.SQLObject):
            city = StringCol()

        person = AnotherPerson.get(2)

        self.assertEquals(person.addressID, 2)
        self.assertEquals(person.address.city, "Sao Carlos")

    def test_foreign_key_orderBy(self):
        class Person(self.Person):
            _defaultOrder = "address"
            address = ForeignKey(foreignKey="Address", dbName="address_id",
                                 notNull=True)

        class Address(self.SQLObject):
            city = StringCol()

        person = Person.selectFirst()
        self.assertEquals(person.addressID, 1)


    def test_multiple_join(self):
        class AnotherPerson(self.Person):
            _table = "person"
            phones = SQLMultipleJoin("Phone", joinColumn="person",
                                     prejoins=['person'])

        class Phone(self.SQLObject):
            person = ForeignKey("AnotherPerson", dbName="person_id")
            number = StringCol()

        person = AnotherPerson.get(2)

        # Make sure that the result is wrapped.
        result = person.phones.orderBy("-number")

        self.assertEquals([phone.number for phone in result],
                          ["8765-5678", "1234-5678"])

    def test_related_join(self):
        class AnotherPerson(self.Person):
            _table = "person"
            phones = SQLRelatedJoin("Phone", otherColumn="phone_id",
                                    intermediateTable="PersonPhone",
                                    joinColumn="person_id", orderBy="id")

        class PersonPhone(self.Person):
            person_id = IntCol()
            phone_id = IntCol()

        class Phone(self.SQLObject):
            number = StringCol()

        person = AnotherPerson.get(2)

        self.assertEquals([phone.number for phone in person.phones],
                          ["1234-5678", "8765-4321"])

        # Make sure that the result is wrapped.
        result = person.phones.orderBy("-number")

        self.assertEquals([phone.number for phone in result],
                          ["8765-4321", "1234-5678"])

    def test_result_set_orderBy(self):
        result = self.Person.select()

        result = result.orderBy("-name")
        self.assertEquals([person.name for person in result],
                          ["John Joe", "John Doe"])

        result = result.orderBy("name")
        self.assertEquals([person.name for person in result],
                          ["John Doe", "John Joe"])

    def test_result_set_orderBy_fully_qualified(self):
        result = self.Person.select()

        result = result.orderBy("-person.name")
        self.assertEquals([person.name for person in result],
                          ["John Joe", "John Doe"])

        result = result.orderBy("person.name")
        self.assertEquals([person.name for person in result],
                          ["John Doe", "John Joe"])

    def test_result_set_count(self):
        result = self.Person.select()
        self.assertEquals(result.count(), 2)

    def test_result_set__getitem__(self):
        result = self.Person.select()
        self.assertEquals(result[0].name, "John Joe")

    def test_result_set__iter__(self):
        result = self.Person.select()
        self.assertEquals(list(result.__iter__())[0].name, "John Joe")

    def test_result_set__nonzero__(self):
        result = self.Person.select()
        self.assertEquals(result.__nonzero__(), True)
        result = self.Person.select(self.Person.q.name == 'No Person')
        self.assertEquals(result.__nonzero__(), False)

    def test_result_set_distinct(self):
        result = self.Person.select("person.name = 'John Joe'",
                                    clauseTables=["phone"])
        self.assertEquals(len(list(result.distinct())), 1)

    def test_result_set_limit(self):
        result = self.Person.select()
        self.assertEquals(len(list(result.limit(1))), 1)

    def test_result_set_union(self):
        result1 = self.Person.selectBy(id=1)
        result2 = self.Person.selectBy(id=2)
        result3 = result1.union(result2, orderBy="name")
        self.assertEquals([person.name for person in result3],
                          ["John Doe", "John Joe"])

    def test_result_set_union_all(self):
        result1 = self.Person.selectBy(id=1)
        result2 = result1.union(result1, unionAll=True)
        self.assertEquals([person.name for person in result2],
                          ["John Joe", "John Joe"])

    def test_result_set_except_(self):
        person = self.Person(id=3, name="John Moe")
        result1 = self.Person.select()
        result2 = self.Person.selectBy(id=2)
        result3 = result1.except_(result2, orderBy="name")
        self.assertEquals([person.name for person in result3],
                          ["John Joe", "John Moe"])

    def test_result_set_intersect(self):
        person = self.Person(id=3, name="John Moe")
        result1 = self.Person.select()
        result2 = self.Person.select(self.Person.id.is_in((2, 3)))
        result3 = result1.intersect(result2, orderBy="name")
        self.assertEquals([person.name for person in result3],
                          ["John Doe", "John Moe"])

    def test_result_set_prejoin(self):
        result = self.Person.select()
        self.assertEquals(result.prejoin(None), result) # Dummy.

    def test_result_set_prejoinClauseTables(self):
        result = self.Person.select()
        self.assertEquals(result.prejoinClauseTables(None), result) # Dummy.

    def test_table_dot_q(self):
        # Table.q.fieldname is a syntax used in SQLObject for
        # sqlbuilder expressions.  Storm can use the main properties
        # for this, so the Table.q syntax just returns those
        # properties:
        class Person(self.SQLObject):
            _idName = "name"
            _idType = unicode
            address = ForeignKey(foreignKey="Phone", dbName='address_id',
                                 notNull=True)

        # *.q.id points to the primary key, no matter its name.
        self.assertEquals(id(Person.q.id), id(Person.name))

        self.assertEquals(id(Person.q.name), id(Person.name))
        self.assertEquals(id(Person.q.address), id(Person.address))
        self.assertEquals(id(Person.q.addressID), id(Person.addressID))

        person = Person.get("John Joe")

        self.assertEquals(id(person.q.id), id(Person.name))
        self.assertEquals(id(person.q.name), id(Person.name))
        self.assertEquals(id(person.q.address), id(Person.address))
        self.assertEquals(id(person.q.addressID), id(Person.addressID))

    def test_set(self):
        class Person(self.Person):
            def set(self, **kw):
                kw["id"] += 1
                super(Person, self).set(**kw)
        person = Person(id=3, name="John Moe")
        self.assertEquals(person.id, 4)
        self.assertEquals(person.name, "John Moe")

    def test_CONTAINSSTRING(self):
        expr = CONTAINSSTRING(self.Person.q.name, "Do")
        result = self.Person.select(expr)
        self.assertEquals([person.name for person in result],
                          ["John Doe"])

        person.name = "Funny !%_ Name"

        expr = NOT(CONTAINSSTRING(self.Person.q.name, "!%_"))
        result = self.Person.select(expr)
        self.assertEquals([person.name for person in result],
                          ["John Joe"])