各位小夥伴們,大家好,我是學謙,咱們又見面了。
《在Power BI 中使用Python》系列的前三篇文章我們分別講解了:
如何在Power BI中使用Python來獲取數據:
如何在Power BI中使用Python進行數據清洗:
如何在Power BI中使用Python進行可視化呈現:
今天我們繼續講解第四篇——PQ數據導出與寫回SQL
眾所周知,Power BI對於數據的輸出是有一定限制的,至少有以下兩點:
1.可視化對象導出CSV格式限制3萬行數據,這對於數據量動輒上百萬甚至上億的表來說是不可接受的;
2.而一直廣為詬病的powerquery數據困難的問題更是一時半會也得不到解決。
那應該怎麼辦呢?
第一個問題,推薦使用DAX Studio,輕鬆導出十萬、百萬條記錄;
第二個問題,沒有現成的工具可以直接解決,但是結合本系列第二篇的內容,我們是否可以想到如何用Python將powerquery中的表輸出為excel甚至實現數據回寫到SQL中呢?
這就是我們今天要學習的內容:
我們在第二講中說過:
Python的處理結果以Dataframe形式輸出,M將Dataframe自動轉換為Table格式。M將其Table類型的數據傳遞給Python,Python會自動將Table轉換為Dataframe。
M將其Table類型的數據傳遞給Python,Python會自動將Table轉換為Dataframe。那麼Python中Dataframe如何輸出呢?
想必瞭解pandas庫的戰友們已經想到答案了。
只要一行簡單的代碼:
<code>= Python.Execute("# 'dataset' 保留此腳本的輸入數據#(lf)dataset.to_excel(r""C:\\Users\\\\xxxxx\\Desktop\\abc.xlsx"")",[dataset=源])/<code>
簡單吧!
運行一下:
簡單吧!
運行一下:
OK啦!
關鍵是:
只有一行代碼!
只要一行代碼!
只需一行代碼!
重要的事情強調三遍!
多年來powerquery廣為人們詬病的——數據清洗後無法導出結果的問題就這麼被一行代碼輕鬆地解決,美滋滋。
好了,既然知道了如何導出excel文件,那麼各位,寫回MySQL數據庫的操作是否可以舉一反三自行解決呢?
我們直接看下圖的神操作:
看到了嗎,mysql數據庫中本來是一張空表,我們在powerquery中運行了一段Python代碼後,表中有了數據。
關鍵代碼解釋:
<code>db = pymysql.connect("localhost","用戶名","密碼","nc" )
#連接數據庫
query = 'insert into `全球疫情_country`(id,displayName,areas,totalConfirmed,totalDeaths,totalRecovered,lastUpdated,lat,long,parentId)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
#鍵入數據
for r in range(len(dataset)):
#按行獲取數據
id0=dataset.iat[r,0]
displayName=dataset.iat[r,1]
areas=dataset.iat[r,2]
……
values = (id0,displayName,areas,totalConfirmed,totalDeaths,totalRecovered,lastUpdated,lat,long,parentId)
cursor.execute(query, values)
cursor.close()
db.commit()
db.close()
#提交數據並關閉數據庫/<code>
代碼沒什麼難度,用的是Python的一個常用庫:pymysql,將dataset中的數據按行導入MySQL中。
但是有一個大BUG一點小問題:
因為全球只有200左右個國家和地區,country層面的數據應該只有200左右。但是,我習慣性地瞥了一眼MySQL右下角,發現:
難道最近的國際局勢變化這麼大,已經有567個國家和地區了?不可能吧。抓緊查詢一下,發現果然有問題:
全球每一個國家和地區的數據都顯示了三次,567/3=189,這還差不多。
而且清空表後再刷新運行,就會發現有的時候是2次,有的時候5次,這意思就是Python代碼運行了多次,造成了數據重複,這背後的原因我們無從得知。這樣可不行啊,總不能每次使用的時候先去重吧,不太現實,如果有時候疏忽了就麻煩了,那該怎麼辦呢?
這個問題先一放,我們來看另一個問題:
每個國家的每日數據我們只保留一次,即便powerquery每次刷新只向MySQL數據庫寫入一次,但我們也不能保證編寫模型的時候只刷新一次吧,因為一旦人工刷新多次,造成的結果和上面被動造成的結果一致,所以,只要我們解決了人工刷新造成數據重複的問題,查詢刷新時被動寫入多次的問題也就順帶解決了。
我們看一下數據,有一列“lastupdated”,是時間格式,也就是查詢的時間,由於我們只關心日期數據,因此只取出日期就可以。所以只要每次寫回MySQL之前,先判斷一下數據庫中是否已經存在當日的數據,如果有,就先刪除,再將新的數據寫入,這樣就達到我們的目的了。
添加以下代碼:
<code>#添加一列日期
dataset.insert(loc=10,column="updateday",value=dataset["lastUpdated"].str[0:10])
#獲取日期
today_date=dataset.iat[0,10]
#刪除當日的已有數據
query_delete='DELETE FROM `全球疫情_country` WHERE updateday='+today_date/<code>
運行一下代碼:
MySQL數據庫的表中初始有378條數據(因為包含了3月27日和3月28日兩天的數據,共189個國家和地區的數據),運行代碼後,仍然是378條,之前已有的3月28日的數據被刪除,然後添加了剛剛查詢到的最新數據。
完美解決!
好了,寫回MySQL數據庫的全部操作和遇到的問題與解決措施到這裡就講解完畢了。你學會了嗎?
寫這篇文章的時候不知道怎麼的,遠程計算機的MySQL數據庫總是出問題,導致我這邊文章前前後後寫了五六個小時。
本節內容細節的點特別多,大家一定要自己動手操作幾遍,後續我會逐步安排相關的視頻講解,大家請注意關注,跟上隊伍。
以下仍然是下期預告環節:
下一篇我們將繼續介紹一個重磅功能——數據條件觸發預警並郵件通知:
說到數據預警,微軟自家的Flow可以設置預警條件併發送郵件,這是原生功能,有興趣的朋友可以去了解。
感謝您對【學謙數據運營】的關注、支持與厚愛,如果本文對您有用,請不要吝惜您的點贊、轉發和點亮在看,有任何問題歡迎大家在留言區詢問,謝謝。
閱讀更多 學謙數據文化 的文章