Python Table.insert方法代码示例

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_passive_override(self):
        """
        Primarily for postgres, tests that when we get a primary key column
        back from reflecting a table which has a default value on it, we
        pre-execute that DefaultClause upon insert, even though DefaultClause
        says "let the database execute this", because in postgres we must have
        all the primary key values in memory before insert; otherwise we can't
        locate the just inserted row.

        """
        # TODO: move this to dialect/postgres
        try:
            meta = MetaData(testing.db)
            testing.db.execute("""
             CREATE TABLE speedy_users
             (
                 speedy_user_id   SERIAL     PRIMARY KEY,

                 user_name        VARCHAR    NOT NULL,
                 user_password    VARCHAR    NOT NULL
             );
            """, None)

            t = Table("speedy_users", meta, autoload=True)
            t.insert().execute(user_name='user', user_password='lala')
            l = t.select().execute().fetchall()
            eq_(l, [(1, 'user', 'lala')])
        finally:
            testing.db.execute("drop table speedy_users", None)

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_autoincrement_fk(self):
        nodes = Table('nodes', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('parent_id', Integer, ForeignKey('nodes.id')),
            Column('data', String(30)))
        nodes.create()

        r = nodes.insert().execute(data='foo')
        id_ = r.last_inserted_ids()[0]
        nodes.insert().execute(data='bar', parent_id=id_)

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
 def setup(self):
     global meta, table, engine
     engine = engines.reconnecting_engine()
     meta = MetaData(engine)
     table = Table('sometable', meta,
         Column('id', Integer, primary_key=True),
         Column('name', String(50)))
     meta.create_all()
     table.insert().execute(
         [{'id':i, 'name':'row %d' % i} for i in range(1, 100)]
     )

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_non_autoincrement(self):
        # sqlite INT primary keys can be non-unique! (only for ints)
        nonai = Table("nonaitest", self.metadata,
            Column('id', Integer, autoincrement=False, primary_key=True),
            Column('data', String(20)))
        nonai.create()


        try:
            # postgres + mysql strict will fail on first row,
            # mysql in legacy mode fails on second row
            nonai.insert().execute(data='row 1')
            nonai.insert().execute(data='row 2')
            assert False
        except sa.exc.SQLError, e:
            assert True

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_autoincrement_single_col(self):
        single = Table('single', self.metadata,
                       Column('id', Integer, primary_key=True))
        single.create()

        r = single.insert().execute()
        id_ = r.last_inserted_ids()[0]
        assert id_ is not None
        eq_(1, sa.select([func.count(sa.text('*'))], from_obj=single).scalar())

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
 def test_implicit_execution(self):
     metadata = MetaData()
     table = Table("test_table", metadata, Column("foo", Integer), test_needs_acid=True)
     conn = testing.db.connect()
     metadata.create_all(bind=conn)
     try:
         trans = conn.begin()
         metadata.bind = conn
         t = table.insert()
         assert t.bind is conn
         table.insert().execute(foo=5)
         table.insert().execute(foo=6)
         table.insert().execute(foo=7)
         trans.rollback()
         metadata.bind = None
         assert conn.execute("select count(1) from test_table").scalar() == 0
     finally:
         metadata.drop_all(bind=conn)

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_row_c_sequence_check(self):
        import csv
        import collections
        from StringIO import StringIO

        metadata = MetaData()
        metadata.bind = 'sqlite://'
        users = Table('users', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(40)),
        )
        users.create()

        users.insert().execute(name='Test')
        row = users.select().execute().fetchone()

        s = StringIO()
        writer = csv.writer(s)
        # csv performs PySequenceCheck call
        writer.writerow(row)
        assert s.getvalue().strip() == '1,Test'

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
 def test_empty_insert(self):
     metadata = MetaData(testing.db)
     t1 = Table('t1', metadata,
             Column('is_true', Boolean, server_default=('1')))
     metadata.create_all()
     
     try:
         result = t1.insert().execute()
         eq_(1, select([func.count(text('*'))], from_obj=t1).scalar())
         eq_(True, t1.select().scalar())
     finally:
         metadata.drop_all()

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_no_rowcount_on_selects_inserts(self):
        """assert that rowcount is only called on deletes and updates.

        This because cursor.rowcount can be expensive on some dialects
        such as Firebird.

        """

        engine = engines.testing_engine()
        metadata.bind = engine

        t = Table('t1', metadata,
            Column('data', String(10))
        )
        metadata.create_all()

        class BreakRowcountMixin(object):
            @property
            def rowcount(self):
                assert False

        execution_ctx_cls = engine.dialect.execution_ctx_cls
        engine.dialect.execution_ctx_cls = type("FakeCtx", 
                                            (BreakRowcountMixin, 
                                            execution_ctx_cls), 
                                            {})

        try:
            r = t.insert().execute({'data': 'd1'}, {'data': 'd2'},
                                   {'data': 'd3'})
            eq_(t.select().execute().fetchall(), [('d1', ), ('d2', ),
                ('d3', )])
            assert_raises(AssertionError, t.update().execute, {'data'
                          : 'd4'})
            assert_raises(AssertionError, t.delete().execute)
        finally:
            engine.dialect.execution_ctx_cls = execution_ctx_cls

# 需要导入模块: from sqlalchemy.test.schema import Table [as 别名]
# 或者: from sqlalchemy.test.schema.Table import insert [as 别名]
    def test_proxy(self):
        
        stmts = []
        cursor_stmts = []
        
        class MyProxy(ConnectionProxy):
            def execute(self, conn, execute, clauseelement, *multiparams, **params):
                stmts.append(
                    (str(clauseelement), params,multiparams)
                )
                return execute(clauseelement, *multiparams, **params)

            def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
                cursor_stmts.append(
                    (str(statement), parameters, None)
                )
                return execute(cursor, statement, parameters, context)
        
        def assert_stmts(expected, received):
            for stmt, params, posn in expected:
                if not received:
                    assert False
                while received:
                    teststmt, testparams, testmultiparams = received.pop(0)
                    teststmt = re.compile(r'[\n\t ]+', re.M).sub(' ', teststmt).strip()
                    if teststmt.startswith(stmt) and (testparams==params or testparams==posn):
                        break

        for engine in (
            engines.testing_engine(options=dict(implicit_returning=False, proxy=MyProxy())),
            engines.testing_engine(options=dict(
                                                    implicit_returning=False, 
                                                    proxy=MyProxy(), 
                                                    strategy='threadlocal'))
        ):
            m = MetaData(engine)

            t1 = Table('t1', m, 
                    Column('c1', Integer, primary_key=True), 
                    Column('c2', String(50), default=func.lower('Foo'), primary_key=True)
            )

            m.create_all()
            try:
                t1.insert().execute(c1=5, c2='some data')
                t1.insert().execute(c1=6)
                eq_(engine.execute("select * from t1").fetchall(),
                    [(5, 'some data'), (6, 'foo')]
                )
            finally:
                m.drop_all()
            
            engine.dispose()
            
            compiled = [
                ("CREATE TABLE t1", {}, None),
                ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, None),
                ("INSERT INTO t1 (c1, c2)", {'c1': 6}, None),
                ("select * from t1", {}, None),
                ("DROP TABLE t1", {}, None)
            ]

            if not testing.against('oracle+zxjdbc'): # or engine.dialect.preexecute_pk_sequences:
                cursor = [
                    ("CREATE TABLE t1", {}, ()),
                    ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, (5, 'some data')),
                    ("SELECT lower", {'lower_2':'Foo'}, ('Foo',)),
                    ("INSERT INTO t1 (c1, c2)", {'c2': 'foo', 'c1': 6}, (6, 'foo')),
                    ("select * from t1", {}, ()),
                    ("DROP TABLE t1", {}, ())
                ]
            else:
                insert2_params = (6, 'Foo')
                if testing.against('oracle+zxjdbc'):
                    from sqlalchemy.dialects.oracle.zxjdbc import ReturningParam
                    insert2_params.append(ReturningParam(12))
                cursor = [
                    ("CREATE TABLE t1", {}, ()),
                    ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, (5, 'some data')),
                    # bind param name 'lower_2' might be incorrect
                    ("INSERT INTO t1 (c1, c2)", {'c1': 6, "lower_2":"Foo"}, insert2_params),  
                    ("select * from t1", {}, ()),
                    ("DROP TABLE t1", {}, ())
                ]
                
            assert_stmts(compiled, stmts)
            assert_stmts(cursor, cursor_stmts)