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