0

I'm trying to upsert in SQL Server from python. Basically I have scraped a website, converted it to DF and I'm already inserting it in my DB.

What I need: When there is data different from the scraped like the item price for example, then update it, and if the id does not exist, then insert.

Follows my code:

for index, row in df.iterrows():
    cursor.execute("""INSERT INTO db_demo1.[dbo].[scrape]
                        (market, product_id, section_item, title_item, title_item_new, price_item,
                        qty, unit, sku, product_image, url, delivery_available,
                        delivery_long_distance, barcode, scrape_date) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                   row.market, row.product_id, row.section_item, row.title_item, row.title_item_new,
                   row.price_item, row.qty, row.unit, row.sku, row.product_image, row.url,
                   row.delivery_available, row.delivery_long_distance, row.barcode, row.scrape_date)
cnxn.commit()
cursor.close()

The data that I have is something like this: enter image description here

In this case, for example, it will check the product_id and see if the price_item or another column have changed, if it did, then it replaces the current value with the new one, and also update the scrape_date with the new date.

@Charlieface I tried some solutions and none of them worked.

First I tried the articles that you posted, I have to change some parameters because it was raising some errors, but the final output: ''' BEGIN TRANSACTION;

DECLARE @val as float 
DECLARE @pid as int
DECLARE @pk as int


SET IDENTITY_INSERT scrape ON
select * from scrape;
UPDATE dbo.scrape WITH (UPDLOCK, SERIALIZABLE) SET [price_item] = @val, [product_id] = @pid WHERE id = @pk;
GO

IF @@ROWCOUNT = 0
BEGIN
  
  INSERT INTO dbo.scrape([id], [product_id], [price_item]) VALUES(1, 1000, 17.5);
  SELECT * FROM scrape
 
END
 SET IDENTITY_INSERT scrape OFF;
COMMIT TRANSACTION;

''' ERRORS> Msg 1088, Level 16, State 11, Line 11 Cannot find the object "scrape" because it does not exist or you do not have permissions. Msg 208, Level 16, State 1, Line 19 Invalid object name 'dbo.scrape'.

I'm the ADM btw, dont get the permission stuff.

second one: '''

  INSERT INTO db_demo1.dbo.scrape(id, product_id, price_item) VALUES(1, 'X', 'X'); -- to be updated
    SELECT * FROM db_demo1.dbo.scrape;
    
    
    MERGE scrape trg
    USING (VALUES ('1','2','3'),
                  ('C','D','E'),
                  ('F','G','H'),
                  ('I','J','K')) src(id, product_id, price_item)
      ON trg.id = src.id
    WHEN MATCHED THEN
       UPDATE SET product_id = src.product_id, price_item = src.price_item
    WHEN NOT MATCHED THEN
       INSERT(id, product_id, price_item)
       VALUES(src.id, src.product_id, src.price_item);
    
    SELECT * FROM scrape;

''' Same error: Invalid object name 'db_demo1.dbo.scrape'.

Someone please can save me?

renan
  • 75
  • 7
  • Does this answer your question? [How to upsert (update or insert) in SQL Server 2005](https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005) – Charlieface Dec 08 '21 at 09:41
  • See also https://sqlperformance.com/2020/09/locking/upsert-anti-pattern. Although in this instance it's probably more efficient to upload the whole dataframe as a Table Valued parameter, or as JSON or XML. Then do a joined `insert` and `update`, or a `merge` – Charlieface Dec 08 '21 at 09:42

0 Answers0