def test_update_sql_expr(self):
stmt = insert(self.table).values([{
"id": 1,
"bar": "ab"
}, {
"id": 2,
"bar": "b"
}])
stmt = stmt.on_duplicate_key_update(
bar=func.coalesce(stmt.inserted.bar),
baz=stmt.inserted.baz + "some literal",
)
expected_sql = (
"INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) ON "
"DUPLICATE KEY UPDATE bar = coalesce(VALUES(bar)), "
"baz = (concat(VALUES(baz), %s))")
self.assert_compile(
stmt,
expected_sql,
checkparams={
"id_m0": 1,
"bar_m0": "ab",
"id_m1": 2,
"bar_m1": "b",
"baz_1": "some literal",
},
)
def test_twelve(self):
t = self.tables.t
actual_ts = self.bind.scalar(func.current_timestamp()).replace(
tzinfo=None) - datetime.datetime(2012, 5, 10, 12, 15, 25)
self._test(
func.current_timestamp() -
func.coalesce(t.c.dtme, func.current_timestamp()),
{"day": actual_ts.days},
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, default=func.foo(1)),
Column(
"col2",
Integer,
default=select([func.coalesce(func.max(foo.c.id))]),
),
)
self.assert_compile(
t.insert(inline=True, values={}),
"INSERT INTO test (col1, col2) VALUES (foo(:foo_1), "
"(SELECT coalesce(max(foo.id)) AS coalesce_1 FROM "
"foo))",
)
def test_inline_defaults(self):
m = MetaData()
foo = Table("foo", m, Column("id", Integer))
t = Table(
"test",
m,
Column("col1", Integer, onupdate=func.foo(1)),
Column(
"col2",
Integer,
onupdate=select([func.coalesce(func.max(foo.c.id))]),
),
Column("col3", String(30)),
)
self.assert_compile(
t.update(inline=True, values={"col3": "foo"}),
"UPDATE test SET col1=foo(:foo_1), col2=(SELECT "
"coalesce(max(foo.id)) AS coalesce_1 FROM foo), "
"col3=:col3",
)
def test_generic_annotation(self):
fn = func.coalesce("x", "y")._annotate({"foo": "bar"})
self.assert_compile(fn, "coalesce(:coalesce_1, :coalesce_2)")
def test_five(self):
t = self.tables.t
self._test(
func.coalesce(t.c.dtme, func.current_timestamp()),
overrides={"epoch": 1336652125.0},
)