I have this Python code that works fine when I run it from PyCharm.
But when I run the same code from SQL Server using sp_execute_external_script
, I get an error. Any idea what is going on here?
The Python code:
import pyodbc
from pandas import DataFrame, read_csv
import glob
def main():
fullFilePath = r"D:\raw files"
conn = pyodbc.connect(
"DRIVER={ODBC Driver 17 for SQL Server};"
"Server=DESKTOP-PCV7785;"
"Database=practice_db;"
"Trusted_Connection=yes;"
)
cursor = conn.cursor()
try:
cursor.execute("CREATE TABLE tbl_babyNames (babyName nvarchar(100), gender nvarchar(2), number int, fileName nvarchar(50))")
conn.commit()
except:
print("Table tbl_babyNames already exists")
files = 0
rows = 0
files_list = [f[len(fullFilePath)+1:] for f in glob.glob(fullFilePath + "\*.txt")]
for file in files_list:
data = read_csv(fullFilePath + "\\" + file, sep=",", header=None, index_col=None)
data.columns=["babyName", "gender", "number"]
df1 = DataFrame(data)
df1["fileName"] = file[:file.find(".txt")]
for row in df1.itertuples():
cursor.execute("""
INSERT INTO practice_db..tbl_babyNames(babyName, gender, number, fileName)
VALUES (?, ?, ?, ?)
""",
row.babyName,
row.gender,
row.number,
row.fileName)
rows += 1
conn.commit()
print("File : {} inserted".format(file))
files += 1
conn.close()
print("Insert is Done!, files: {0} , rows: {1}".format(files, rows))
main()
What I am running in SQL Server:
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pyodbc
from pandas import DataFrame, read_csv
import glob
def main():
fullFilePath = r"D:\raw files"
conn = pyodbc.connect(
"DRIVER={ODBC Driver 17 for SQL Server};"
"Server=DESKTOP-PCV7785;"
"Database=practice_db;"
"Trusted_Connection=yes;"
)
cursor = conn.cursor()
try:
cursor.execute("CREATE TABLE tbl_babyNames (babyName nvarchar(100), gender nvarchar(2), number int, fileName nvarchar(50))")
conn.commit()
except:
print("Table tbl_babyNames already exists")
files = 0
rows = 0
files_list = [f[len(fullFilePath)+1:] for f in glob.glob(fullFilePath + "\*.txt")]
for file in files_list:
data = read_csv(fullFilePath + "\\" + file, sep=",", header=None, index_col=None)
data.columns=["babyName", "gender", "number"]
df1 = DataFrame(data)
df1["fileName"] = file[:file.find(".txt")]
for row in df1.itertuples():
cursor.execute("""
INSERT INTO practice_db..tbl_babyNames(babyName, gender, number, fileName)
VALUES (?, ?, ?, ?)
""",
row.babyName,
row.gender,
row.number,
row.fileName)
rows += 1
conn.commit()
print("File : {} inserted".format(file))
files += 1
conn.close()
print("Insert is Done!, files: {0} , rows: {1}".format(files, rows))
main()
'
The error I get:
Msg 39004, Level 16, State 20, Line 10
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.Msg 39019, Level 16, State 2, Line 10
An external script error occurred:Error in execution. Check the output for more information.
Traceback (most recent call last):
File "", line 5, in
File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\BB9FCA34-F670-4555-8B58-B6A3C172718C\sqlindb_0.py", line 115, in transform
main() File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\BB9FCA34-F670-4555-8B58-B6A3C172718C\sqlindb_0.py", line 45, in main
"DRIVER={ODBC Driver 17 for SQL Server};"Msg 39019, Level 16, State 2, Line 10
An external script error occurred:pyodbc.OperationalError: ('08001', '[08001]
[Microsoft][ODBC Driver 17 for SQL Server] Named Pipes Provider: Could not open a connection to SQL Server [5]. (5) (SQLDriverConnect);
[08001] [Microsoft][ODBC Driver 17 for SQL Server] Login timeout expired (0);
[08001] [Microsoft][ODBC Driver 17 for SQL Server] A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (5)')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\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py",
line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params) RuntimeError: revoscalepy function failed.
User contributions licensed under CC BY-SA 3.0