编程错误 - sqlalchemy - on_conflict_do_update [英] ProgrammingError - sqlalchemy - on_conflict_do_update

问题描述

按照这个问题:

正如 Ilja Everilä 在他的答案中提到的，我创建了一个表对象:

As Ilja Everilä mentioned in his answer, I created a table object:

from sqlalchemy import *
metadata = MetaData()
idTagTable = Table('id_tag', metadata,
                 Column('id', String(255), primary_key = True),     
                 Column('category', String(20), nullable = False),
                 Column('createddate', Date, nullable = False),
                 Column('updatedon', Date, nullable = False)
                 )

创建表对象后，我更改了插入和更新语句:

After creating a table object, I changed insert and update statements:

insert_statement = sqlalchemy.dialects.postgresql.insert(idTagTable)
upsert_statement = insert_statement.on_conflict_do_update(
        constraint=PrimaryKeyConstraint('id'),
        set_={"updatedon": insert_statement.excluded.updateon,
              "category":insert_statement.excluded.category}
)
insert_values = df.to_dict(orient='records')
conn.execute(upsert_statement, insert_values)

现在我遇到了编程错误:

Now I am getting Programming Error:

Traceback (most recent call last):

File "<ipython-input-66-0fc6a1bf9c6b>", line 7, in <module>
conn.execute(upsert_statement, insert_values)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
exc_info

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
context)

File "/home/ubuntu/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 467, in do_executemany
cursor.executemany(statement, parameters)

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near 
")"
LINE 1: ...category) VALUES ('sports') ON CONFLICT () DO UPDAT...
                                                    ^

无法理解为什么我会收到此错误.

Not Able to understand why I am getting this error.

推荐答案

PrimaryKeyConstraint 对象用作 constraint= 参数未绑定到任何表，并且在呈现时似乎不会产生任何结果, 如 ON CONFLICT () 所示.而是将表的主键作为 conflict_target 传递，Postgresql 将执行唯一索引推断:

The PrimaryKeyConstraint object you're using as constraint= argument is not bound to any table and would seem to produce nothing when rendered, as seen in ON CONFLICT (). Instead pass the primary key(s) of your table as the conflict_target and Postgresql will perform unique index inference:

upsert_statement = insert_statement.on_conflict_do_update(
        constraint=idTagTable.primary_key,
        set_={"updatedon": insert_statement.excluded.updateon,
              "category":insert_statement.excluded.category}
)

这篇关于编程错误 - sqlalchemy - on_conflict_do_update的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！