Python sqlalchemy.literal_column() Examples

def test_correlated_select(self):
        s = select(
            [literal_column("*")], t1.c.col1 == t2.c.col1, from_obj=[t1, t2]
        ).correlate(t2)

        class Vis(CloningVisitor):
            def visit_select(self, select):
                select.where.non_generative(select, t1.c.col2 == 7)

        self.assert_compile(
            select([t2]).where(
                t2.c.col1 == Vis().traverse(s).scalar_subquery()
            ),
            "SELECT table2.col1, table2.col2, table2.col3 "
            "FROM table2 WHERE table2.col1 = "
            "(SELECT * FROM table1 WHERE table1.col1 = table2.col1 "
            "AND table1.col2 = :col2_1)",
        ) 

def test_limit_offset_no_int_coercion_two(self):
        exp1 = literal_column("Q")
        exp2 = literal_column("Y")
        sel = select([1]).limit(exp1).offset(exp2)

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for limit",
            getattr,
            sel,
            "_limit",
        )

        assert_raises_message(
            exc.CompileError,
            "This SELECT structure does not use a simple integer "
            "value for offset",
            getattr,
            sel,
            "_offset",
        ) 

def _delete_version(self, version):
        self.heads.remove(version)

        ret = self.context.impl._exec(
            self.context._version.delete().where(
                self.context._version.c.version_num
                == literal_column("'%s'" % version)
            )
        )
        if (
            not self.context.as_sql
            and self.context.dialect.supports_sane_rowcount
            and ret.rowcount != 1
        ):
            raise util.CommandError(
                "Online migration expected to match one "
                "row when deleting '%s' in '%s'; "
                "%d found"
                % (version, self.context.version_table, ret.rowcount)
            ) 

def _update_version(self, from_, to_):
        assert to_ not in self.heads
        self.heads.remove(from_)
        self.heads.add(to_)

        ret = self.context.impl._exec(
            self.context._version.update()
            .values(version_num=literal_column("'%s'" % to_))
            .where(
                self.context._version.c.version_num
                == literal_column("'%s'" % from_)
            )
        )
        if (
            not self.context.as_sql
            and self.context.dialect.supports_sane_rowcount
            and ret.rowcount != 1
        ):
            raise util.CommandError(
                "Online migration expected to match one "
                "row when updating '%s' to '%s' in '%s'; "
                "%d found"
                % (from_, to_, self.context.version_table, ret.rowcount)
            ) 

def test_cache_key_limit_offset_values(self):
        s1 = select([column("q")]).limit(10)
        s2 = select([column("q")]).limit(25)
        s3 = select([column("q")]).limit(25).offset(5)
        s4 = select([column("q")]).limit(25).offset(18)
        s5 = select([column("q")]).limit(7).offset(12)
        s6 = select([column("q")]).limit(literal_column("q")).offset(12)

        for should_eq_left, should_eq_right in [(s1, s2), (s3, s4), (s3, s5)]:
            eq_(
                should_eq_left._generate_cache_key().key,
                should_eq_right._generate_cache_key().key,
            )

        for shouldnt_eq_left, shouldnt_eq_right in [
            (s1, s3),
            (s5, s6),
            (s2, s3),
        ]:
            ne_(
                shouldnt_eq_left._generate_cache_key().key,
                shouldnt_eq_right._generate_cache_key().key,
            ) 

def define_tables(cls, metadata):
        Table('autoinc_pk', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50))
              )
        Table('manual_pk', metadata,
              Column('id', Integer, primary_key=True, autoincrement=False),
              Column('data', String(50))
              )
        Table('includes_defaults', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50)),
              Column('x', Integer, default=5),
              Column('y', Integer,
                     default=literal_column("2", type_=Integer) + literal(2))) 

def test_keys_anon_labels(self, connection):
        """test [ticket:3483]"""

        users = self.tables.users

        connection.execute(users.insert(), user_id=1, user_name="foo")
        result = connection.execute(
            select(
                [
                    users.c.user_id,
                    users.c.user_name.label(None),
                    func.count(literal_column("1")),
                ]
            ).group_by(users.c.user_id, users.c.user_name)
        )

        eq_(result.keys(), ["user_id", "user_name_1", "count_1"])
        row = result.first()
        eq_(row._fields, ("user_id", "user_name_1", "count_1"))
        eq_(list(row._mapping.keys()), ["user_id", "user_name_1", "count_1"]) 

def define_tables(cls, metadata):
        Table('autoinc_pk', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50))
              )
        Table('manual_pk', metadata,
              Column('id', Integer, primary_key=True, autoincrement=False),
              Column('data', String(50))
              )
        Table('includes_defaults', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50)),
              Column('x', Integer, default=5),
              Column('y', Integer,
                     default=literal_column("2", type_=Integer) + literal(2))) 

def test_text_in_select_nonfrom(self):

        generate_series = text(
            "generate_series(:x, :y, :z) as s(a)"
        ).bindparams(x=None, y=None, z=None)

        s = select(
            [(func.current_date() + literal_column("s.a")).label("dates")]
        ).select_from(generate_series)

        self.assert_compile(
            s,
            "SELECT CURRENT_DATE + s.a AS dates FROM "
            "generate_series(:x, :y, :z) as s(a)",
            checkparams={"y": None, "x": None, "z": None},
        )

        self.assert_compile(
            s.params(x=5, y=6, z=7),
            "SELECT CURRENT_DATE + s.a AS dates FROM "
            "generate_series(:x, :y, :z) as s(a)",
            checkparams={"y": 6, "x": 5, "z": 7},
        ) 

def define_tables(cls, metadata):
        Table('autoinc_pk', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50))
              )
        Table('manual_pk', metadata,
              Column('id', Integer, primary_key=True, autoincrement=False),
              Column('data', String(50))
              )
        Table('includes_defaults', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50)),
              Column('x', Integer, default=5),
              Column('y', Integer,
                     default=literal_column("2", type_=Integer) + literal(2))) 

def test_select_composition_six(self):
        # test that "auto-labeling of subquery columns"
        # doesn't interfere with literal columns,
        # exported columns don't get quoted.
        # [ticket:4730] refines this but for the moment the behavior with
        # no columns is being maintained.
        self.assert_compile(
            select(
                [
                    literal_column("column1 AS foobar"),
                    literal_column("column2 AS hoho"),
                    table1.c.myid,
                ],
                from_obj=[table1],
            )
            .subquery()
            .select(),
            "SELECT anon_1.column1 AS foobar, anon_1.column2 AS hoho, "
            "anon_1.myid FROM "
            "(SELECT column1 AS foobar, column2 AS hoho, "
            "mytable.myid AS myid FROM mytable) AS anon_1",
        ) 

def define_tables(cls, metadata):
        Table('autoinc_pk', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50))
              )
        Table('manual_pk', metadata,
              Column('id', Integer, primary_key=True, autoincrement=False),
              Column('data', String(50))
              )
        Table('includes_defaults', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('data', String(50)),
              Column('x', Integer, default=5),
              Column('y', Integer,
                     default=literal_column("2", type_=Integer) + literal(2))) 

def test_implicitly_boolean(self):
        # test for expressions that the database always considers as boolean
        # even if there is no boolean datatype.
        assert not self.table1.c.myid._is_implicitly_boolean
        assert (self.table1.c.myid == 5)._is_implicitly_boolean
        assert (self.table1.c.myid == 5).self_group()._is_implicitly_boolean
        assert (self.table1.c.myid == 5).label("x")._is_implicitly_boolean
        assert not_(self.table1.c.myid == 5)._is_implicitly_boolean
        assert or_(
            self.table1.c.myid == 5, self.table1.c.myid == 7
        )._is_implicitly_boolean
        assert not column("x", Boolean)._is_implicitly_boolean
        assert not (self.table1.c.myid + 5)._is_implicitly_boolean
        assert not not_(column("x", Boolean))._is_implicitly_boolean
        assert (
            not select([self.table1.c.myid])
            .scalar_subquery()
            ._is_implicitly_boolean
        )
        assert not text("x = y")._is_implicitly_boolean
        assert not literal_column("x = y")._is_implicitly_boolean 

def test_simple_limit_expression_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = (
            select([t])
            .where(t.c.x == 5)
            .order_by(t.c.y)
            .limit(10)
            .offset(literal_column("20"))
        )

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.y "
            "FROM (SELECT t.x AS x, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > 20 AND mssql_rn <= :param_1 + 20",
            checkparams={"param_1": 10, "x_1": 5},
        ) 

def test_table_to_alias_6(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            select([t1alias, t2]).where(
                t1alias.c.col1
                == vis.traverse(
                    select(
                        [literal_column("*")],
                        t1.c.col1 == t2.c.col2,
                        from_obj=[t1, t2],
                    )
                    .correlate(t2)
                    .scalar_subquery()
                )
            ),
            "SELECT t1alias.col1, t1alias.col2, t1alias.col3, "
            "table2.col1, table2.col2, table2.col3 "
            "FROM table1 AS t1alias, table2 "
            "WHERE t1alias.col1 = "
            "(SELECT * FROM table1 AS t1alias "
            "WHERE t1alias.col1 = table2.col2)",
        ) 

def test_table_to_alias_5(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            select([t1alias, t2]).where(
                t1alias.c.col1
                == vis.traverse(
                    select(
                        [literal_column("*")],
                        t1.c.col1 == t2.c.col2,
                        from_obj=[t1, t2],
                    )
                    .correlate(t1)
                    .scalar_subquery()
                )
            ),
            "SELECT t1alias.col1, t1alias.col2, t1alias.col3, "
            "table2.col1, table2.col2, table2.col3 "
            "FROM table1 AS t1alias, table2 WHERE t1alias.col1 = "
            "(SELECT * FROM table2 WHERE t1alias.col1 = table2.col2)",
        ) 

def test_select_setup_joins_adapt_element_two(self):
        s = future_select(literal_column("1")).join_from(
            t1, t2, t1.c.col1 == t2.c.col2
        )

        t1a = t1.alias()

        s2 = sql_util.ClauseAdapter(t1a).traverse(s)

        self.assert_compile(
            s, "SELECT 1 FROM table1 JOIN table2 ON table1.col1 = table2.col2"
        )
        self.assert_compile(
            s2,
            "SELECT 1 FROM table1 AS table1_1 "
            "JOIN table2 ON table1_1.col1 = table2.col2",
        ) 

def test_table_to_alias_13(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        t2alias = t2.alias("t2alias")
        vis.chain(sql_util.ClauseAdapter(t2alias))
        self.assert_compile(
            vis.traverse(
                select([literal_column("*")], t1.c.col1 == t2.c.col2)
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "AS t2alias WHERE t1alias.col1 = "
            "t2alias.col2",
        ) 

def test_table_to_alias_11(self):
        s = select([literal_column("*")], from_obj=[t1]).alias("foo")
        self.assert_compile(
            s.select(), "SELECT foo.* FROM (SELECT * FROM table1) " "AS foo"
        ) 

def test_table_to_alias_10(self):
        s = select([literal_column("*")], from_obj=[t1]).alias("foo")
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(s.select()),
            "SELECT foo.* FROM (SELECT * FROM table1 " "AS t1alias) AS foo",
        ) 

def test_order_by_literal_col_quoting_one(self):
        col = literal_column("SUM(ABC)").label("SUM(ABC)")
        tbl = table("my_table")
        query = select([col]).select_from(tbl).order_by(col)
        self.assert_compile(
            query,
            'SELECT SUM(ABC) AS "SUM(ABC)" FROM my_table ORDER BY "SUM(ABC)"',
        ) 

def test_multiple_col_binds(self):
        self.assert_compile(
            select(
                [literal_column("*")],
                or_(
                    table1.c.myid == 12,
                    table1.c.myid == "asdf",
                    table1.c.myid == "foo",
                ),
            ),
            "SELECT * FROM mytable WHERE mytable.myid = :myid_1 "
            "OR mytable.myid = :myid_2 OR mytable.myid = :myid_3",
        ) 

def test_table_to_alias_4(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(
                select(
                    [literal_column("*")],
                    t1.c.col1 == t2.c.col2,
                    from_obj=[t1, t2],
                )
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "WHERE t1alias.col1 = table2.col2",
        ) 

def test_table_to_alias_3(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(
                select([literal_column("*")], t1.c.col1 == t2.c.col2)
            ),
            "SELECT * FROM table1 AS t1alias, table2 "
            "WHERE t1alias.col1 = table2.col2",
        ) 

def test_table_to_alias_2(self):
        t1alias = t1.alias("t1alias")
        vis = sql_util.ClauseAdapter(t1alias)
        self.assert_compile(
            vis.traverse(select([literal_column("*")], from_obj=[t1])),
            "SELECT * FROM table1 AS t1alias",
        ) 

def test_row_case_sensitive_unoptimized(self):
        with engines.testing_engine().connect() as ins_conn:
            row = ins_conn.execute(
                select(
                    [
                        literal_column("1").label("case_insensitive"),
                        literal_column("2").label("CaseSensitive"),
                        text("3 AS screw_up_the_cols"),
                    ]
                )
            ).first()

            eq_(
                list(row._fields),
                ["case_insensitive", "CaseSensitive", "screw_up_the_cols"],
            )

            in_("case_insensitive", row._keymap)
            in_("CaseSensitive", row._keymap)
            not_in_("casesensitive", row._keymap)

            eq_(row._mapping["case_insensitive"], 1)
            eq_(row._mapping["CaseSensitive"], 2)
            eq_(row._mapping["screw_up_the_cols"], 3)

            assert_raises(KeyError, lambda: row._mapping["Case_insensitive"])
            assert_raises(KeyError, lambda: row._mapping["casesensitive"])
            assert_raises(KeyError, lambda: row._mapping["screw_UP_the_cols"]) 

def test_row_case_insensitive(self):
        with testing.expect_deprecated(
            "The create_engine.case_sensitive parameter is deprecated"
        ):
            with engines.testing_engine(
                options={"case_sensitive": False}
            ).connect() as ins_conn:
                row = ins_conn.execute(
                    select(
                        [
                            literal_column("1").label("case_insensitive"),
                            literal_column("2").label("CaseSensitive"),
                        ]
                    )
                ).first()

                eq_(
                    list(row._mapping.keys()),
                    ["case_insensitive", "CaseSensitive"],
                )

                in_("case_insensitive", row._keymap)
                in_("CaseSensitive", row._keymap)
                in_("casesensitive", row._keymap)

                eq_(row._mapping["case_insensitive"], 1)
                eq_(row._mapping["CaseSensitive"], 2)
                eq_(row._mapping["Case_insensitive"], 1)
                eq_(row._mapping["casesensitive"], 2) 

def test_clone_anon_label(self):
        from sqlalchemy.sql.elements import Grouping

        c1 = Grouping(literal_column("q"))
        s1 = select([c1])

        class Vis(CloningVisitor):
            def visit_grouping(self, elem):
                pass

        vis = Vis()
        s2 = vis.traverse(s1)
        eq_(list(s2.selected_columns)[0].anon_label, c1.anon_label) 

def test_named_labels_literal_column(self):
        c1 = literal_column("x")
        eq_(str(select([c1.label("y")])), "SELECT x AS y") 

def test_startswith_literal(self):
        self.assert_compile(
            column("x").startswith(literal_column("y")),
            "x LIKE y || '%'",
            checkparams={},
        )