使用 SQLAlchemy 更新 PostgreSQL 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14219775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update a PostgreSQL array using SQLAlchemy
提问by skyler
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.
我正在尝试使用 SQLAlchemy Core 中的 SQL 语句更新 PostgreSQL 表上的整数数组。我首先尝试使用查询生成器，但也不知道该怎么做。我相信我使用的方言 Psycopg2 可以自动将数组形成为 PostgreSQL 可以接受的格式。
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:
以及 SQLAlchemy 语句：
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?
我究竟做错了什么？
回答by Audrius Ka?ukauskas
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):
然后您可以通过以下方式简单地更新您的阵列（适用于 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:
或者，如果您愿意，也可以使用text()构造编写原始 SQL ：
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)
