Python sqlalchemy.sql.literal_column() Examples

def test_ambiguous_column_case_sensitive(self):
        with testing.expect_deprecated(
            "The create_engine.case_sensitive parameter is deprecated"
        ):
            eng = engines.testing_engine(options=dict(case_sensitive=False))

        with eng.connect() as conn:
            row = conn.execute(
                select(
                    [
                        literal_column("1").label("SOMECOL"),
                        literal_column("1").label("SOMECOL"),
                    ]
                )
            ).first()

            assert_raises_message(
                exc.InvalidRequestError,
                "Ambiguous column name",
                lambda: row._mapping["somecol"],
            ) 

def get_timestamp_expression(
        self, time_grain: Optional[str]
    ) -> Union[TimestampExpression, Label]:
        """
        Return a SQLAlchemy Core element representation of self to be used in a query.

        :param time_grain: Optional time grain, e.g. P1Y
        :return: A TimeExpression object wrapped in a Label if supported by db
        """
        label = utils.DTTM_ALIAS

        db_ = self.table.database
        pdf = self.python_date_format
        is_epoch = pdf in ("epoch_s", "epoch_ms")
        if not self.expression and not time_grain and not is_epoch:
            sqla_col = column(self.column_name, type_=DateTime)
            return self.table.make_sqla_column_compatible(sqla_col, label)
        if self.expression:
            col = literal_column(self.expression)
        else:
            col = column(self.column_name)
        time_expr = db_.db_engine_spec.get_timestamp_expr(
            col, pdf, time_grain, self.type
        )
        return self.table.make_sqla_column_compatible(time_expr, label) 

def test_aggregate_order_by_multi_col(self):
        m = MetaData()
        table = Table("table1", m, Column("a", Integer), Column("b", Integer))
        expr = func.string_agg(
            table.c.a,
            aggregate_order_by(
                literal_column("','"), table.c.a, table.c.b.desc()
            ),
        )
        stmt = select([expr])

        self.assert_compile(
            stmt,
            "SELECT string_agg(table1.a, "
            "',' ORDER BY table1.a, table1.b DESC) "
            "AS string_agg_1 FROM table1",
        ) 

def test_on_conflict_as_cte(self):
        i = insert(self.table1, values=dict(name="foo"))
        i = (
            i.on_conflict_do_update(
                constraint=self.excl_constr_anon,
                set_=dict(name=i.excluded.name),
                where=((self.table1.c.name != i.excluded.name)),
            )
            .returning(literal_column("1"))
            .cte("i_upsert")
        )

        stmt = select([i])

        self.assert_compile(
            stmt,
            "WITH i_upsert AS "
            "(INSERT INTO mytable (name) VALUES (%(name)s) "
            "ON CONFLICT (name, description) "
            "WHERE description != %(description_1)s "
            "DO UPDATE SET name = excluded.name "
            "WHERE mytable.name != excluded.name RETURNING 1) "
            "SELECT i_upsert.1 "
            "FROM i_upsert",
        ) 

def test_deprecated_subquery_standalone(self):
        from sqlalchemy import subquery

        with testing.expect_deprecated(
            r"The standalone subquery\(\) function is deprecated"
        ):
            stmt = subquery(
                None,
                [literal_column("1").label("a")],
                order_by=literal_column("1"),
            )

        self.assert_compile(
            select([stmt]),
            "SELECT anon_1.a FROM (SELECT 1 AS a ORDER BY 1) AS anon_1",
        ) 

def test_literal_column_wo_label_currently_maintained(self):
        # test related to [ticket:4730] where we are maintaining that
        # literal_column() proxied outwards *without* a label is maintained
        # as is; in most cases literal_column would need proxying however
        # at least if the column is being used to generate quoting in some
        # way, it's maintined as given
        col = sql.literal_column('"NEEDS QUOTES"')

        self.assert_compile(
            select([col]).alias().select(),
            'SELECT anon_1."NEEDS QUOTES" FROM '
            '(SELECT "NEEDS QUOTES") AS anon_1',
        ) 

def test_row_case_insensitive_unoptimized(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"),
                            text("3 AS screw_up_the_cols"),
                        ]
                    )
                ).first()

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

                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["screw_up_the_cols"], 3)
                eq_(row._mapping["Case_insensitive"], 1)
                eq_(row._mapping["casesensitive"], 2)
                eq_(row._mapping["screw_UP_the_cols"], 3) 

def test_literal_column_already_with_quotes(self):
        # Lower case names
        metadata = MetaData()
        table = Table("t1", metadata, Column("col1", Integer))

        # Note that 'col1' is already quoted (literal_column)
        columns = [sql.literal_column("'col1'").label("label1")]
        x = select(columns, from_obj=[table]).alias("alias1")
        x = x.select()
        self.assert_compile(
            x,
            "SELECT "
            "alias1.label1 "
            "FROM ("
            "SELECT "
            "'col1' AS label1 "
            "FROM t1"
            ") AS alias1",
        )

        # Not lower case names
        metadata = MetaData()
        table = Table("T1", metadata, Column("Col1", Integer))

        # Note that 'Col1' is already quoted (literal_column)
        columns = [sql.literal_column("'Col1'").label("Label1")]
        x = select(columns, from_obj=[table]).alias("Alias1")
        x = x.select()
        self.assert_compile(
            x,
            "SELECT "
            '"Alias1"."Label1" '
            "FROM ("
            "SELECT "
            "'Col1' AS \"Label1\" "
            'FROM "T1"'
            ') AS "Alias1"',
        ) 

def test_literal_column_label_alias_samename(self):
        col = sql.literal_column("NEEDS QUOTES").label("NEEDS QUOTES")

        self.assert_compile(
            select([col]).alias().select(),
            'SELECT anon_1."NEEDS QUOTES" FROM (SELECT NEEDS QUOTES AS '
            '"NEEDS QUOTES") AS anon_1',
        ) 

def test_literal_column_label_alias_samename_explcit_quote(self):
        col = sql.literal_column("NEEDS QUOTES").label(
            quoted_name("NEEDS QUOTES", True)
        )

        self.assert_compile(
            select([col]).alias().select(),
            'SELECT anon_1."NEEDS QUOTES" FROM '
            '(SELECT NEEDS QUOTES AS "NEEDS QUOTES") AS anon_1',
        ) 

def test_literal_column_label_alias_diffname_explcit_quote(self):
        col = sql.literal_column("NEEDS QUOTES").label(
            quoted_name("NEEDS QUOTES_", True)
        )

        self.assert_compile(
            select([col]).alias().select(),
            'SELECT anon_1."NEEDS QUOTES_" FROM '
            '(SELECT NEEDS QUOTES AS "NEEDS QUOTES_") AS anon_1',
        ) 

def adhoc_metric_to_sqla(
        self, metric: Dict[str, Any], cols: Dict[str, Any]
    ) -> Optional[Column]:
        """
        Turn an adhoc metric into a sqlalchemy column.

        :param dict metric: Adhoc metric definition
        :param dict cols: Columns for the current table
        :returns: The metric defined as a sqlalchemy column
        :rtype: sqlalchemy.sql.column
        """
        expression_type = metric.get("expressionType")
        label = utils.get_metric_name(metric)

        if expression_type == utils.ADHOC_METRIC_EXPRESSION_TYPES["SIMPLE"]:
            column_name = metric["column"].get("column_name")
            table_column = cols.get(column_name)
            if table_column:
                sqla_column = table_column.get_sqla_col()
            else:
                sqla_column = column(column_name)
            sqla_metric = self.sqla_aggregations[metric["aggregate"]](sqla_column)
        elif expression_type == utils.ADHOC_METRIC_EXPRESSION_TYPES["SQL"]:
            sqla_metric = literal_column(metric.get("sqlExpression"))
        else:
            return None

        return self.make_sqla_column_compatible(sqla_metric, label) 

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

def test_literal_column_default_no_label(self):
        self._run_test(default=literal_column("1", type_=self.MyInteger)) 

def get_sqla_col(self, label: Optional[str] = None) -> Column:
        label = label or self.column_name
        if self.expression:
            col = literal_column(self.expression)
        else:
            db_engine_spec = self.table.database.db_engine_spec
            type_ = db_engine_spec.get_sqla_column_type(self.type)
            col = column(self.column_name, type_=type_)
        col = self.table.make_sqla_column_compatible(col, label)
        return col 

def get_sqla_col(self, label: Optional[str] = None) -> Column:
        label = label or self.metric_name
        sqla_col = literal_column(self.expression)
        return self.table.make_sqla_column_compatible(sqla_col, label) 

def _limit_using_window_function(self, query):
        """ Apply a limit using a window function

            This approach enables us to limit the number of eagerly loaded related entities
        """
        # Only do it when there is a limit
        if self.skip or self.limit:
            # First, add a row counter:
            query = query.add_columns(
                # for every group, count the rows with row_number().
                func.row_number().over(
                    # Groups are partitioned by self._window_over_columns,
                    partition_by=self._window_over_columns,
                    # We have to apply the same ordering from the outside query;
                    # otherwise, the numbering will be undetermined
                    order_by=self.mongoquery.handler_sort.compile_columns()
                ).label('group_row_n')  # give it a name that we can use later
            )

            # Now, make ourselves into a subquery
            query = query.from_self()

            # Well, it turns out that subsequent joins somehow work.
            # I have no idea how, but they do.
            # Otherwise, we would have had to ban using 'joins' after 'limit' in nested queries.

            # And apply the LIMIT condition using row numbers
            # These two statements simulate skip/limit using window functions
            if self.skip:
                query = query.filter(literal_column('group_row_n') > self.skip)
            if self.limit:
                query = query.filter(literal_column('group_row_n') <= ((self.skip or 0) + self.limit))

        # Done
        return query 

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_row_getitem_column(self, connection):
        col = literal_column("1").label("foo")

        with testing.expect_deprecated(
            "Using non-integer/slice indices on Row is deprecated "
            "and will be removed in version 2.0;"
        ):
            row = connection.execute(select([col])).first()
            eq_(row[col], 1)

        eq_(row._mapping[col], 1) 

def test_literal_column_label_embedded_select_samename(self):
        col = sql.literal_column("NEEDS QUOTES").label("NEEDS QUOTES")

        with testing.expect_deprecated(
            r"The SelectBase.select\(\) method is deprecated"
        ):
            self.assert_compile(
                select([col]).select(),
                'SELECT anon_1."NEEDS QUOTES" FROM (SELECT NEEDS QUOTES AS '
                '"NEEDS QUOTES") AS anon_1',
            ) 

def test_literal_column_label_embedded_select_diffname(self):
        col = sql.literal_column("NEEDS QUOTES").label("NEEDS QUOTES_")

        with testing.expect_deprecated(
            r"The SelectBase.select\(\) method is deprecated"
        ):
            self.assert_compile(
                select([col]).select(),
                'SELECT anon_1."NEEDS QUOTES_" FROM (SELECT NEEDS QUOTES AS '
                '"NEEDS QUOTES_") AS anon_1',
            ) 

def test_literal_column_label_embedded_select_diffname_explcit_quote(self):
        col = sql.literal_column("NEEDS QUOTES").label(
            quoted_name("NEEDS QUOTES_", True)
        )

        with testing.expect_deprecated(
            r"The SelectBase.select\(\) method is deprecated"
        ):
            self.assert_compile(
                select([col]).select(),
                'SELECT anon_1."NEEDS QUOTES_" FROM '
                '(SELECT NEEDS QUOTES AS "NEEDS QUOTES_") AS anon_1',
            ) 

def test_literal_column_label_embedded_select_samename_explcit_quote(self):
        col = sql.literal_column("NEEDS QUOTES").label(
            quoted_name("NEEDS QUOTES", True)
        )

        with testing.expect_deprecated(
            r"The SelectBase.select\(\) method is deprecated"
        ):
            self.assert_compile(
                select([col]).select(),
                'SELECT anon_1."NEEDS QUOTES" FROM '
                '(SELECT NEEDS QUOTES AS "NEEDS QUOTES") AS anon_1',
            ) 

def test_aggregate_orcer_by_no_arg(self):
        assert_raises_message(
            TypeError,
            "at least one ORDER BY element is required",
            aggregate_order_by,
            literal_column("','"),
        ) 

def test_aggregate_order_by_two(self):
        m = MetaData()
        table = Table("table1", m, Column("a", Integer), Column("b", Integer))
        expr = func.string_agg(
            table.c.a, aggregate_order_by(literal_column("','"), table.c.a)
        )
        stmt = select([expr])

        self.assert_compile(
            stmt,
            "SELECT string_agg(table1.a, ',' ORDER BY table1.a) "
            "AS string_agg_1 FROM table1",
        ) 

def visit_BYTEINT(self, type_, **kw):
       return 'BYTEINT'



#@compiles(Select, 'teradata')
#def compile_select(element, compiler, **kw):
#    """
#    """
#
#    if not getattr(element, '_window_visit', None):
#      if element._limit is not None or element._offset is not None:
#          limit, offset = element._limit, element._offset
#
#          orderby=compiler.process(element._order_by_clause)
#          if orderby:
#            element = element._generate()
#            element._window_visit=True
#            #element._limit = None
#            #element._offset = None  cant set to none...
#
#            # add a ROW NUMBER() OVER(ORDER BY) column
#            element = element.column(sql.literal_column('ROW NUMBER() OVER (ORDER BY %s)' % orderby).label('rownum')).order_by(None)
#
#            # wrap into a subquery
#            limitselect = sql.select([c for c in element.alias().c if c.key != 'rownum'])
#
#            limitselect._window_visit=True
#            limitselect._is_wrapper=True
#
#            if offset is not None:
#              limitselect.append_whereclause(sql.column('rownum') > offset)
#              if limit is not None:
#                  limitselect.append_whereclause(sql.column('rownum') <= (limit + offset))
#            else:
#              limitselect.append_whereclause(sql.column("rownum") <= limit)
#
#            element = limitselect
#
#    kw['iswrapper'] = getattr(element, '_is_wrapper', False)
#    return compiler.visit_select(element, **kw) 

def get_recent_network_coverage(probe_cc, test_groups):
    where_clause = [
        sql.text("test_day >= current_date - interval '31 day'"),
        sql.text("test_day < current_date"),
        sql.text("probe_cc = :probe_cc"),
    ]
    if test_groups is not None:
        tg_or = []
        for tg in test_groups:
            try:
                tg_names = TEST_GROUPS[tg]
                tg_or += [
                    sql.literal_column("test_name") == tg_name for tg_name in tg_names
                ]
            except KeyError:
                raise BadRequest("invalid test_group")
        where_clause.append(or_(*tg_or))

    s = (
        select([sql.text("COUNT(DISTINCT probe_asn)"), sql.text("test_day")])
        .where(and_(*where_clause))
        .group_by(sql.text("test_day"))
        .order_by(sql.text("test_day"))
        .select_from(sql.table("ooexpl_daily_msm_count"))
    )

    network_map = {k: 0 for k in TEST_GROUPS.keys()}
    q = current_app.db_session.execute(s, {"probe_cc": probe_cc})
    for count, date in q:
        network_map[date.strftime("%Y-%m-%d")] = count

    network_coverage = []
    for test_day in last_30days():
        network_coverage.append(
            {"count": network_map.get(test_day, 0), "test_day": test_day}
        )
    return network_coverage 

def test_render_check_constraint_sqlexpr(self):
        c = column("c")
        five = literal_column("5")
        ten = literal_column("10")
        eq_ignore_whitespace(
            autogenerate.render._render_check_constraint(
                CheckConstraint(and_(c > five, c < ten)), self.autogen_context
            ),
            "sa.CheckConstraint(!U'c > 5 AND c < 10')",
        ) 

def query():
    def query(table):
        col1 = literal_column("TIMESTAMP_TRUNC(timestamp, DAY)").label("timestamp_label")
        col2 = func.sum(table.c.integer)
        query = (
            select([
                col1,
                col2,
            ])
            .where(col1 < '2017-01-01 00:00:00')
            .group_by(col1)
            .order_by(col2)
        )
        return query
    return query 

def api_private_im_networks():
    probe_cc = request.args.get("probe_cc")
    if probe_cc is None or len(probe_cc) != 2:
        raise BadRequest("missing probe_cc")

    test_names = [
        sql.literal_column("test_name") == tg_name for tg_name in TEST_GROUPS["im"]
    ]
    s = (
        select(
            [
                sql.text("SUM(count) as msm_count"),
                sql.text("MAX(test_day)"),
                sql.text("probe_asn"),
                sql.text("test_name"),
            ]
        )
        .where(
            and_(
                sql.text("test_day >= current_date - interval '31 day'"),
                # We exclude the last day to wait for the pipeline
                sql.text("test_day < current_date"),
                sql.text("probe_cc = :probe_cc"),
                or_(*test_names),
            )
        )
        .group_by(sql.text("test_name, probe_asn"))
        .order_by(sql.text("test_name, msm_count DESC"))
        .select_from(sql.table("ooexpl_daily_msm_count"))
    )

    results = {}
    q = current_app.db_session.execute(s, {"probe_cc": probe_cc})
    for msm_count, last_tested, probe_asn, test_name in q:
        results[test_name] = results.get(
            test_name,
            {"anomaly_networks": [], "ok_networks": [], "last_tested": last_tested},
        )
        results[test_name]["ok_networks"].append(
            {
                "asn": probe_asn,
                "name": "",
                "total_count": msm_count,
                "last_tested": last_tested,
            }
        )
        if results[test_name]["last_tested"] < last_tested:
            results[test_name]["last_tested"] = last_tested

    return jsonify(results)