예제 #1
0
    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()
예제 #2
0
파일: postgres.py 프로젝트: jrus/sqlalchemy
    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()
예제 #3
0
    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()
예제 #4
0
    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()
예제 #5
0
    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()
예제 #6
0
    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()
예제 #7
0
    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()
예제 #8
0
    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()
예제 #9
0
    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()
예제 #10
0
    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()
예제 #11
0
    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()
예제 #12
0
    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()
예제 #13
0
    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()
예제 #14
0
    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()
예제 #15
0
파일: postgres.py 프로젝트: jrus/sqlalchemy
    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()
예제 #16
0
    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()