def test_update_to_expression_two(self, randomized_param_order_update, t,
idx_to_value):
"""test update from an expression.
this logic is triggered currently by a left side that doesn't
have a key. The current supported use case is updating the index
of a PostgreSQL ARRAY type.
"""
dialect = default.StrCompileDialect()
dialect.paramstyle = "qmark"
dialect.positional = True
stmt = t.update().ordered_values(*[(col[idx], val)
for col, idx, val in idx_to_value])
self.assert_compile(
stmt,
"UPDATE foo SET %s" %
(", ".join("%s[?]=?" % col.key for col, idx, val in idx_to_value)),
dialect=dialect,
checkpositional=tuple(
itertools.chain.from_iterable(
(idx, val) for col, idx, val in idx_to_value)),
)
def test_update_bound_ordering(self):
"""test that bound parameters between the UPDATE and FROM clauses
order correctly in different SQL compilation scenarios.
"""
table1 = self.tables.mytable
table2 = self.tables.myothertable
sel = select(table2).where(table2.c.otherid == 5).alias()
upd = (table1.update().where(table1.c.name == sel.c.othername).values(
name="foo"))
dialect = default.StrCompileDialect()
dialect.positional = True
self.assert_compile(
upd,
"UPDATE mytable SET name=:name FROM (SELECT "
"myothertable.otherid AS otherid, "
"myothertable.othername AS othername "
"FROM myothertable "
"WHERE myothertable.otherid = :otherid_1) AS anon_1 "
"WHERE mytable.name = anon_1.othername",
checkpositional=("foo", 5),
dialect=dialect,
)
self.assert_compile(
upd,
"UPDATE mytable, (SELECT myothertable.otherid AS otherid, "
"myothertable.othername AS othername "
"FROM myothertable "
"WHERE myothertable.otherid = %s) AS anon_1 SET mytable.name=%s "
"WHERE mytable.name = anon_1.othername",
checkpositional=(5, "foo"),
dialect=mysql.dialect(),
)
def test_update_to_expression_two(self):
"""test update from an expression.
this logic is triggered currently by a left side that doesn't
have a key. The current supported use case is updating the index
of a PostgreSQL ARRAY type.
"""
from sqlalchemy import ARRAY
t = table(
"foo",
column("data1", ARRAY(Integer)),
column("data2", ARRAY(Integer)),
)
stmt = t.update().ordered_values(
(t.c.data1[5], 7), (t.c.data2[10], 18)
)
dialect = default.StrCompileDialect()
dialect.paramstyle = "qmark"
dialect.positional = True
self.assert_compile(
stmt,
"UPDATE foo SET data1[?]=?, data2[?]=?",
dialect=dialect,
checkpositional=(5, 7, 10, 18),
)
def test_autocorrelate_error(self):
table1, table2 = self.tables.mytable, self.tables.myothertable
stmt = table1.delete().where(table1.c.myid == table2.c.otherid).where(
~exists().where(table2.c.otherid == table1.c.myid).where(
table2.c.othername == 'x'))
assert_raises_message(
exc.InvalidRequestError,
".*returned no FROM clauses due to auto-correlation.*",
stmt.compile,
dialect=default.StrCompileDialect())
def test_autocorrelate_error(self):
users, addresses = self.tables.users, self.tables.addresses
stmt = users.update().values(name="newname").where(
users.c.id == addresses.c.user_id).where(~exists().where(
addresses.c.user_id == users.c.id).where(
addresses.c.email_address == 'foo'))
assert_raises_message(
exc.InvalidRequestError,
".*returned no FROM clauses due to auto-correlation.*",
stmt.compile,
dialect=default.StrCompileDialect())
def randomized_param_order_update(self):
from sqlalchemy.sql.dml import UpdateDMLState
super_process_ordered_values = UpdateDMLState._process_ordered_values
# this fixture is needed for Python 3.6 and above to work around
# dictionaries being insert-ordered. in python 2.7 the previous
# logic fails pretty easily without this fixture.
def _process_ordered_values(self, statement):
super_process_ordered_values(self, statement)
tuples = list(self._dict_parameters.items())
random.shuffle(tuples)
self._dict_parameters = dict(tuples)
dialect = default.StrCompileDialect()
dialect.paramstyle = "qmark"
dialect.positional = True
with mock.patch.object(UpdateDMLState, "_process_ordered_values",
_process_ordered_values):
yield
def test_update_to_expression_three(self):
# this test is from test_defaults but exercises a particular
# parameter ordering issue
metadata = MetaData()
q = Table(
"q",
metadata,
Column("x", Integer, default=2),
Column("y", Integer, onupdate=5),
Column("z", Integer),
)
p = Table(
"p",
metadata,
Column("s", Integer),
Column("t", Integer),
Column("u", Integer, onupdate=1),
)
cte = (
q.update().where(q.c.z == 1).values(x=7).returning(q.c.z).cte("c")
)
stmt = select([p.c.s, cte.c.z]).where(p.c.s == cte.c.z)
dialect = default.StrCompileDialect()
dialect.paramstyle = "qmark"
dialect.positional = True
self.assert_compile(
stmt,
"WITH c AS (UPDATE q SET x=?, y=? WHERE q.z = ? RETURNING q.z) "
"SELECT p.s, c.z FROM p, c WHERE p.s = c.z",
checkpositional=(7, None, 1),
dialect=dialect,
)
def assert_compile(self,
clause,
result,
params=None,
checkparams=None,
dialect=None,
checkpositional=None,
check_prefetch=None,
use_default_dialect=False,
allow_dialect_select=False,
literal_binds=False,
schema_translate_map=None):
if use_default_dialect:
dialect = default.DefaultDialect()
elif allow_dialect_select:
dialect = None
else:
if dialect is None:
dialect = getattr(self, '__dialect__', None)
if dialect is None:
dialect = config.db.dialect
elif dialect == 'default':
dialect = default.DefaultDialect()
elif dialect == 'default_enhanced':
dialect = default.StrCompileDialect()
elif isinstance(dialect, util.string_types):
dialect = url.URL(dialect).get_dialect()()
kw = {}
compile_kwargs = {}
if schema_translate_map:
kw['schema_translate_map'] = schema_translate_map
if params is not None:
kw['column_keys'] = list(params)
if literal_binds:
compile_kwargs['literal_binds'] = True
if isinstance(clause, orm.Query):
context = clause._compile_context()
context.statement.use_labels = True
clause = context.statement
if compile_kwargs:
kw['compile_kwargs'] = compile_kwargs
c = clause.compile(dialect=dialect, **kw)
param_str = repr(getattr(c, 'params', {}))
if util.py3k:
param_str = param_str.encode('utf-8').decode('ascii', 'ignore')
print(("\nSQL String:\n" + util.text_type(c) +
param_str).encode('utf-8'))
else:
print("\nSQL String:\n" + util.text_type(c).encode('utf-8') +
param_str)
cc = re.sub(r'[\n\t]', '', util.text_type(c))
eq_(cc, result, "%r != %r on dialect %r" % (cc, result, dialect))
if checkparams is not None:
eq_(c.construct_params(params), checkparams)
if checkpositional is not None:
p = c.construct_params(params)
eq_(tuple([p[x] for x in c.positiontup]), checkpositional)
if check_prefetch is not None:
eq_(c.prefetch, check_prefetch)
def assert_compile(
self,
clause,
result,
params=None,
checkparams=None,
dialect=None,
checkpositional=None,
check_prefetch=None,
use_default_dialect=False,
allow_dialect_select=False,
literal_binds=False,
schema_translate_map=None,
):
if use_default_dialect:
dialect = default.DefaultDialect()
elif allow_dialect_select:
dialect = None
else:
if dialect is None:
dialect = getattr(self, "__dialect__", None)
if dialect is None:
dialect = config.db.dialect
elif dialect == "default":
dialect = default.DefaultDialect()
elif dialect == "default_enhanced":
dialect = default.StrCompileDialect()
elif isinstance(dialect, util.string_types):
dialect = url.URL(dialect).get_dialect()()
kw = {}
compile_kwargs = {}
if schema_translate_map:
kw["schema_translate_map"] = schema_translate_map
if params is not None:
kw["column_keys"] = list(params)
if literal_binds:
compile_kwargs["literal_binds"] = True
if isinstance(clause, orm.Query):
context = clause._compile_context()
context.statement.use_labels = True
clause = context.statement
elif isinstance(clause, orm.persistence.BulkUD):
with mock.patch.object(clause, "_execute_stmt") as stmt_mock:
clause.exec_()
clause = stmt_mock.mock_calls[0][1][0]
if compile_kwargs:
kw["compile_kwargs"] = compile_kwargs
c = clause.compile(dialect=dialect, **kw)
param_str = repr(getattr(c, "params", {}))
if util.py3k:
param_str = param_str.encode("utf-8").decode("ascii", "ignore")
print(("\nSQL String:\n" + util.text_type(c) +
param_str).encode("utf-8"))
else:
print("\nSQL String:\n" + util.text_type(c).encode("utf-8") +
param_str)
cc = re.sub(r"[\n\t]", "", util.text_type(c))
eq_(cc, result, "%r != %r on dialect %r" % (cc, result, dialect))
if checkparams is not None:
eq_(c.construct_params(params), checkparams)
if checkpositional is not None:
p = c.construct_params(params)
eq_(tuple([p[x] for x in c.positiontup]), checkpositional)
if check_prefetch is not None:
eq_(c.prefetch, check_prefetch)