CODEWITHSUNDEEP
pythonsql-server
Kelvin Lo
Kelvin Lo

Reputation: 199

SQL Server how to UPDATE if exists else INSERT by Python

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

Upvotes: 1

Views: 2658

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

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

Upvotes: 4

Venkataraman R
Venkataraman R

Reputation: 12959

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

Upvotes: 3

Related Questions