How to modify python script to append data on file using sql server 2019?

Python
, ,
1

I need to append data to excel file already exist from table students on SQL Server .

So I need to append data when dbo.fn_FileExists(@FullFilePath)=1

Excel file already Created on D:\ExportExcel\dbo.students.xlsx with student IDs 1 and 2.

student table with old data

studentid Name
1 ahmed
2 eslam

Table structure:

CREATE TABLE [dbo].[students](
[StudentId] [int] NOT NULL,
[Name] varchar NULL,
CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED
(
[StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (3, N’Sayed’)
INSERT [dbo].[students] ([StudentId], [Name]) VALUES (4, N’Michel’)
Python script used

DECLARE @PythonScript NVARCHAR(MAX) = N’’
declare @SQL NVARCHAR(MAX) = N’select studentid,Name from dbo.students;’
declare @ExportPath varchar(max)=‘D:\ExportExcel'
declare @TableName varchar(max)=‘dbo.students’
declare @FullFilePath varchar(max) = concat(@ExportPath,@TableName+’.xlsx’)
–IF File Not Exist
if(dbo.fn_FileExists(@FullFilePath)=0)
BEGIN
—print ‘Create File’
SET @PythonScript = N’
FullFilePath = ExcelFilePath+TableName+“.xlsx”
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(“.”)[-1],index=False)’

EXEC sp_execute_external_script
@language = N’Python’
,@script = @PythonScript
,@input_data_1 = @SQL
,@params = N’@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200)’
,@ExcelFilePath = @ExportPath – file path where Excel files are placed
,@TableName = @TableName
END
ELSE
BEGIN
—append data
—Here code i need to write to append data
print ‘Append data’
END
When use Python script it will not append data to Excel file from table students. Meaning it will not add student IDs 3 and 4 .

So How to append data from table students to excel file using Python script?

Expected result to file after append

after append data to excel

studentid Name
1 ahmed
2 eslam
3 Sayed
4 Michel

1 Like
2

This is probably not the best place for this question. This would be better asked on Python.org’s site or in a Python document/book.

3

Well, there are something wrong with your script, you can try below script to append data on file using SQL server.

-- ... (previous code)

ELSE
BEGIN
    -- Append data
    -- Here's the code to append data to the existing Excel file

    SET @PythonScript = N'
import pandas as pd

# Read the existing Excel file
existing_data = pd.read_excel(ExcelFilePath, sheet_name=TableName.split(".")[-1])

# Create a DataFrame from the SQL query result
new_data = pd.read_sql_query(InputDataSet, conn)

# Append the new data to the existing data
combined_data = existing_data.append(new_data, ignore_index=True)

# Write the combined data back to the Excel file
combined_data.to_excel(ExcelFilePath, sheet_name=TableName.split(".")[-1], index=False)
'

    EXEC sp_execute_external_script
        @language = N'Python',
        @script = @PythonScript,
        @input_data_1 = @SQL,
        @params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200)',
        @ExcelFilePath = @FullFilePath, -- Use the full file path here
        @TableName = @TableName
END

Thanks