我想用最少的精力重新启动,为了简单起见,我减少了列,这不起作用:
sql = '''INSERT INTO temp.tickets
(id, created_at, updated_at, emails, status)
VALUES
(%s, %s, %s, %s, %s)
ON CONFLICT (id)
DO UPDATE SET ( emails, status) values (%s,%s)
'''
cursor = cm.cursor()
## cm is a custom module
cursor.execute(sql, (ticket['id'],
ticket['created_at'],
ticket['updated_at'],
ticket['emails'], ticket['status'], )
此代码显示错误:
return super(DictCursor, self).execute(query, vars)
IndexError: tuple index out of range
我需要在cursor.execute()
中更改什么才能工作?
Bellow代码可以工作,但我喜欢使用%s
而不是类型:每列使用email = excluded.email
sql = '''INSERT INTO temp.tickets
(id, created_at, updated_at, emails, status)
VALUES
(%s, %s, %s, %s, %s)
ON CONFLICT (id)
DO UPDATE SET emails = excluded.eamils, status = excluded.status
'''
cursor = cm.cursor()
# cm is a custom module
cursor.execute(sql, (ticket['id'],
ticket['created_at'],
ticket['updated_at'],
ticket['emails'], ticket['status'], )
有两个相关问题链接1,链接2
我会尝试这样的东西:
sql = '''INSERT INTO temp.tickets
(id, created_at, updated_at, emails, status)
VALUES
(%s, %s, %s, %s, %s)
ON CONFLICT (id)
DO UPDATE SET ( emails, status) values (%s,%s)
'''
cursor = cm.cursor()
## cm is a custom module
cursor.execute(sql, (ticket['id'],
ticket['created_at'],
ticket['updated_at'],
ticket['emails'],
ticket['status'],
ticket['emails'],
ticket['status'] )
%s
的数目必须与参数的数目相匹配。
当Postgres遇到捕获的冲突时,它基本上会创建一个名为EXCLUDED的记录,其中包含您试图插入的值。您可以在DO UPDATE中引用此记录。尝试以下操作:
INSERT INTO temp.tickets
(id, created_at, updated_at, emails, status)
VALUES
(%s, %s, %s, %s, %s)
ON CONFLICT (id)
DO UPDATE
SET emails = excluded.emails
, status = excluded.status
, updated_at = excluded.updated_at -- my assumption.
...
您将不得不按照源语言的要求格式化。