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.