Python sqlalchemy.dialects.postgresql.aggregate_order_by() Examples

The following are 6 code examples of sqlalchemy.dialects.postgresql.aggregate_order_by(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy.dialects.postgresql , or try the search function .
Example #1
Source File: test_compiler.py    From sqlalchemy with MIT License 6 votes vote down vote up 
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",
        )
Example #2
Source File: postgres.py    From ivre with GNU General Public License v3.0 5 votes vote down vote up 
def _get_ips_ports(self, flt, limit=None, skip=None):
        req = flt.query(select([self.tables.scan.id]))
        if skip is not None:
            req = req.offset(skip)
        if limit is not None:
            req = req.limit(limit)
        base = req.cte("base")
        return (
            {
                "addr": rec[2], "starttime": rec[1],
                "ports": [
                    {"proto": proto, "port": int(port), "state_state": state}
                    for proto, port, state in (
                        elt.split(',') for elt in
                        ''.join(rec[0])[3:-3].split(')","(')
                    )
                ]
            }
            for rec in
            self.db.execute(
                select([
                    func.array_agg(postgresql.aggregate_order_by(
                        tuple_(self.tables.port.protocol,
                               self.tables.port.port,
                               self.tables.port.state).label('a'),
                        tuple_(self.tables.port.protocol,
                               self.tables.port.port).label('a')
                    )).label('ports'),
                    self.tables.scan.time_start, self.tables.scan.addr,
                ])
                .select_from(join(self.tables.port, self.tables.scan))
                .group_by(self.tables.scan.addr, self.tables.scan.time_start)
                .where(and_(self.tables.port.port >= 0,
                            self.tables.scan.id.in_(base)))
            )
        )
Example #3
Source File: test_compiler.py    From sqlalchemy with MIT License 5 votes vote down vote up 
def test_aggregate_order_by_one(self):
        m = MetaData()
        table = Table("table1", m, Column("a", Integer), Column("b", Integer))
        expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
        stmt = select([expr])

        # note this tests that the object exports FROM objects
        # correctly
        self.assert_compile(
            stmt,
            "SELECT array_agg(table1.a ORDER BY table1.b DESC) "
            "AS array_agg_1 FROM table1",
        )
Example #4
Source File: test_compiler.py    From sqlalchemy with MIT License 5 votes vote down vote up 
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",
        )
Example #5
Source File: test_compiler.py    From sqlalchemy with MIT License 5 votes vote down vote up 
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("','"),
        )
Example #6
Source File: test_compiler.py    From sqlalchemy with MIT License 5 votes vote down vote up 
def test_aggregate_order_by_adapt(self):
        m = MetaData()
        table = Table("table1", m, Column("a", Integer), Column("b", Integer))
        expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
        stmt = select([expr])

        a1 = table.alias("foo")
        stmt2 = sql_util.ClauseAdapter(a1).traverse(stmt)
        self.assert_compile(
            stmt2,
            "SELECT array_agg(foo.a ORDER BY foo.b DESC) AS array_agg_1 "
            "FROM table1 AS foo",
        )