代码之家 › 专栏 › 技术社区 › Vivek Mehendiratta

编程错误-sqlalchemy-on\u conflict\u do\u update

sqlalchemy

Vivek Mehendiratta · 技术社区 · 7 年前

在此之后 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和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...
                                                    ^

无法理解为什么会出现此错误。

1 回复 | 直到 3 年前

Ilja Everilä 7 年前

这个 PrimaryKeyConstraint 您正在用作的对象 constraint= 参数未绑定到任何表,在呈现时似乎不会产生任何结果,如中所示 ON CONFLICT () 。而是将表的主键作为 冲突\u目标 Postgresql将执行唯一索引推断:

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