samuelcolvin / buildpg Goto Github PK

View Code? Open in Web Editor NEW

79.0 79.0 11.0 124 KB

Query building for the postgresql prepared statements and asyncpg.

License: MIT License

Makefile 1.21% Python 98.79%

buildpg's People

Contributors

Stargazers

Watchers

Forkers

andreasnel wayhomeuk esprengle amybalmforth euri10 generalcommission tomhamiltonstubber arontel bitner jasongi webclinic017

buildpg's Issues

reuse arguments

eg. it should be possible to reuse $1.

fetchval_b etc. available directly on a pool

eg. await pool.fetchval_b(...)

Issue with comparisons on Falsey Values.

conditions = [
    V('x.field') == V('y.field'),
    V('x.field2') == 0,
]
join = clauses.Join('table2', functools.reduce(utils.and_, conditions))

Expected:

JOIN table2 ON (x.field == $1 AND x.field2 == $2)

Actual:

JOIN table2 ON (x.field == $1 AND x.field2 == )

PR incoming

force kwargs for all _b functions

so arguments can't be confused with the equivilant non _b function.

JSONB Examples?

Hi -- dug through the code, tests, and README and didn't see any JSONB examples.

We've effectively resorted to large RawDangerous() blocks which is a concern.

Any chance the README could provide at minimum a few examples for using this library to work with JSONB queries?

Func('now')

should raise a more sensible error

Currently

from buildpg import Func
print(Func('now'))

Raises an ugly RuntimeError: generator raised StopIteration error which is not easy to track down.

This was with buildpg v0.1, later version might have fixed the problem.

in_ doesn't produce valid postgres syntax

Using in_ with a list/tuple won't work. You end up with an error like this:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$11"

The solution could be to generate the tuple from the iterable ourselves.

There's a related asyncpg issue here:

MagicStack/asyncpg#767

and also in their FAQ

https://magicstack.github.io/asyncpg/current/faq.html#why-do-i-get-postgressyntaxerror-when-using-expression-in-1

support Values.render_set()

So we can do:

await conn.execute_b('update foo set :values__set', values=Values(a=1, b=2))

Unsafe Checker does not work with accessing composite types

HAving a composite type called user_details, and wanting to select on that type does not work

V("(user_details).ssn") == identifier,

Results in

buildpg.components.UnsafeError: str contain unsafe (non word) characters: "(user_details).ssn"

Please check 8.16.3 at https://www.postgresql.org/docs/10/rowtypes.html

The unsafe should probably just disregard brackets

RecursionError: maximum recursion depth exceeded while calling a Python object

if I do funcs.comma_sep(*flat_tickers) with a very long list, I get a RecursionError: maximum recursion depth exceeded while calling a Python object

I coudnt make a simple reproduction other that with the attached list.
funcs.comma_sep(*flat_tickers[:2932]) is ok
funcs.comma_sep(*flat_tickers[:2933]) crashed

sfjapi_1           | Out[30]: Traceback (most recent call last):
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/IPython/core/formatters.py", line 222, in catch_format_error
sfjapi_1           |     r = method(self, *args, **kwargs)
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/IPython/core/formatters.py", line 707, in __call__
sfjapi_1           |     printer.pretty(obj)
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/IPython/lib/pretty.py", line 410, in pretty
sfjapi_1           |     return _repr_pprint(obj, self, cycle)
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/IPython/lib/pretty.py", line 778, in _repr_pprint
sfjapi_1           |     output = repr(obj)
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 81, in __repr__
sfjapi_1           |     return f'<SQL: "{self}">'
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 68, in __str__
sfjapi_1           |     return ''.join(self._get_chunks(self.render()))
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 74, in _get_chunks
sfjapi_1           |     yield from cls._get_chunks(chunk.render())
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 74, in _get_chunks
sfjapi_1           |     yield from cls._get_chunks(chunk.render())
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 74, in _get_chunks
sfjapi_1           |     yield from cls._get_chunks(chunk.render())
sfjapi_1           |   [Previous line repeated 2928 more times]
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/components.py", line 72, in _get_chunks
sfjapi_1           |     for chunk in gen:
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/logic.py", line 255, in render
sfjapi_1           |     yield from self._bracket(self.v1)
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/logic.py", line 247, in _bracket
sfjapi_1           |     if self._should_parenthesise(v):
sfjapi_1           |   File "/opt/venv/lib/python3.9/site-packages/buildpg/logic.py", line 242, in _should_parenthesise
sfjapi_1           |     sub_op = getattr(v, 'op', None)
sfjapi_1           | RecursionError: maximum recursion depth exceeded while calling a Python object

I have the same sys.getrecursionlimit() set at 3000 both in container and in my host, both crash, just not exactly at the same index.

Is there anything I can do except increasing that limit ?

partial logic

easy way to extend existing where clause.

Pep440 issue installing branch with poetry

While waiting for #44 to be merged I wanted to use my fork, howwever poetry finds the version = ' dev' is not pep404 compatible and refuses to install/
something like 0.4.0.dev maybe would be more appropriate ?

Values(..., skip_none=False)

option to remove null values from Values()

Use Formatter.parse

Switch from regexes to using Formatter.parse.

Then :values__set could be replaced with :values:set etc., but that's a whole new thing and shouldn't delay this.

ref #25

@@ operator for search

as a function and in logic

convert True and False

if isinstance(v, bool):
    return str(v).lower()

On Var.__init__

partial logic

eg. some logic is in raw str, then want to add AND foobar. Not possible atm.

Uppercase column names in Values

>>> render(":values__names", values=Values(FOO=1, BAR=2))
('FOO, BAR', [])
>>> render(":values__names", values=Values(**{'"FOO"':1, '"BAR"':2}))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/nic/.local/share/virtualenvs/nic-UOxQ1ZmO/lib/python3.7/site-packages/buildpg/components.py", line 96, in __init__
    check_word_many(self.names)
  File "/home/nic/.local/share/virtualenvs/nic-UOxQ1ZmO/lib/python3.7/site-packages/buildpg/components.py", line 30, in check_word_many
    raise UnsafeError(f'raw arguments contain unsafe (non word) characters: {unsafe}')
buildpg.components.UnsafeError: raw arguments contain unsafe (non word) characters: ['"FOO"', '"BAR"']

Postgres doesn't distinguish between a column named foo and a column named FOO unless you put the column name in double quotes "FOO".

Is there a way to allow unsafe or mark a name safe?

UnsafeError on aliases, can I bypass it ?

I haven't found a way to build an alias column with "unsafe" characters

Currently when doing this I get an UnsafeError

V('col1').as_('alias can be any string')

    raise UnsafeError(f'str contain unsafe (non word) characters: "{s}"')
buildpg.components.UnsafeError: str contain unsafe (non word) characters: "alias can be any string"

it's legit to have spaces in alias, slashes also

Check for empty/null values

How can I check for null or empty values in a query?

I have tried to write my query in the following ways without any success:

V(col_name) is None
V(col_name) == Empty()
V(col_name) is Empty()

add sql trove classifier

https://pypi.org/pypi?%3Aaction=list_classifiers

add Programming Language :: SQL

cant cast to arrays because of brackets

related to #31 (and #27 to some extent) I know I can use RawDangerous, just wanted to mention it

this works:

V('x').cast("int")
<SQL: "x::int">

but casting to arrays fails because of the brackets:

V('x').cast("int[]")
Traceback (most recent call last):
  File "/home/lotso/.asdf/installs/python/3.10.5/lib/python3.10/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "/home/lotso/PycharmProjects/buildpg/buildpg/logic.py", line 217, in cast
    return self.operate(Operator.cast, as_var(cast_type))
  File "/home/lotso/PycharmProjects/buildpg/buildpg/logic.py", line 276, in as_var
    return Var(n)
  File "/home/lotso/PycharmProjects/buildpg/buildpg/logic.py", line 316, in __init__
    super().__init__(VarLiteral(v1), op=op, v2=v2)
  File "/home/lotso/PycharmProjects/buildpg/buildpg/components.py", line 60, in __init__
    check_word(s)
  File "/home/lotso/PycharmProjects/buildpg/buildpg/components.py", line 25, in check_word
    raise UnsafeError(f'str contain unsafe (non word) characters: "{s}"')
buildpg.components.UnsafeError: str contain unsafe (non word) characters: "int[]"

it feels like there are some funcs that could (should ?) allow unsafe characters, space in aliases, brackets in cast ?

I just dicovered that, having to write the below snippet.
It's part of a HAVING clause logic I intended to push, but I think I would be shooting myself i the foot here, since countries is client-controlled...

    if countries:
        countries_logic = S(countries).operate(RawDangerous('::character varying[]')).contained_by(RawDangerous("array_agg(distinct dc.alpha_2)"))
        having_logic = (
            countries_logic
            if not having_logic
            else iand(having_logic, countries_logic)

countries
['CI']
S(countries).operate(RawDangerous('::character varying[]')).contained_by(RawDangerous("array_agg(distinct dc.alpha_2)"))
<SQL: "(['CI']::character varying[]) <@ array_agg(distinct dc.alpha_2)">

Offset

class Offset(Clause):
    base = 'OFFSET'

    def __init__(self, offset_value):
        super().__init__(offset_value)

SET query with params

I've been trying to find a way to pass params into asyncpg SET query. Can this library support that? Heres the example I tried, which fails with asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"

import asyncio

from buildpg import asyncpg

identity = "arn:aws:sts::123456:assumed-role/thing_1"


async def main():
    async with asyncpg.create_pool_b("postgresql://john:[email protected]/my_db") as pool:
        await pool.execute_b("set session iam.identity = :ident", ident=identity)


if __name__ == "__main__":
    asyncio.run(main())

NULLS FIRST/NULLS LAST

I'd like to make a pull request: adding operators for NULLS FIRST and NULLS LAST ordering. Is this OK?