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_generic_now(self):
assert isinstance(func.now().type, sqltypes.DateTime)
for ret, dialect in [
("CURRENT_TIMESTAMP", sqlite.dialect()),
("now()", postgresql.dialect()),
("now()", mysql.dialect()),
("CURRENT_TIMESTAMP", oracle.dialect()),
]:
self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self):
assert func.random().type == sqltypes.NULLTYPE
assert isinstance(func.random(type_=Integer).type, Integer)
for ret, dialect in [
("random()", sqlite.dialect()),
("random()", postgresql.dialect()),
("rand()", mysql.dialect()),
("random()", oracle.dialect()),
]:
self.assert_compile(func.random(), ret, dialect=dialect)
def test_unconsumed_names_values_dict(self):
table1 = self.tables.mytable
checkparams = {"myid": 3, "name": "jack", "unknowncol": "oops"}
stmt = insert(table1, values=checkparams)
assert_raises_message(
exc.CompileError,
"Unconsumed column names: unknowncol",
stmt.compile,
dialect=postgresql.dialect(),
)
def test_anticipate_no_pk_non_composite_pk_implicit_returning(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True, autoincrement=False),
Column("q", Integer),
)
d = postgresql.dialect()
d.implicit_returning = True
with expect_warnings("Column 't.x' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(
t.insert(),
"INSERT INTO t (q) VALUES (%(q)s)",
params={"q": 5},
dialect=d,
)
def test_anticipate_no_pk_composite_pk_prefetch(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True),
Column("y", Integer, primary_key=True),
)
d = postgresql.dialect()
d.implicit_returning = False
with expect_warnings("Column 't.y' is marked as a member.*"
"Note that as of SQLAlchemy 1.1,"):
self.assert_compile(
t.insert(),
"INSERT INTO t (x) VALUES (%(x)s)",
params={"x": 5},
dialect=d,
)
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_seq(self):
m = MetaData()
t1 = Table(
"t",
m,
Column("id", Integer, Sequence("id_seq"), primary_key=True),
Column("data", String),
)
stmt = t1.insert().from_select(("data", ), select([t1.c.data]))
self.assert_compile(
stmt,
"INSERT INTO t (data, id) SELECT t.data, "
"nextval('id_seq') AS next_value_1 FROM t",
dialect=postgresql.dialect(),
)
def test_unresolvable_distinct_label(self):
from sqlalchemy_1_3.dialects import postgresql
stmt = select([table1.c.myid.label("foo")]).distinct("not a label")
self._test_exception(stmt, "not a label", dialect=postgresql.dialect())
class InsertImplicitReturningTest(_InsertTestBase, fixtures.TablesTest,
AssertsCompiledSQL):
__dialect__ = postgresql.dialect(implicit_returning=True)
def test_insert_select(self):
table1 = self.tables.mytable
sel = select([table1.c.myid,
table1.c.name]).where(table1.c.name == "foo")
ins = self.tables.myothertable.insert().from_select(
("otherid", "othername"), sel)
self.assert_compile(
ins,
"INSERT INTO myothertable (otherid, othername) "
"SELECT mytable.myid, mytable.name FROM mytable "
"WHERE mytable.name = %(name_1)s",
checkparams={"name_1": "foo"},
)
def test_insert_select_return_defaults(self):
table1 = self.tables.mytable
sel = select([table1.c.myid,
table1.c.name]).where(table1.c.name == "foo")
ins = (self.tables.myothertable.insert().from_select(
("otherid", "othername"),
sel).return_defaults(self.tables.myothertable.c.otherid))
self.assert_compile(
ins,
"INSERT INTO myothertable (otherid, othername) "
"SELECT mytable.myid, mytable.name FROM mytable "
"WHERE mytable.name = %(name_1)s",
checkparams={"name_1": "foo"},
)
def test_insert_multiple_values(self):
ins = self.tables.myothertable.insert().values([{
"othername": "foo"
}, {
"othername": "bar"
}])
self.assert_compile(
ins,
"INSERT INTO myothertable (othername) "
"VALUES (%(othername_m0)s), "
"(%(othername_m1)s)",
checkparams={
"othername_m1": "bar",
"othername_m0": "foo"
},
)
def test_insert_multiple_values_literal_binds(self):
ins = self.tables.myothertable.insert().values([{
"othername": "foo"
}, {
"othername": "bar"
}])
self.assert_compile(
ins,
"INSERT INTO myothertable (othername) VALUES ('foo'), ('bar')",
checkparams={},
literal_binds=True,
)
def test_insert_multiple_values_return_defaults(self):
# TODO: not sure if this should raise an
# error or what
ins = (self.tables.myothertable.insert().values([{
"othername": "foo"
}, {
"othername": "bar"
}]).return_defaults(self.tables.myothertable.c.otherid))
self.assert_compile(
ins,
"INSERT INTO myothertable (othername) "
"VALUES (%(othername_m0)s), "
"(%(othername_m1)s)",
checkparams={
"othername_m1": "bar",
"othername_m0": "foo"
},
)
def test_insert_single_list_values(self):
ins = self.tables.myothertable.insert().values([{"othername": "foo"}])
self.assert_compile(
ins,
"INSERT INTO myothertable (othername) "
"VALUES (%(othername_m0)s)",
checkparams={"othername_m0": "foo"},
)
def test_insert_single_element_values(self):
ins = self.tables.myothertable.insert().values({"othername": "foo"})
self.assert_compile(
ins,
"INSERT INTO myothertable (othername) "
"VALUES (%(othername)s) RETURNING myothertable.otherid",
checkparams={"othername": "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(),
)