Python sqlalchemy.dialects.postgresql.dialect() Examples

def test_create_index_with_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", String))

        idx1 = Index("test_idx1", tbl.c.data)
        idx2 = Index("test_idx2", tbl.c.data, postgresql_using="btree")
        idx3 = Index("test_idx3", tbl.c.data, postgresql_using="hash")

        self.assert_compile(
            schema.CreateIndex(idx1),
            "CREATE INDEX test_idx1 ON testtbl " "(data)",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl " "USING btree (data)",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx3),
            "CREATE INDEX test_idx3 ON testtbl " "USING hash (data)",
            dialect=postgresql.dialect(),
        ) 

def test_format(self):
        seq = Sequence("my_seq_no_schema")
        dialect = postgresql.dialect()
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == "my_seq_no_schema"
        )
        seq = Sequence("my_seq", schema="some_schema")
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == "some_schema.my_seq"
        )
        seq = Sequence("My_Seq", schema="Some_Schema")
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == '"Some_Schema"."My_Seq"'
        ) 

def create(self, bind=None, checkfirst=True):
        """Emit ``CREATE TYPE`` for this
        :class:`~.postgresql.ENUM`.

        If the underlying dialect does not support
        Postgresql CREATE TYPE, no action is taken.

        :param bind: a connectable :class:`.Engine`,
         :class:`.Connection`, or similar object to emit
         SQL.
        :param checkfirst: if ``True``, a query against
         the PG catalog will be first performed to see
         if the type does not exist already before
         creating.

        """
        if not bind.dialect.supports_native_enum:
            return

        if not checkfirst or \
                not bind.dialect.has_type(
                    bind, self.name, schema=self.schema):
            bind.execute(CreateEnumType(self)) 

def test_numeric_codes(self):
        from sqlalchemy.dialects.postgresql import (
            pg8000,
            pygresql,
            psycopg2,
            psycopg2cffi,
            base,
        )

        dialects = (
            pg8000.dialect(),
            pygresql.dialect(),
            psycopg2.dialect(),
            psycopg2cffi.dialect(),
        )
        for dialect in dialects:
            typ = Numeric().dialect_impl(dialect)
            for code in (
                base._INT_TYPES + base._FLOAT_TYPES + base._DECIMAL_TYPES
            ):
                proc = typ.result_processor(dialect, code)
                val = 23.7
                if proc is not None:
                    val = proc(val)
                assert val in (23.7, decimal.Decimal("23.7")) 

def test_custom_subclass(self, connection):
        class MyEnum(TypeDecorator):
            impl = Enum("oneHI", "twoHI", "threeHI", name="myenum")

            def process_bind_param(self, value, dialect):
                if value is not None:
                    value += "HI"
                return value

            def process_result_value(self, value, dialect):
                if value is not None:
                    value += "THERE"
                return value

        t1 = Table("table1", self.metadata, Column("data", MyEnum()))
        self.metadata.create_all(testing.db)

        connection.execute(t1.insert(), {"data": "two"})
        eq_(connection.scalar(select([t1.c.data])), "twoHITHERE") 

def test_standalone_enum(self):
        metadata = MetaData(testing.db)
        etype = Enum(
            "four", "five", "six", name="fourfivesixtype", metadata=metadata
        )
        etype.create()
        try:
            assert testing.db.dialect.has_type(testing.db, "fourfivesixtype")
        finally:
            etype.drop()
            assert not testing.db.dialect.has_type(
                testing.db, "fourfivesixtype"
            )
        metadata.create_all()
        try:
            assert testing.db.dialect.has_type(testing.db, "fourfivesixtype")
        finally:
            metadata.drop_all()
            assert not testing.db.dialect.has_type(
                testing.db, "fourfivesixtype"
            ) 

def create(self, bind=None, checkfirst=True):
        """Emit ``CREATE TYPE`` for this
        :class:`~.postgresql.ENUM`.

        If the underlying dialect does not support
        Postgresql CREATE TYPE, no action is taken.

        :param bind: a connectable :class:`.Engine`,
         :class:`.Connection`, or similar object to emit
         SQL.
        :param checkfirst: if ``True``, a query against
         the PG catalog will be first performed to see
         if the type does not exist already before
         creating.

        """
        if not bind.dialect.supports_native_enum:
            return

        if not checkfirst or \
                not bind.dialect.has_type(
                    bind, self.name, schema=self.schema):
            bind.execute(CreateEnumType(self)) 

def get_strict_matching_stickers(session, context):
    """Query all strictly matching stickers for given tags."""
    matching_stickers = get_strict_matching_query(session, context)

    limit = context.limit if context.limit else 50
    matching_stickers = matching_stickers.offset(context.offset).limit(limit)

    #    if config['logging']['debug']:
    #        print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
    #        print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}).params)

    matching_stickers = matching_stickers.all()

    if config["logging"]["debug"]:
        pprint("Strict results:")
        pprint(matching_stickers)

    return matching_stickers 

def get_fuzzy_matching_stickers(session, context):
    """Get fuzzy matching stickers."""
    limit = context.limit if context.limit else 50
    matching_stickers = (
        get_fuzzy_matching_query(session, context)
        .offset(context.fuzzy_offset)
        .limit(limit)
    )

    #    if config['logging']['debug']:
    #        print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
    #        print(matching_stickers.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}).params)

    matching_stickers = matching_stickers.all()
    if config["logging"]["debug"]:
        pprint("Fuzzy results:")
        pprint(matching_stickers)
    return matching_stickers 

def get_sql_string(sqlalchemy_query):
    """
    Return SQL string compiled from the given sqlalchemy query (using the PostgreSQL dialect).

    Parameters
    ----------
    sqlalchemy_query : sqlalchemy.sql.Selectable
        SQLAlchemy query

    Returns
    -------
    str
        SQL string compiled from the sqlalchemy query.
    """
    assert isinstance(sqlalchemy_query, Selectable)
    compiled_query = sqlalchemy_query.compile(
        dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}
    )
    sql = str(compiled_query)
    return sql 

def create(self, bind=None, checkfirst=True):
        """Emit ``CREATE TYPE`` for this
        :class:`~.postgresql.ENUM`.

        If the underlying dialect does not support
        Postgresql CREATE TYPE, no action is taken.

        :param bind: a connectable :class:`.Engine`,
         :class:`.Connection`, or similar object to emit
         SQL.
        :param checkfirst: if ``True``, a query against
         the PG catalog will be first performed to see
         if the type does not exist already before
         creating.

        """
        if not bind.dialect.supports_native_enum:
            return

        if not checkfirst or \
                not bind.dialect.has_type(
                    bind, self.name, schema=self.schema):
            bind.execute(CreateEnumType(self)) 

def _already_in_filter(self, names):
        ''' Checks if the parameter name already added into the filter '''

        infilter = None
        if names:
            if not isinstance(self.query, type(None)):
                if not isinstance(self.query.whereclause, type(None)):
                    wc = str(self.query.whereclause.compile(dialect=postgresql.dialect(),
                             compile_kwargs={'literal_binds': True}))
                    infilter = any([name in wc for name in names])

        return infilter

    #
    # Methods specific to functional queries
    # 

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 result_processor(self, dialect, coltype):
        super_rp = super(_ArrayOfEnum, self).result_processor(dialect, coltype)

        def handle_raw_string(value):
            inner = re.match(r"^{(.*)}$", value).group(1)
            return inner.split(",") if inner else []

        def process(value):
            if value is None:
                return None
            return super_rp(handle_raw_string(value))

        return process 

def define_tables(cls, metadata):
        class ProcValue(TypeDecorator):
            impl = cls.ARRAY(Integer, dimensions=2)

            def process_bind_param(self, value, dialect):
                if value is None:
                    return None
                return [[x + 5 for x in v] for v in value]

            def process_result_value(self, value, dialect):
                if value is None:
                    return None
                return [[x - 7 for x in v] for v in value]

        Table(
            "arrtable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("intarr", cls.ARRAY(Integer)),
            Column("strarr", cls.ARRAY(Unicode())),
            Column("dimarr", ProcValue),
        )

        Table(
            "dim_arrtable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("intarr", cls.ARRAY(Integer, dimensions=1)),
            Column("strarr", cls.ARRAY(Unicode(), dimensions=1)),
            Column("dimarr", ProcValue),
        ) 

def test_oracle(self):
        from sqlalchemy.dialects import oracle
        dialect = oracle.dialect()
        query = str(self.group_concat_md.compile(dialect=dialect))

        expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
                    '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
                    '''metric_dimension.name || '=' || metric_dimension.value) AS dimensions '''
                    '''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
        self.assertEqual(expected, query)

        query = str(self.group_concat_md_order.compile(dialect=dialect))

        expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
                    '''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
                    '''metric_dimension.name ASC) AS dimensions '''
                    '''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
        self.assertEqual(expected, query)

        expected = (
            """SELECT metric_dimension.dimension_set_id \n"""
            """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'"""
            """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN"""
            """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""")
        query = str(self.order_by_field.compile(dialect=dialect))
        self.assertEqual(expected, query) 

def test_insert_returning(self):
        dialect = postgresql.dialect()
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )

        i = insert(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING mytable.myid, "
            "mytable.name",
            dialect=dialect,
        )
        i = insert(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING mytable.myid, "
            "mytable.name, mytable.description",
            dialect=dialect,
        )
        i = insert(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING length(mytable.name) "
            "AS length_1",
            dialect=dialect,
        ) 

def dialect(self) -> Dialect:
        return postgresql.dialect() 

def test_create_partial_index(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("data", Integer))
        idx = Index(
            "test_idx1",
            tbl.c.data,
            postgresql_where=and_(tbl.c.data > 5, tbl.c.data < 10),
        )
        idx = Index(
            "test_idx1",
            tbl.c.data,
            postgresql_where=and_(tbl.c.data > 5, tbl.c.data < 10),
        )

        # test quoting and all that

        idx2 = Index(
            "test_idx2",
            tbl.c.data,
            postgresql_where=and_(tbl.c.data > "a", tbl.c.data < "b's"),
        )
        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE INDEX test_idx1 ON testtbl (data) "
            "WHERE data > 5 AND data < 10",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl (data) "
            "WHERE data > 'a' AND data < 'b''s'",
            dialect=postgresql.dialect(),
        ) 

def get_table_oid(self, table_name, schema=None):
        """Return the oid from `table_name` and `schema`."""

        return self.dialect.get_table_oid(self.bind, table_name, schema,
                                          info_cache=self.info_cache) 

def visit_ENUM(self, type_):
        return self.dialect.identifier_preparer.format_type(type_) 

def visit_enum(self, type_):
        if not type_.native_enum or not self.dialect.supports_native_enum:
            return super(PGTypeCompiler, self).visit_enum(type_)
        else:
            return self.visit_ENUM(type_) 

def get_column_specification(self, column, **kwargs):

        colspec = self.preparer.format_column(column)
        impl_type = column.type.dialect_impl(self.dialect)
        if column.primary_key and \
            column is column.table._autoincrement_column and \
            (
                self.dialect.supports_smallserial or
                not isinstance(impl_type, sqltypes.SmallInteger)
            ) and (
                column.default is None or
                (
                    isinstance(column.default, schema.Sequence) and
                    column.default.optional
                )):
            if isinstance(impl_type, sqltypes.BigInteger):
                colspec += " BIGSERIAL"
            elif isinstance(impl_type, sqltypes.SmallInteger):
                colspec += " SMALLSERIAL"
            else:
                colspec += " SERIAL"
        else:
            colspec += " " + self.dialect.type_compiler.process(column.type)
            default = self.get_column_default_string(column)
            if default is not None:
                colspec += " DEFAULT " + default

        if not column.nullable:
            colspec += " NOT NULL"
        return colspec 

def render_literal_value(self, value, type_):
        value = super(PGCompiler, self).render_literal_value(value, type_)

        if self.dialect._backslash_escapes:
            value = value.replace('\\', '\\\\')
        return value 

def test_create_index_with_labeled_ops(self):
        m = MetaData()
        tbl = Table(
            "testtbl",
            m,
            Column("data", String),
            Column("data2", Integer, key="d2"),
        )

        idx = Index(
            "test_idx1",
            func.lower(tbl.c.data).label("data_lower"),
            postgresql_ops={"data_lower": "text_pattern_ops"},
        )

        idx2 = Index(
            "test_idx2",
            (func.xyz(tbl.c.data) + tbl.c.d2).label("bar"),
            tbl.c.d2.label("foo"),
            postgresql_ops={"bar": "text_pattern_ops", "foo": "int4_ops"},
        )

        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE INDEX test_idx1 ON testtbl "
            "(lower(data) text_pattern_ops)",
            dialect=postgresql.dialect(),
        )
        self.assert_compile(
            schema.CreateIndex(idx2),
            "CREATE INDEX test_idx2 ON testtbl "
            "((xyz(data) + data2) text_pattern_ops, "
            "data2 int4_ops)",
            dialect=postgresql.dialect(),
        ) 

def result_processor(self, dialect, coltype):
        item_proc = self.item_type.\
            dialect_impl(dialect).\
            result_processor(dialect, coltype)

        def process(value):
            if value is None:
                return value
            else:
                return self._proc_array(
                    value,
                    item_proc,
                    self.dimensions,
                    tuple if self.as_tuple else list)
        return process 

def bind_processor(self, dialect):
        item_proc = self.item_type.\
            dialect_impl(dialect).\
            bind_processor(dialect)

        def process(value):
            if value is None:
                return value
            else:
                return self._proc_array(
                    value,
                    item_proc,
                    self.dimensions,
                    list)
        return process 

def show(self, prop='query'):
        ''' Prints into the console

        Displays the query to the console with parameter variables plugged in.
        Works only in local mode.  Input prop can be one of query, joins, or filter.

        Allowed Values for Prop:
            - query: displays the entire query (default if nothing specified)
            - joins: displays the tables that have been joined in the query
            - filter: displays only the filter used on the query

        Parameters:
            prop (str):
                The type of info to print.  Can be 'query', 'joins', or 'filter'.

        Returns:
            The SQL string

        '''

        assert prop in [None, 'query', 'joins', 'filter'], 'Input must be query, joins, or filter'

        if self.data_origin == 'db':
            if not prop or prop == 'query':
                sql = self._get_sql(self.query)
            elif prop == 'joins':
                sql = self._joins
            elif prop == 'filter':
                if hasattr(self.query, 'whereclause'):
                    sql = self.query.whereclause.compile(dialect=postgresql.dialect(), compile_kwargs={'literal_binds': True})
                else:
                    sql = 'cannot extract filter from where clause'
            else:
                sql = self.__getattribute__(prop)

            return str(sql)
        elif self.data_origin == 'api':
            sql = self.search_filter
            return sql 

def result_processor(self, dialect, coltype):
        if self.as_uuid:
            def process(value):
                if value is not None:
                    value = _python_UUID(value)
                return value
            return process
        else:
            return None 

def test_sybase(self):
        from sqlalchemy.dialects import sybase as diale_
        dialect = diale_.dialect()
        query = str(self.group_concat_md.compile(dialect=dialect))

        expected = (
            '''SELECT metric_dimension.dimension_set_id, LIST(metric_dimension.name || '=' '''
            '''|| metric_dimension.value, ',') AS dimensions '''
            '''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
        self.assertEqual(expected, query)

        query = str(self.group_concat_md_order.compile(dialect=dialect))

        expected = (
            '''SELECT metric_dimension.dimension_set_id, LIST(metric_dimension.name || '=' '''
            '''|| metric_dimension.value, ',') AS dimensions '''
            '''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
        self.assertEqual(expected, query)

        expected = (
            """SELECT metric_dimension.dimension_set_id \n"""
            """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'"""
            """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN"""
            """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""")
        query = str(self.order_by_field.compile(dialect=dialect))
        self.assertEqual(expected, query)