I'm confused about the syntax for SqlAlchemy's ON DUPLICATE KEY UPDATE.
I've tried to adapt the example in link below, also studying the documentation for insert specifically.
https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert
My code is the following:
class GivenName(Base):
# ORM table definition
__tablename__ = 'given_name'
given_company_name = Column(String(255), index=True, primary_key=True)
given_company_name_trimmed = Column(String(255), index=True)
given_org_number = Column(String(255))
given_clean_org_number = Column(BigInteger, index=True, primary_key=True)
# two rows to be inserted in json format
insertion_as_dict = [{'given_clean_org_number': 0.0,
'given_company_name': 'staby gÄrdshotell',
'given_company_name_trimmed': 'staby gardshotell',
'given_org_number': None},
{'given_clean_org_number': 5568978430.0,
'given_company_name': 'staccato etg ab',
'given_company_name_trimmed': 'staccato etg',
'given_org_number': 5568978430.0}]
# insertion statement adapted from the sqlalchemy example in link above
insert_stmt = insert(GivenName).values(insertion_as_dict)
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
values=insert_stmt.inserted
)
session = session_factory()
session.execute(on_duplicate_key_stmt)
session.close()
For some reason this produces the error:
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")
I don't know if I'm missing something in the description which only seems to demo a one record insertion. They explicitly set the key worded arguments id and data. Is this supposed to correspond to columns in the database or are these api keywords?