Python sqlalchemy.dialects.postgresql.array() Examples

def test_array_literal_getitem_multidim(self):
        obj = postgresql.array(
            [postgresql.array([1, 2]), postgresql.array([3, 4])]
        )

        self.assert_compile(
            obj,
            "ARRAY[ARRAY[%(param_1)s, %(param_2)s], "
            "ARRAY[%(param_3)s, %(param_4)s]]",
        )
        self.assert_compile(
            obj[1],
            "(ARRAY[ARRAY[%(param_1)s, %(param_2)s], "
            "ARRAY[%(param_3)s, %(param_4)s]])[%(param_5)s]",
        )
        self.assert_compile(
            obj[1][0],
            "(ARRAY[ARRAY[%(param_1)s, %(param_2)s], "
            "ARRAY[%(param_3)s, %(param_4)s]])[%(param_5)s][%(param_6)s]",
        ) 

def ts_locs_array(
    config: ColumnElement, text: ColumnElement, tsquery: ColumnElement,
) -> ColumnElement:
    options = f"HighlightAll = TRUE, StartSel = {TS_START}, StopSel = {TS_STOP}"
    delimited = func.ts_headline(config, text, tsquery, options)
    parts = func.unnest(func.string_to_array(delimited, TS_START)).alias()
    part = column(parts.name)
    part_len = func.length(part) - len(TS_STOP)
    match_pos = func.sum(part_len).over(rows=(None, -1)) + len(TS_STOP)
    match_len = func.strpos(part, TS_STOP) - 1
    return func.array(
        select([postgresql.array([match_pos, match_len])])
        .select_from(parts)
        .offset(1)
        .as_scalar(),
    )

# When you add a new operator to this, also update zerver/lib/narrow.py 

def preprocess_column_and_value(self):
        """ Preprocess the column and the value

            Certain operations will only work if the types are cast correctly.
            This is where it happens.
        """
        col, val = self.column, self.value

        # Case 1. Both column and value are arrays
        if self.is_column_array() and self.is_value_array():
            # Cast the value to ARRAY[] with the same type that the column has
            # Only in this case Postgres will be able to handles them both
            val = cast(pg.array(val), pg.ARRAY(col.type.item_type))

        # Case 2. JSON column
        if self.is_column_json():
            # This is the type to which JSON column is coerced: same as `value`
            # Doc: "Suggest a type for a `coerced` Python value in an expression."
            coerce_type = col.type.coerce_compared_value('=', val)  # HACKY: use sqlalchemy type coercion
            # Now, replace the `col` used in operations with this new coerced expression
            col = cast(col, coerce_type)

        # Done
        self.column_expression = col
        self.value_expression = val 

def test_array_literal_multidimensional_roundtrip(self, connection):
        eq_(
            connection.scalar(
                select(
                    [
                        postgresql.array(
                            [
                                postgresql.array([1, 2]),
                                postgresql.array([3, 4]),
                            ]
                        )
                    ]
                )
            ),
            [[1, 2], [3, 4]],
        )

        eq_(
            connection.scalar(
                select(
                    [
                        postgresql.array(
                            [
                                postgresql.array([1, 2]),
                                postgresql.array([3, 4]),
                            ]
                        )[2][1]
                    ]
                )
            ),
            3,
        ) 

def contained_by(self, other):
            """Boolean expression.  Test if elements are a proper subset of the
            elements of the argument array expression.
            """
            return self.expr.op('<@')(other) 

def test_where_has_all(self):
        self._test_where(
            self.hashcol.has_all(postgresql.array(["1", "2"])),
            "test_table.hash ?& ARRAY[%(param_1)s, %(param_2)s]",
        ) 

def test_array_literal_roundtrip(self, connection):
        eq_(
            connection.scalar(
                select(
                    [postgresql.array([1, 2]) + postgresql.array([3, 4, 5])]
                )
            ),
            [1, 2, 3, 4, 5],
        )

        eq_(
            connection.scalar(
                select(
                    [
                        (
                            postgresql.array([1, 2])
                            + postgresql.array([3, 4, 5])
                        )[3]
                    ]
                )
            ),
            3,
        )

        eq_(
            connection.scalar(
                select(
                    [
                        (
                            postgresql.array([1, 2])
                            + postgresql.array([3, 4, 5])
                        )[2:4]
                    ]
                )
            ),
            [2, 3, 4],
        ) 

def test_array_functions_plus_getitem(self):
        """test parenthesizing of functions plus indexing, which seems
        to be required by PostgreSQL.

        """
        stmt = select(
            [
                func.array_cat(
                    array([1, 2, 3]),
                    array([4, 5, 6]),
                    type_=postgresql.ARRAY(Integer),
                )[2:5]
            ]
        )
        self.assert_compile(
            stmt,
            "SELECT (array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], "
            "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))"
            "[%(param_7)s:%(param_8)s] AS anon_1",
        )

        self.assert_compile(
            func.array_cat(
                array([1, 2, 3]),
                array([4, 5, 6]),
                type_=postgresql.ARRAY(Integer),
            )[3],
            "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], "
            "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(array_cat_1)s]",
        ) 

def test_array_overlap(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col.overlap(array([4, 5, 6]))]),
            "SELECT x && ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
            "AS anon_1",
            checkparams={"param_1": 4, "param_3": 6, "param_2": 5},
        ) 

def test_array_contained_by(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col.contained_by(array([4, 5, 6]))]),
            "SELECT x <@ ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
            "AS anon_1",
            checkparams={"param_1": 4, "param_3": 6, "param_2": 5},
        ) 

def test_array_contains(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col.contains(array([4, 5, 6]))]),
            "SELECT x @> ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
            "AS anon_1",
            checkparams={"param_1": 4, "param_3": 6, "param_2": 5},
        ) 

def test_array_index_slice_exprs(self, connection):
        """test a variety of expressions that sometimes need parenthesizing"""

        stmt = select([array([1, 2, 3, 4])[2:3]])
        eq_(connection.execute(stmt).scalar(), [2, 3])

        stmt = select([array([1, 2, 3, 4])[2]])
        eq_(connection.execute(stmt).scalar(), 2)

        stmt = select([(array([1, 2]) + array([3, 4]))[2:3]])
        eq_(connection.execute(stmt).scalar(), [2, 3])

        stmt = select([array([1, 2]) + array([3, 4])[2:3]])
        eq_(connection.execute(stmt).scalar(), [1, 2, 4])

        stmt = select([array([1, 2])[2:3] + array([3, 4])])
        eq_(connection.execute(stmt).scalar(), [2, 3, 4])

        stmt = select(
            [
                func.array_cat(
                    array([1, 2, 3]),
                    array([4, 5, 6]),
                    type_=self.ARRAY(Integer),
                )[2:5]
            ]
        )
        eq_(connection.execute(stmt).scalar(), [2, 3, 4, 5]) 

def any(self, other, operator=operators.eq):
            """Return ``other operator ANY (array)`` clause.

            Argument places are switched, because ANY requires array
            expression to be on the right hand-side.

            E.g.::

                from sqlalchemy.sql import operators

                conn.execute(
                    select([table.c.data]).where(
                            table.c.data.any(7, operator=operators.lt)
                        )
                )

            :param other: expression to be compared
            :param operator: an operator object from the
             :mod:`sqlalchemy.sql.operators`
             package, defaults to :func:`.operators.eq`.

            .. seealso::

                :class:`.postgresql.Any`

                :meth:`.postgresql.ARRAY.Comparator.all`

            """
            return Any(other, self.expr, operator=operator) 

def _bind_param(self, operator, obj):
        return array([
            expression.BindParameter(None, o, _compared_to_operator=operator,
                                     _compared_to_type=self.type, unique=True)
            for o in obj
        ]) 

def __init__(self, clauses, **kw):
        super(array, self).__init__(*clauses, **kw)
        self.type = ARRAY(self.type) 

def matrix(self):
            """Text array expression.  Returns array of [key, value] pairs."""
            return _HStoreMatrixFunction(self.expr) 

def vals(self):
            """Text array expression.  Returns array of values."""
            return _HStoreValsFunction(self.expr) 

def keys(self):
            """Text array expression.  Returns array of keys."""
            return _HStoreKeysFunction(self.expr) 

def slice(self, array):
            """HStore expression.  Returns a subset of an hstore defined by
            array of keys.
            """
            return _HStoreSliceFunction(self.expr, array) 

def has_any(self, other):
            """Boolean expression.  Test for presence of any key in the PG
            array.
            """
            return self.expr.op('?|')(other) 

def has_all(self, other):
            """Boolean expression.  Test for presence of all keys in the PG
            array.
            """
            return self.expr.op('?&')(other) 

def array(self):
            """Text array expression.  Returns array of alternating keys and
            values.
            """
            return _HStoreArrayFunction(self.expr) 

def vals(self):
            """Text array expression.  Returns array of values."""
            return _HStoreValsFunction(self.expr) 

def keys(self):
            """Text array expression.  Returns array of keys."""
            return _HStoreKeysFunction(self.expr) 

def slice(self, array):
            """HStore expression.  Returns a subset of an hstore defined by
            array of keys.
            """
            return _HStoreSliceFunction(self.expr, array) 

def contains(self, other, **kwargs):
            """Boolean expression.  Test if keys (or array) are a superset
            of/contained the keys of the argument jsonb expression.
            """
            return self.operate(CONTAINS, other, result_type=sqltypes.Boolean) 

def array(self):
            """Text array expression.  Returns array of alternating keys and
            values.
            """
            return _HStoreArrayFunction(self.expr) 

def vals(self):
            """Text array expression.  Returns array of values."""
            return _HStoreValsFunction(self.expr) 

def keys(self):
            """Text array expression.  Returns array of keys."""
            return _HStoreKeysFunction(self.expr) 

def slice(self, array):
            """HStore expression.  Returns a subset of an hstore defined by
            array of keys.
            """
            return _HStoreSliceFunction(self.expr, array)