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

[ahmedsa.aziz.ba]

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.

studentid	Name
1	ahmed
2	eslam

Table structure:

CREATE TABLE [dbo].[students](
    [StudentId] [int] NOT NULL,
    [Name] [varchar](50) 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

studentid	Name
1	ahmed
2	eslam
3	Sayed
4	Michel

[Calcio365]

I’d love to help, but that doesn’t look like a python script. With the way you are declaring variables, it looks like you’re maybe coming from a different programming language? In python you don’t declare, set, then execute your variables as python is a dynamically typed language.

Earlier today, I was doing the opposite of what you are trying to do and was taking information from an excel spreadsheet and putting it into a sql database. I don’t know what sql database manager you are using, so the details will vary a little based on what you’re using. But for sqlite3, you would want to do something like this to get the information from your database:

import sqlite3

conn = sqlite3.connect('your_database_file.db')
cur = conn.cursor()

query = """SELECT * FROM [dbo].[students]"""

cur.execute(query)
results = cur.fetchall()
conn.close()

Obviously the specifics will vary depending on your specific database management system, but I hope that gives you a gist of what you’d be looking for with a python script. After retrieving your data from the database, you will want to look into the openpyxl module to help you put those results into an excel spreadsheet.