SQL Server how to UPDATE if exists else INSERT by Python

Question

I've searched many articles in stack overflow but not able to find one meeting my requirement. Below is what I'm referring to.

UPDATE if exists else INSERT in SQL Server 2008

My requirement Using Python to create an ETL procedure: extract data from a source, transform to a dataframe then upload to MS SQL Server. If a duplicate row with same product_id is found, then UPDATE SQL database otherwise INSERT a new row.

My Code

import pandas as pd
import pyodbc

# Import CSV
data = pd.read_csv(r'C:\Users\...\product.csv')
df = pd.DataFrame(data)
records = df.values.tolist()

# Connect to SQL Server
server = 'AAABBBCCC\SQLEXPRESS' 
database = 'abcdefg' 
username = 'User' 
password = '' 
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';Trusted_Connection=True;')
cursor = cnxn.cursor()

#Insert Data
sql_insert = '''
    UPDATE [atetestdb].[dbo].[products] 
    SET product_name = ?
    ,price = ?
    WHERE product_id = ?

    IF @@ROWCOUNT = 0
        INSERT INTO [atetestdb].[dbo].[products] 
            (product_id, product_name, price)
        VALUES (?, ?, ?)
'''

cursor.executemany(sql_insert, records)

cnxn.commit()

My Result The error message I got:

The SQL contains 6 parameter markers, but 3 parameters were supplied

It seems something wrong with the SQL comment. Could someone help? Any other solution to meet my requirement is also appreciated.

My Dataset in SQL database

product_id	product_name	price
1	Desk	1900
2	Printer	200
3	Tablet	350
4	Keyboard	80
5	Monitor	1200
6	Desk	900

My Dataset in csv file

product_id	product_name	price
5	Monitor	600

Thanks for the reminder. I modified my table image to text.

Kelvin Lo
– Kelvin Lo

2022-01-25 03:25:20 +00:00
Commented Jan 25, 2022 at 3:25 — Kelvin Lo
– Kelvin Lo, Commented Jan 25, 2022 at 3:25

Kelvin Lo · Accepted Answer · 2022-01-25 12:53:29Z

4

Change the batch to have local variables for each parameter, eg:

sql_insert = '''
    declare @product_id int = ?
    declare @product_name varchar(100) = ?
    declare @price decimal(10,2) = ?
        
    UPDATE [atetestdb].[dbo].[products] 
    SET product_name = @product_name, price = @price
    WHERE product_id = @product_id

    IF @@ROWCOUNT = 0
        INSERT INTO [atetestdb].[dbo].[products] 
            (product_id, product_name, price)
        VALUES (@product_id, @product_name, @price)
'''

edited Jan 25, 2022 at 12:53

Kelvin Lo

1993 silver badges11 bronze badges

answered Jan 25, 2022 at 2:53

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kelvin Lo Over a year ago

Thank you, it works! I found the order of declare statement has to match my table. So, I have to swap the product_id to first row.

Kelvin Lo · Accepted Answer · 2022-01-25 07:06:43Z

3

You can go for simple MERGE statement.

MERGE [atetestdb].[dbo].[products] as t
USING (Values(?,?,?)) AS s(product_id, product_name, price)
ON t.product_id = s.product_id 
WHEN MATCHED THEN UPDATE SET 
         t.product_name = s.product_name
        ,t.price = s.price
WHEN NOT MATCHED BY TARGET 
     THEN INSERT (product_id, product_name, price)
          VALUES (s.product_id, s.product_name, s.price);

edited Jan 25, 2022 at 7:06

Kelvin Lo

1993 silver badges11 bronze badges

answered Jan 25, 2022 at 3:18

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

4 Comments

Kelvin Lo Over a year ago

Thanks! But it pops out the error message "Incorrect syntax near the keyword 'Values'".

Venkataraman R Over a year ago

@KelvinLo, sorry. I have updated my answer. it should work

Kelvin Lo Over a year ago

Thank you @Venkataraman R Your code also works! I corrected some variables and adjusted column order to make it work.

Venkataraman R Over a year ago

@KelvinLo, Glad it helped. you are welcome.

Collectives™ on Stack Overflow

SQL Server how to UPDATE if exists else INSERT by Python

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related