sqlalchemy - 编程错误 - sqlalchemy - on_conflict_do_update

Question

关注此question :

正如 Ilja Everilä 在他的 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_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=参数未绑定(bind)到任何表，并且在呈现时似乎不产生任何内容，如 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}
)