0

I am having excel file which updates every day. I am taking the excel file and uploading into SQL server using Python. My code as follow,


import pandas as pd
import pyodbc

# Connecting SQL Server
server = 'BIA-BI' 
database = 'AT_new' 
username = 'xxx' 
password = 'xxx' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

df = pd.read_excel("data.xlsx")

#I have already created table in SQL Server

for row in df.itertuples():
    cursor.execute('''
                INSERT INTO AT_new.dbo.Dispatch_Detail(Name,Day,Value)
                VALUES (?,?,?)
                ''',
                row.Name,
                row.Day,
                row.Value                   
                   
               
                )
cnxn.commit()


The above code is working fine. I can able to push the data from excel into SQL server. My excel file is updating every day and when I run the above every day, the SQL server generate duplicates. Every day 10 to 20 new records are adding in the excel.

Example : Today's my excel file record is 100 and tomorrow it become 125 records (25 new records). When I run the python code, sql server has 225 records. It gets duplicated previous records as well.

I don't know how to remove the duplicate rrecordsin Python for the SQL server table. Whenever I run the python script, it should not generate the duplicates.

Can anyone advise me? I tried other posts but no success.

sthambi
  • 89
  • 7
  • SQL-intensive solution uses "WHERE NOT EXISTS", here is an example https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exists – Ruperto Oct 01 '21 at 00:29

0 Answers0