예제 #1
0
    def test_column_targeting(self, connection):
        table = self.tables.tables
        result = connection.execute(
            table.insert().returning(table.c.id, table.c.full),
            {
                "persons": 1,
                "full": False
            },
        )

        row = result.first()._mapping
        assert row[table.c.id] == row["id"] == 1
        assert row[table.c.full] == row["full"]
        assert row["full"] is False

        result = connection.execute(table.insert().values(
            persons=5, full=True,
            goofy="somegoofy").returning(table.c.persons, table.c.full,
                                         table.c.goofy))
        row = result.first()._mapping
        assert row[table.c.persons] == row["persons"] == 5
        assert row[table.c.full] == row["full"]

        eq_(row[table.c.goofy], row["goofy"])
        eq_(row["goofy"], "FOOsomegoofyBAR")
예제 #2
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=func.foobar()),
        )

        self.assert_compile(
            table.insert().values(),
            "INSERT INTO sometable (foo) VALUES (foobar())",
            inline_flag=False,
        )

        self.assert_compile(
            table.insert(),
            "INSERT INTO sometable (foo) VALUES (foobar())",
            params={},
            inline_flag=False,
        )

        self.assert_compile(
            table.insert().values().inline(),
            "INSERT INTO sometable (foo) VALUES (foobar())",
            inline_flag=True,
        )

        self.assert_compile(
            table.insert().inline(),
            "INSERT INTO sometable (foo) VALUES (foobar())",
            params={},
            inline_flag=True,
        )
예제 #3
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('foo', Integer, default=func.foobar()))

        self.assert_compile(table.insert(values={}, inline=True),
                            'INSERT INTO sometable (foo) VALUES (foobar())')

        self.assert_compile(table.insert(inline=True),
                            'INSERT INTO sometable (foo) VALUES (foobar())',
                            params={})
예제 #4
0
    def test_anon_expressions(self, connection):
        table = self.tables.tables
        GoofyType = self.GoofyType
        result = connection.execute(
            table.insert().values(goofy="someOTHERgoofy").returning(
                func.lower(table.c.goofy, type_=GoofyType)))
        row = result.first()
        eq_(row[0], "foosomeothergoofyBAR")

        result = connection.execute(
            table.insert().values(persons=12).returning(table.c.persons + 18))
        row = result.first()
        eq_(row[0], 30)
예제 #5
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('foo', Integer, default=func.foobar()))

        self.assert_compile(table.insert(values={}, inline=True),
                            'INSERT INTO sometable (foo) VALUES (foobar())')

        self.assert_compile(
            table.insert(
                inline=True),
            'INSERT INTO sometable (foo) VALUES (foobar())',
            params={})
예제 #6
0
    def test_sql_expression_pk_noautoinc_returning(self):
        # test that return_defaults() works with a primary key where we are
        # sending a SQL expression, and we need to get the server-calculated
        # value back.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, autoincrement=False, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        returning_dialect = postgresql.dialect()
        returning_dialect.implicit_returning = True
        compiled = stmt.compile(
            dialect=returning_dialect, column_keys=["data"]
        )
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [table.c.id])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES "
            "(foobar(), %(data)s) RETURNING sometable.id",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=returning_dialect,
        )
예제 #7
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer, default=func.foobar()))

        values = [
            {'id': 1, 'data': 'data1'},
            {'id': 2, 'data': 'data2', 'foo': 'plainfoo'},
            {'id': 3, 'data': 'data3'},
        ]

        checkparams = {
            'id_0': 1,
            'id_1': 2,
            'id_2': 3,
            'data_0': 'data1',
            'data_1': 'data2',
            'data_2': 'data3',
            'foo_1': 'plainfoo',
        }

        self.assert_compile(
            table.insert().values(values),
            'INSERT INTO sometable (id, data, foo) VALUES '
            '(%(id_0)s, %(data_0)s, foobar()), '
            '(%(id_1)s, %(data_1)s, %(foo_1)s), '
            '(%(id_2)s, %(data_2)s, foobar())',
            checkparams=checkparams,
            dialect=postgresql.dialect())
예제 #8
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer, default=func.foobar()))

        values = [
            {'id': 1, 'data': 'data1'},
            {'id': 2, 'data': 'data2', 'foo': 'plainfoo'},
            {'id': 3, 'data': 'data3'},
        ]

        checkparams = {
            'id_m0': 1,
            'id_m1': 2,
            'id_m2': 3,
            'data_m0': 'data1',
            'data_m1': 'data2',
            'data_m2': 'data3',
            'foo_m1': 'plainfoo',
        }

        self.assert_compile(
            table.insert().values(values),
            'INSERT INTO sometable (id, data, foo) VALUES '
            '(%(id_m0)s, %(data_m0)s, foobar()), '
            '(%(id_m1)s, %(data_m1)s, %(foo_m1)s), '
            '(%(id_m2)s, %(data_m2)s, foobar())',
            checkparams=checkparams,
            dialect=postgresql.dialect())
예제 #9
0
    def test_server_default_absent_value(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, server_default=func.foobar()),
        )

        values = [
            {
                "id": 1,
                "data": "data1",
                "foo": "plainfoo"
            },
            {
                "id": 2,
                "data": "data2"
            },
            {
                "id": 3,
                "data": "data3",
                "foo": "otherfoo"
            },
        ]

        assert_raises_message(
            exc.CompileError,
            "INSERT value for column sometable.foo is explicitly rendered "
            "as a boundparameter in the VALUES clause; a Python-side value or "
            "SQL expression is required",
            table.insert().values(values).compile,
        )
예제 #10
0
    def test_sql_expression_pk_noautoinc_returning(self):
        # test that return_defaults() works with a primary key where we are
        # sending a SQL expression, and we need to get the server-calculated
        # value back.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column(
                "id",
                Integer,
                autoincrement=False,
                primary_key=True,
            ),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        returning_dialect = postgresql.dialect()
        returning_dialect.implicit_returning = True
        compiled = stmt.compile(dialect=returning_dialect,
                                column_keys=["data"])
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [table.c.id])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES "
            "(foobar(), %(data)s) RETURNING sometable.id",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=returning_dialect,
        )
예제 #11
0
    def test_sql_expression_pk_autoinc_lastinserted(self):
        # test that postfetch isn't invoked for a SQL expression
        # in a primary key column.  the DB either needs to support a lastrowid
        # that can return it, or RETURNING.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        compiled = stmt.compile(dialect=sqlite.dialect(), column_keys=["data"])
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES "
            "(foobar(), ?)",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=sqlite.dialect(),
        )
예제 #12
0
    def test_python_fn_default(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, default=lambda: 10),
        )

        values = [
            {"id": 1, "data": "data1"},
            {"id": 2, "data": "data2", "foo": 15},
            {"id": 3, "data": "data3"},
        ]

        checkparams = {
            "id_m0": 1,
            "id_m1": 2,
            "id_m2": 3,
            "data_m0": "data1",
            "data_m1": "data2",
            "data_m2": "data3",
            "foo": None,  # evaluated later
            "foo_m1": 15,
            "foo_m2": None,  # evaluated later
        }

        stmt = table.insert().values(values)
        eq_(
            dict(
                [
                    (k, v.type._type_affinity)
                    for (k, v) in stmt.compile(
                        dialect=postgresql.dialect()
                    ).binds.items()
                ]
            ),
            {
                "foo": Integer,
                "data_m2": String,
                "id_m0": Integer,
                "id_m2": Integer,
                "foo_m1": Integer,
                "data_m1": String,
                "id_m1": Integer,
                "foo_m2": Integer,
                "data_m0": String,
            },
        )

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data, foo) VALUES "
            "(%(id_m0)s, %(data_m0)s, %(foo)s), "
            "(%(id_m1)s, %(data_m1)s, %(foo_m1)s), "
            "(%(id_m2)s, %(data_m2)s, %(foo_m2)s)",
            checkparams=checkparams,
            dialect=postgresql.dialect(),
        )
예제 #13
0
    def test_insert_from_select_fn_defaults(self):
        metadata = MetaData()

        def foo(ctx):
            return 12

        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=foo),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id, foo) SELECT "
            "mytable.myid, :foo AS anon_1 "
            "FROM mytable WHERE mytable.name = :name_1",
            # value filled in at execution time
            checkparams={
                "name_1": "foo",
                "foo": None
            },
        )
예제 #14
0
    def test_server_default(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, server_default=func.foobar()),
        )

        values = [
            {"id": 1, "data": "data1"},
            {"id": 2, "data": "data2", "foo": "plainfoo"},
            {"id": 3, "data": "data3"},
        ]

        checkparams = {
            "id_m0": 1,
            "id_m1": 2,
            "id_m2": 3,
            "data_m0": "data1",
            "data_m1": "data2",
            "data_m2": "data3",
        }

        self.assert_compile(
            table.insert().values(values),
            "INSERT INTO sometable (id, data) VALUES "
            "(%(id_m0)s, %(data_m0)s), "
            "(%(id_m1)s, %(data_m1)s), "
            "(%(id_m2)s, %(data_m2)s)",
            checkparams=checkparams,
            dialect=postgresql.dialect(),
        )
예제 #15
0
    def test_update_returning_w_type_coerce_expression(self, connection):
        table = self.tables.tables
        connection.execute(
            table.insert(),
            [
                {
                    "persons": 5,
                    "goofy": "somegoofy1"
                },
                {
                    "persons": 3,
                    "goofy": "somegoofy2"
                },
            ],
        )

        result = connection.execute(table.update().where(
            table.c.persons > 4).values(goofy="newgoofy").returning(
                type_coerce(table.c.goofy, String)))
        eq_(result.fetchall(), [("FOOnewgoofy", )])

        result2 = connection.execute(
            select(table.c.id, table.c.goofy).order_by(table.c.id))
        eq_(
            result2.fetchall(),
            [(1, "FOOnewgoofyBAR"), (2, "FOOsomegoofy2BAR")],
        )
예제 #16
0
    def test_update_returning_w_expression_one(self, connection):
        table = self.tables.tables
        connection.execute(
            table.insert(),
            [
                {
                    "persons": 5,
                    "full": False,
                    "strval": "str1"
                },
                {
                    "persons": 3,
                    "full": False,
                    "strval": "str2"
                },
            ],
        )

        result = connection.execute(table.update().where(
            table.c.persons > 4).values(full=True).returning(table.c.strval +
                                                             "hi"))
        eq_(result.fetchall(), [("str1hi", )])

        result2 = connection.execute(
            select(table.c.id, table.c.strval).order_by(table.c.id))
        eq_(result2.fetchall(), [(1, "str1"), (2, "str2")])
예제 #17
0
    def test_server_default_absent_value(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer, server_default=func.foobar()))

        values = [
            {
                'id': 1,
                'data': 'data1',
                'foo': 'plainfoo'
            },
            {
                'id': 2,
                'data': 'data2'
            },
            {
                'id': 3,
                'data': 'data3',
                'foo': 'otherfoo'
            },
        ]

        assert_raises_message(
            exc.CompileError,
            "INSERT value for column sometable.foo is explicitly rendered "
            "as a boundparameter in the VALUES clause; a Python-side value or "
            "SQL expression is required",
            table.insert().values(values).compile)
예제 #18
0
    def test_python_scalar_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String), Column('foo',
                                                     Integer,
                                                     default=10))

        values = [
            {
                'id': 1,
                'data': 'data1'
            },
            {
                'id': 2,
                'data': 'data2',
                'foo': 15
            },
            {
                'id': 3,
                'data': 'data3'
            },
        ]

        checkparams = {
            'id_0': 1,
            'id_1': 2,
            'id_2': 3,
            'data_0': 'data1',
            'data_1': 'data2',
            'data_2': 'data3',
            'foo': None,  # evaluated later
            'foo_1': 15,
            'foo_2': None  # evaluated later
        }

        stmt = table.insert().values(values)

        eq_(
            dict([(k, v.type._type_affinity) for (k, v) in stmt.compile(
                dialect=postgresql.dialect()).binds.items()]), {
                    'foo': Integer,
                    'data_2': String,
                    'id_0': Integer,
                    'id_2': Integer,
                    'foo_1': Integer,
                    'data_1': String,
                    'id_1': Integer,
                    'foo_2': Integer,
                    'data_0': String
                })

        self.assert_compile(stmt,
                            'INSERT INTO sometable (id, data, foo) VALUES '
                            '(%(id_0)s, %(data_0)s, %(foo)s), '
                            '(%(id_1)s, %(data_1)s, %(foo_1)s), '
                            '(%(id_2)s, %(data_2)s, %(foo_2)s)',
                            checkparams=checkparams,
                            dialect=postgresql.dialect())
예제 #19
0
 def test_anon_expressions_selected_columns(self, table_fixture):
     table = table_fixture
     stmt = (table.insert().values(goofy="someOTHERgoofy").returning(
         func.lower(table.c.x)))
     self.assert_compile(
         select(stmt.exported_columns[0]),
         "SELECT lower(foo.x) AS lower_1 FROM foo",
     )
예제 #20
0
    def test_insert_returning(self, connection):
        table = self.tables.tables
        result = connection.execute(table.insert().returning(table.c.id), {
            "persons": 1,
            "full": False
        })

        eq_(result.fetchall(), [(1, )])
예제 #21
0
 def test_named_expressions_selected_columns(self, table_fixture):
     table = table_fixture
     stmt = (table.insert().values(goofy="someOTHERgoofy").returning(
         func.lower(table.c.x).label("goof")))
     self.assert_compile(
         select(stmt.exported_columns.goof),
         "SELECT lower(foo.x) AS goof FROM foo",
     )
예제 #22
0
    def test_insert(self, connection):
        table = self.tables.tables
        result = connection.execute(table.insert().returning(table.c.foo_id),
                                    dict(data="somedata"))
        row = result.first()._mapping
        assert row[table.c.foo_id] == row["id"] == 1

        result = connection.execute(table.select()).first()._mapping
        assert row[table.c.foo_id] == row["id"] == 1
예제 #23
0
 def test_insert(self, connection):
     table = self.tables.tables
     r = connection.execute(table.insert().values(data="hi").returning(
         table.c.id))
     eq_(r.first(), tuple([testing.db.dialect.default_sequence_base]))
     eq_(
         connection.scalar(self.sequences.tid_seq),
         testing.db.dialect.default_sequence_base + 1,
     )
예제 #24
0
    def test_sql_functions(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String), Column('foo', Integer))

        values = [
            {
                "id": 1,
                "data": "foo",
                "foo": func.foob()
            },
            {
                "id": 2,
                "data": "bar",
                "foo": func.foob()
            },
            {
                "id": 3,
                "data": "bar",
                "foo": func.bar()
            },
            {
                "id": 4,
                "data": "bar",
                "foo": 15
            },
            {
                "id": 5,
                "data": "bar",
                "foo": func.foob()
            },
        ]
        checkparams = {
            'id_0': 1,
            'data_0': 'foo',
            'id_1': 2,
            'data_1': 'bar',
            'id_2': 3,
            'data_2': 'bar',
            'id_3': 4,
            'data_3': 'bar',
            'foo_3': 15,
            'id_4': 5,
            'data_4': 'bar'
        }

        self.assert_compile(table.insert().values(values),
                            "INSERT INTO sometable (id, data, foo) VALUES "
                            "(%(id_0)s, %(data_0)s, foob()), "
                            "(%(id_1)s, %(data_1)s, foob()), "
                            "(%(id_2)s, %(data_2)s, bar()), "
                            "(%(id_3)s, %(data_3)s, %(foo_3)s), "
                            "(%(id_4)s, %(data_4)s, foob())",
                            checkparams=checkparams,
                            dialect=postgresql.dialect())
예제 #25
0
    def test_inline_default(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=func.foobar()),
        )

        self.assert_compile(
            table.insert(values={}, inline=True),
            "INSERT INTO sometable (foo) VALUES (foobar())",
        )

        self.assert_compile(
            table.insert(inline=True),
            "INSERT INTO sometable (foo) VALUES (foobar())",
            params={},
        )
예제 #26
0
    def test_insert_literal_binds_sequence_notimplemented(self):
        table = Table('x', MetaData(), Column('y', Integer, Sequence('y_seq')))
        dialect = default.DefaultDialect()
        dialect.supports_sequences = True

        stmt = table.insert().values(myid=3, name='jack')

        assert_raises(NotImplementedError,
                      stmt.compile,
                      compile_kwargs=dict(literal_binds=True),
                      dialect=dialect)
예제 #27
0
파일: db.py 프로젝트: joshbenner/mudsling
    def insert(self, table, **kw):
        """
        Convenience insert of a single row to the specified table.

        Executes as an operation, returning no value.

        :param table: The table to insert into.
        :param kw: The columns to insert and their values.

        :rtype: twisted.internet.defer.Deferred
        """
        return self.operation(table.insert().values(**kw))
예제 #28
0
    def insert(self, table, **kw):
        """
        Convenience insert of a single row to the specified table.

        Executes as an operation, returning no value.

        :param table: The table to insert into.
        :param kw: The columns to insert and their values.

        :rtype: twisted.internet.defer.Deferred
        """
        return self.operation(table.insert().values(**kw))
예제 #29
0
    def test_insert_literal_binds_sequence_notimplemented(self):
        table = Table("x", MetaData(), Column("y", Integer, Sequence("y_seq")))
        dialect = default.DefaultDialect()
        dialect.supports_sequences = True

        stmt = table.insert().values(myid=3, name="jack")

        assert_raises(
            NotImplementedError,
            stmt.compile,
            compile_kwargs=dict(literal_binds=True),
            dialect=dialect,
        )
예제 #30
0
 def test_insert_from_select_with_sql_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=func.foobar()))
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id"], sel)
     self.assert_compile(ins, "INSERT INTO sometable (id, foo) SELECT "
                         "mytable.myid, foobar() AS foobar_1 "
                         "FROM mytable WHERE mytable.name = :name_1",
                         checkparams={"name_1": "foo"})
예제 #31
0
 def test_no_ipk_on_returning(self, connection):
     table = self.tables.tables
     result = connection.execute(table.insert().returning(table.c.id), {
         "persons": 1,
         "full": False
     })
     assert_raises_message(
         sa_exc.InvalidRequestError,
         r"Can't call inserted_primary_key when returning\(\) is used.",
         getattr,
         result,
         "inserted_primary_key",
     )
예제 #32
0
 def test_insert_from_select_override_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=12))
     table1 = self.tables.mytable
     sel = select([table1.c.myid,
                   table1.c.myid.label('q')]).where(table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id", "foo"], sel)
     self.assert_compile(ins, "INSERT INTO sometable (id, foo) SELECT "
                         "mytable.myid, mytable.myid AS q "
                         "FROM mytable WHERE mytable.name = :name_1",
                         checkparams={"name_1": "foo"})
예제 #33
0
    def test_insert_returning_w_type_coerce_expression(self, connection):
        table = self.tables.tables
        result = connection.execute(
            table.insert().returning(type_coerce(table.c.goofy, String)),
            {
                "persons": 5,
                "goofy": "somegoofy"
            },
        )

        eq_(result.fetchall(), [("FOOsomegoofy", )])

        result2 = connection.execute(
            select(table.c.id, table.c.goofy).order_by(table.c.id))
        eq_(result2.fetchall(), [(1, "FOOsomegoofyBAR")])
예제 #34
0
 def test_insert_from_select_no_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=func.foobar()))
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id"], sel, include_defaults=False)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id) SELECT mytable.myid "
         "FROM mytable WHERE mytable.name = :name_1",
         checkparams={"name_1": "foo"}
     )
예제 #35
0
    def test_insert_returning_w_expression_one(self, connection):
        table = self.tables.tables
        result = connection.execute(
            table.insert().returning(table.c.strval + "hi"),
            {
                "persons": 5,
                "full": False,
                "strval": "str1"
            },
        )

        eq_(result.fetchall(), [("str1hi", )])

        result2 = connection.execute(
            select(table.c.id, table.c.strval).order_by(table.c.id))
        eq_(result2.fetchall(), [(1, "str1")])
예제 #36
0
    def test_delete_full_returning(self, connection):
        table = self.tables.tables
        connection.execute(
            table.insert(),
            [{
                "persons": 5,
                "full": False
            }, {
                "persons": 3,
                "full": False
            }],
        )

        result = connection.execute(table.delete().returning(
            table.c.id, table.c.full))
        eq_(result.fetchall(), [(1, False), (2, False)])
예제 #37
0
 def test_insert_from_select_with_python_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=12))
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id"], sel)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id, foo) SELECT "
         "mytable.myid, :foo AS anon_1 "
         "FROM mytable WHERE mytable.name = :name_1",
         # value filled in at execution time
         checkparams={"name_1": "foo", "foo": None}
     )
예제 #38
0
    def test_update_full_returning(self, connection):
        table = self.tables.tables
        connection.execute(
            table.insert(),
            [{
                "persons": 5,
                "full": False
            }, {
                "persons": 3,
                "full": False
            }],
        )

        result = connection.execute(table.update().where(
            table.c.persons > 2).values(full=True).returning(
                table.c.id, table.c.full))
        eq_(result.fetchall(), [(1, True), (2, True)])
예제 #39
0
    def test_python_scalar_default(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer, default=10))

        values = [
            {'id': 1, 'data': 'data1'},
            {'id': 2, 'data': 'data2', 'foo': 15},
            {'id': 3, 'data': 'data3'},
        ]

        checkparams = {
            'id_m0': 1,
            'id_m1': 2,
            'id_m2': 3,
            'data_m0': 'data1',
            'data_m1': 'data2',
            'data_m2': 'data3',
            'foo': None,  # evaluated later
            'foo_m1': 15,
            'foo_m2': None  # evaluated later
        }

        stmt = table.insert().values(values)

        eq_(
            dict([
                (k, v.type._type_affinity)
                for (k, v) in
                stmt.compile(dialect=postgresql.dialect()).binds.items()]),
            {
                'foo': Integer, 'data_m2': String, 'id_m0': Integer,
                'id_m2': Integer, 'foo_m1': Integer, 'data_m1': String,
                'id_m1': Integer, 'foo_m2': Integer, 'data_m0': String}
        )

        self.assert_compile(
            stmt,
            'INSERT INTO sometable (id, data, foo) VALUES '
            '(%(id_m0)s, %(data_m0)s, %(foo)s), '
            '(%(id_m1)s, %(data_m1)s, %(foo_m1)s), '
            '(%(id_m2)s, %(data_m2)s, %(foo_m2)s)',
            checkparams=checkparams,
            dialect=postgresql.dialect())
예제 #40
0
 def test_insert_from_select_no_defaults(self):
     metadata = MetaData()
     table = Table(
         "sometable",
         metadata,
         Column("id", Integer, primary_key=True),
         Column("foo", Integer, default=func.foobar()),
     )
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == "foo")
     ins = table.insert().from_select(["id"], sel, include_defaults=False)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id) SELECT mytable.myid "
         "FROM mytable WHERE mytable.name = :name_1",
         checkparams={"name_1": "foo"},
     )
예제 #41
0
 def test_insert_from_select_with_python_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=12))
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id"], sel)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id, foo) SELECT "
         "mytable.myid, :foo AS anon_1 "
         "FROM mytable WHERE mytable.name = :name_1",
         # value filled in at execution time
         checkparams={"name_1": "foo", "foo": None}
     )
예제 #42
0
 def test_insert_from_select_with_sql_defaults(self):
     metadata = MetaData()
     table = Table(
         "sometable",
         metadata,
         Column("id", Integer, primary_key=True),
         Column("foo", Integer, default=func.foobar()),
     )
     table1 = self.tables.mytable
     sel = select([table1.c.myid]).where(table1.c.name == "foo")
     ins = table.insert().from_select(["id"], sel)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id, foo) SELECT "
         "mytable.myid, foobar() AS foobar_1 "
         "FROM mytable WHERE mytable.name = :name_1",
         checkparams={"name_1": "foo"},
     )
예제 #43
0
 def test_insert_from_select_override_defaults(self):
     metadata = MetaData()
     table = Table('sometable', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('foo', Integer, default=12))
     table1 = self.tables.mytable
     sel = select(
         [table1.c.myid, table1.c.myid.label('q')]).where(
         table1.c.name == 'foo')
     ins = table.insert().\
         from_select(["id", "foo"], sel)
     self.assert_compile(
         ins,
         "INSERT INTO sometable (id, foo) SELECT "
         "mytable.myid, mytable.myid AS q "
         "FROM mytable WHERE mytable.name = :name_1",
         checkparams={"name_1": "foo"}
     )
예제 #44
0
    def test_sql_functions(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer),
        )

        values = [
            {"id": 1, "data": "foo", "foo": func.foob()},
            {"id": 2, "data": "bar", "foo": func.foob()},
            {"id": 3, "data": "bar", "foo": func.bar()},
            {"id": 4, "data": "bar", "foo": 15},
            {"id": 5, "data": "bar", "foo": func.foob()},
        ]
        checkparams = {
            "id_m0": 1,
            "data_m0": "foo",
            "id_m1": 2,
            "data_m1": "bar",
            "id_m2": 3,
            "data_m2": "bar",
            "id_m3": 4,
            "data_m3": "bar",
            "foo_m3": 15,
            "id_m4": 5,
            "data_m4": "bar",
        }

        self.assert_compile(
            table.insert().values(values),
            "INSERT INTO sometable (id, data, foo) VALUES "
            "(%(id_m0)s, %(data_m0)s, foob()), "
            "(%(id_m1)s, %(data_m1)s, foob()), "
            "(%(id_m2)s, %(data_m2)s, bar()), "
            "(%(id_m3)s, %(data_m3)s, %(foo_m3)s), "
            "(%(id_m4)s, %(data_m4)s, foob())",
            checkparams=checkparams,
            dialect=postgresql.dialect(),
        )
예제 #45
0
    def test_sql_functions(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer))

        values = [
            {"id": 1, "data": "foo", "foo": func.foob()},
            {"id": 2, "data": "bar", "foo": func.foob()},
            {"id": 3, "data": "bar", "foo": func.bar()},
            {"id": 4, "data": "bar", "foo": 15},
            {"id": 5, "data": "bar", "foo": func.foob()},
        ]
        checkparams = {
            'id_m0': 1,
            'data_m0': 'foo',

            'id_m1': 2,
            'data_m1': 'bar',

            'id_m2': 3,
            'data_m2': 'bar',

            'id_m3': 4,
            'data_m3': 'bar',
            'foo_m3': 15,

            'id_m4': 5,
            'data_m4': 'bar'
        }

        self.assert_compile(
            table.insert().values(values),
            "INSERT INTO sometable (id, data, foo) VALUES "
            "(%(id_m0)s, %(data_m0)s, foob()), "
            "(%(id_m1)s, %(data_m1)s, foob()), "
            "(%(id_m2)s, %(data_m2)s, bar()), "
            "(%(id_m3)s, %(data_m3)s, %(foo_m3)s), "
            "(%(id_m4)s, %(data_m4)s, foob())",
            checkparams=checkparams,
            dialect=postgresql.dialect())
예제 #46
0
    def test_server_default_absent_value(self):
        metadata = MetaData()
        table = Table('sometable', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('data', String),
                      Column('foo', Integer, server_default=func.foobar()))

        values = [
            {'id': 1, 'data': 'data1', 'foo': 'plainfoo'},
            {'id': 2, 'data': 'data2'},
            {'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
        ]

        assert_raises_message(
            exc.CompileError,
            "INSERT value for column sometable.foo is explicitly rendered "
            "as a boundparameter in the VALUES clause; a Python-side value or "
            "SQL expression is required",
            table.insert().values(values).compile
        )
예제 #47
0
    def test_insert_from_select_fn_defaults(self):
        metadata = MetaData()

        def foo(ctx):
            return 12

        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=foo),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id, foo) SELECT "
            "mytable.myid, :foo AS anon_1 "
            "FROM mytable WHERE mytable.name = :name_1",
            # value filled in at execution time
            checkparams={"name_1": "foo", "foo": None},
        )
예제 #48
0
    def test_server_default_absent_value(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
            Column("foo", Integer, server_default=func.foobar()),
        )

        values = [
            {"id": 1, "data": "data1", "foo": "plainfoo"},
            {"id": 2, "data": "data2"},
            {"id": 3, "data": "data3", "foo": "otherfoo"},
        ]

        assert_raises_message(
            exc.CompileError,
            "INSERT value for column sometable.foo is explicitly rendered "
            "as a boundparameter in the VALUES clause; a Python-side value or "
            "SQL expression is required",
            table.insert().values(values).compile,
        )
예제 #49
0
    def test_sql_expression_pk_autoinc_lastinserted(self):
        # test that postfetch isn't invoked for a SQL expression
        # in a primary key column.  the DB either needs to support a lastrowid
        # that can return it, or RETURNING.  [ticket:3133]
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", String),
        )

        stmt = table.insert().return_defaults().values(id=func.foobar())
        compiled = stmt.compile(dialect=sqlite.dialect(), column_keys=["data"])
        eq_(compiled.postfetch, [])
        eq_(compiled.returning, [])

        self.assert_compile(
            stmt,
            "INSERT INTO sometable (id, data) VALUES " "(foobar(), ?)",
            checkparams={"data": "foo"},
            params={"data": "foo"},
            dialect=sqlite.dialect(),
        )