SqlAlchemy ON DUPLICATE KEY UPDATE for bulk upsert

Viewed 878

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?

0 Answers
Related