buildpg

buildpg copied to clipboard

Not sure if that's what https://github.com/samuelcolvin/buildpg/issues/14 implies, but I found myself not finding any way to do something like:

UPDATE b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   a
WHERE  b.id = 123    -- optional, to update only selected row
AND    a.id = b.id;

with Values as it is currently.

Hopefully I got it correctly, it's only been a few hours I'm playing with the lib !

I'm a bit confused, are you talking about the where clause? I'm currently using the following:

from buildpg import Empty, V, Values

...

    if user_id:
        where = Where(V('user_id') == user_id)
    else:
        where = Empty()

    items = await conn.fetch_b(
        """
        select *
        from foobar
        :where
        order by whatever desc
        """,
        where=where,
    )

or maybe you want SetValues?

Could you give an example of how you're using this in python?

this let me do the below, but maybe I misunderstood what you meant in #14 !

I wasn't able to achieve that with SetValues

v={'column1':'table2.col1', 'column2':'table2.col2'}
render('update table1 set :values__set from table2', values=Values(**v))
Out[8]: 
('update table1 set (column1, column2) = ($1, $2) from table2',
 ['table2.col1', 'table2.col2'])

edit: I'm not talking about the where clause

or maybe you want SetValues?

Could you give an example of how you're using this in python?

ok my bad, I'm gonna not need this, you're right SetValues is enough, I failed to see that it was the same. sorry for the noise !

render('update table1 set :values from table2', values=SetValues(**v))
Out[10]: 
('update table1 set column1 = $1, column2 = $2 from table2',
 ['table2.col1', 'table2.col2'])

You're completely right that this is what I meant by #14, feel free to re-open this if you like.

When I get a chance I want to do a lot of work to this project, most importantly I want to 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.

ok reopening then !

I achieved what I want with SetValues, that generates a different sql from what I ended up with values__set and with the sql I originally wanted to build, both are valid, not sure that's what you want !

v={'column1':'table2.col1', 'column2':'table2.col2'}

with PR:

render('update table1 set :values__set from table2', values=Values(**v))
Out[8]: 
('update table1 set (column1, column2) = ($1, $2) from table2',
 ['table2.col1', 'table2.col2'])

with SetValues:

render('update table1 set :values from table2', values=SetValues(**v))
Out[10]: 
('update table1 set column1 = $1, column2 = $2 from table2',
 ['table2.col1', 'table2.col2'])

on a side note, the blablabla :values:set syntax would feel more natural indeed

I think the syntax needs to be the same as SetValues

I switched the Values render_set method to use the col1=1, col2=2 syntax. I always had a preference for the column-list syntax but I guess it's a matter of taste, both are valid sql anyway:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]