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'})
def test_insertid_reserved(self):
meta = self.metadata
table = Table("select", meta, Column("col", Integer, primary_key=True))
table.create()
table.insert().execute(col=7)
eq_(table.select().scalar(), 7)
def test_insert_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:
result = table.insert(firebird_returning=[table.c.id]).execute({'persons': 1, 'full': False})
eq_(result.fetchall(), [(1,)])
# Multiple inserts only return the last row
result2 = table.insert(firebird_returning=[table]).execute(
[{'persons': 2, 'full': False}, {'persons': 3, 'full': True}])
eq_(result2.fetchall(), [(3,3,True)])
result3 = table.insert(firebird_returning=[table.c.id]).execute({'persons': 4, 'full': False})
eq_([dict(row) for row in result3], [{'ID':4}])
result4 = testing.db.execute('insert into tables (id, persons, "full") values (5, 10, 1) returning persons')
eq_([dict(row) for row in result4], [{'PERSONS': 10}])
finally:
table.drop()
def test_insert_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta,
Column('id', Integer, primary_key=True),
Column('persons', Integer),
Column('full', Boolean)
)
table.create()
try:
result = table.insert(postgres_returning=[table.c.id]).execute({'persons': 1, 'full': False})
eq_(result.fetchall(), [(1,)])
@testing.fails_on('postgres', 'Known limitation of psycopg2')
def test_executemany():
# return value is documented as failing with psycopg2/executemany
result2 = table.insert(postgres_returning=[table]).execute(
[{'persons': 2, 'full': False}, {'persons': 3, 'full': True}])
eq_(result2.fetchall(), [(2, 2, False), (3,3,True)])
test_executemany()
result3 = table.insert(postgres_returning=[(table.c.id*2).label('double_id')]).execute({'persons': 4, 'full': False})
eq_([dict(row) for row in result3], [{'double_id':8}])
result4 = testing.db.execute('insert into tables (id, persons, "full") values (5, 10, true) returning persons')
eq_([dict(row) for row in result4], [{'persons': 10}])
finally:
table.drop()
def _assert_data_noautoincrement(self, table):
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)
def _assert_data_noautoincrement(self, table):
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)
def test_insertid_reserved(self):
meta = self.metadata
table = Table("select", meta, Column("col", Integer, primary_key=True))
table.create()
table.insert().execute(col=7)
eq_(table.select().scalar(), 7)
def test_table_round_trip(self):
oracle.RESERVED_WORDS.remove('UNION')
metadata = self.metadata
table = Table("t1", metadata,
Column("option", Integer),
Column("plain", Integer, quote=True),
# test that quote works for a reserved word
# that the dialect isn't aware of when quote
# is set
Column("union", Integer, quote=True))
metadata.create_all()
table.insert().execute(
{"option": 1, "plain": 1, "union": 1}
)
eq_(
testing.db.execute(table.select()).first(),
(1, 1, 1)
)
table.update().values(option=2, plain=2, union=2).execute()
eq_(
testing.db.execute(table.select()).first(),
(2, 2, 2)
)
def test_insert_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta,
Column('id', Integer, primary_key=True),
Column('persons', Integer),
Column('full', Boolean)
)
table.create()
try:
result = table.insert(postgres_returning=[table.c.id]).execute({'persons': 1, 'full': False})
self.assertEqual(result.fetchall(), [(1,)])
# Multiple inserts only return the last row
result2 = table.insert(postgres_returning=[table]).execute(
[{'persons': 2, 'full': False}, {'persons': 3, 'full': True}])
self.assertEqual(result2.fetchall(), [(3,3,True)])
result3 = table.insert(postgres_returning=[(table.c.id*2).label('double_id')]).execute({'persons': 4, 'full': False})
self.assertEqual([dict(row) for row in result3], [{'double_id':8}])
result4 = testing.db.execute('insert into tables (id, persons, "full") values (5, 10, true) returning persons')
self.assertEqual([dict(row) for row in result4], [{'persons': 10}])
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()
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_insertid_reserved(self):
meta = MetaData(testing.db)
table = Table('select', meta, Column('col', Integer, primary_key=True))
table.create()
meta2 = MetaData(testing.db)
try:
table.insert().execute(col=7)
finally:
table.drop()
def test_quoted_column_unicode(self):
metadata = self.metadata
table = Table("atable", metadata,
Column(u("méil"), Unicode(255), primary_key=True))
metadata.create_all()
table.insert().execute({u('méil'): u('’é')}, )
result = testing.db.execute(
table.select().where(table.c[u('méil')] == u('’é'))).scalar()
eq_(result, u('’é'))
def test_quoted_column_non_unicode(self):
metadata = self.metadata
table = Table(
"atable", metadata,
Column("_underscorecolumn", Unicode(255), primary_key=True))
metadata.create_all()
table.insert().execute({'_underscorecolumn': u('’é')}, )
result = testing.db.execute(table.select().where(
table.c._underscorecolumn == u('’é'))).scalar()
eq_(result, u('’é'))
def test_quoted_column_unicode(self):
metadata = self.metadata
table = Table("atable", metadata,
Column(u("méil"), Unicode(255), primary_key=True))
metadata.create_all()
table.insert().execute(
{u('méil'): u('’é')},
)
result = testing.db.execute(
table.select().where(table.c[u('méil')] == u('’é'))
).scalar()
eq_(result, u('’é'))
def test_insertid_reserved(self):
meta = MetaData(testing.db)
table = Table(
'select', meta,
Column('col', Integer, primary_key=True)
)
table.create()
meta2 = MetaData(testing.db)
try:
table.insert().execute(col=7)
finally:
table.drop()
def test_insert_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta, Column('id', Integer, primary_key=True),
Column('persons', Integer), Column('full', Boolean))
table.create()
try:
result = table.insert(postgres_returning=[table.c.id]).execute({
'persons':
1,
'full':
False
})
eq_(result.fetchall(), [(1, )])
@testing.fails_on('postgres', 'Known limitation of psycopg2')
def test_executemany():
# return value is documented as failing with psycopg2/executemany
result2 = table.insert(postgres_returning=[table]).execute([{
'persons':
2,
'full':
False
}, {
'persons':
3,
'full':
True
}])
eq_(result2.fetchall(), [(2, 2, False), (3, 3, True)])
test_executemany()
result3 = table.insert(
postgres_returning=[(table.c.id *
2).label('double_id')]).execute({
'persons':
4,
'full':
False
})
eq_([dict(row) for row in result3], [{'double_id': 8}])
result4 = testing.db.execute(
'insert into tables (id, persons, "full") values (5, 10, true) returning persons'
)
eq_([dict(row) for row in result4], [{'persons': 10}])
finally:
table.drop()
def test_quoted_column_non_unicode(self):
metadata = self.metadata
table = Table("atable", metadata,
Column("_underscorecolumn",
Unicode(255),
primary_key=True))
metadata.create_all()
table.insert().execute(
{'_underscorecolumn': u('’é')},
)
result = testing.db.execute(
table.select().where(table.c._underscorecolumn == u('’é'))
).scalar()
eq_(result, u('’é'))
def test_insertid_reserved(self, connection):
meta = self.metadata
table = Table("select", meta, Column("col", Integer, primary_key=True))
table.create(connection)
connection.execute(table.insert(), {"col": 7})
eq_(connection.scalar(table.select()), 7)
def test_insert_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta, Column('id', Integer, primary_key=True),
Column('persons', Integer), Column('full', Boolean))
table.create()
try:
result = table.insert(postgres_returning=[table.c.id]).execute({
'persons':
1,
'full':
False
})
self.assertEqual(result.fetchall(), [(1, )])
# Multiple inserts only return the last row
result2 = table.insert(postgres_returning=[table]).execute([{
'persons':
2,
'full':
False
}, {
'persons':
3,
'full':
True
}])
self.assertEqual(result2.fetchall(), [(3, 3, True)])
result3 = table.insert(
postgres_returning=[(table.c.id *
2).label('double_id')]).execute({
'persons':
4,
'full':
False
})
self.assertEqual([dict(row) for row in result3], [{
'double_id': 8
}])
result4 = testing.db.execute(
'insert into tables (id, persons, "full") values (5, 10, true) returning persons'
)
self.assertEqual([dict(row) for row in result4], [{'persons': 10}])
finally:
table.drop()
def test_insert_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:
result = table.insert(firebird_returning=[table.c.id]).execute({
'persons':
1,
'full':
False
})
eq_(result.fetchall(), [(1, )])
# Multiple inserts only return the last row
result2 = table.insert(firebird_returning=[table]).execute([{
'persons':
2,
'full':
False
}, {
'persons':
3,
'full':
True
}])
eq_(result2.fetchall(), [(3, 3, True)])
result3 = table.insert(firebird_returning=[table.c.id]).execute({
'persons':
4,
'full':
False
})
eq_([dict(row) for row in result3], [{'ID': 4}])
result4 = testing.db.execute(
'insert into tables (id, persons, "full") values (5, 10, 1) returning persons'
)
eq_([dict(row) for row in result4], [{'PERSONS': 10}])
finally:
table.drop()
def test_update_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta,
Column('id', Integer, 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.update(table.c.persons > 4, dict(full=True), postgres_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(), [(1,True),(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 test_extract_expression(self):
meta = MetaData(testing.db)
table = Table('test', meta,
Column('dt', DateTime),
Column('d', Date))
meta.create_all()
try:
table.insert().execute(
{'dt': datetime.datetime(2010, 5, 1, 12, 11, 10),
'd': datetime.date(2010, 5, 1)})
rs = select([extract('year', table.c.dt),
extract('month', table.c.d)]).execute()
row = rs.first()
assert row[0] == 2010
assert row[1] == 5
rs.close()
finally:
meta.drop_all()
def test_returning_no_autoinc(self):
meta = self.metadata
table = Table('t1', meta, Column('id', Integer, primary_key=True),
Column('data', String(50)))
table.create()
result = table.insert().values(
id=1,
data=func.lower('SomeString')).\
returning(table.c.id, table.c.data).execute()
eq_(result.fetchall(), [(1, 'somestring')])
def initiateLinkSearch(link):
table = Table(tableHtml, metadata, autoload=True)
s = engine.execute(table.select().where(table.columns.linkValue == link))
links = s.fetchall()
if (len(links) == 0):
insertedHtml = engine.execute(
table.insert().values(linkValue=link)).inserted_primary_key[0]
else:
insertedHtml = links[0].id
return insertedHtml, link
def test_compiled_insert(self):
table = Table('testtable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String(30)))
metadata.create_all()
ins = table.insert(values={'data':bindparam('x')}).compile()
ins.execute({'x':"five"}, {'x':"seven"})
assert table.select().execute().fetchall() == [(1, 'five'), (2, 'seven')]
def test_extract_expression(self):
meta = MetaData(testing.db)
table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
meta.create_all()
try:
table.insert().execute({
"dt":
datetime.datetime(2010, 5, 1, 12, 11, 10),
"d":
datetime.date(2010, 5, 1),
})
rs = select(
[extract("year", table.c.dt),
extract("month", table.c.d)]).execute()
row = rs.first()
assert row[0] == 2010
assert row[1] == 5
rs.close()
finally:
meta.drop_all()
def test_extract_expression(self):
meta = MetaData(testing.db)
table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
meta.create_all()
try:
table.insert().execute(
{
"dt": datetime.datetime(2010, 5, 1, 12, 11, 10),
"d": datetime.date(2010, 5, 1),
}
)
rs = select(
[extract("year", table.c.dt), extract("month", table.c.d)]
).execute()
row = rs.first()
assert row[0] == 2010
assert row[1] == 5
rs.close()
finally:
meta.drop_all()
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'})
def test_compiled_insert(self):
table = Table('testtable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String(30)))
metadata.create_all()
ins = table.insert(values={'data': bindparam('x')}).compile()
ins.execute({'x': "five"}, {'x': "seven"})
assert table.select().execute().fetchall() == [(1, 'five'),
(2, 'seven')]
def test_returning_no_autoinc(self):
meta = self.metadata
table = Table(
't1', meta,
Column('id', Integer, primary_key=True),
Column('data', String(50)))
table.create()
result = table.insert().values(
id=1,
data=func.lower('SomeString')).\
returning(table.c.id, table.c.data).execute()
eq_(result.fetchall(), [(1, 'somestring')])
def test_returning_no_autoinc(self, connection):
meta = self.metadata
table = Table(
"t1",
meta,
Column("id", Integer, primary_key=True),
Column("data", String(50)),
)
table.create(connection)
result = connection.execute(table.insert().values(
id=1,
data=func.lower("SomeString")).returning(table.c.id, table.c.data))
eq_(result.fetchall(), [(1, "somestring")])
def test_executemany():
# return value is documented as failing with psycopg2/executemany
result2 = table.insert(postgres_returning=[table]).execute([{
'persons':
2,
'full':
False
}, {
'persons':
3,
'full':
True
}])
eq_(result2.fetchall(), [(2, 2, False), (3, 3, True)])
def test_update_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta, Column('id', Integer, 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.update(table.c.persons > 4,
dict(full=True),
postgres_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(), [(1, True), (2, False)])
finally:
table.drop()
def test_returning_no_autoinc(self):
meta = MetaData(testing.db)
table = Table('t1', meta, Column('id', Integer, primary_key=True),
Column('data', String(50)))
table.create()
try:
result = table.insert().values(
id=1, data=func.lower('SomeString')).returning(
table.c.id, table.c.data).execute()
eq_(result.fetchall(), [(1, 'somestring')])
finally:
# this will hang if the "SET IDENTITY_INSERT t1 OFF" occurs
# before the result is fetched
table.drop()
def test_returning_no_autoinc(self):
meta = MetaData(testing.db)
table = Table('t1', meta, Column('id', Integer,
primary_key=True), Column('data', String(50)))
table.create()
try:
result = table.insert().values(id=1,
data=func.lower('SomeString'
)).returning(table.c.id, table.c.data).execute()
eq_(result.fetchall(), [(1, 'somestring')])
finally:
# this will hang if the "SET IDENTITY_INSERT t1 OFF" occurs
# before the result is fetched
table.drop()
def test_returning_no_autoinc(self):
meta = self.metadata
table = Table(
"t1",
meta,
Column("id", Integer, primary_key=True),
Column("data", String(50)),
)
table.create()
result = (
table.insert()
.values(id=1, data=func.lower("SomeString"))
.returning(table.c.id, table.c.data)
.execute()
)
eq_(result.fetchall(), [(1, "somestring")])
def test_extract_expression(self, connection):
meta = self.metadata
table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
meta.create_all(connection)
connection.execute(
table.insert(),
{
"dt": datetime.datetime(2010, 5, 1, 12, 11, 10),
"d": datetime.date(2010, 5, 1),
},
)
rs = connection.execute(
select(extract("year", table.c.dt), extract("month", table.c.d)))
row = rs.first()
assert row[0] == 2010
assert row[1] == 5
rs.close()
def go():
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'})
def go():
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'})
def test_executemany():
# return value is documented as failing with psycopg2/executemany
result2 = table.insert(postgres_returning=[table]).execute(
[{'persons': 2, 'full': False}, {'persons': 3, 'full': True}])
eq_(result2.fetchall(), [(2, 2, False), (3,3,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'})
class InsertTest(TestBase, AssertsExecutionResults):
__only_on__ = 'postgres'
def setUpAll(self):
global metadata
metadata = MetaData(testing.db)
def tearDown(self):
metadata.drop_all()
metadata.tables.clear()
def test_compiled_insert(self):
table = Table('testtable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String(30)))
metadata.create_all()
ins = table.insert(values={'data': bindparam('x')}).compile()
ins.execute({'x': "five"}, {'x': "seven"})
assert table.select().execute().fetchall() == [(1, 'five'),
(2, 'seven')]
def test_sequence_insert(self):
table = Table(
'testtable', metadata,
Column('id', Integer, Sequence('my_seq'), primary_key=True),
Column('data', String(30)))
metadata.create_all()
self._assert_data_with_sequence(table, "my_seq")
def test_opt_sequence_insert(self):
table = Table(
'testtable', metadata,
Column('id',
Integer,
Sequence('my_seq', optional=True),
primary_key=True), Column('data', String(30)))
metadata.create_all()
self._assert_data_autoincrement(table)
def test_autoincrement_insert(self):
table = Table('testtable', metadata,
Column('id', Integer, primary_key=True),
Column('data', String(30)))
metadata.create_all()
self._assert_data_autoincrement(table)
def test_noautoincrement_insert(self):
table = Table(
'testtable', metadata,
Column('id', Integer, primary_key=True, autoincrement=False),
Column('data', String(30)))
metadata.create_all()
self._assert_data_noautoincrement(table)
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()
def _assert_data_with_sequence(self, table, seqname):
def go():
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'})
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 (id, data) VALUES (nextval('%s'), :data)"
% seqname, [{
'data': 'd5'
}, {
'data': 'd6'
}]),
("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{
'id':
33,
'data':
'd7'
}]),
("INSERT INTO testtable (id, data) VALUES (nextval('%s'), :data)"
% seqname, [{
'data': 'd8'
}]),
])
assert table.select().execute().fetchall() == [
(30, 'd1'),
(1, 'd2'),
(31, 'd3'),
(32, 'd4'),
(2, 'd5'),
(3, 'd6'),
(33, 'd7'),
(4, 'd8'),
]
# cant test reflection here since the Sequence must be
# explicitly specified
def _assert_data_noautoincrement(self, table):
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:
assert "violates not-null constraint" in str(e)
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'})
