def test_column_targeting(self, connection):
table = self.tables.tables
result = connection.execute(
table.insert().returning(table.c.id, table.c.full),
{
"persons": 1,
"full": False
},
)
row = result.first()._mapping
assert row[table.c.id] == row["id"] == 1
assert row[table.c.full] == row["full"]
assert row["full"] is False
result = connection.execute(table.insert().values(
persons=5, full=True,
goofy="somegoofy").returning(table.c.persons, table.c.full,
table.c.goofy))
row = result.first()._mapping
assert row[table.c.persons] == row["persons"] == 5
assert row[table.c.full] == row["full"]
eq_(row[table.c.goofy], row["goofy"])
eq_(row["goofy"], "FOOsomegoofyBAR")
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_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_anon_expressions(self, connection):
table = self.tables.tables
GoofyType = self.GoofyType
result = connection.execute(
table.insert().values(goofy="someOTHERgoofy").returning(
func.lower(table.c.goofy, type_=GoofyType)))
row = result.first()
eq_(row[0], "foosomeothergoofyBAR")
result = connection.execute(
table.insert().values(persons=12).returning(table.c.persons + 18))
row = result.first()
eq_(row[0], 30)
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_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_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_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_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_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_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(),
)
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(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_update_returning_w_type_coerce_expression(self, connection):
table = self.tables.tables
connection.execute(
table.insert(),
[
{
"persons": 5,
"goofy": "somegoofy1"
},
{
"persons": 3,
"goofy": "somegoofy2"
},
],
)
result = connection.execute(table.update().where(
table.c.persons > 4).values(goofy="newgoofy").returning(
type_coerce(table.c.goofy, String)))
eq_(result.fetchall(), [("FOOnewgoofy", )])
result2 = connection.execute(
select(table.c.id, table.c.goofy).order_by(table.c.id))
eq_(
result2.fetchall(),
[(1, "FOOnewgoofyBAR"), (2, "FOOsomegoofy2BAR")],
)
def test_update_returning_w_expression_one(self, connection):
table = self.tables.tables
connection.execute(
table.insert(),
[
{
"persons": 5,
"full": False,
"strval": "str1"
},
{
"persons": 3,
"full": False,
"strval": "str2"
},
],
)
result = connection.execute(table.update().where(
table.c.persons > 4).values(full=True).returning(table.c.strval +
"hi"))
eq_(result.fetchall(), [("str1hi", )])
result2 = connection.execute(
select(table.c.id, table.c.strval).order_by(table.c.id))
eq_(result2.fetchall(), [(1, "str1"), (2, "str2")])
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_python_scalar_default(self):
metadata = MetaData()
table = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String), Column('foo',
Integer,
default=10))
values = [
{
'id': 1,
'data': 'data1'
},
{
'id': 2,
'data': 'data2',
'foo': 15
},
{
'id': 3,
'data': 'data3'
},
]
checkparams = {
'id_0': 1,
'id_1': 2,
'id_2': 3,
'data_0': 'data1',
'data_1': 'data2',
'data_2': 'data3',
'foo': None, # evaluated later
'foo_1': 15,
'foo_2': 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_2': String,
'id_0': Integer,
'id_2': Integer,
'foo_1': Integer,
'data_1': String,
'id_1': Integer,
'foo_2': Integer,
'data_0': String
})
self.assert_compile(stmt,
'INSERT INTO sometable (id, data, foo) VALUES '
'(%(id_0)s, %(data_0)s, %(foo)s), '
'(%(id_1)s, %(data_1)s, %(foo_1)s), '
'(%(id_2)s, %(data_2)s, %(foo_2)s)',
checkparams=checkparams,
dialect=postgresql.dialect())
def test_anon_expressions_selected_columns(self, table_fixture):
table = table_fixture
stmt = (table.insert().values(goofy="someOTHERgoofy").returning(
func.lower(table.c.x)))
self.assert_compile(
select(stmt.exported_columns[0]),
"SELECT lower(foo.x) AS lower_1 FROM foo",
)
def test_insert_returning(self, connection):
table = self.tables.tables
result = connection.execute(table.insert().returning(table.c.id), {
"persons": 1,
"full": False
})
eq_(result.fetchall(), [(1, )])
def test_named_expressions_selected_columns(self, table_fixture):
table = table_fixture
stmt = (table.insert().values(goofy="someOTHERgoofy").returning(
func.lower(table.c.x).label("goof")))
self.assert_compile(
select(stmt.exported_columns.goof),
"SELECT lower(foo.x) AS goof FROM foo",
)
def test_insert(self, connection):
table = self.tables.tables
result = connection.execute(table.insert().returning(table.c.foo_id),
dict(data="somedata"))
row = result.first()._mapping
assert row[table.c.foo_id] == row["id"] == 1
result = connection.execute(table.select()).first()._mapping
assert row[table.c.foo_id] == row["id"] == 1
def test_insert(self, connection):
table = self.tables.tables
r = connection.execute(table.insert().values(data="hi").returning(
table.c.id))
eq_(r.first(), tuple([testing.db.dialect.default_sequence_base]))
eq_(
connection.scalar(self.sequences.tid_seq),
testing.db.dialect.default_sequence_base + 1,
)
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_0': 1,
'data_0': 'foo',
'id_1': 2,
'data_1': 'bar',
'id_2': 3,
'data_2': 'bar',
'id_3': 4,
'data_3': 'bar',
'foo_3': 15,
'id_4': 5,
'data_4': 'bar'
}
self.assert_compile(table.insert().values(values),
"INSERT INTO sometable (id, data, foo) VALUES "
"(%(id_0)s, %(data_0)s, foob()), "
"(%(id_1)s, %(data_1)s, foob()), "
"(%(id_2)s, %(data_2)s, bar()), "
"(%(id_3)s, %(data_3)s, %(foo_3)s), "
"(%(id_4)s, %(data_4)s, foob())",
checkparams=checkparams,
dialect=postgresql.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 insert(self, table, **kw):
"""
Convenience insert of a single row to the specified table.
Executes as an operation, returning no value.
:param table: The table to insert into.
:param kw: The columns to insert and their values.
:rtype: twisted.internet.defer.Deferred
"""
return self.operation(table.insert().values(**kw))
def insert(self, table, **kw):
"""
Convenience insert of a single row to the specified table.
Executes as an operation, returning no value.
:param table: The table to insert into.
:param kw: The columns to insert and their values.
:rtype: twisted.internet.defer.Deferred
"""
return self.operation(table.insert().values(**kw))
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_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_no_ipk_on_returning(self, connection):
table = self.tables.tables
result = connection.execute(table.insert().returning(table.c.id), {
"persons": 1,
"full": False
})
assert_raises_message(
sa_exc.InvalidRequestError,
r"Can't call inserted_primary_key when returning\(\) is used.",
getattr,
result,
"inserted_primary_key",
)
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_insert_returning_w_type_coerce_expression(self, connection):
table = self.tables.tables
result = connection.execute(
table.insert().returning(type_coerce(table.c.goofy, String)),
{
"persons": 5,
"goofy": "somegoofy"
},
)
eq_(result.fetchall(), [("FOOsomegoofy", )])
result2 = connection.execute(
select(table.c.id, table.c.goofy).order_by(table.c.id))
eq_(result2.fetchall(), [(1, "FOOsomegoofyBAR")])
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_returning_w_expression_one(self, connection):
table = self.tables.tables
result = connection.execute(
table.insert().returning(table.c.strval + "hi"),
{
"persons": 5,
"full": False,
"strval": "str1"
},
)
eq_(result.fetchall(), [("str1hi", )])
result2 = connection.execute(
select(table.c.id, table.c.strval).order_by(table.c.id))
eq_(result2.fetchall(), [(1, "str1")])
def test_delete_full_returning(self, connection):
table = self.tables.tables
connection.execute(
table.insert(),
[{
"persons": 5,
"full": False
}, {
"persons": 3,
"full": False
}],
)
result = connection.execute(table.delete().returning(
table.c.id, table.c.full))
eq_(result.fetchall(), [(1, False), (2, False)])
def test_insert_from_select_with_python_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]).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_update_full_returning(self, connection):
table = self.tables.tables
connection.execute(
table.insert(),
[{
"persons": 5,
"full": False
}, {
"persons": 3,
"full": False
}],
)
result = connection.execute(table.update().where(
table.c.persons > 2).values(full=True).returning(
table.c.id, table.c.full))
eq_(result.fetchall(), [(1, True), (2, True)])
def test_python_scalar_default(self):
metadata = MetaData()
table = Table('sometable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String),
Column('foo', Integer, default=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())
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_with_python_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]).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_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_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_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_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_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(),
)
