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_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_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_match_compile_kw(self):
expr = literal("x").match(literal("y"))
self.assert_compile(
expr,
"MATCH ('x') AGAINST ('y' IN BOOLEAN MODE)",
literal_binds=True,
)
def test_literal_binds_plain(self):
m = MetaData()
t = Table(
"t",
m,
Column("x", Integer, server_default=literal("a") + literal("b")),
)
self.assert_compile(CreateTable(t),
"CREATE TABLE t (x INTEGER DEFAULT 'a' || 'b')")
def test_seven(self):
self._test(
literal(datetime.timedelta(seconds=10)) -
literal(datetime.timedelta(seconds=10)),
"all",
overrides={
"hour": 0,
"minute": 0,
"month": 0,
"year": 0,
"day": 0,
"epoch": 0,
},
)
def __clause_element__(self):
# helper method for SQLAlchemy to interpret
# the Amount object as a SQL element
if isinstance(self.amount, (float, int, Decimal)):
return literal(self.amount)
else:
return self.amount
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_assorted(self):
table1 = table("mytable", column("myid", Integer))
table2 = table("myothertable", column("otherid", Integer))
# test an expression with a function
self.assert_compile(
func.lala(3, 4, literal("five"), table1.c.myid) * table2.c.otherid,
"lala(:lala_1, :lala_2, :param_1, mytable.myid) * "
"myothertable.otherid",
)
# test it in a SELECT
self.assert_compile(
select([func.count(table1.c.myid)]),
"SELECT count(mytable.myid) AS count_1 FROM mytable",
)
# test a "dotted" function name
self.assert_compile(
select([func.foo.bar.lala(table1.c.myid)]),
"SELECT foo.bar.lala(mytable.myid) AS lala_1 FROM mytable",
)
# test the bind parameter name with a "dotted" function name is
# only the name (limits the length of the bind param name)
self.assert_compile(
select([func.foo.bar.lala(12)]),
"SELECT foo.bar.lala(:lala_2) AS lala_1",
)
# test a dotted func off the engine itself
self.assert_compile(func.lala.hoho(7), "lala.hoho(:hoho_1)")
# test None becomes NULL
self.assert_compile(
func.my_func(1, 2, None, 3),
"my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
)
# test pickling
self.assert_compile(
util.pickle.loads(util.pickle.dumps(func.my_func(1, 2, None, 3))),
"my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
)
# assert func raises AttributeError for __bases__ attribute, since
# its not a class fixes pydoc
try:
func.__bases__
assert False
except AttributeError:
assert True
def test_strict_binds(self):
"""test the 'strict' compiler binds."""
from sqlalchemy_1_3.dialects.mssql.base import MSSQLStrictCompiler
mxodbc_dialect = mxodbc.dialect()
mxodbc_dialect.statement_compiler = MSSQLStrictCompiler
t = table("sometable", column("foo"))
for expr, compiled in [
(
select([literal("x"), literal("y")]),
"SELECT 'x' AS anon_1, 'y' AS anon_2",
),
(
select([t]).where(t.c.foo.in_(["x", "y", "z"])),
"SELECT sometable.foo FROM sometable WHERE sometable.foo "
"IN ('x', 'y', 'z')",
),
(t.c.foo.in_([None]), "sometable.foo IN (NULL)"),
]:
self.assert_compile(expr, compiled, dialect=mxodbc_dialect)
def test_percent_signs_literal_binds(self):
stmt = select([literal("percent % signs %%")])
self.assert_compile(
stmt,
"SELECT 'percent % signs %%' AS anon_1",
dialect="sqlite",
literal_binds=True,
)
self.assert_compile(
stmt,
"SELECT 'percent %% signs %%%%' AS anon_1",
dialect="mysql",
literal_binds=True,
)
def test_quoted_name_bindparam_ok(self):
from sqlalchemy_1_3.sql.elements import quoted_name
with testing.db.connect() as conn:
eq_(
conn.scalar(
select(
[
cast(
literal(quoted_name("some_name", False)),
String,
)
]
)
),
"some_name",
)
def test_concat_compile_kw(self):
expr = literal("x", type_=String) + literal("y", type_=String)
self.assert_compile(expr, "concat('x', 'y')", literal_binds=True)
def test_insert_values_col_expression(self):
with testing.db.connect() as conn:
conn.execute(cattable.insert().values({cattable.c.id: literal(5)}))
eq_(conn.scalar(select([cattable.c.id])), 5)
def test_literal_binds_w_quotes(self):
m = MetaData()
t = Table("t", m, Column("x", Integer,
server_default=literal("5 ' 8")))
self.assert_compile(CreateTable(t),
"""CREATE TABLE t (x INTEGER DEFAULT '5 '' 8')""")
def test_literal_default_no_label(self):
self._run_test(default=literal("INT_1", type_=self.MyInteger))
