def test_sql_expression_pk_noautoinc_returning(self):
# test that return_defaults() works with a primary key where we are
# sending a SQL expression, and we need to get the server-calculated
# value back. [ticket:3133]
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, autoincrement=False, primary_key=True),
Column("data", String),
)
stmt = table.insert().return_defaults().values(id=func.foobar())
returning_dialect = postgresql.dialect()
returning_dialect.implicit_returning = True
compiled = stmt.compile(dialect=returning_dialect,
column_keys=["data"])
eq_(compiled.postfetch, [])
eq_(compiled.returning, [table.c.id])
self.assert_compile(
stmt,
"INSERT INTO sometable (id, data) VALUES "
"(foobar(), %(data)s) RETURNING sometable.id",
checkparams={"data": "foo"},
params={"data": "foo"},
dialect=returning_dialect,
)
def test_insert_from_select_fn_defaults(self):
metadata = MetaData()
def foo(ctx):
return 12
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer, default=foo),
)
table1 = self.tables.mytable
sel = select([table1.c.myid]).where(table1.c.name == "foo")
ins = table.insert().from_select(["id"], sel)
self.assert_compile(
ins,
"INSERT INTO sometable (id, foo) SELECT "
"mytable.myid, :foo AS anon_1 "
"FROM mytable WHERE mytable.name = :name_1",
# value filled in at execution time
checkparams={
"name_1": "foo",
"foo": None
},
)
def test_server_default_absent_value(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer, server_default=func.foobar()),
)
values = [
{
"id": 1,
"data": "data1",
"foo": "plainfoo"
},
{
"id": 2,
"data": "data2"
},
{
"id": 3,
"data": "data3",
"foo": "otherfoo"
},
]
assert_raises_message(
exc.CompileError,
"INSERT value for column sometable.foo is explicitly rendered "
"as a boundparameter in the VALUES clause; a Python-side value or "
"SQL expression is required",
table.insert().values(values).compile,
)
def test_sql_expression_pk_autoinc_lastinserted(self):
# test that postfetch isn't invoked for a SQL expression
# in a primary key column. the DB either needs to support a lastrowid
# that can return it, or RETURNING. [ticket:3133]
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
)
stmt = table.insert().return_defaults().values(id=func.foobar())
compiled = stmt.compile(dialect=sqlite.dialect(), column_keys=["data"])
eq_(compiled.postfetch, [])
eq_(compiled.returning, [])
self.assert_compile(
stmt,
"INSERT INTO sometable (id, data) VALUES "
"(foobar(), ?)",
checkparams={"data": "foo"},
params={"data": "foo"},
dialect=sqlite.dialect(),
)
def test_inline_default(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer, default=func.foobar()),
)
self.assert_compile(
table.insert(values={}, inline=True),
"INSERT INTO sometable (foo) VALUES (foobar())",
)
self.assert_compile(
table.insert(inline=True),
"INSERT INTO sometable (foo) VALUES (foobar())",
params={},
)
def test_insert_literal_binds_sequence_notimplemented(self):
table = Table("x", MetaData(), Column("y", Integer, Sequence("y_seq")))
dialect = default.DefaultDialect()
dialect.supports_sequences = True
stmt = table.insert().values(myid=3, name="jack")
assert_raises(
NotImplementedError,
stmt.compile,
compile_kwargs=dict(literal_binds=True),
dialect=dialect,
)
def test_insert_from_select_no_defaults(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer, default=func.foobar()),
)
table1 = self.tables.mytable
sel = select([table1.c.myid]).where(table1.c.name == "foo")
ins = table.insert().from_select(["id"], sel, include_defaults=False)
self.assert_compile(
ins,
"INSERT INTO sometable (id) SELECT mytable.myid "
"FROM mytable WHERE mytable.name = :name_1",
checkparams={"name_1": "foo"},
)
def test_inline_default(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer, default=func.foobar()),
)
values = [
{
"id": 1,
"data": "data1"
},
{
"id": 2,
"data": "data2",
"foo": "plainfoo"
},
{
"id": 3,
"data": "data3"
},
]
checkparams = {
"id_m0": 1,
"id_m1": 2,
"id_m2": 3,
"data_m0": "data1",
"data_m1": "data2",
"data_m2": "data3",
"foo_m1": "plainfoo",
}
self.assert_compile(
table.insert().values(values),
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_m0)s, %(data_m0)s, foobar()), "
"(%(id_m1)s, %(data_m1)s, %(foo_m1)s), "
"(%(id_m2)s, %(data_m2)s, foobar())",
checkparams=checkparams,
dialect=postgresql.dialect(),
)
def test_insert_from_select_override_defaults(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer, default=12),
)
table1 = self.tables.mytable
sel = select([table1.c.myid,
table1.c.myid.label("q")]).where(table1.c.name == "foo")
ins = table.insert().from_select(["id", "foo"], sel)
self.assert_compile(
ins,
"INSERT INTO sometable (id, foo) SELECT "
"mytable.myid, mytable.myid AS q "
"FROM mytable WHERE mytable.name = :name_1",
checkparams={"name_1": "foo"},
)
def test_sql_functions(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer),
)
values = [
{
"id": 1,
"data": "foo",
"foo": func.foob()
},
{
"id": 2,
"data": "bar",
"foo": func.foob()
},
{
"id": 3,
"data": "bar",
"foo": func.bar()
},
{
"id": 4,
"data": "bar",
"foo": 15
},
{
"id": 5,
"data": "bar",
"foo": func.foob()
},
]
checkparams = {
"id_m0": 1,
"data_m0": "foo",
"id_m1": 2,
"data_m1": "bar",
"id_m2": 3,
"data_m2": "bar",
"id_m3": 4,
"data_m3": "bar",
"foo_m3": 15,
"id_m4": 5,
"data_m4": "bar",
}
self.assert_compile(
table.insert().values(values),
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_m0)s, %(data_m0)s, foob()), "
"(%(id_m1)s, %(data_m1)s, foob()), "
"(%(id_m2)s, %(data_m2)s, bar()), "
"(%(id_m3)s, %(data_m3)s, %(foo_m3)s), "
"(%(id_m4)s, %(data_m4)s, foob())",
checkparams=checkparams,
dialect=postgresql.dialect(),
)
def test_python_fn_default(self):
metadata = MetaData()
table = Table(
"sometable",
metadata,
Column("id", Integer, primary_key=True),
Column("data", String),
Column("foo", Integer, default=lambda: 10),
)
values = [
{
"id": 1,
"data": "data1"
},
{
"id": 2,
"data": "data2",
"foo": 15
},
{
"id": 3,
"data": "data3"
},
]
checkparams = {
"id_m0": 1,
"id_m1": 2,
"id_m2": 3,
"data_m0": "data1",
"data_m1": "data2",
"data_m2": "data3",
"foo": None, # evaluated later
"foo_m1": 15,
"foo_m2": None, # evaluated later
}
stmt = table.insert().values(values)
eq_(
dict([(k, v.type._type_affinity) for (k, v) in stmt.compile(
dialect=postgresql.dialect()).binds.items()]),
{
"foo": Integer,
"data_m2": String,
"id_m0": Integer,
"id_m2": Integer,
"foo_m1": Integer,
"data_m1": String,
"id_m1": Integer,
"foo_m2": Integer,
"data_m0": String,
},
)
self.assert_compile(
stmt,
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_m0)s, %(data_m0)s, %(foo)s), "
"(%(id_m1)s, %(data_m1)s, %(foo_m1)s), "
"(%(id_m2)s, %(data_m2)s, %(foo_m2)s)",
checkparams=checkparams,
dialect=postgresql.dialect(),
)