Update a PostgreSQL array using SQLAlchemy

Question

I'm trying to update an integer array on a PostgreSQL table using a SQL statement in SQLAlchemy Core. I first tried using the query generator, but couldn't figure out how to do that either. I believe that Psycopg2, which is the dialect that I'm using, can automatically form the array into a format that PostgreSQL can accept.

Here's the table schema:

CREATE TABLE surveys (
    survey_id serial PRIMARY KEY,
    question_ids_ordered INTEGER[],
    created_at TIMESTAMP NOT NULL DEFAULT now(),
);

And the SQLAlchemy statement:

survey_id = 46
question_ids_ordered = [237, 238, 239, 240, 241, 242, 243]

with engine.begin() as conn:
    conn.execute("""UPDATE surveys
                    SET question_ids_ordered = %s
                    WHERE survey_id = %s""",
                    question_ids_ordered, survey_id)

And the traceback I receive is:

Traceback (most recent call last):
  File "foo.py", line 16, in <module>
    res = add_question_to_group(current_user, 46, 358, question_ids_ordered, new_group_name="Jellyfish?")
  File "/vagrant/workspace/panel/panel/survey.py", line 80, in add_question_to_group
    question_ids_ordered, survey_id)
  File "/home/vagrant/.virtualenvs/project/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 664, in execute
    params)
  File "/home/vagrant/.virtualenvs/project/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 808, in _execute_text
    statement, parameters
  File "/home/vagrant/.virtualenvs/project/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 831, in _execute_context
    None, None)
  File "/home/vagrant/.virtualenvs/project/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 827, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/home/vagrant/.virtualenvs/project/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 513, in _init_statement
    for p in parameters]
sqlalchemy.exc.StatementError: 'int' object is not iterable (original cause: TypeError: 'int' object is not iterable) 'UPDATE surveys\n                        SET question_ids_ordered = %s\n                        WHERE survey_id = %s' ([237, 238, 239, 240, 241, 242, 243], 46)

What am I doing wrong?

If you are resorting to raw SQL, why not ditch the ORM altogether? Please read the docs. — Paulo Scardine
– Paulo Scardine, Commented Jan 8, 2013 at 16:48
In this example I'm not using ORM. Nor am I using ORM anywhere in my app; I'm using SQLAlchemy Core, not ORM. — skyler
– skyler, Commented Jan 8, 2013 at 16:51
The ORM is the best part of SQLAlchemy, as it gives you portability and reusable queries (building raw SQL using string concatenation sucks). Out of curiosity, what SQLAlchemy Core gives you that Psycopg2 does not? — Paulo Scardine
– Paulo Scardine, Commented Jan 8, 2013 at 16:54
I do a lot of INSERTs with multiple VALUES clauses. Doing it by hand means a lot of redundant code (or one-off functions that I don't particularly like). SQLAlchemy makes these kinds of inserts easy to do, and in addition uses a connection pool, which is handy. — skyler
– skyler, Commented Jan 8, 2013 at 16:56

Demitri · Accepted Answer · 2015-09-06 16:42:48Z

24

If your table is defined like this:

from datetime import datetime
from sqlalchemy import *
from sqlalchemy.dialects.postgresql import ARRAY

meta = MetaData()
surveys_table = Table('surveys', meta,
    Column('surveys_id', Integer, primary_key=True),
    Column('questions_ids_ordered', ARRAY(Integer)),
    Column('created_at', DateTime, nullable=False, default=datetime.utcnow)
)

Then you can simply update your array in the following way (works with psycopg2):

engine = create_engine('postgresql://localhost')
conn = engine.connect()
u = surveys_table.update().where(surveys_table.c.id == 46).\
     values(questions_ids_ordered=[237, 238, 239, 240, 241, 242, 243])
conn.execute(u)
conn.close()

Or, if you prefer, write raw SQL using text() construct:

from sqlalchemy.sql import text
with engine.connect() as conn:
    u = text('UPDATE surveys SET questions_ids_ordered = :q WHERE id = :id')
    conn.execute(u, q=[237, 238, 239, 240, 241, 242, 243], id=46)

edited Sep 6, 2015 at 16:42

Demitri

14.2k4 gold badges44 silver badges43 bronze badges

answered Jan 8, 2013 at 18:33

Audrius Kažukauskas

13.6k4 gold badges55 silver badges54 bronze badges

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

7 Comments

skyler Over a year ago

Thank you for your answer. Could you help with doing this in SQL?

Audrius Kažukauskas Over a year ago

OK, although I think that using SA expressions looks nicer. Not sure why passing strings and args directly to execute() doesn't work, but it does when using text() construct. Will extend my example.

skyler Over a year ago

I agree too that SA expressions look cleaner, but in the case where I'm using an array argument like this, I'm joining through 4 or 5 tables. In my opinion, when this many joins are involved, plain SQL is cleaner to read and easier to understand.

whoKnows Over a year ago

Now how would you append one value onto that array?

Audrius Kažukauskas Over a year ago

@whoKnows please post your problem as a separate question, not inside comments of an answer to unrelated question.

|

Lucian · Accepted Answer · 2021-12-01 21:12:13Z

1

In Flask, the model of your Survey table will look something like this:

class Survey(UserMixin, db.Model):
    __tablename__ = 'surveys'
    id = db.Column(db.Integer, primary_key=True)
    question_ids_ordered = db.Column('question_ids_ordered', db.ARRAY(db.Integer))

And you want to update the question_ids_ordered field, you can use ORM like so:

from sqlalchemy.orm.attributes import flag_modified

survery = (Survey
           .query
           .filter(Survey.id == 46)).first()

survery.question_ids_ordered.remove(237)
flag_modified(survery, "question_ids_ordered")
db.session.merge(survery)
db.session.flush()
db.session.commit()

answered Dec 1, 2021 at 21:12

Lucian

89411 silver badges37 bronze badges

Collectives™ on Stack Overflow

Update a PostgreSQL array using SQLAlchemy

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related