def test_unconsumed_names_values_dict(self):
t = table("t", column("x"), column("y"))
t2 = table("t2", column("q"), column("z"))
assert_raises_message(
exc.CompileError,
"Unconsumed column names: j",
t.update()
.values(x=5, j=7)
.values({t2.c.z: 5})
.where(t.c.x == t2.c.q)
.compile,
)
def test_unconsumed_names_kwargs(self):
t = table("t", column("x"), column("y"))
assert_raises_message(
exc.CompileError,
"Unconsumed column names: z",
t.insert().values(x=5, z=5).compile,
)
def test_insert_from_select_dont_mutate_raw_columns(self):
# test [ticket:3603]
from sqlalchemy_1_3 import table
table_ = table(
"mytable",
Column("foo", String),
Column("bar", String, default="baz"),
)
stmt = select([table_.c.foo])
insert = table_.insert().from_select(["foo"], stmt)
self.assert_compile(stmt, "SELECT mytable.foo FROM mytable")
self.assert_compile(
insert,
"INSERT INTO mytable (foo, bar) "
"SELECT mytable.foo, :bar AS anon_1 FROM mytable",
)
self.assert_compile(stmt, "SELECT mytable.foo FROM mytable")
self.assert_compile(
insert,
"INSERT INTO mytable (foo, bar) "
"SELECT mytable.foo, :bar AS anon_1 FROM mytable",
)
def test_legacy_typemap(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
with testing.expect_deprecated(
"The text.typemap parameter is deprecated"):
t = text(
"select id, name from user",
typemap=dict(id=Integer, name=String),
)
stmt = select([table1.c.myid
]).select_from(table1.join(t, table1.c.myid == t.c.id))
compiled = stmt.compile()
eq_(
compiled._create_result_map(),
{
"myid": (
"myid",
(table1.c.myid, "myid", "myid"),
table1.c.myid.type,
)
},
)
def test_unconsumed_names_kwargs_w_keys(self):
t = table("t", column("x"), column("y"))
assert_raises_message(
exc.CompileError,
"Unconsumed column names: j",
t.update().values(x=5, j=7).compile,
column_keys=["j"],
)
def test_anticipate_no_pk_lower_case_table(self):
t = table(
"t",
Column("id", Integer, primary_key=True, autoincrement=False),
Column("notpk", String(10), nullable=True),
)
with expect_warnings("Column 't.id' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(t.insert(),
"INSERT INTO t () VALUES ()",
params={})
def test_no_alias_construct(self):
a = table("a", column("x"))
assert_raises_message(
NotImplementedError,
"The Lateral class is not intended to be constructed directly. "
r"Please use the lateral\(\) standalone",
Lateral,
a,
"foo",
)
def test_bindparam_name_no_consume_error(self):
t = table("t", column("x"), column("y"))
# bindparam names don't get counted
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i, "INSERT INTO t (x) VALUES ((:param_1 + :x2))")
# even if in the params list
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i,
"INSERT INTO t (x) VALUES ((:param_1 + :x2))",
params={"x2": 1})
def test_binds_in_select(self):
t = table("t", column("a"), column("b"), column("c"))
@compiles(BindParameter)
def gen_bind(element, compiler, **kw):
return "BIND(%s)" % compiler.visit_bindparam(element, **kw)
self.assert_compile(
t.select().where(t.c.c == 5),
"SELECT t.a, t.b, t.c FROM t WHERE t.c = BIND(:c_1)",
use_default_dialect=True,
)
def test_no_alias_construct(self):
a = table("a", column("x"))
assert_raises_message(
NotImplementedError,
"The TableSample class is not intended to be constructed "
"directly. "
r"Please use the tablesample\(\) standalone",
TableSample,
a,
"foo",
)
def test_labels_no_collision(self):
t = table("foo", column("id"), column("foo_id"))
self.assert_compile(
t.update().where(t.c.id == 5),
"UPDATE foo SET id=:id, foo_id=:foo_id WHERE foo.id = :id_1",
)
self.assert_compile(
t.update().where(t.c.id == bindparam(key=t.c.id._label)),
"UPDATE foo SET id=:id, foo_id=:foo_id WHERE foo.id = :foo_id_1",
)
def test_select(self):
t1 = table("t1", column("c1"), column("c2"))
@compiles(Select, "sqlite")
def compile_(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(s1, "SELECT t1.c1, t1.c2 FROM t1")
from sqlalchemy_1_3.dialects.sqlite import base as sqlite
self.assert_compile(s1, "OVERRIDE", dialect=sqlite.dialect())
def test_unknown_mode(self):
t = table("t", column("c"))
with testing.expect_deprecated(
"The select.for_update parameter is deprecated and "
"will be removed in a future release."):
assert_raises_message(
exc.ArgumentError,
"Unknown for_update argument: 'unknown_mode'",
t.select,
t.c.c == 7,
for_update="unknown_mode",
)
def _assert_legacy(self, leg, read=False, nowait=False):
t = table("t", column("c"))
with testing.expect_deprecated(
"The select.for_update parameter is deprecated and "
"will be removed in a future release."):
s1 = select([t], for_update=leg)
if leg is False:
assert s1._for_update_arg is None
assert s1.for_update is None
else:
eq_(s1._for_update_arg.read, read)
eq_(s1._for_update_arg.nowait, nowait)
eq_(s1.for_update, leg)
def test_binds_in_dml(self):
t = table("t", column("a"), column("b"), column("c"))
@compiles(BindParameter)
def gen_bind(element, compiler, **kw):
return "BIND(%s)" % compiler.visit_bindparam(element, **kw)
self.assert_compile(
t.insert(),
"INSERT INTO t (a, b) VALUES (BIND(:a), BIND(:b))",
{
"a": 1,
"b": 2
},
use_default_dialect=True,
)
def test_binds_that_match_columns(self):
"""test bind params named after column names
replace the normal SET/VALUES generation."""
t = table("foo", column("x"), column("y"))
i = t.insert().values(x=3 + bindparam("x"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES ((:param_1 + :x))")
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x), :y)",
params={
"x": 1,
"y": 2
},
)
i = t.insert().values(x=bindparam("y"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES (:y)")
i = t.insert().values(x=bindparam("y"), y=5)
assert_raises(exc.CompileError, i.compile)
i = t.insert().values(x=3 + bindparam("y"), y=5)
assert_raises(exc.CompileError, i.compile)
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES ((:param_1 + :x2))")
self.assert_compile(i,
"INSERT INTO foo (x) VALUES ((:param_1 + :x2))",
params={})
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x2), :y)",
params={
"x": 1,
"y": 2
},
)
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x2), :y)",
params={
"x2": 1,
"y": 2
},
)
def test_callout_to_compiler(self):
class InsertFromSelect(ClauseElement):
def __init__(self, table, select):
self.table = table
self.select = select
@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
return "INSERT INTO %s (%s)" % (
compiler.process(element.table, asfrom=True),
compiler.process(element.select),
)
t1 = table("mytable", column("x"), column("y"), column("z"))
self.assert_compile(
InsertFromSelect(t1,
select([t1]).where(t1.c.x > 5)),
"INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z "
"FROM mytable WHERE mytable.x > :x_1)",
)
def test_binds_that_match_columns(self):
"""test bind params named after column names
replace the normal SET/VALUES generation."""
t = table("foo", column("x"), column("y"))
u = t.update().where(t.c.x == bindparam("x"))
assert_raises(exc.CompileError, u.compile)
self.assert_compile(u, "UPDATE foo SET WHERE foo.x = :x", params={})
assert_raises(exc.CompileError, u.values(x=7).compile)
self.assert_compile(
u.values(y=7), "UPDATE foo SET y=:y WHERE foo.x = :x"
)
assert_raises(
exc.CompileError, u.values(x=7).compile, column_keys=["x", "y"]
)
assert_raises(exc.CompileError, u.compile, column_keys=["x", "y"])
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x) WHERE foo.x = :x",
)
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x) WHERE foo.x = :x",
params={"x": 1},
)
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x), y=:y WHERE foo.x = :x",
params={"x": 1, "y": 2},
)
def test_annotations(self):
"""test that annotated clause constructs use the
decorated class' compiler.
"""
t1 = table("t1", column("c1"), column("c2"))
dispatch = Select._compiler_dispatch
try:
@compiles(Select)
def compile_(element, compiler, **kw):
return "OVERRIDE"
s1 = select([t1])
self.assert_compile(s1, "OVERRIDE")
self.assert_compile(s1._annotate({}), "OVERRIDE")
finally:
Select._compiler_dispatch = dispatch
if hasattr(Select, "_compiler_dispatcher"):
del Select._compiler_dispatcher
def test_legacy_setter(self):
t = table("t", column("c"))
s = select([t])
s.for_update = "nowait"
eq_(s._for_update_arg.nowait, True)
