发布时间:2019-05-20编辑:佚名阅读(1823)
--启用扩展脚本,重启服务 exec sp_configure 'external scripts enabled',1 reconfigure go --简单实例 EXEC sp_execute_external_script @language = N'Python', @script = N'print(1+3,"kk")' go
SQL Server 数据传递到 Python 中(默认为 pandas 类型)
--默认输入的数据集变量:InputDataSet execute sp_execute_external_script @language = N'Python', @script = N' print(InputDataSet) print(type(InputDataSet)) ',@input_data_1 = N'SELECT database_id,name FROM sys.databases' go
来自外部脚本的 STDOUT 消息:
database_id name
0 1 master
1 2 tempdb
2 3 model
3 4 msdb
<class 'pandas.core.frame.DataFrame'>
--自定义输入的数据集变量,如:dbname execute sp_execute_external_script @language = N'Python', @script = N' print(dbname) print(type(dbname)) ',@input_data_1 = N'SELECT database_id,name FROM sys.databases' ,@input_data_1_name = N'dbname' go
来自外部脚本的 STDOUT 消息:
database_id name
0 1 master
1 2 tempdb
2 3 model
3 4 msdb
<class 'pandas.core.frame.DataFrame'>
Python 数据传递到 SQL Server 中 (读取外部excel示例)
--以文本格式打印显示 execute sp_execute_external_script @language = N'Python', @script = N' import pandas as pd xl = pd.ExcelFile("D:/Database/data.xlsx") df = xl.parse(xl.sheet_names[0]) print(df) print(df.dtypes)' go
来自外部脚本的 STDOUT 消息:
id name createdate
0 1 地方 2017-10-04
1 2 复活节 2017-10-25
2 3 瑞特 2017-11-15
3 4 健康良好 2017-12-06
4 5 是否 2017-12-27
id int64
name object
createdate datetime64[ns]
dtype: object
sql server 表数据通过python导出excel:
execute sp_execute_external_script @language = N'Python', @script = N' import pandas as pd writer = pd.ExcelWriter("E:/00000/sample.xlsx" ) InputDataSet.to_excel(writer,"Sheet1", index=False) writer.save() ',@input_data_1 = N'SELECT database_id,name FROM sys.databases' go
Python 和 SQL 数据类型对应
-- 以网格显示 (Python 字段 createdate 类型 datetime64[ns] 先转换为 object )
execute sp_execute_external_script @language = N'Python', @script = N' import pandas as pd xl = pd.ExcelFile("D:/Database/data.xlsx") df = xl.parse(xl.sheet_names[0]) df["createdate"] = df["createdate"].dt.strftime("%m/%d/%Y") print(df.dtypes) ',@output_data_1_name = N'df' WITH RESULT SETS (("id" INT null,"name" VARCHAR(20) null,"createdate" DATE null)) go
id name createdate
1 地方 2017-10-04
2 复活节 2017-10-25
3 瑞特 2017-11-15
4 健康良好 2017-12-06
5 是否 2017-12-27
Python 字段 createdate 类型 datetime64[ns] 先转换为 object,否则报错如下:
消息 39004,级别 16,状态 20,第 53 行
执行 "sp_execute_external_script" 时发生了“Python”脚本错误(HRESULT 0x80004004)。
消息 39019,级别 16,状态 2,第 53 行
发生外部脚本错误:
Invalid BXL stream SqlSatelliteCall function failed. Please see the console output for more information. Traceback (most recent call last): File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall", params) File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call ret = px_call(functionname, params) RuntimeError: The type numpy.ndarray(numpy.datetime) for createdate is not supported.
保存到表 (excel数据导入数据库,现在的方法方便多了,不会再出奇奇怪怪的问题了)
--保存到表 (excel数据导入数据库,现在的方法方便多了,不会再出奇奇怪怪的问题了) create table #test("id" INT null,"name" VARCHAR(20) null,"createdate" DATE null) go insert into #test execute sp_execute_external_script @language = N'Python', @script = N' import pandas as pd xl = pd.ExcelFile("D:/Database/data.xlsx") df = xl.parse(xl.sheet_names[0]) df["createdate"] = df["createdate"].dt.strftime("%m/%d/%Y") ',@output_data_1_name = N'df' go select * from #test go --drop table #test
Python 与 SQL Server 参数传递交互
--SQL Server 传递变量到 Python execute sp_execute_external_script @language = N'Python' ,@script = N'print(par01,par02)' ,@params = N'@par01 int,@par02 varchar(20)' ,@par01 = 99999 ,@par02 = 'kk' go
来自外部脚本的 STDOUT 消息:
99999 kk
--Python 输出变量到 SQL Server declare @id int,@name varchar(20) execute sp_execute_external_script @language = N'Python' ,@script = N'par01,par02 = 99,"kkkkk"' ,@params = N'@par01 int OUTPUT,@par02 varchar(20) OUTPUT' ,@par01 = @id OUTPUT ,@par02 = @name OUTPUT select @id,@name go
(无列名) (无列名)
99 kkkkk
其他:
--运行外部脚本的每个活动工作线程帐户(当前仅支持 R) SELECT * FROM sys.dm_external_script_requests; --每种类型的外部脚本请求返回一行 SELECT * FROM sys.dm_external_script_execution_stats;
关键字: SQL Server 2017 Python 交互
0人
0人
0人
0人