def test_delete_returning(self):
meta = MetaData(testing.db)
table = Table(
'tables', meta,
Column('id', Integer, Sequence('gen_tables_id'), primary_key=True),
Column('persons', Integer), Column('full', Boolean))
table.create()
try:
table.insert().execute([{
'persons': 5,
'full': False
}, {
'persons': 3,
'full': False
}])
result = table.delete(table.c.persons > 4,
firebird_returning=[table.c.id]).execute()
self.assertEqual(result.fetchall(), [(1, )])
result2 = select([table.c.id,
table.c.full]).order_by(table.c.id).execute()
self.assertEqual(result2.fetchall(), [
(2, False),
])
finally:
table.drop()
def test_delete_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta,
Column('id', Integer, Sequence('gen_tables_id'), primary_key=True),
Column('persons', Integer),
Column('full', Boolean)
)
table.create()
try:
table.insert().execute([{'persons': 5, 'full': False}, {'persons': 3, 'full': False}])
result = table.delete(table.c.persons > 4, firebird_returning=[table.c.id]).execute()
eq_(result.fetchall(), [(1,)])
result2 = select([table.c.id, table.c.full]).order_by(table.c.id).execute()
eq_(result2.fetchall(), [(2,False),])
finally:
table.drop()
def _assert_data_autoincrement(self, table):
def go():
# execute with explicit id
r = table.insert().execute({'id':30, 'data':'d1'})
assert r.last_inserted_ids() == [30]
# execute with prefetch id
r = table.insert().execute({'data':'d2'})
assert r.last_inserted_ids() == [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'})
# note that the test framework doesnt capture the "preexecute" of a seqeuence
# or default. we just see it in the bind params.
self.assert_sql(testing.db, go, [], with_sequences=[
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{'id':30, 'data':'d1'}
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{'id':1, 'data':'d2'}
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}]
),
(
"INSERT INTO testtable (data) VALUES (:data)",
[{'data':'d5'}, {'data':'d6'}]
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{'id':33, 'data':'d7'}]
),
(
"INSERT INTO testtable (data) VALUES (:data)",
[{'data':'d8'}]
),
])
assert 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(testing.db)
table = Table(table.name, m2, autoload=True)
def go():
table.insert().execute({'id':30, 'data':'d1'})
r = table.insert().execute({'data':'d2'})
assert r.last_inserted_ids() == [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'})
self.assert_sql(testing.db, go, [], with_sequences=[
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{'id':30, 'data':'d1'}
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{'id':5, 'data':'d2'}
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}]
),
(
"INSERT INTO testtable (data) VALUES (:data)",
[{'data':'d5'}, {'data':'d6'}]
),
(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{'id':33, 'data':'d7'}]
),
(
"INSERT INTO testtable (data) VALUES (:data)",
[{'data':'d8'}]
),
])
assert table.select().execute().fetchall() == [
(30, 'd1'),
(5, 'd2'),
(31, 'd3'),
(32, 'd4'),
(6, 'd5'),
(7, 'd6'),
(33, 'd7'),
(8, 'd8'),
]
table.delete().execute()
try:
table.insert().execute({'data':'d2'}, {'data':'d3'})
assert False
except exc.IntegrityError, e:
assert "violates not-null constraint" in str(e)
table.insert().execute({'id':31, 'data':'d2'}, {'id':32, 'data':'d3'})
table.insert(inline=True).execute({'id':33, 'data':'d4'})
assert table.select().execute().fetchall() == [
(30, 'd1'),
(31, 'd2'),
(32, 'd3'),
(33, 'd4'),
]
table.delete().execute()
# test the same series of events using a reflected
# version of the table
m2 = MetaData(testing.db)
table = Table(table.name, m2, autoload=True)
table.insert().execute({'id':30, 'data':'d1'})
try:
table.insert().execute({'data':'d2'})
assert False
except exc.IntegrityError, e:
assert "violates not-null constraint" in str(e)
try:
table.insert().execute({'data':'d2'}, {'data':'d3'})
assert False
except exc.IntegrityError, e:
def _assert_data_autoincrement(self, table):
def go():
# execute with explicit id
r = table.insert().execute({'id': 30, 'data': 'd1'})
assert r.last_inserted_ids() == [30]
# execute with prefetch id
r = table.insert().execute({'data': 'd2'})
assert r.last_inserted_ids() == [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'})
# note that the test framework doesnt capture the "preexecute" of a seqeuence
# or default. we just see it in the bind params.
self.assert_sql(
testing.db,
go, [],
with_sequences=[
("INSERT INTO testtable (id, data) VALUES (:id, :data)", {
'id': 30,
'data': 'd1'
}),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", {
'id': 1,
'data': 'd2'
}),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{
'id':
31,
'data':
'd3'
}, {
'id':
32,
'data':
'd4'
}]),
("INSERT INTO testtable (data) VALUES (:data)", [{
'data': 'd5'
}, {
'data': 'd6'
}]),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{
'id':
33,
'data':
'd7'
}]),
("INSERT INTO testtable (data) VALUES (:data)", [{
'data': 'd8'
}]),
])
assert 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(testing.db)
table = Table(table.name, m2, autoload=True)
def go():
table.insert().execute({'id': 30, 'data': 'd1'})
r = table.insert().execute({'data': 'd2'})
assert r.last_inserted_ids() == [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'})
self.assert_sql(
testing.db,
go, [],
with_sequences=[
("INSERT INTO testtable (id, data) VALUES (:id, :data)", {
'id': 30,
'data': 'd1'
}),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", {
'id': 5,
'data': 'd2'
}),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{
'id':
31,
'data':
'd3'
}, {
'id':
32,
'data':
'd4'
}]),
("INSERT INTO testtable (data) VALUES (:data)", [{
'data': 'd5'
}, {
'data': 'd6'
}]),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{
'id':
33,
'data':
'd7'
}]),
("INSERT INTO testtable (data) VALUES (:data)", [{
'data': 'd8'
}]),
])
assert table.select().execute().fetchall() == [
(30, 'd1'),
(5, 'd2'),
(31, 'd3'),
(32, 'd4'),
(6, 'd5'),
(7, 'd6'),
(33, 'd7'),
(8, 'd8'),
]
table.delete().execute()
table.insert().execute({
'id': 31,
'data': 'd2'
}, {
'id': 32,
'data': 'd3'
})
table.insert(inline=True).execute({'id': 33, 'data': 'd4'})
assert table.select().execute().fetchall() == [
(30, 'd1'),
(31, 'd2'),
(32, 'd3'),
(33, 'd4'),
]
table.delete().execute()
# test the same series of events using a reflected
# version of the table
m2 = MetaData(testing.db)
table = Table(table.name, m2, autoload=True)
table.insert().execute({'id': 30, 'data': 'd1'})
try:
table.insert().execute({'data': 'd2'})
assert False
except exc.IntegrityError, e:
assert "violates not-null constraint" in str(e)
try:
table.insert().execute({'data': 'd2'}, {'data': 'd3'})
assert False
except exc.IntegrityError, e: