2022年8月27日星期六

9 番外-寫入SQLite資料庫

8月 27, 2022

嘗試將政府公開資訊-每日外幣參考匯率的資料寫入SQLite資料庫

必須引用 sqlite3模組
首先建立資料庫連線，指定資料庫名稱，若不存在則自動產生
再來是在資料庫裡建立資料表，通常之後的資料都是用新增或更新
但是比較簡略的方式就是重新建立資料表

因此在這邊用了一個語法”DROP TABLE IF EXISTS XXX” 若存在資料表XXX則刪除

接著再重新產生資料表

備註：

cursor 物件的execute( )方法，主要功能是執行SQL 陳述式 (SQL statementstatement) 運作SQL資料庫的相關功能

再來是透過 requests模組取得資料，分割處理資料

最後是寫入SQLite資料庫

import sqlite3
conn = sqlite3.connect("0809-5-sqlite.db") # 建立資料庫連線
cursor = conn.cursor() # 建立 cursor 物件
# 建立一個資料表
sqlstr="DROP TABLE IF EXISTS taichung" #如果存在taichung資料表就刪除
cursor.execute(sqlstr)

sqlstr="CREATE TABLE IF NOT EXISTS taichung ('編號' 	INTEGER ,'案類' TEXT,'發生時間' TEXT , '發生地點' TEXT)"
cursor.execute(sqlstr)

import requests
html = requests.get("https://datacenter.taichung.gov.tw/swagger/OpenData/e62d8d47-f9be-49a2-bb29-21ba4bf7cd75") #資料來源：https://data.gov.tw/dataset/11339
html.encoding="utf-8-sig" #多了\ufeff "utf-8" --> "utf-8-sig"   
#print(html.text)

#切割換行
list1=html.text.split("\r\n")
#print(len(list1))  # 239
#print(list1[0])

#print(list1[0].split(",")) #['編號', '案類', '發生時間', '發生地點']

for i in range(1,len(list1)-1):
    list2=list1[i].split(",")
    list2[3]=list2[3].strip(" ")
    d1=list2[0]
    d2=list2[1]
    d3=list2[2]
    d4=list2[3]
    print(d1+d2+d3+d4)
    sqlstr="insert into taichung values({},'{}','{}','{}')".format(int(d1),d2,d3,d4)
    cursor.execute(sqlstr)  
    
results = cursor.execute("SELECT * FROM taichung where 編號>235")
for item in results:
    print(item)
conn.commit() # 主動更新
conn.close()  # 關閉資料庫連

execute( ) 如果只有一個參數，就必須是完整的SQL 陳述式

sqlstr="insert into taichung values({},'{}','{}','{}')".format(int(d1),d2,d3,d4) 
cursor.execute(sqlstr)

但也可以輸入兩個參數，在陳述式中使用問號佔位符，然後在第二個參數輸入相對應的資料

sqlstr="insert into taichung values(?,?,?,?)"
cursor.execute(sqlstr,(d1,d2,d3,d4))

execute( )只能一次輸入一個SQL 陳述式，也就是一次只能輸入一筆資料

如果有大量資料要寫入的話，可以使用 executemany( )

原本的迴圈就可以改成將資料存成串列

data=[]
for i in range(1,len(list1)-1):
  list2=list1[i].split(",")
  list2[3]=list2[3].strip(" ")
  d1=list2[0]
  d2=list2[1]
  d3=list2[2]
  d4=list2[3]
  print(d1+d2+d3+d4)
  data.append([d1,d2,d3,d4])

然後，透過executemany()一次寫入

sqlstr="insert into taichung values(?,?,?,?)"
cursor.executemany(sqlstr,data)

備註：

executemany()，第二個參數，必須是可以疊代的序列物件，例如：串列、元組、字串、二進位文件

要查看資料庫的內容，除了使用SQLiteDatabaseBrowser查看之外

也可以使用以下語法，將資料寫出在控制台

輸出的資料型態是元組 <class ‘tuple’>

results = cursor.execute("SELECT * FROM taichung where 編號>235") 
for item in results: 
  print(item)

也可以使用fetchone()，取出第一筆資料

cursor.execute(“SELECT * FROM taichung”)
results=cursor.fetchone()
print(results)

或者fetchall()，取出所有資料

cursor.execute("SELECT * FROM taichung")
results=cursor.fetchall()
print(results)

或者fetchmany(N)，取出前N筆資料

cursor.execute("SELECT * FROM taichung")
results=cursor.fetchmany(2)
print(results)

參考資料

https://docs.python.org/zh-tw/3/glossary.html#term-iterator
https://docs.python.org/zh-tw/3/library/stdtypes.html#iterator-types
https://docs.python.org/zh-tw/3/library/stdtypes.html#sequence-types-list-tuple-range
https://docs.python.org/zh-tw/3/glossary.html#term-sequence

繼續閱讀

Py / Python網頁資料擷取與分析班-筆記 8/9

8月 27, 2022

最近去自強工業科學基金會上「Python網頁資料擷取與分析班」

這個是政府有補助的職訓課程，講師是吳清輝老師
如果全程參與，在課程結束之後會補助/退費 80%
也就是自己負擔 20%，感覺蠻划算的
總共有10週/次的課程
課程內容跟之前在東吳上的課程相比較，算是進階課程
也就是直接從python抓取資料開始
課程進度／內容
1.資料處理能力，CSV與JSON讀取與寫入
2.資料處理能力，XML與SQLite資料庫讀取與寫入
3.網頁資料擷取與轉換Python存取網站方式
4-1.網頁資料擷取
4-2.轉換BeautifulSoup模組
5-1.資料分析能力
5-2.NumPy模組
6.資料分析能力Pandas模組
7.資料視覺化能力各種圖表之呈現
8.資料視覺化能力圖表繪製其他技巧
現在資料科學算是很熱門的議題
可以處理資料的方式很多種，python是其中一種
想說有人帶入門是比較快的學習方式
之後會把每週的筆記整理PO上來

第一次上課的主要內容

_用requests下載資料臺中市住宅竊盜資訊
_用檔案物件儲存為CSV檔案
_用pandas產生DataFrame與輸出Excel檔
使用的資料是來自政府公開資訊網
所以直接用requests模組就可以抓取資料
可以透過file模組存成csv 或 txt檔等文字檔或者二進制文件

備註：

1.取得資料的時候要注意資料的檔案格式、編碼格式，存檔的時候也要儲存為相同的格式

2.如果要轉存成其他形式的檔案，還要注意資料的欄位

下面的程式碼就是直接取得政府開放資料的csv檔，並且儲存成相同格式與編碼的檔案

資料來源：臺中市住宅竊盜資訊

import requests
import pandas as pd

html = requests.get("https://datacenter.taichung.gov.tw/swagger/OpenData/e62d8d47-f9be-49a2-bb29-21ba4bf7cd75")
html.encoding="utf-8-sig" #多了\ufeff "utf-8" --> "utf-8-sig"   
#print(html.text)

#切割換行
list1=html.text.split("\r\n")
#print(len(list1))  # 239
#print(list1[0])

#print(list1[0].split(",")) #['編號', '案類', '發生時間', '發生地點']

cols = list1[0].split(",")
data= []
for i in range(1,len(list1)-1):
    list2=list1[i].split(",")
    list2[3]=list2[3].strip(" ")    
    data.append(list2)
#print(data)
index=[]
for i in range(1,len(list1)-1):     
    index.append(i)
#print(index)

df = pd.DataFrame(data,index,cols)
print(df)
df.to_excel("住宅竊盜_0809.xlsx")

備註：

html.encoding="utf-8-sig" #多了\ufeff "utf-8" --> "utf-8-sig"

因為檔案是用uft-8-sig編碼，文件會有 BOM 訊息，輸出的時候第一列前面會有\ufeff

不過有時候又會沒有，不知道是不是檔案更新的關係

轉存的結果

在spyder的控制台也可以看到輸出

參考資料

Python File(文件) 方法
open()
file-object
二进制文件
纯文本文件
Python CSV — \ufeff 問題
utf-8 與 utf-8-sig 有什麼區別？

繼續閱讀

2022年8月27日 星期六

嘗試將政府公開資訊-每日外幣參考匯率的資料寫入SQLite資料庫

必須引用 sqlite3模組首先建立資料庫連線，指定資料庫名稱，若不存在則自動產生再來是在資料庫裡建立資料表，通常之後的資料都是用新增或更新但是比較簡略的方式就是重新建立資料表

因此在這邊用了一個語法”DROP TABLE IF EXISTS XXX” 若存在資料表XXX則刪除

接著再重新產生資料表

備註：

cursor 物件的execute( )方法，主要功能是執行SQL 陳述式 (SQL statementstatement) 運作SQL資料庫的相關功能

execute( ) 如果只有一個參數，就必須是完整的SQL 陳述式

execute( )只能一次輸入一個SQL 陳述式，也就是一次只能輸入一筆資料

如果有大量資料要寫入的話，可以使用 executemany( )

備註：

executemany()，第二個參數，必須是可以疊代的序列物件，例如：串列、元組、字串、二進位文件

要查看資料庫的內容，除了使用SQLiteDatabaseBrowser查看之外

也可以使用以下語法，將資料寫出在控制台

輸出的資料型態是元組 <class ‘tuple’>results = cursor.execute("SELECT * FROM taichung where 編號>235") for item in results: print(item)

也可以使用fetchone()，取出第一筆資料

cursor.execute(“SELECT * FROM taichung”)results=cursor.fetchone()print(results)

或者fetchall()，取出所有資料

cursor.execute("SELECT * FROM taichung") results=cursor.fetchall() print(results)

或者fetchmany(N)，取出前N筆資料

cursor.execute("SELECT * FROM taichung") results=cursor.fetchmany(2) print(results)

參考資料

https://docs.python.org/zh-tw/3/glossary.html#term-iteratorhttps://docs.python.org/zh-tw/3/library/stdtypes.html#iterator-typeshttps://docs.python.org/zh-tw/3/library/stdtypes.html#sequence-types-list-tuple-rangehttps://docs.python.org/zh-tw/3/glossary.html#term-sequence

最近去自強工業科學基金會上「Python網頁資料擷取與分析班」

第一次上課的主要內容

_用requests下載資料臺中市住宅竊盜資訊_用檔案物件儲存為CSV檔案_用pandas產生DataFrame與輸出Excel檔使用的資料是來自政府公開資訊網所以直接用requests模組就可以抓取資料可以透過file模組存成csv 或 txt檔等文字檔 或者二進制文件

備註：

1.取得資料的時候要注意資料的檔案格式、編碼格式，存檔的時候也要儲存為相同的格式

2.如果要轉存成其他形式的檔案，還要注意資料的欄位

下面的程式碼就是直接取得政府開放資料的csv檔，並且儲存成相同格式與編碼的檔案

備註：

html.encoding="utf-8-sig" #多了\ufeff "utf-8" --> "utf-8-sig"因為檔案是用uft-8-sig編碼，文件會有 BOM 訊息，輸出的時候第一列前面會有\ufeff不過有時候又會沒有，不知道是不是檔案更新的關係

轉存的結果

在spyder的控制台也可以看到輸出

參考資料

Python File(文件) 方法open()file-object二进制文件纯文本文件Python CSV — \ufeff 問題utf-8 與 utf-8-sig 有什麼區別？

Select Language

文章回應

熱門文章

近期文章

文章行事曆

文章分類

瀏覽量

追蹤者

關於莊生

Facebook粉絲頁

隱私權

訂閱

標籤雲

Blog Archive

2022年8月27日星期六

必須引用 sqlite3模組
首先建立資料庫連線，指定資料庫名稱，若不存在則自動產生
再來是在資料庫裡建立資料表，通常之後的資料都是用新增或更新
但是比較簡略的方式就是重新建立資料表

輸出的資料型態是元組 <class ‘tuple’>
results = cursor.execute("SELECT * FROM taichung where 編號>235") for item in results: print(item)

cursor.execute(“SELECT * FROM taichung”)
results=cursor.fetchone()
print(results)

https://docs.python.org/zh-tw/3/glossary.html#term-iterator
https://docs.python.org/zh-tw/3/library/stdtypes.html#iterator-types
https://docs.python.org/zh-tw/3/library/stdtypes.html#sequence-types-list-tuple-range
https://docs.python.org/zh-tw/3/glossary.html#term-sequence

html.encoding="utf-8-sig" #多了\ufeff "utf-8" --> "utf-8-sig"
因為檔案是用uft-8-sig編碼，文件會有 BOM 訊息，輸出的時候第一列前面會有\ufeff
不過有時候又會沒有，不知道是不是檔案更新的關係

Python File(文件) 方法
open()
file-object
二进制文件
纯文本文件
Python CSV — \ufeff 問題
utf-8 與 utf-8-sig 有什麼區別？