current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
    
If you are resorting to raw SQL, why not ditch the ORM altogether? Please read the docs. –  Paulo Scardine Jan 8 '13 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 Jan 8 '13 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 Jan 8 '13 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 Jan 8 '13 at 16:56

1 Answer 1

up vote 4 down vote accepted

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:

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)
share|improve this answer
    
Thank you for your answer. Could you help with doing this in SQL? –  skyler Jan 8 '13 at 22:02
    
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. –  Audrius Kažukauskas Jan 9 '13 at 10:13
    
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. –  skyler Jan 9 '13 at 14:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.