Power bi数据回写sql server（1）没有中间商赚差价

Power BI数据回写SQL Server（1）没有中间商赚差价

我们在【重磅来袭】在Power BI 中使用Python（4）——PQ数据导出&写回SQL 讲过如何在Power BI中调用Python实现powerquery获取和处理的数据回写到MySQL中。

有不少朋友提问，能否回写到SQL SERVER中呢？

答案是肯定的。有两个大的解决方案：

第一个，由于本质上我们调用的是Python脚本，所以回写入哪个数据库由Python来决定。写入MySQL的库是pymysql，而如果要写入SQL SERVER我们需要更换一个库：

pip install pymssql

从名字上我们也能看出，这两个库的作者是同一个人，因此用法几乎完全一致。只不过在对待表名是中文时处理方式不太一样，MySQL需要在表名上加“表名”符号，SQL SERVER则不需要。

点击：转换-运行Python脚本，编辑代码，运行。

可以看到在运行Python脚本前，SQL数据库共378条数据，运行后是578条，增加了200条，这说明前几天只有189个国家和地区的数据，而今天更新有200个国家和地区的数据，这也直接说明病毒还在继续向更多国家蔓延，防控形势不容乐观。

*获取完整源代码，请关注【学谦数据运营】，回复关键字“powerbi-python-sqlserver” *

第二个办法，其实更简单一些，而且直接跳过了Python，因为Power BI和SQL Server都是微软家的拳头产品，自家人，肯定办事方便些。

我们先从SQL Server导入一张表到powerquery中：

点开高级编辑器：

let 源 = Sql.Database("DESKTOP-NLIOB2L\MSSQLSERVER1", "test1"), dbo_Sheet1 = 源{[Schema="dbo",Item="Sheet1"]}[Data] in dbo_Sheet1

将以上代码适当进行修改：

let 源 = Sql.Database("DESKTOP-NLIOB2L\MSSQLSERVER1", "test1",[Query="select * from Sheet1"]) in 源

点运行：

两种查询方式得到的结果完全一致。

但是修改后的代码意义却变了：

[Query="select * from Sheet1"]

这实现了在PowerQuery中直接输入SQL Server代码并运行：

这就代表着我们可以通过编写SQL语句向SQL Server插入数据了：

let Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content], ChangedType = Table.TransformColumnTypes(Source,\{\{"KeyValue", type text}, {"NumberValue", Int64.Type}, {"DateValue", type date}}), insert=Sql.Database("DESKTOP-NLIOB2L\MSSQLSERVER1", "test1",[Query="INSERT INTO Sheet1(KeyValue,NumberValue,DateValue)VALUES('A',3,'2019/1/1')"]) in insert

看一下运行过程：

可以看到原表中只有2017年的数据，运行后增加了5行2019/1/1的数据，查询一次却增加多行的原因我们在【重磅来袭】在Power BI 中使用Python（4）——PQ数据导出&写回SQL中也说过，尚未明确知晓什么原理，只能通过其他办法来处理，稍后再说。

当然我们也可以同时插入多行数据：

结果：

但是这样我们只能实现自己手动填写数据写入SQL语句去运行，而无法将PQ查询的结果写入SQL。

所以还得想别的办法。

我们再来试试Value.NativeQuery方法，是将一条record记录数据直接插入数据库中：

Value.NativeQuery(Sql.Database("DESKTOP-NLIOB2L\MSSQLSERVER1", "test1"), "INSERT INTO Sheet1 VALUES(@KeyValue,@NumberValue,@DateValue)", [KeyValue="NativeQuery",NumberValue=3,DateValue="2019/1/1"] )

运行结果：

测试没有问题。

那么重要的就来了：

如果我们能够将PQ返回的表按行转换为一条条的record记录，再逐条导入SQL Server，那么我们的需求就得到了解决。

第一步：使用Table.ToRecords函数将table转为record list：

第二步：我们再做一个循环，逐行读取这些records，并用Value.NativeQuery函数套在这些records上：

insert=List.Transform(records,(x)=>Value.NativeQuery( Sql.Database("DESKTOP-NLIOB2L\MSSQLSERVER1", "test1"), "INSERT INTO Sheet1 VALUES(@KeyValue,@NumberValue,@DateValue)", x ))

就得到结果了：