def test_rowcount_flag(self): metadata = self.metadata engine = engines.testing_engine(options={"enable_rowcount": True}) assert engine.dialect.supports_sane_rowcount metadata.bind = engine t = Table("t1", metadata, Column("data", String(10))) metadata.create_all() r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"}) r = t.update().where(t.c.data == "d2").values(data="d3").execute() eq_(r.rowcount, 1) r = t.delete().where(t.c.data == "d3").execute() eq_(r.rowcount, 2) r = t.delete().execution_options(enable_rowcount=False).execute() eq_(r.rowcount, -1) engine.dispose() engine = engines.testing_engine(options={"enable_rowcount": False}) assert not engine.dialect.supports_sane_rowcount metadata.bind = engine r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"}) r = t.update().where(t.c.data == "d2").values(data="d3").execute() eq_(r.rowcount, -1) r = t.delete().where(t.c.data == "d3").execute() eq_(r.rowcount, -1) r = t.delete().execution_options(enable_rowcount=True).execute() eq_(r.rowcount, 1) r.close() engine.dispose()
def test_boolean_roundtrip_reflected(self, boolean_table, store, expected): meta2 = MetaData(testing.db) table = Table("mysql_bool", meta2, autoload=True) eq_(colspec(table.c.b3), "b3 TINYINT(1)") eq_regex(colspec(table.c.b4), r"b4 TINYINT(?:\(1\))? UNSIGNED") meta2 = MetaData(testing.db) table = Table( "mysql_bool", meta2, Column("b1", BOOLEAN), Column("b2", Boolean), Column("b3", BOOLEAN), Column("b4", BOOLEAN), autoload=True, ) eq_(colspec(table.c.b3), "b3 BOOL") eq_(colspec(table.c.b4), "b4 BOOL") with testing.db.connect() as conn: expected = expected or store conn.execute(table.insert(store)) row = conn.execute(table.select()).first() eq_(list(row), expected) for i, val in enumerate(expected): if isinstance(val, bool): self.assert_(val is row[i]) conn.execute(table.delete())
def test_delete_schema_legacy(self): meta = self.metadata eng = engines.testing_engine(options=dict(legacy_schema_aliasing=True)) meta.bind = eng con = eng.connect() con.execute("create schema paj") @event.listens_for(meta, "after_drop") def cleanup(target, connection, **kw): connection.execute("drop schema paj") tbl = Table( "test", meta, Column("id", Integer, primary_key=True), schema="paj" ) tbl.create() tbl.insert().execute({"id": 1}) eq_(tbl.select().scalar(), 1) tbl.delete(tbl.c.id == 1).execute() eq_(tbl.select().scalar(), None)
def test_bit_50_roundtrip_reflected(self, bit_table, store, expected): meta2 = MetaData() bit_table = Table("mysql_bits", meta2, autoload_with=testing.db) with testing.db.connect() as conn: expected = expected or store conn.execute(bit_table.insert(store)) row = conn.execute(bit_table.select()).first() eq_(list(row), expected) conn.execute(bit_table.delete())
def test_delete_schema(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, primary_key=True), schema="paj", ) self.assert_compile( tbl.delete(tbl.c.id == 1), "DELETE FROM paj.test WHERE paj.test.id = " ":id_1", ) s = select([tbl.c.id]).where(tbl.c.id == 1) self.assert_compile( tbl.delete().where(tbl.c.id.in_(s)), "DELETE FROM paj.test WHERE paj.test.id IN " "(SELECT paj.test.id FROM paj.test " "WHERE paj.test.id = :id_1)", )
def test_delete_schema_multipart(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, primary_key=True), schema="banana.paj", ) self.assert_compile( tbl.delete(tbl.c.id == 1), "DELETE FROM banana.paj.test WHERE " "banana.paj.test.id = :id_1", ) s = select([tbl.c.id]).where(tbl.c.id == 1) self.assert_compile( tbl.delete().where(tbl.c.id.in_(s)), "DELETE FROM banana.paj.test WHERE " "banana.paj.test.id IN (SELECT banana.paj.test.id " "FROM banana.paj.test WHERE " "banana.paj.test.id = :id_1)", )
def test_schema_roundtrips(self): meta = self.metadata users = Table( "users", meta, Column("id", Integer, primary_key=True), Column("name", String(50)), schema="test_schema", ) users.create() users.insert().execute(id=1, name="name1") users.insert().execute(id=2, name="name2") users.insert().execute(id=3, name="name3") users.insert().execute(id=4, name="name4") eq_( users.select().where(users.c.name == "name2").execute().fetchall(), [(2, "name2")], ) eq_( users.select(use_labels=True) .where(users.c.name == "name2") .execute() .fetchall(), [(2, "name2")], ) users.delete().where(users.c.id == 3).execute() eq_( users.select().where(users.c.name == "name3").execute().fetchall(), [], ) users.update().where(users.c.name == "name4").execute(name="newname") eq_( users.select(use_labels=True) .where(users.c.id == 4) .execute() .fetchall(), [(4, "newname")], )
def test_delete_schema_multipart_both_need_quoting(self): metadata = MetaData() tbl = Table( "test", metadata, Column("id", Integer, primary_key=True), schema="banana split.paj with a space", ) self.assert_compile( tbl.delete(tbl.c.id == 1), "DELETE FROM [banana split].[paj with a " "space].test WHERE [banana split].[paj " "with a space].test.id = :id_1", ) s = select([tbl.c.id]).where(tbl.c.id == 1) self.assert_compile( tbl.delete().where(tbl.c.id.in_(s)), "DELETE FROM [banana split].[paj with a space].test " "WHERE [banana split].[paj with a space].test.id IN " "(SELECT [banana split].[paj with a space].test.id " "FROM [banana split].[paj with a space].test " "WHERE [banana split].[paj with a space].test.id = :id_1)", )
def _test_round_trip(self, type_, data, deprecate_large_types=True, expected=None): if (testing.db.dialect.deprecate_large_types is not deprecate_large_types): engine = engines.testing_engine( options={"deprecate_large_types": deprecate_large_types}) else: engine = testing.db binary_table = Table( "binary_table", self.metadata, Column("id", Integer, primary_key=True), Column("data", type_), ) binary_table.create(engine) if expected is None: expected = data with engine.connect() as conn: conn.execute(binary_table.insert(), data=data) eq_(conn.scalar(select([binary_table.c.data])), expected) eq_( conn.scalar( text("select data from binary_table").columns( binary_table.c.data)), expected, ) conn.execute(binary_table.delete()) conn.execute(binary_table.insert(), data=None) eq_(conn.scalar(select([binary_table.c.data])), None) eq_( conn.scalar( text("select data from binary_table").columns( binary_table.c.data)), None, )
def test_native_odbc_execute(self): t1 = Table("t1", MetaData(), Column("c1", Integer)) dbapi = mock_dbapi() engine = engines.testing_engine( "mssql+mxodbc://localhost", options={"module": dbapi, "_initialize": False}, ) conn = engine.connect() # crud: uses execute conn.execute(t1.insert().values(c1="foo")) conn.execute(t1.delete().where(t1.c.c1 == "foo")) conn.execute(t1.update().where(t1.c.c1 == "foo").values(c1="bar")) # select: uses executedirect conn.execute(t1.select()) # manual flagging conn.execution_options(native_odbc_execute=True).execute(t1.select()) conn.execution_options(native_odbc_execute=False).execute( t1.insert().values(c1="foo") ) eq_( # fmt: off [ c[2] for c in dbapi.connect.return_value.cursor. return_value.execute.mock_calls ], # fmt: on [ {"direct": True}, {"direct": True}, {"direct": True}, {"direct": True}, {"direct": False}, {"direct": True}, ] )
def test_update(self): """ Tests sending functions and SQL expressions to the VALUES and SET clauses of INSERT/UPDATE instances, and that column-level defaults get overridden. """ meta = self.metadata t = Table( "t1", meta, Column( "id", Integer, Sequence("t1idseq", optional=True), primary_key=True, ), Column("value", Integer), ) t2 = Table( "t2", meta, Column( "id", Integer, Sequence("t2idseq", optional=True), primary_key=True, ), Column("value", Integer, default=7), Column("stuff", String(20), onupdate="thisisstuff"), ) meta.create_all() t.insert(values=dict(value=func.length("one"))).execute() assert t.select().execute().first()["value"] == 3 t.update(values=dict(value=func.length("asfda"))).execute() assert t.select().execute().first()["value"] == 5 r = t.insert(values=dict(value=func.length("sfsaafsda"))).execute() id_ = r.inserted_primary_key[0] assert t.select(t.c.id == id_).execute().first()["value"] == 9 t.update(values={t.c.value: func.length("asdf")}).execute() assert t.select().execute().first()["value"] == 4 t2.insert().execute() t2.insert(values=dict(value=func.length("one"))).execute() t2.insert(values=dict(value=func.length("asfda") + -19)).execute( stuff="hi" ) res = exec_sorted(select([t2.c.value, t2.c.stuff])) eq_(res, [(-14, "hi"), (3, None), (7, None)]) t2.update(values=dict(value=func.length("asdsafasd"))).execute( stuff="some stuff" ) assert select([t2.c.value, t2.c.stuff]).execute().fetchall() == [ (9, "some stuff"), (9, "some stuff"), (9, "some stuff"), ] t2.delete().execute() t2.insert(values=dict(value=func.length("one") + 8)).execute() assert t2.select().execute().first()["value"] == 11 t2.update(values=dict(value=func.length("asfda"))).execute() eq_( select([t2.c.value, t2.c.stuff]).execute().first(), (5, "thisisstuff"), ) t2.update( values={t2.c.value: func.length("asfdaasdf"), t2.c.stuff: "foo"} ).execute() eq_(select([t2.c.value, t2.c.stuff]).execute().first(), (9, "foo"))
def _assert_data_autoincrement_returning(self, table): engine = engines.testing_engine(options={"implicit_returning": True}) with self.sql_execution_asserter(engine) as asserter: with engine.connect() as conn: # execute with explicit id r = conn.execute(table.insert(), {"id": 30, "data": "d1"}) eq_(r.inserted_primary_key, [30]) # execute with prefetch id r = conn.execute(table.insert(), {"data": "d2"}) eq_(r.inserted_primary_key, [1]) # executemany with explicit ids conn.execute( table.insert(), { "id": 31, "data": "d3" }, { "id": 32, "data": "d4" }, ) # executemany, uses SERIAL conn.execute(table.insert(), {"data": "d5"}, {"data": "d6"}) # single execute, explicit id, inline conn.execute(table.insert(inline=True), { "id": 33, "data": "d7" }) # single execute, inline, uses SERIAL conn.execute(table.insert(inline=True), {"data": "d8"}) asserter.assert_( DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", { "id": 30, "data": "d1" }, ), DialectSQL( "INSERT INTO testtable (data) VALUES (:data) RETURNING " "testtable.id", {"data": "d2"}, ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ "id": 31, "data": "d3" }, { "id": 32, "data": "d4" }], ), DialectSQL( "INSERT INTO testtable (data) VALUES (:data)", [{ "data": "d5" }, { "data": "d6" }], ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ "id": 33, "data": "d7" }], ), DialectSQL("INSERT INTO testtable (data) VALUES (:data)", [{ "data": "d8" }]), ) with engine.connect() as conn: eq_( conn.execute(table.select()).fetchall(), [ (30, "d1"), (1, "d2"), (31, "d3"), (32, "d4"), (2, "d5"), (3, "d6"), (33, "d7"), (4, "d8"), ], ) conn.execute(table.delete()) # test the same series of events using a reflected version of # the table m2 = MetaData(engine) table = Table(table.name, m2, autoload=True) with self.sql_execution_asserter(engine) as asserter: with engine.connect() as conn: conn.execute(table.insert(), {"id": 30, "data": "d1"}) r = conn.execute(table.insert(), {"data": "d2"}) eq_(r.inserted_primary_key, [5]) conn.execute( table.insert(), { "id": 31, "data": "d3" }, { "id": 32, "data": "d4" }, ) conn.execute(table.insert(), {"data": "d5"}, {"data": "d6"}) conn.execute(table.insert(inline=True), { "id": 33, "data": "d7" }) conn.execute(table.insert(inline=True), {"data": "d8"}) asserter.assert_( DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", { "id": 30, "data": "d1" }, ), DialectSQL( "INSERT INTO testtable (data) VALUES (:data) RETURNING " "testtable.id", {"data": "d2"}, ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ "id": 31, "data": "d3" }, { "id": 32, "data": "d4" }], ), DialectSQL( "INSERT INTO testtable (data) VALUES (:data)", [{ "data": "d5" }, { "data": "d6" }], ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ "id": 33, "data": "d7" }], ), DialectSQL("INSERT INTO testtable (data) VALUES (:data)", [{ "data": "d8" }]), ) with engine.connect() as conn: eq_( conn.execute(table.select()).fetchall(), [ (30, "d1"), (5, "d2"), (31, "d3"), (32, "d4"), (6, "d5"), (7, "d6"), (33, "d7"), (8, "d8"), ], ) conn.execute(table.delete())
def test_autoincrement(self): Table( "ai_1", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_2", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_3", metadata, Column("int_n", Integer, DefaultClause("0"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), ) Table( "ai_4", metadata, Column("int_n", Integer, DefaultClause("0"), primary_key=True), Column("int_n2", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_5", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_6", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), ) Table( "ai_7", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, autoincrement=True, primary_key=True), ) Table( "ai_8", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), ) metadata.create_all() table_names = [ "ai_1", "ai_2", "ai_3", "ai_4", "ai_5", "ai_6", "ai_7", "ai_8", ] mr = MetaData(testing.db) for name in table_names: tbl = Table(name, mr, autoload=True) tbl = metadata.tables[name] # test that the flag itself reflects appropriately for col in tbl.c: if "int_y" in col.name: is_(col.autoincrement, True) is_(tbl._autoincrement_column, col) else: eq_(col.autoincrement, "auto") is_not(tbl._autoincrement_column, col) # mxodbc can't handle scope_identity() with DEFAULT VALUES if testing.db.driver == "mxodbc": eng = [ engines.testing_engine( options={"implicit_returning": True}) ] else: eng = [ engines.testing_engine( options={"implicit_returning": False}), engines.testing_engine( options={"implicit_returning": True}), ] for counter, engine in enumerate(eng): with engine.begin() as conn: conn.execute(tbl.insert()) if "int_y" in tbl.c: eq_( conn.execute(select([tbl.c.int_y])).scalar(), counter + 1, ) assert (list(conn.execute( tbl.select()).first()).count(counter + 1) == 1) else: assert 1 not in list( conn.execute(tbl.select()).first()) conn.execute(tbl.delete())