python - sqlalchemy on_duplicate_key_update

Question

灵感来自https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert

def upsert_by_inner_machine_id(cls, session: Session, data: Results):
        update_field = ['is_risk', 'threat_access', 'risk_file',
                        'attack_event', 'access_rule', 'update_time']
        # stmt = sqlutil.upsert(cls, data, update_field)

        stmt = insert(cls).values(data)
        # d = {f: getattr(stmt.inserted, f) for f in update_field}
        stmt.on_duplicate_key_update({
            'is_risk': 'is_risk + VALUES(is_risk)',
            'threat_access': 'threat_access + VALUES(threat_access)',
            'risk_file': 'risk_file + VALUES(risk_file)',
            'attack_event': 'attack_event + VALUES(attack_event)',
            'access_rule': 'access_rule + VALUES(access_rule)',
            'update_time': stmt.inserted.update_time,
        })
        # print(stmt)
        session.execute(stmt)

我使用了on_duplicate_key_update，但出现了类似sqlalchemy.exc.IntegrityError:(MySQLdb._exceptions.IntegrityError) (1062，“ key 'inner_machine_id'的重复条目'7'”)的错误 .

    _mysql.connection.query(self, query)
sqlalchemy.exc.IntegrityError: (MySQLdb._exceptions.IntegrityError) (1062, "Duplicate entry '7' for key 'inner_machine_id'")
[SQL: INSERT INTO risk_inner_machine (inner_machine_id, is_risk, threat_access, risk_file, attack_event, access_rule, update_time) VALUES (%s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s)]
[parameters: (7, 0, 0, 0, 2, 0, 1558678678, 22, 0, 0, 0, 1, 0, 1558678678, 3, 0, 2, 0, 0, 0, 1558678678, 26, 0, 0, 0, 1, 0, 1558678678, 4, 0, 0, 0, 1, 0, 1558678678)]
(Background on this error at: http://sqlalche.me/e/gkpj)

即使我也无法使用print(stmt)来回显sql

  File "/Users/wyx/loh_workspace/loh-web/.env/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/wyx/loh_workspace/loh-web/.env/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/wyx/loh_workspace/loh-web/.env/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "/Users/wyx/loh_workspace/loh-web/.env/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 2400, in visit_insert
    "in-place multirow inserts." % self.dialect.name
sqlalchemy.exc.CompileError: The 'default' dialect with current database version settings does not support in-place multirow inserts.

<小时/>

感谢Ilja Everilä指出我代码中的错误。但问题无法解决

然后我简化并更新我的问题。

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT '0',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
)

class User(BaseModel):
    __tablename__ = 'user'
    id = Column(INTEGER(unsigned=True), primary_key=True)
    name = Column(VARCHAR(120))
    age = Column(INTEGER(unsigned=True))
    ts = Column(TIMESTAMP())


i = 11
data = dict(name=f'bulko{i}', age=i)
stmt = insert(User).values(data)
stmt = stmt.on_duplicate_key_update({
    'age': 'age + VALUES(age)',
})

session.execute(stmt)
session.commit()

出现错误

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1366, "Incorrect integer value: 'age + VALUES(age)' for column 'age' at row 1")
[SQL: INSERT INTO user (name, age) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age = %s]
[parameters: ('bulko11', 11, 'age + VALUES(age)')]

我运行INSERT INTO user (name,age) VALUES ('bulko11', 11) ON DUPLICATE KEY UPDATE Age = Age + VALUES(age); 在 mysql 中是有效的。我不知道 sqlalchemy 有什么问题。我是 sqlalchemy 新手。

Answer 1

我也遇到过这个问题，最后找到了解决办法。它并不优雅，但似乎很有效。只是问题简化尝试的一个小变化:

from sqlalchemy import text

...
stmt = stmt.on_duplicate_key_update(
    age=text("age+VALUES(age)"),
)