Passing a Python Variable To an "IN" of Postgresql

Question

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!

AKX · Accepted Answer · 2022-01-20 15:34:57Z

2

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.

answered Jan 20, 2022 at 15:34

AKX

171k16 gold badges146 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrian Klaver Over a year ago

You don't have to convert to a tuple, just keep as list and change ref IN %s to ref = ANY(%s) per List adaptation. Added benefit it won't error if the list is empty, whereas an empty tuple and IN will.

AKX Over a year ago

@AdrianKlaver Good points. I'm used to using tuples with IN, per tuple adaptation. :)

Richard Huxton · Accepted Answer · 2022-01-20 17:36:20Z

1

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.

answered Jan 20, 2022 at 17:36

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Collectives™ on Stack Overflow

Passing a Python Variable To an "IN" of Postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related