def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def test_insert_with_values_func(self):
table1 = self.tables.mytable
self.assert_compile(
insert(table1, values=dict(myid=func.lala())),
"INSERT INTO mytable (myid) VALUES (lala())",
)
def test_generic_insert_bind_params_all_columns(self):
table1 = self.tables.mytable
self.assert_compile(
insert(table1),
"INSERT INTO mytable (myid, name, description) "
"VALUES (:myid, :name, :description)",
)
def test_insert_values(self):
table1 = self.tables.mytable
values1 = {table1.c.myid: bindparam("userid")}
values2 = {table1.c.name: bindparam("username")}
self.assert_compile(
insert(table1, values=values1).values(values2),
"INSERT INTO mytable (myid, name) VALUES (:userid, :username)",
)
def test_insert_with_values_dict(self):
table1 = self.tables.mytable
checkparams = {"myid": 3, "name": "jack"}
self.assert_compile(
insert(table1, dict(myid=3, name="jack")),
"INSERT INTO mytable (myid, name) VALUES (:myid, :name)",
checkparams=checkparams,
)
def test_insert_with_user_supplied_bind_params(self):
table1 = self.tables.mytable
values = {
table1.c.myid: bindparam("userid"),
table1.c.name: bindparam("username"),
}
self.assert_compile(
insert(table1, values),
"INSERT INTO mytable (myid, name) VALUES (:userid, :username)",
)
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_insert_with_values_tuple(self):
table1 = self.tables.mytable
checkparams = {
"myid": 3,
"name": "jack",
"description": "mydescription",
}
self.assert_compile(
insert(table1, (3, "jack", "mydescription")),
"INSERT INTO mytable (myid, name, description) "
"VALUES (:myid, :name, :description)",
checkparams=checkparams,
)