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})
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_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_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_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 testidentity(self):
meta = MetaData(testing.db)
table = Table(
'identity_test', meta,
Column('col1', Integer, Sequence('fred', 2, 3), primary_key=True))
table.create()
meta2 = MetaData(testing.db)
try:
table2 = Table('identity_test', meta2, autoload=True)
assert table2.c['col1'].sequence.start == 2
assert table2.c['col1'].sequence.increment == 3
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 testidentity(self):
meta = MetaData(testing.db)
table = Table(
'identity_test', meta,
Column('col1', Integer, Sequence('fred', 2, 3), primary_key=True)
)
table.create()
meta2 = MetaData(testing.db)
try:
table2 = Table('identity_test', meta2, autoload=True)
assert table2.c['col1'].sequence.start == 2
assert table2.c['col1'].sequence.increment == 3
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_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_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_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_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()
