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)