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?
Update table elements in SQLAlchemy. Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
You will learn: Install a Postgres server locally and create a database. Use Python with SQLAlchemy to connect to the database and create tables. Use Python with SQLAlchemy to insert data and query the database. Change models models and migrate the database with Alembic.
psycopg2 is driver to run query for PostgreSQL from python. SQLAlchemy is the ORM which is not same as database driver. It will give you flexibility so you can write your code without any database specific standard. ORM provide database independence for programmer.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With