[Python] SQLAlchemy ORM Insert or Update when importing from JSON

I have a SQLAlchemy ORM Model populated by a JSON file that occasionally changes. The JSON file does not provide a integer primary key but has a unique alphanumeric ProductCode.

My model looks like this:

class ProductDescriptor(Base):
__tablename__ = 'product_descriptor'

id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
ProductCode: Mapped[str] = mapped_column(String(50), unique=True)

DisplayName: Mapped[str] = mapped_column(String(50))
Description: Mapped[str] = mapped_column(String(1000))
... more columns.


I have been looking at the answer provided in SQLAlchemy insert or update (upsert) using ORM session which makes sense until this line:

on_duplicate_stmt = insert_stmt.on_duplicate_key_update(dict(txt=insert_stmt.inserted.txt))


which because the incoming data lacks a key I have nothing to compare. Do I need to change the ProductCode definition? At the moment I am using Unique=True.

Currently my code looks like:

product_list = []
for product in products:

# Create filtered list of dicts to be send to the DB
product_list.append({
'ProductName': product.get('ProductName'),
'DisplayName': product.get('DisplayName'),
'Description': product.get('Description'),
... more columns
})

insert_stmt = insert(ProductDescriptor).values(product_list)

# This is where it goes wrong
on_duplicate_stmt = insert_stmt.on_duplicate_key_update()

# Trying to deal with integrity errors and rollback requests
for product in product_list:
try:
self.session.add(resource)
self.session.commit()
except IntegrityError:
pass

• How can I efficiently create a update or create function for bulk records?
• Do I need to turn my unique field into a key field?
• Can I keep an autoincrement key field as well?

In Django I would be using the update_or_create method where I can specify the key field and provide a defaults dictionary.

Profile.objects.update_or_create(custid=user_profile.custid, defaults=defaults)

Continue reading...