Python sqlalchemy.dialects.postgresql.ARRAY Examples

def languages_column(column_name) -> sa.Column:
    """A TEXT[] column of length > 0.

    Return an ARRAY(TEXT, as_tuple=True) column.

    :param column_name: the name of the column
    :returns: a SQLAlchemy Column for a non-null ARRAY(TEXT, as_tuple=True)
              type.
    """
    return sa.Column(
        pg.ARRAY(pg.TEXT, as_tuple=True),
        sa.CheckConstraint(
            'COALESCE(ARRAY_LENGTH({}, 1), 0) > 0'.format(column_name)
        ),
        nullable=False,
        default=['English'],
    ) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('domains', sa.Column('allowed_vfolder_hosts',
                                       postgresql.ARRAY(sa.String()), nullable=True))
    op.add_column('groups', sa.Column('allowed_vfolder_hosts',
                                      postgresql.ARRAY(sa.String()), nullable=True))
    # ### end Alembic commands ###

    print('\nSet domain and group\'s allowed_vfolder_hosts with empty array.')
    connection = op.get_bind()
    query = ("UPDATE domains SET allowed_vfolder_hosts = '{}';")
    connection.execute(query)
    query = ("UPDATE groups SET allowed_vfolder_hosts = '{}';")
    connection.execute(query)

    op.alter_column('domains', column_name='allowed_vfolder_hosts', nullable=False)
    op.alter_column('groups', column_name='allowed_vfolder_hosts', nullable=False) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('domains',
                  sa.Column('allowed_docker_registries',
                            postgresql.ARRAY(sa.String()), nullable=True))
    # ### end Alembic commands ###

    print('\nSet default allowed_docker_registries.')
    allowed_registries = os.environ.get('ALLOWED_DOCKER_REGISTRIES', None)
    if allowed_registries:
        allowed_registries = allowed_registries.replace(' ', '')
        allowed_registries = '{index.docker.io,' + allowed_registries + '}'
    else:
        allowed_registries = '{index.docker.io}'
    connection = op.get_bind()
    query = ("UPDATE domains SET allowed_docker_registries = '{}';".format(allowed_registries))
    connection.execute(query)

    op.alter_column('domains', column_name='allowed_docker_registries',
                    nullable=False) 

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_getitem_slice_type(self):
        m = MetaData()
        arrtable = Table(
            "arrtable",
            m,
            Column("intarr", postgresql.ARRAY(Integer)),
            Column("strarr", postgresql.ARRAY(String)),
        )

        # type affinity is Array...
        is_(arrtable.c.intarr[1:3].type._type_affinity, ARRAY)
        is_(arrtable.c.strarr[1:3].type._type_affinity, ARRAY)

        # but the slice returns the actual type
        assert isinstance(arrtable.c.intarr[1:3].type, postgresql.ARRAY)
        assert isinstance(arrtable.c.strarr[1:3].type, postgresql.ARRAY) 

def test_array_plus_native_enum_create(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column(
                "data_1",
                self.ARRAY(postgresql.ENUM("a", "b", "c", name="my_enum_1")),
            ),
            Column(
                "data_2",
                self.ARRAY(types.Enum("a", "b", "c", name="my_enum_2")),
            ),
        )

        t.create(testing.db)
        eq_(
            set(e["name"] for e in inspect(testing.db).get_enums()),
            set(["my_enum_1", "my_enum_2"]),
        )
        t.drop(testing.db)
        eq_(inspect(testing.db).get_enums(), []) 

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 test_arrays_pg(self, connection):
        metadata = self.metadata
        t1 = Table(
            "t",
            metadata,
            Column("x", postgresql.ARRAY(Float)),
            Column("y", postgresql.ARRAY(REAL)),
            Column("z", postgresql.ARRAY(postgresql.DOUBLE_PRECISION)),
            Column("q", postgresql.ARRAY(Numeric)),
        )
        metadata.create_all()
        connection.execute(
            t1.insert(), x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")]
        )
        row = connection.execute(t1.select()).first()
        eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")])) 

def test_arrays_base(self, connection):
        metadata = self.metadata
        t1 = Table(
            "t",
            metadata,
            Column("x", sqltypes.ARRAY(Float)),
            Column("y", sqltypes.ARRAY(REAL)),
            Column("z", sqltypes.ARRAY(postgresql.DOUBLE_PRECISION)),
            Column("q", sqltypes.ARRAY(Numeric)),
        )
        metadata.create_all()
        connection.execute(
            t1.insert(), x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")]
        )
        row = connection.execute(t1.select()).first()
        eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")])) 

def test_reflect_array_column(self):
        metadata2 = MetaData(testing.db)
        tbl = Table("arrtable", metadata2, autoload=True)
        assert isinstance(tbl.c.intarr.type, self.ARRAY)
        assert isinstance(tbl.c.strarr.type, self.ARRAY)
        assert isinstance(tbl.c.intarr.type.item_type, Integer)
        assert isinstance(tbl.c.strarr.type.item_type, String) 

def test_array_int_index(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col[3]]),
            "SELECT x[%(x_1)s] AS anon_1",
            checkparams={"x_1": 3},
        ) 

def test_array_any(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col.any(7, operator=operators.lt)]),
            "SELECT %(param_1)s < ANY (x) AS anon_1",
            checkparams={"param_1": 7},
        ) 

def test_array_all(self):
        col = column("x", postgresql.ARRAY(Integer))
        self.assert_compile(
            select([col.all(7, operator=operators.lt)]),
            "SELECT %(param_1)s < ALL (x) AS anon_1",
            checkparams={"param_1": 7},
        ) 

def test_raises_non_native_enums(self, array_cls, enum_cls):
        Table(
            "my_table",
            self.metadata,
            Column(
                "my_col",
                array_cls(
                    enum_cls(
                        "foo",
                        "bar",
                        "baz",
                        name="my_enum",
                        create_constraint=True,
                        native_enum=False,
                    )
                ),
            ),
        )

        testing.assert_raises_message(
            CompileError,
            "PostgreSQL dialect cannot produce the CHECK constraint "
            "for ARRAY of non-native ENUM; please specify "
            "create_constraint=False on this Enum datatype.",
            self.metadata.create_all,
            testing.db,
        ) 

def test_array_type_render_str(self):
        self.assert_compile(postgresql.ARRAY(Unicode(30)), "VARCHAR(30)[]") 

def test_array_str_collation(self):
        m = self.metadata

        t = Table(
            "t",
            m,
            Column("data", sqltypes.ARRAY(String(50, collation="en_US"))),
        )

        t.create() 

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 test_any_all_exprs_array(self, connection):
        stmt = select(
            [
                3
                == any_(
                    func.array_cat(
                        array([1, 2, 3]),
                        array([4, 5, 6]),
                        type_=self.ARRAY(Integer),
                    )
                )
            ]
        )
        eq_(connection.execute(stmt).scalar(), True) 

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

def test_index_reflection_with_include(self):
        """reflect indexes with include set"""

        metadata = self.metadata

        Table(
            "t",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("x", ARRAY(Integer)),
            Column("name", String(20)),
        )
        metadata.create_all()
        with testing.db.connect() as conn:
            conn.execute("CREATE INDEX idx1 ON t (x) INCLUDE (name)")

            # prior to #5205, this would return:
            # [{'column_names': ['x', 'name'],
            #  'name': 'idx1', 'unique': False}]

            with testing.expect_warnings(
                "INCLUDE columns for "
                "covering index idx1 ignored during reflection"
            ):
                ind = testing.db.dialect.get_indexes(conn, "t", None)
            eq_(
                ind,
                [{"unique": False, "column_names": ["x"], "name": "idx1"}],
            ) 

def test_array_type_render_str_collate_multidim(self):
        self.assert_compile(
            postgresql.ARRAY(Unicode(30, collation="en_US"), dimensions=2),
            'VARCHAR(30)[][] COLLATE "en_US"',
        )

        self.assert_compile(
            postgresql.ARRAY(Unicode(30, collation="en_US"), dimensions=3),
            'VARCHAR(30)[][][] COLLATE "en_US"',
        ) 

def test_array_type_render_str_multidim(self):
        self.assert_compile(
            postgresql.ARRAY(Unicode(30), dimensions=2), "VARCHAR(30)[][]"
        )

        self.assert_compile(
            postgresql.ARRAY(Unicode(30), dimensions=3), "VARCHAR(30)[][][]"
        ) 

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_literal(self):
        obj = postgresql.array([1, 2]) + postgresql.array([3, 4, 5])

        self.assert_compile(
            obj,
            "ARRAY[%(param_1)s, %(param_2)s] || "
            "ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]",
            params={
                "param_1": 1,
                "param_2": 2,
                "param_3": 3,
                "param_4": 4,
                "param_5": 5,
            },
        )
        self.assert_compile(
            obj[1],
            "(ARRAY[%(param_1)s, %(param_2)s] || ARRAY[%(param_3)s, "
            "%(param_4)s, %(param_5)s])[%(param_6)s]",
            params={
                "param_1": 1,
                "param_2": 2,
                "param_3": 3,
                "param_4": 4,
                "param_5": 5,
            },
        ) 

def __init__(self, clauses, **kw):
        super(array, self).__init__(*clauses, **kw)
        if isinstance(self.type, ARRAY):
            self.type = ARRAY(
                self.type.item_type,
                dimensions=self.type.dimensions + 1
                if self.type.dimensions is not None
                else 2,
            )
        else:
            self.type = ARRAY(self.type) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`_expression.all_`

    """

    return arrexpr.all(other, operator) 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`_expression.any_`

    """

    return arrexpr.any(other, operator) 

def visit_check_constraint(self, constraint):
        if constraint._type_bound:
            typ = list(constraint.columns)[0].type
            if (
                isinstance(typ, sqltypes.ARRAY)
                and isinstance(typ.item_type, sqltypes.Enum)
                and not typ.item_type.native_enum
            ):
                raise exc.CompileError(
                    "PostgreSQL dialect cannot produce the CHECK constraint "
                    "for ARRAY of non-native ENUM; please specify "
                    "create_constraint=False on this Enum datatype."
                )

        return super(PGDDLCompiler, self).visit_check_constraint(constraint) 

def visit_array(self, element, **kw):
        return "ARRAY[%s]" % self.visit_clauselist(element, **kw) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('rule',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('type', sa.String(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('action', sa.Enum('added', 'removed', 'both', name='rule_actions'), nullable=False),
    sa.Column('alerters', postgresql.ARRAY(sa.String()), nullable=False),
    sa.Column('config', postgresql.JSONB(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###