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_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'},
]
checkparams = {
'id_0': 1,
'id_1': 2,
'id_2': 3,
'data_0': 'data1',
'data_1': 'data2',
'data_2': 'data3',
'foo_0': 'plainfoo',
'foo_2': 'otherfoo',
}
# note the effect here is that the first set of params
# takes effect for the rest of them, when one is absent
self.assert_compile(table.insert().values(values),
'INSERT INTO sometable (id, data, foo) VALUES '
'(%(id_0)s, %(data_0)s, %(foo_0)s), '
'(%(id_1)s, %(data_1)s, %(foo_0)s), '
'(%(id_2)s, %(data_2)s, %(foo_2)s)',
checkparams=checkparams, dialect=postgresql.dialect())
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_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_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_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_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_0': 1,
'id_1': 2,
'id_2': 3,
'data_0': 'data1',
'data_1': 'data2',
'data_2': 'data3',
'foo_1': 'plainfoo',
}
self.assert_compile(
table.insert().values(values),
'INSERT INTO sometable (id, data, foo) VALUES '
'(%(id_0)s, %(data_0)s, foobar()), '
'(%(id_1)s, %(data_1)s, %(foo_1)s), '
'(%(id_2)s, %(data_2)s, foobar())',
checkparams=checkparams,
dialect=postgresql.dialect())
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'},
]
checkparams = {
'id_0': 1,
'id_1': 2,
'id_2': 3,
'data_0': 'data1',
'data_1': 'data2',
'data_2': 'data3',
'foo_0': 'plainfoo',
'foo_2': 'otherfoo',
}
# note the effect here is that the first set of params
# takes effect for the rest of them, when one is absent
self.assert_compile(
table.insert().values(values),
'INSERT INTO sometable (id, data, foo) VALUES '
'(%(id_0)s, %(data_0)s, %(foo_0)s), '
'(%(id_1)s, %(data_1)s, %(foo_0)s), '
'(%(id_2)s, %(data_2)s, %(foo_2)s)',
checkparams=checkparams,
dialect=postgresql.dialect())
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_server_default(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"},
{"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",
}
self.assert_compile(
table.insert().values(values),
"INSERT INTO sometable (id, data) VALUES "
"(%(id_m0)s, %(data_m0)s), "
"(%(id_m1)s, %(data_m1)s), "
"(%(id_m2)s, %(data_m2)s)",
checkparams=checkparams,
dialect=postgresql.dialect(),
)
def test_query_expr(self):
(User, ) = self.classes("User")
self._run_cache_key_fixture(
lambda: (
with_expression(User.name, true()),
with_expression(User.name, null()),
with_expression(User.name, func.foobar()),
with_expression(User.name, User.name == "test"),
Load(User).with_expression(User.name, true()),
Load(User).with_expression(User.name, null()),
Load(User).with_expression(User.name, func.foobar()),
Load(User).with_expression(User.name, User.name == "test"),
),
compare_values=True,
)
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(),
"INSERT INTO sometable (foo) VALUES (foobar())",
inline_flag=False,
)
self.assert_compile(
table.insert(),
"INSERT INTO sometable (foo) VALUES (foobar())",
params={},
inline_flag=False,
)
self.assert_compile(
table.insert().values().inline(),
"INSERT INTO sometable (foo) VALUES (foobar())",
inline_flag=True,
)
self.assert_compile(
table.insert().inline(),
"INSERT INTO sometable (foo) VALUES (foobar())",
params={},
inline_flag=True,
)
def test_as_comparison_annotate(self):
fn = func.foobar("x", "y", "q", "p", "r").as_comparison(2, 5)
from sqlalchemy.sql import annotation
fn_annotated = annotation._deep_annotate(fn, {"token": "yes"})
eq_(fn.left._annotations, {})
eq_(fn_annotated.left._annotations, {"token": "yes"})
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_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_from_select_with_sql_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)
self.assert_compile(ins, "INSERT INTO sometable (id, foo) SELECT "
"mytable.myid, foobar() AS foobar_1 "
"FROM mytable WHERE mytable.name = :name_1",
checkparams={"name_1": "foo"})
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_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_with_sql_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)
self.assert_compile(
ins,
"INSERT INTO sometable (id, foo) SELECT "
"mytable.myid, foobar() AS foobar_1 "
"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("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_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_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 __eq__(self, other):
return func.foobar(self.__clause_element__()) ==\
func.foobar(other)
def __eq__(self, other):
return func.foobar(
self.__clause_element__()) == func.foobar(other)
