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

[ahmedbarbary]

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

after append data to excel

studentid Name
1 ahmed
2 eslam
3 Sayed
4 Michel

Larz60+ write Aug-03-2022, 10:56 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
fixed this time.

[Pedroski55]

Why don't you just export the table as a csv and convert to XL?

If not, you can get all the studentids and names like this, it is very fast.

import pymysql

def mysqlFetch(table_name): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123',
        # listening on the default port, no need to set the server access port
        #port=22000,
        user='me',  
        password = 'me', 
        db='allstudentsdb', 
        ) 
      
    cur = conn.cursor()

    # Select query     
    sql = f"SELECT studentid, name FROM {table_name}"
    cur.execute(sql)
    output = cur.fetchall()      
    #for i in output: 
        #print(i) 
      
    # To close the connection 
    conn.close()
    return output

# this will return a tuple of tuples
# each inner tuple will be (studentid, name)
my_students = mysqlFetch(table_name)
# make a dictionary of student numbers and names
# it is very convenient to put data from a dictionary into an XL file
namesnums_dict = {}
for a in my_students:
    # make the studinr a string
    key = str(a[0])
    namesnums_dict[key] = a[1]

print('namesnums_dict{} is now ' + str(len(namesnums_dict)) + ' long')
for item in namesnums_dict.items():
    print('data is', item)

# it is easy to put the names in XL from here
# if you don't know how, ask another question