我正在尝试使用SQLAlchemy Core中的SQL语句更新PostgreSQL表上的整数数组。我第一次尝试使用查询生成器,但也不知道如何做到这一点。我相信Psycopg2,这是我正在使用的方言,可以自动将数组形成PostgreSQL可以接受的格式。
下面是表架构:
CREATE TABLE surveys (
survey_id serial PRIMARY KEY,
question_ids_ordered INTEGER[],
created_at TIMESTAMP NOT NULL DEFAULT now(),
);
以及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)
我收到的回溯是:
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)
我做错什么了?
如果您的表是这样定义的:
然后,您可以简单地按以下方式更新阵列(与psycopg2一起使用):
或者,如果愿意,可以使用
text()
构造编写原始SQL:相关问题 更多 >
编程相关推荐