def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def test_update_11(self):
table1 = self.tables.mytable
values = {
table1.c.name: table1.c.name + "lala",
table1.c.myid: func.do_stuff(table1.c.myid, literal("hoho")),
}
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
values=values,
),
"UPDATE mytable "
"SET "
"myid=do_stuff(mytable.myid, :param_1), "
"name=(mytable.name || :name_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def test_update_4(self):
table1 = self.tables.mytable
self.assert_compile(
update(table1, values={table1.c.name: table1.c.myid}),
"UPDATE mytable SET name=mytable.myid",
)
def test_update_ordered_parameters_2(self):
table1 = self.tables.mytable
# Confirm that we can pass values as list value pairs
# note these are ordered *differently* from table.c
values = [
(table1.c.name, table1.c.name + "lala"),
("description", "some desc"),
(table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))),
]
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
preserve_parameter_order=True,
).values(values),
"UPDATE mytable "
"SET "
"name=(mytable.name || :name_1), "
"description=:description, "
"myid=do_stuff(mytable.myid, :param_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def test_update_ordereddict(self):
table1 = self.tables.mytable
# Confirm that ordered dicts are treated as normal dicts,
# columns sorted in table order
values = util.OrderedDict(
(
(table1.c.name, table1.c.name + "lala"),
(table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))),
)
)
self.assert_compile(
update(
table1,
(table1.c.myid == func.hoho(4))
& (
table1.c.name
== literal("foo") + table1.c.name + literal("lala")
),
values=values,
),
"UPDATE mytable "
"SET "
"myid=do_stuff(mytable.myid, :param_1), "
"name=(mytable.name || :name_1) "
"WHERE "
"mytable.myid = hoho(:hoho_1) AND "
"mytable.name = :param_2 || mytable.name || :param_3",
)
def test_update_3(self):
table1 = self.tables.mytable
self.assert_compile(
update(table1, table1.c.myid == 7),
"UPDATE mytable SET name=:name WHERE mytable.myid = :myid_1",
params={"name": "fred"},
)
def test_update_8(self):
table1 = self.tables.mytable
self.assert_compile(
update(table1, table1.c.myid == 12),
"UPDATE mytable SET myid=:myid WHERE mytable.myid = :myid_1",
params={"myid": 18},
checkparams={"myid": 18, "myid_1": 12},
)
def test_update_7(self):
table1 = self.tables.mytable
self.assert_compile(
update(table1, table1.c.myid == 12, values={table1.c.myid: 9}),
"UPDATE mytable "
"SET myid=:myid, description=:description "
"WHERE mytable.myid = :myid_1",
params={"myid_1": 12, "myid": 9, "description": "test"},
)
def _assert_b_is_locked(self, should_be_locked):
B = self.classes.B
with testing.db.begin() as alt_trans:
alt_trans.execute("set innodb_lock_wait_timeout=1")
# set x/y > 10
try:
alt_trans.execute(update(B).values(x=15, y=19))
except (exc.InternalError, exc.OperationalError) as err:
assert "Lock wait timeout exceeded" in str(err)
assert should_be_locked
else:
assert not should_be_locked
def test_update_5(self):
table1 = self.tables.mytable
self.assert_compile(
update(
table1,
whereclause=table1.c.name == bindparam("crit"),
values={table1.c.name: "hi"},
),
"UPDATE mytable SET name=:name WHERE mytable.name = :crit",
params={"crit": "notthere"},
checkparams={"crit": "notthere", "name": "hi"},
)
def test_alias_two_mysql(self):
table1 = self.tables.mytable
talias1 = table1.alias("t1")
self.assert_compile(
update(talias1, table1.c.myid == 7).values(
{table1.c.name: "fred"}
),
"UPDATE mytable AS t1, mytable SET mytable.name=%s "
"WHERE mytable.myid = %s",
checkparams={"mytable_name": "fred", "myid_1": 7},
dialect="mysql",
)
def test_update_from_join_mysql_no_whereclause_one(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).values(name="newname"),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=%s",
checkparams={"name": "newname"},
dialect=mysql.dialect(),
)
def test_update_from_join_mysql_no_whereclause_two(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j).values({users.c.name: addresses.c.email_address}),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=addresses.email_address",
checkparams={},
dialect=mysql.dialect(),
)
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description",
)
u = (update(table1, values=dict(name="foo")).returning(table1).where(
table1.c.name == "bar"))
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description WHERE mytable.name = "
":name_1",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"LEN(inserted.name) AS length_1",
)
def test_correlated_update_three(self):
table1 = self.tables.mytable
table2 = self.tables.myothertable
# test against a regular constructed subquery
s = select([table2], table2.c.otherid == table1.c.myid)
u = update(table1, table1.c.name == "jack", values={table1.c.name: s})
self.assert_compile(
u,
"UPDATE mytable SET name=(SELECT myothertable.otherid, "
"myothertable.othername FROM myothertable WHERE "
"myothertable.otherid = mytable.myid) "
"WHERE mytable.name = :name_1",
)
def test_correlated_update_four(self):
table1 = self.tables.mytable
table2 = self.tables.myothertable
# test a non-correlated WHERE clause
s = select([table2.c.othername], table2.c.otherid == 7)
u = update(table1, table1.c.name == s)
self.assert_compile(
u,
"UPDATE mytable SET myid=:myid, name=:name, "
"description=:description WHERE mytable.name = "
"(SELECT myothertable.othername FROM myothertable "
"WHERE myothertable.otherid = :otherid_1)",
)
def test_update_10(self):
table1 = self.tables.mytable
v1 = {table1.c.name: table1.c.myid}
v2 = {table1.c.name: table1.c.name + "foo"}
self.assert_compile(
update(table1, table1.c.myid == 12, values=v1).values(v2),
"UPDATE mytable "
"SET "
"name=(mytable.name || :name_1), "
"description=:description "
"WHERE mytable.myid = :myid_1",
params={"description": "test"},
)
def test_alias_one(self):
table1 = self.tables.mytable
talias1 = table1.alias("t1")
# this case is nonsensical. the UPDATE is entirely
# against the alias, but we name the table-bound column
# in values. The behavior here isn't really defined
self.assert_compile(
update(talias1, talias1.c.myid == 7).values(
{table1.c.name: "fred"}
),
"UPDATE mytable AS t1 "
"SET name=:name "
"WHERE t1.myid = :myid_1",
)
def test_update_6(self):
table1 = self.tables.mytable
self.assert_compile(
update(
table1,
table1.c.myid == 12,
values={table1.c.name: table1.c.myid},
),
"UPDATE mytable "
"SET name=mytable.myid, description=:description "
"WHERE mytable.myid = :myid_1",
params={"description": "test"},
checkparams={"description": "test", "myid_1": 12},
)
def test_correlated_update_two(self):
table1 = self.tables.mytable
mt = table1.alias()
u = update(
table1,
values={
table1.c.name: select([mt.c.name], mt.c.myid == table1.c.myid)
},
)
self.assert_compile(
u,
"UPDATE mytable SET name=(SELECT mytable_1.name FROM "
"mytable AS mytable_1 WHERE "
"mytable_1.myid = mytable.myid)",
)
def test_correlated_update_one(self):
table1 = self.tables.mytable
# test against a straight text subquery
u = update(
table1,
values={
table1.c.name: text(
"(select name from mytable where id=mytable.id)"
)
},
)
self.assert_compile(
u,
"UPDATE mytable SET name=(select name from mytable "
"where id=mytable.id)",
)
def test_update_from_join_mysql_whereclause(self):
users, addresses = self.tables.users, self.tables.addresses
j = users.join(addresses)
self.assert_compile(
update(j)
.values(name="newname")
.where(addresses.c.email_address == "e1"),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"SET users.name=%s "
"WHERE "
"addresses.email_address = %s",
checkparams={"email_address_1": "e1", "name": "newname"},
dialect=mysql.dialect(),
)
def test_update_from_join_mysql_no_whereclause_three(self):
users, addresses, dingalings = (
self.tables.users,
self.tables.addresses,
self.tables.dingalings,
)
j = users.join(addresses).join(dingalings)
self.assert_compile(
update(j).values({users.c.name: dingalings.c.id}),
""
"UPDATE users "
"INNER JOIN addresses ON users.id = addresses.user_id "
"INNER JOIN dingalings ON addresses.id = dingalings.address_id "
"SET users.name=dingalings.id",
checkparams={},
dialect=mysql.dialect(),
)
def test_alias_two(self):
table1 = self.tables.mytable
talias1 = table1.alias("t1")
# Here, compared to
# test_alias_one(), here we actually have UPDATE..FROM,
# which is causing the "table1.c.name" param to be handled
# as an "extra table", hence we see the full table name rendered.
self.assert_compile(
update(talias1, table1.c.myid == 7).values(
{table1.c.name: "fred"}
),
"UPDATE mytable AS t1 "
"SET name=:mytable_name "
"FROM mytable "
"WHERE mytable.myid = :myid_1",
checkparams={"mytable_name": "fred", "myid_1": 7},
)
def test_exec_join_multitable(self):
users, addresses = self.tables.users, self.tables.addresses
values = {addresses.c.email_address: "updated", users.c.name: "ed2"}
testing.db.execute(
update(users.join(addresses))
.values(values)
.where(users.c.name == "ed")
)
expected = [
(1, 7, "x", "*****@*****.**"),
(2, 8, "x", "updated"),
(3, 8, "x", "updated"),
(4, 8, "x", "updated"),
(5, 9, "x", "*****@*****.**"),
]
self._assert_addresses(addresses, expected)
expected = [(7, "jack"), (8, "ed2"), (9, "fred"), (10, "chuck")]
self._assert_users(users, expected)
