Ejemplo n.º 1
0
    def test_merge_update_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [
                dict(id=1, name='Ulf'),
                dict(id=2, name='Karl'),
            ]
        )
        config.db.execute(
            s.insert(),
            [
                dict(id=1, age=10),
                dict(id=2, age=20),
                dict(id=3, age=30),
            ]
        )

        m = merge(t, s, t.c.id == s.c.id).update(
                    values={t.c.age: s.c.age},
                    where=t.c.id == s.c.id
                )
        mr = config.db.execute(m)

        r = config.db.execute(t.select().where(t.c.id == 1)).first()
        assert r.age == 10
        r = config.db.execute(t.select().where(t.c.id == 2)).first()
        assert r.age == 20
        r = config.db.execute(t.select().where(t.c.id == 3)).fetchall()
        assert len(r) == 0
Ejemplo n.º 2
0
    def test_merge_update_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [
                dict(id=1, name='Ulf'),
                dict(id=2, name='Karl'),
            ]
        )
        config.db.execute(
            s.insert(),
            [
                dict(id=1, age=10),
                dict(id=2, age=20),
                dict(id=3, age=30),
            ]
        )

        m = merge(t, s, t.c.id == s.c.id).update(
                    values={t.c.age: s.c.age},
                    where=t.c.id == s.c.id
                )
        mr = config.db.execute(m)

        r = config.db.execute(t.select().where(t.c.id == 1)).first()
        assert r.age == 10
        r = config.db.execute(t.select().where(t.c.id == 2)).first()
        assert r.age == 20
        r = config.db.execute(t.select().where(t.c.id == 3)).fetchall()
        assert len(r) == 0
Ejemplo n.º 3
0
    def test_merge_delete_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [
                dict(id=1, name='Ulf'),
            ]
        )
        config.db.execute(
            s.insert(),
            [
                dict(id=1, age=10),
                dict(id=2, age=20),
                dict(id=3, age=2),
            ]
        )

        m = merge(t, s, t.c.id == s.c.id).insert(
                where=s.c.age > 5
            )
        config.db.execute(m)
        r = config.db.execute(t.select()).fetchall()
        assert len(r) == 2
Ejemplo n.º 4
0
    def test_merge_insert_computed_column(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(t.insert(), [dict(id=1, name="Ulf")])
        config.db.execute(s.insert(), [dict(id=1, age=10), dict(id=2, age=20)])

        source_expr = select(
            [
                (s.c.id * literal_column("20")).label("id"),
                (s.c.age * literal_column("20")).label("age"),
                literal_column("'Bernd'").label("name"),
            ]
        ).alias("source")

        # Expecting both rows of source_expr to be inserted
        m = merge(t, source_expr, t.c.id == source_expr.c.id).insert()
        config.db.execute(m)
        results = config.db.execute(t.select().where(t.c.name == "Bernd")).fetchall()
        assert len(results) == 2

        for r in results:
            assert r.id > 10
            assert r.age >= 10
            assert r.name == "Bernd"
Ejemplo n.º 5
0
    def test_merge_delete_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [
                dict(id=1, name='Ulf'),
            ]
        )
        config.db.execute(
            s.insert(),
            [
                dict(id=1, age=10),
                dict(id=2, age=20),
                dict(id=3, age=2),
            ]
        )

        m = merge(t, s, t.c.id == s.c.id).insert(
                where=s.c.age > 5
            )
        config.db.execute(m)
        r = config.db.execute(t.select()).fetchall()
        assert len(r) == 2
Ejemplo n.º 6
0
    def test_merge_update(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(t.insert(), [dict(id=1, age=None, name='Ulf')])
        config.db.execute(s.insert(), [dict(id=1, age=10)])

        m = merge(t, s, t.c.id == s.c.id).update()
        mr = config.db.execute(m)

        r = config.db.execute(t.select()).first()
        assert r.age == 10
Ejemplo n.º 7
0
    def test_merge_update_delete_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(t.insert(), [dict(id=1, name='Ulf')])
        config.db.execute(s.insert(), [dict(id=1, age=10)])

        m = merge(t, s, t.c.id == s.c.id).update(values={
            t.c.age: s.c.age
        }).delete(t.c.id == s.c.id)
        config.db.execute(m)
        r = config.db.execute(t.select()).fetchall()
        assert len(r) == 0
Ejemplo n.º 8
0
    def test_merge_update(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [dict(id=1, age=None, name='Ulf')]
        )
        config.db.execute(
            s.insert(),
            [dict(id=1, age=10)]
        )

        m = merge(t, s, t.c.id == s.c.id).update()
        mr = config.db.execute(m)

        r = config.db.execute(t.select()).first()
        assert r.age == 10
Ejemplo n.º 9
0
    def test_merge_update_delete_where(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(
            t.insert(),
            [dict(id=1, name='Ulf')]
        )
        config.db.execute(
            s.insert(),
            [dict(id=1, age=10)]
        )

        m = merge(t, s, t.c.id == s.c.id).update(
                values={t.c.age: s.c.age}
            ).delete(t.c.id == s.c.id)
        config.db.execute(m)
        r = config.db.execute(t.select()).fetchall()
        assert len(r) == 0
Ejemplo n.º 10
0
    def test_merge_update_computed_column(self):
        t = self.tables.t
        s = self.tables.s

        config.db.execute(t.insert(), [dict(id=1, name="Ulf")])
        config.db.execute(s.insert(), [dict(id=1, age=10), dict(id=2, age=20)])

        # Only id=1 will match, so only one row should be updated.
        source_expr = select([
            s.c.id.label("id"),
            (s.c.age * literal_column("20")).label("age"),
            literal_column("'Bernd'").label("name"),
        ]).alias("source")

        m = merge(t, source_expr, t.c.id == source_expr.c.id).update()
        mr = config.db.execute(m)

        results = config.db.execute(
            t.select().where(t.c.name == "Bernd")).fetchall()
        assert len(results) == 1
        r = results[0]
        assert r.age == 200
        assert r.name == "Bernd"