def _assert_cyclic_constraint_no_alter(self, metadata, auto=False, sqlite_warning=False): table_assertions = [] if auto: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) else: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) assertions = [AllOf(*table_assertions)] with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) asserter.assert_(*assertions) assertions = [ AllOf(CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b")) ] if sqlite_warning: with expect_warnings("Can't sort tables for DROP; "): with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), else: with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), asserter.assert_(*assertions)
def _test_disable_scope_identity(self): engine = engines.testing_engine(options={"use_scope_identity": False}) metadata = self.metadata t1 = Table( "t1", metadata, Column("id", Integer, primary_key=True), Column("data", String(50)), implicit_returning=False, ) metadata.create_all(engine) with self.sql_execution_asserter(engine) as asserter: with engine.begin() as conn: conn.execute(t1.insert(), {"data": "somedata"}) # TODO: need a dialect SQL that acts like Cursor SQL asserter.assert_( DialectSQL( "INSERT INTO t1 (data) VALUES (:data)", {"data": "somedata"} ), CursorSQL( "SELECT @@identity AS lastrowid", consume_statement=False ), )
def _assert_data_with_sequence_returning(self, table, seqname): engine = engines.testing_engine(options={"implicit_returning": True}) with self.sql_execution_asserter(engine) as asserter: with engine.connect() as conn: conn.execute(table.insert(), {"id": 30, "data": "d1"}) conn.execute(table.insert(), {"data": "d2"}) 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(), {"id": 33, "data": "d7"}) conn.execute(table.insert().inline(), {"data": "d8"}) asserter.assert_( DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {"id": 30, "data": "d1"}, ), DialectSQL( "INSERT INTO testtable (id, data) VALUES " "(nextval('my_seq'), :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 (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{"data": "d5"}, {"data": "d6"}], ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{"id": 33, "data": "d7"}], ), DialectSQL( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{"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"), ], )
def _assert_data_with_sequence_returning(self, table, seqname): engine = \ engines.testing_engine(options={'implicit_returning': True}) with self.sql_execution_asserter(engine) as asserter: with engine.connect() as conn: conn.execute(table.insert(), {'id': 30, 'data': 'd1'}) conn.execute(table.insert(), {'data': 'd2'}) 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 (id, data) VALUES " "(nextval('my_seq'), :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 (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{'data': 'd5'}, {'data': 'd6'}]), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', [{'id': 33, 'data': 'd7'}]), DialectSQL( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{'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'), ] )
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 _assert_data_with_sequence(self, table, seqname): self.engine = \ engines.testing_engine(options={'implicit_returning': False}) metadata.bind = self.engine with self.sql_execution_asserter(self.engine) as asserter: table.insert().execute({'id': 30, 'data': 'd1'}) table.insert().execute({'data': 'd2'}) table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) table.insert().execute({'data': 'd5'}, {'data': 'd6'}) table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) table.insert(inline=True).execute({'data': 'd8'}) asserter.assert_( DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 30, 'data': 'd1' }), CursorSQL("select nextval('my_seq')"), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 1, 'data': 'd2' }), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', [{ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }]), DialectSQL( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{ 'data': 'd5' }, { 'data': 'd6' }]), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', [{ 'id': 33, 'data': 'd7' }]), DialectSQL( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{ 'data': 'd8' }]), ) eq_(table.select().execute().fetchall(), [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ])
def _assert_data_autoincrement(self, table): self.engine = \ engines.testing_engine(options={'implicit_returning': False}) metadata.bind = self.engine with self.sql_execution_asserter(self.engine) as asserter: # execute with explicit id r = table.insert().execute({'id': 30, 'data': 'd1'}) assert r.inserted_primary_key == [30] # execute with prefetch id r = table.insert().execute({'data': 'd2'}) assert r.inserted_primary_key == [1] # executemany with explicit ids table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) # executemany, uses SERIAL table.insert().execute({'data': 'd5'}, {'data': 'd6'}) # single execute, explicit id, inline table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) # single execute, inline, uses SERIAL table.insert(inline=True).execute({'data': 'd8'}) asserter.assert_( DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 30, 'data': 'd1' }), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 1, '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' }]), ) eq_(table.select().execute().fetchall(), [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ]) table.delete().execute() # test the same series of events using a reflected version of # the table m2 = MetaData(self.engine) table = Table(table.name, m2, autoload=True) with self.sql_execution_asserter(self.engine) as asserter: table.insert().execute({'id': 30, 'data': 'd1'}) r = table.insert().execute({'data': 'd2'}) assert r.inserted_primary_key == [5] table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) table.insert().execute({'data': 'd5'}, {'data': 'd6'}) table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) table.insert(inline=True).execute({'data': 'd8'}) asserter.assert_( DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 30, 'data': 'd1' }), DialectSQL('INSERT INTO testtable (id, data) VALUES (:id, :data)', { 'id': 5, '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' }]), ) eq_(table.select().execute().fetchall(), [ (30, 'd1'), (5, 'd2'), (31, 'd3'), (32, 'd4'), (6, 'd5'), (7, 'd6'), (33, 'd7'), (8, 'd8'), ]) table.delete().execute()
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.begin() 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(), {"id": 33, "data": "d7"}) # single execute, inline, uses SERIAL conn.execute(table.insert().inline(), {"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.begin() 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() table = Table(table.name, m2, autoload_with=engine) with self.sql_execution_asserter(engine) as asserter: with engine.begin() 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(), {"id": 33, "data": "d7"}) conn.execute(table.insert().inline(), {"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.begin() 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())