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
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
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
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"
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
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
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
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
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
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"