我们在【重磅来袭】在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 ) )
就得到结果了:
还是那句感叹:
只不过,日期格式跟之前的并不太一致:
好在这并不是什么大问题,在SQL中设置一下datevalue字段的格式为date就可以搞定:
至于刷新时重复导入或者每日刷新多次的问题,大家结合上一篇文章自己就可以解决,无非就是用DELETE函数,这里就不再赘述了。
说到这里,我们再回过头来探讨一下Power BI和MySQL有没有可能也跳过Python这个“中间商”直接交易呢?
看图:
你说呢?
以下,后续文章预告:
今天我们讲的是PQ生成record列表,再逐个导入SQL中,那有没有办法将PQ中的table作为一个整体导入SQL中呢?
PowerQuery还为我们提供了其他方式,比如调用存储过程。
由于存储过程是SQL语言中很重要的一个内容,我们将用一整篇文章来详细说明,敬请期待。