ProgrammingError - sqlalchemy - on_conflict_do_update

Question

遵循这个question

正如Ilja在他的answer中提到的，我创建了一个表对象：

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)
                 )

创建表对象后，更改了insert和update语句：

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)

现在我收到了编程错误：

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...
                                                    ^

无法理解我为什么会犯这个错误。

Answer 1

您使用的作为PrimaryKeyConstraint参数的constraint=对象不绑定到任何表，呈现时似乎不会产生任何结果，如ON CONFLICT ()中所示。相反，传递表的主键，因为conflict_target和Postgresql将执行唯一的索引推断：

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