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