2

I'm working with Python, SQLAlchemy(I don't use the ORM) and Postgres. I have a table in Postgres which includes two text[]fields (to & from), and some others(irrelevant). If a 'from' column includes a substring "priority", then the 'to' column may include a substring "head". If it does I would like to change it to "tail". Alternatively I would also be happy with a solution, to change the whole 'to' text[] column.

table : from text[] to text[]

What I have done so far:

eng = engine.execute("select to from table, unnest(from) f where f like (%s);", ('%priority%'))

for row in eng:
   if row[0][0].find('head'):
       row[0][0].replace('head', 'tail')

Now, I don't know how to do an UPDATE

I tried:

#this only updates it, but if the next time I do the first query it's still the same(I know it's because it's only a select statement. I just included it if it might be helpful somehow)
engine.execute('SELECT regexp_replace(to::varchar, %s, %s) from table, 'head', 'tail');

Then I tried:

engine.execute("update table set to = (%s) WHERE to = (%s)", 'head', 'tail')

It's probably best if the UPDATE statement also includes a condition that 'from' needs to include "priority".

Any help would be very much appreciated.

Majmun
  • 19
  • 5

1 Answers1

1

You can solve the whole problem soleley by using sql. Here you go:

update table
    -- replace head by tail in the to-array
    set to = array_replace(to, 'head', 'tail')
where ARRAY['priority'] <@ from
    and ARRAY['head'] <@ to

Note 1: Maybe you need to wrap your names (table, from, to) with "", because they are reserved keywords in sql.

update "table"
    set "to" = array_replace("to", 'head', 'tail')
where ARRAY['priority'] <@ "from"
    and ARRAY['head'] <@ "to"

Note 2: The <@ means "is contained by", see https://www.postgresql.org/docs/current/functions-array.html.

and
  • 127
  • 11