Click here to Skip to main content
15,667,355 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I work on sql server 2017 i run python script from sql server

this script export data from sql server table to excel file

but i get error when add

shutil.copy(@FixedPath,@ExportPath)


(1 row affected)
Msg 39004, Level 16, State 20, Line 4
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 4
An external script error occurred: 

Error in execution.  Check the output for more information.
Traceback (most recent call last):
  File "<string>", line 3, in <module>
  File "C:\SQL-AHMEDSALAHSQL-ExtensibilityData-PY\AHMEDSALAHSQL01\DFBE7FD1-2FB2-4FAC-BA3B-DA9EB2FEE613\sqlindb.py", line 41
    shutil.copy(@FixedPath,@ExportPath)
                ^
SyntaxError: invalid syntax

SqlSatelliteCall error: Error in execution.  Check the output for more information.
STDOUT message(s) from external script: 
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 587, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 358, in rx_native_call
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.


Completion time: 2022-04-30T05:07:14.7548215+02:00


so how to solve error ?

What I have tried:

declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
insert into #FinalExportList(TableName,Cols)
values
('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')

DECLARE
@TableName NVARCHAR(200)
,@SQL NVARCHAR(MAX) = N''
,@PythonScript NVARCHAR(MAX) = N''
,@ExportFilePath NVARCHAR(MAX) = N''


SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END


-- Just for testing purpose top 10 records are selected
SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
,@TableName = TableName
FROM #FinalExportList


SET @PythonScript = N'import shutil
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
shutil.copy(@FixedPath,@ExportPath)'

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
Posted
Updated 29-Apr-22 22:42pm
Comments
Richard MacCutchan 30-Apr-22 3:44am    
The message is clear, you are passing SQL variable names to the Python script, instead of strings containing the path names.

1 solution

If you reduce your query to this:
SQL
DECLARE @ExportPath NVARCHAR(MAX)='DEST'
DECLARE @FixedPath NVARCHAR(MAX)='SOURCE'
DECLARE @PythonScript NVARCHAR(MAX) = N''
SET @PythonScript = N'shutil.copy(@FixedPath,@ExportPath)'
print @PythonScript
You will get this string as the results:
shutil.copy(@FixedPath,@ExportPath)

Which contains the names of SQL variables instead of the content.

Change it to this:
SQL
DECLARE @ExportPath NVARCHAR(MAX)='DEST'
DECLARE @FixedPath NVARCHAR(MAX)='SOURCE'
DECLARE @PythonScript NVARCHAR(MAX) = N''
SET @PythonScript = N'shutil.copy(' + @FixedPath + ',' + @ExportPath + ')'
print @PythonScript
And you get what you wanted:
shutil.copy(SOURCE,DEST)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900