Python sqlalchemy.literal() Examples

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_update_sql_expr(self):
        stmt = insert(self.table).values(
            [{"id": 1, "bar": "ab"}, {"id": 2, "bar": "b"}]
        )
        stmt = stmt.on_duplicate_key_update(
            bar=func.coalesce(stmt.inserted.bar),
            baz=stmt.inserted.baz + "some literal",
        )
        expected_sql = (
            "INSERT INTO foos (id, bar) VALUES (%s, %s), (%s, %s) ON "
            "DUPLICATE KEY UPDATE bar = coalesce(VALUES(bar)), "
            "baz = (concat(VALUES(baz), %s))"
        )
        self.assert_compile(
            stmt,
            expected_sql,
            checkparams={
                "id_m0": 1,
                "bar_m0": "ab",
                "id_m1": 2,
                "bar_m1": "b",
                "baz_1": "some literal",
            },
        ) 

def test_or_and_as_columns(self, connection):
        true, false = literal(True), literal(False)

        eq_(connection.execute(select([and_(true, false)])).scalar(), False)
        eq_(connection.execute(select([and_(true, true)])).scalar(), True)
        eq_(connection.execute(select([or_(true, false)])).scalar(), True)
        eq_(connection.execute(select([or_(false, false)])).scalar(), False)
        eq_(
            connection.execute(select([not_(or_(false, false))])).scalar(),
            True,
        )

        row = connection.execute(
            select(
                [or_(false, false).label("x"), and_(true, false).label("y")]
            )
        ).first()
        assert row.x == False  # noqa
        assert row.y == False  # noqa

        row = connection.execute(
            select([or_(true, false).label("x"), and_(true, false).label("y")])
        ).first()
        assert row.x == True  # noqa
        assert row.y == False  # noqa 

def test_limit_six(self):
        t = table("sometable", column("col1"), column("col2"))

        s = (
            select([t])
            .limit(10)
            .offset(literal(10) + literal(20))
            .order_by(t.c.col2)
        )
        self.assert_compile(
            s,
            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT anon_2.col1 AS "
            "col1, anon_2.col2 AS col2, ROWNUM AS ora_rn FROM "
            "(SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
            "FROM sometable ORDER BY sometable.col2) anon_2 WHERE "
            "ROWNUM <= :param_1 + :param_2 + :param_3) anon_1 "
            "WHERE ora_rn > :param_2 + :param_3",
            checkparams={"param_1": 10, "param_2": 10, "param_3": 20},
        ) 

def test_column_accessor_labels_w_dots(self, connection):
        users = self.tables.users

        connection.execute(users.insert(), dict(user_id=1, user_name="john"))
        # test using literal tablename.colname
        r = connection.execute(
            text(
                'select users.user_id AS "users.user_id", '
                'users.user_name AS "users.user_name" '
                "from users",
                bind=testing.db,
            ).execution_options(sqlite_raw_colnames=True)
        ).first()
        eq_(r._mapping["users.user_id"], 1)
        eq_(r._mapping["users.user_name"], "john")
        not_in_("user_name", r._mapping)
        eq_(list(r._fields), ["users.user_id", "users.user_name"]) 

def before_compile(query):
    """ensure all queries for VersionedStartEnd include criteria """

    for ent in query.column_descriptions:
        entity = ent["entity"]
        if entity is None:
            continue
        insp = inspect(ent["entity"])
        mapper = getattr(insp, "mapper", None)
        if mapper and issubclass(mapper.class_, VersionedStartEnd):
            query = query.enable_assertions(False).filter(
                # using a literal "now" because SQLite's "between"
                # seems to be inclusive. In practice, this would be
                # ``func.now()`` and we'd be using PostgreSQL
                literal(
                    current_time() + datetime.timedelta(seconds=1)
                ).between(ent["entity"].start, ent["entity"].end)
            )

    return query 

def _test_result_processor(self, cls, use_cache):
        class MyType(TypeDecorator):
            impl = String()

            def process_result_value(self, value, dialect):
                return "HI " + value

        with self._proxy_fixture(cls):
            with self.engine.connect() as conn:
                if use_cache:
                    cache = {}
                    conn = conn.execution_options(compiled_cache=cache)

                stmt = select([literal("THERE", type_=MyType())])
                for i in range(2):
                    r = conn.execute(stmt)
                    eq_(r.scalar(), "HI THERE") 

def test_select_from_aliased_w_subclass(self):
        Engineer = self.classes.Engineer

        sess = create_session()

        a1 = aliased(Engineer)
        self.assert_compile(
            sess.query(a1.employee_id).select_from(a1),
            "SELECT employees_1.employee_id AS employees_1_employee_id "
            "FROM employees AS employees_1 WHERE employees_1.type "
            "IN ([POSTCOMPILE_type_1])",
        )

        self.assert_compile(
            sess.query(literal("1")).select_from(a1),
            "SELECT :param_1 AS anon_1 FROM employees AS employees_1 "
            "WHERE employees_1.type IN ([POSTCOMPILE_type_1])",
        ) 

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_select_composition_five(self):
        # test that use_labels doesn't interfere
        # with literal columns that have textual labels
        self.assert_compile(
            select(
                [
                    text("column1 AS foobar"),
                    text("column2 AS hoho"),
                    table1.c.myid,
                ],
                from_obj=table1,
                use_labels=True,
            ),
            "SELECT column1 AS foobar, column2 AS hoho, "
            "mytable.myid AS mytable_myid FROM mytable",
        ) 

def test_union_literal_expressions_compile(self):
        """test that column expressions translate during
            the _from_statement() portion of union(), others"""

        User = self.classes.User

        s = Session()
        q1 = s.query(User, literal("x"))
        q2 = s.query(User, literal_column("'y'"))
        q3 = q1.union(q2)

        self.assert_compile(
            q3,
            "SELECT anon_1.users_id AS anon_1_users_id, "
            "anon_1.users_name AS anon_1_users_name, "
            "anon_1.anon_2 AS anon_1_anon_2 FROM "
            "(SELECT users.id AS users_id, users.name AS users_name, "
            ":param_1 AS anon_2 FROM users "
            "UNION SELECT users.id AS users_id, users.name AS users_name, "
            "'y' FROM users) 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_select_composition_four(self):
        # test that use_labels doesn't interfere with literal columns
        self.assert_compile(
            select(
                [
                    text("column1"),
                    column("column2"),
                    column("column3").label("bar"),
                    table1.c.myid,
                ],
                from_obj=table1,
                use_labels=True,
            ),
            "SELECT column1, column2, column3 AS bar, "
            "mytable.myid AS mytable_myid "
            "FROM mytable",
        ) 

def _literal(t, expr):
    if isinstance(expr, ir.IntervalScalar):
        if expr.type().unit in {'ms', 'ns'}:
            raise com.UnsupportedOperationError(
                'MySQL does not allow operation '
                'with INTERVAL offset {}'.format(expr.type().unit)
            )
        text_unit = expr.type().resolution.upper()
        value = expr.op().value
        return sa.text('INTERVAL :value {}'.format(text_unit)).bindparams(
            value=value
        )
    elif isinstance(expr, ir.SetScalar):
        return list(map(sa.literal, expr.op().value))
    else:
        value = expr.op().value
        if isinstance(value, pd.Timestamp):
            value = value.to_pydatetime()
        return sa.literal(value) 

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_type_coerce_existing_typed(self, connection):
        MyType = self.MyType
        coerce_fn = type_coerce
        t = self.tables.t

        # type_coerce does upgrade the given expression to the
        # given type.

        connection.execute(
            t.insert().values(data=coerce_fn(literal("d1"), MyType))
        )

        eq_(
            connection.execute(
                select([coerce_fn(t.c.data, MyType)])
            ).fetchall(),
            [("BIND_INd1BIND_OUT",)],
        ) 

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 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_update_value_missing_broken_multi_rowcount(self):
        @util.memoized_property
        def rowcount(self):
            if len(self.context.compiled_parameters) > 1:
                return -1
            else:
                return self.context.rowcount

        with patch.object(
            config.db.dialect, "supports_sane_multi_rowcount", False
        ):
            with patch(
                "sqlalchemy.engine.cursor.CursorResult.rowcount", rowcount
            ):
                Parent, Child = self._fixture()
                sess = Session()
                p1 = Parent(id=1, data=1)
                sess.add(p1)
                sess.flush()

                sess.execute(self.tables.parent.delete())

                p1.data = literal(1)
                assert_raises_message(
                    orm_exc.StaleDataError,
                    r"UPDATE statement on table 'parent' expected to "
                    r"update 1 row\(s\); 0 were matched.",
                    sess.flush,
                ) 

def test_union_literal_expressions_results(self):
        User = self.classes.User

        s = Session()

        x_literal = literal("x")
        q1 = s.query(User, x_literal)
        q2 = s.query(User, literal_column("'y'"))
        q3 = q1.union(q2)

        q4 = s.query(User, literal_column("'x'").label("foo"))
        q5 = s.query(User, literal("y"))
        q6 = q4.union(q5)

        eq_([x["name"] for x in q6.column_descriptions], ["User", "foo"])

        for q in (
            q3.order_by(User.id, x_literal),
            q6.order_by(User.id, "foo"),
        ):
            eq_(
                q.all(),
                [
                    (User(id=7, name="jack"), "x"),
                    (User(id=7, name="jack"), "y"),
                    (User(id=8, name="ed"), "x"),
                    (User(id=8, name="ed"), "y"),
                    (User(id=9, name="fred"), "x"),
                    (User(id=9, name="fred"), "y"),
                    (User(id=10, name="chuck"), "x"),
                    (User(id=10, name="chuck"), "y"),
                ],
            ) 

def test_docstring(self):
        A = self._fixture()
        eq_(A.value.__doc__, "This is a class-level docstring")

        # no docstring here since we get a literal
        a1 = A(_value=10)
        eq_(a1.value, 5) 

def _regex_operation(self, left, right):
        """
        Get database-specific regex operation.
        Don't forget to check if there is a support for regex operator before using it.
        """
        if self.dialect == 'mysql':
            return literal(left).op('REGEXP BINARY', is_comparison=True)(right)
        elif self.dialect == 'postgresql':
            return literal(left).op('~', is_comparison=True)(right)
        elif self.dialect == 'oracle':
            return func.REGEXP_LIKE(left, right)
        return None 

def test_actual_literal_adapters(self, data, expected):
        is_(literal(data).type.__class__, expected) 

def test_literal_default_label(self):
        self._run_test(
            default=literal("INT_1", type_=self.MyInteger).label("foo")
        ) 

def test_literal_binds_plain(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column("x", Integer, server_default=literal("a") + literal("b")),
        )
        self.assert_compile(
            CreateTable(t), "CREATE TABLE t (x INTEGER DEFAULT 'a' || 'b')"
        ) 

def test_literal_binds_w_quotes(self):
        m = MetaData()
        t = Table(
            "t", m, Column("x", Integer, server_default=literal("5 ' 8"))
        )
        self.assert_compile(
            CreateTable(t), """CREATE TABLE t (x INTEGER DEFAULT '5 '' 8')"""
        ) 

def _compare_param_dict(self, a, b):
        if list(a) != list(b):
            return False

        from sqlalchemy.types import NullType

        for a_k, a_i in a.items():
            b_i = b[a_k]

            # compare BindParameter on the left to
            # literal value on the right
            assert a_i.compare(literal(b_i, type_=NullType())) 

def test_render_datetime(self, value):
        lit = literal(value)

        assert_raises_message(
            NotImplementedError,
            "Don't know how to literal-quote value.*",
            lit.compile,
            dialect=testing.db.dialect,
            compile_kwargs={"literal_binds": True},
        ) 

def test_typedecorator_literal_render_fallback_bound(self):
        # fall back to process_bind_param for literal
        # value rendering.
        class MyType(types.TypeDecorator):
            impl = String

            def process_bind_param(self, value, dialect):
                return "HI->%s<-THERE" % value

        self.assert_compile(
            select([literal("test", MyType)]),
            "SELECT 'HI->test<-THERE' AS anon_1",
            dialect="default",
            literal_binds=True,
        )