Passing a Python Variable To an "IN" of Postgresql

Hope you guys doing good today,

I have a python script that pulls a name from stock_picking table which is :

self.env.cr.execute("SELECT name FROM stock_picking WHERE sale_id = %s", [pulled_so_id])
raw_pick = self.env.cr.fetchall()

When executed, query above working well and showing result of this three names :

[('WH/OUT/00013',), ('WH/OUT/00011',), ('WH/OUT/00012',)]

I converted those three result to string, removing several unwanted character, and assigning three of it into a variable. Long story short, it become like this :

pulled_picking_name = str(raw_pick5)

#  In my terminal, a variable above now contains clean result of :
#  'WH/OUT/00013','WH/OUT/00011','WH/OUT/00012'

And now, i'd like to put this pulled_picking_name into an UPDATE query in my same python script with this line :

 self.env.cr.execute("UPDATE account_move SET partner_id = %s WHERE ref IN (%s)",
                                [pulled_partner_id, pulled_picking_name])

But then nothing really happens.

I filled that IN (%s) with my self-declared string and it worked well. I also test that three result directly into psql with UPDATE query above and it worked great also.

But when i'm assigning that three result into variable and pass it to my query, nothing really happens. No error message or anything.

I feel like i'm missing something but i can figured out what it is :(

Please, every help is really appreciated!

Solution 1:

You absolutely don't need to "convert to string and remove unwanted characters". What you're getting with

raw_pick = self.env.cr.fetchall()
# [('WH/OUT/00013',), ('WH/OUT/00011',), ('WH/OUT/00012',)]

is a list of row tuples; since each row of your result set has one column, you get 1-tuples.

If you want a plain array of strings (or whatever the first column's datatype is), do

raw_pick = [row[0] for row in self.env.cr.fetchall()]
# ['WH/OUT/00013', 'WH/OUT/00011', 'WH/OUT/00012']

To use in, you'll need to pass in a tuple to the Postgres adapter. Assuming you want to do something to all of those raw_pick strings,

names = tuple(raw_pick)
self.env.cr.execute(
    "UPDATE account_move SET partner_id = %s WHERE ref IN %s",
    [pulled_partner_id, tuple(raw_pick)],
)

does the trick.

Solution 2:

More to the point IMHO is why on Earth would are you messing around dragging the data back and fore to some Python code?

UPDATE account_move SET partner_id = %s WHERE ref IN (
  SELECT name FROM stock_picking WHERE sale_id = %s
);

There are other ways to write that query, but that is just the two parts stuck together.