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()
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 = 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_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, 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_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})
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 createTable(self,
tablename,
columns,
scheme=None,
temporary=False,
truncate=False,
bind=None):
"""Creates a (temporary) table from sqlalchemy columns and returns the corresponding tablemapper"""
if bind:
mdata = MetaData(bind=bind)
else:
bind = self.dbeng
mdata = self.mdata
if truncate:
self.truncateTable(tablename, scheme)
if tablename in mdata.tables:
table = mdata.tables[tablename]
else:
if temporary:
table = Table(tablename,
mdata,
*columns,
prefixes=['TEMPORARY'],
postgresql_on_commit='PRESERVE ROWS')
else:
table = Table(tablename, mdata, *columns, schema=scheme)
table.create(bind=bind, checkfirst=True)
return tableMapFactory(tablename, table)
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_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_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_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_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 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 = 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):
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_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_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()
