Insert or update when importing from JSON

Question

My SQLAlchemy ORM model is populated by a JSON file that occasionally changes. The JSON file does not provide an integer primary key but has a unique alphanumeric ProductCode. My model:

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))
    ...

This answer makes sense until this line:

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

Because the incoming data lacks a key I have nothing to compare. Do I need to change the ProductCode definition? I am using Unique=True. My code:

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 an 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)

The answer you cited is for MySQL. Is that your database backend as well? — Gord Thompson
– Gord Thompson, Commented Jan 30 at 14:08
Have you checked the docs to see how SQLite differs from MySQL in this regard? — Gord Thompson
– Gord Thompson, Commented Jan 30 at 15:48

Gord Thompson · Accepted Answer · 2025-01-30 17:31:46Z

SQLite lets us specify the matching columns for ON CONFLICT, like so:

from sqlalchemy.dialects.sqlite import insert

new_values = json.loads("""\
[
  {"ProductCode": "code_1", "DisplayName": "display_1", "Description": "description_1"},
  {"ProductCode": "code_2", "DisplayName": "display_2", "Description": "description_2"}
]
""")
insert_stmt = insert(ProductDescriptor).values(new_values)
do_update_stmt = insert_stmt.on_conflict_do_update(
    index_elements=["ProductCode"],
    set_=dict(
        DisplayName=insert_stmt.excluded.DisplayName,
        Description=insert_stmt.excluded.Description,
    ),
)

engine.echo = True
with engine.begin() as conn:
    conn.execute(do_update_stmt)
"""
BEGIN (implicit)
INSERT INTO product_descriptor ("ProductCode", "DisplayName", "Description") VALUES (?, ?, ?), (?, ?, ?) ON CONFLICT ("ProductCode") DO UPDATE SET "DisplayName" = excluded."DisplayName", "Description" = excluded."Description"
[no key 0.00093s] ('code_1', 'display_1', 'description_1', 'code_2', 'display_2', 'description_2')
COMMIT
"""

Note that if "ProductCode" is a unique non-nullable column then it is in fact a natural primary key, so the autoincrement integer "id" column is not really necessary

Collectives™ on Stack Overflow

Insert or update when importing from JSON

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related