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(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_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_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_no_table_needs_pl(self):
Subset = self.classes.Subset
selectable = select([column("x"), column("y"), column("z")]).alias()
assert_raises_message(
sa.exc.ArgumentError,
"could not assemble any primary key columns",
mapper,
Subset,
selectable,
)
def test_no_tables(self):
Subset = self.classes.Subset
selectable = select([column("x"), column("y"), column("z")]).alias()
mapper(Subset, selectable, primary_key=[selectable.c.x])
self.assert_compile(
Session().query(Subset),
"SELECT anon_1.x AS anon_1_x, anon_1.y AS anon_1_y, "
"anon_1.z AS anon_1_z FROM (SELECT x, y, z) AS anon_1",
use_default_dialect=True,
)
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_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_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_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_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_insert_from_select_union(self):
mytable = self.tables.mytable
name = column("name")
description = column("desc")
sel = select([name,
mytable.c.description]).union(select([name,
description]))
ins = mytable.insert().from_select(
[mytable.c.name, mytable.c.description], sel)
self.assert_compile(
ins,
"INSERT INTO mytable (name, description) "
"SELECT name, mytable.description FROM mytable "
'UNION SELECT name, "desc"',
)
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_string_text_explicit_literal_binds(self):
# the literal experssion here coerces the right side to
# Unicode on Python 3 for plain string, test with unicode
# string just to confirm literal is doing this
self.assert_compile(
column("x", String()) == literal(util.u("foo")),
"x = N'foo'",
literal_binds=True,
)
def test_evaluate_unmapped_col(self):
User = self.classes.User
s = Session()
jill = s.query(User).get(3)
s.query(User).update({column("name"): "moonbeam"},
synchronize_session="evaluate")
eq_(jill.name, "jill")
s.expire(jill)
eq_(jill.name, "moonbeam")
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_collate(self):
self.assert_compile(column("foo").collate("utf8"), "foo COLLATE utf8")
self.assert_compile(
column("foo").collate("fr_FR"),
'foo COLLATE "fr_FR"',
dialect="postgresql",
)
self.assert_compile(
column("foo").collate("utf8_GERMAN_ci"),
"foo COLLATE `utf8_GERMAN_ci`",
dialect="mysql",
)
self.assert_compile(
column("foo").collate("SQL_Latin1_General_CP1_CI_AS"),
"foo COLLATE SQL_Latin1_General_CP1_CI_AS",
dialect="mssql",
)
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_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_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_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 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 _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_column(self):
class MyThingy(ColumnClause):
def __init__(self, arg=None):
super(MyThingy, self).__init__(arg or "MYTHINGY!")
@compiles(MyThingy)
def visit_thingy(thingy, compiler, **kw):
return ">>%s<<" % thingy.name
self.assert_compile(select([column("foo"), MyThingy()]),
"SELECT foo, >>MYTHINGY!<<")
self.assert_compile(
select([MyThingy("x"), MyThingy("y")]).where(MyThingy() == 5),
"SELECT >>x<<, >>y<< WHERE >>MYTHINGY!<< = :MYTHINGY!_1",
)
def test_stateful(self):
class MyThingy(ColumnClause):
def __init__(self):
super(MyThingy, self).__init__("MYTHINGY!")
@compiles(MyThingy)
def visit_thingy(thingy, compiler, **kw):
if not hasattr(compiler, "counter"):
compiler.counter = 0
compiler.counter += 1
return str(compiler.counter)
self.assert_compile(
select([column("foo"), MyThingy()]).order_by(desc(MyThingy())),
"SELECT foo, 1 ORDER BY 2 DESC",
)
self.assert_compile(
select([MyThingy(), MyThingy()]).where(MyThingy() == 5),
"SELECT 1, 2 WHERE 3 = :MYTHINGY!_1",
)
def test_string_text_literal_binds_explicit_unicode_right(self):
self.assert_compile(
column("x", String()) == util.u("foo"),
"x = 'foo'",
literal_binds=True,
)
def test_text_text_literal_binds(self):
self.assert_compile(column("x", Text()) == "foo",
"x = 'foo'",
literal_binds=True)
def test_select_for_update(self):
with testing.expect_deprecated(
"The select.for_update parameter is deprecated and "
"will be removed in a future release."):
select([column("x")], for_update=True)
def test_legacy_setter(self):
t = table("t", column("c"))
s = select([t])
s.for_update = "nowait"
eq_(s._for_update_arg.nowait, True)
def test_select_autocommit(self):
with testing.expect_deprecated(
"The select.autocommit parameter is deprecated and "
"will be removed in a future release."):
select([column("x")], autocommit=True)
