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(), )