Py / Python網頁資料擷取與分析班-筆記 8/9 番外-寫入SQLite資料庫

Py / Python網頁資料擷取與分析班-筆記 8/9 番外-寫入SQLite資料庫

Post Views: 1,459

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

必須引用 sqlite3模組

首先建立資料庫連線，指定資料庫名稱，若不存在則自動產生

再來是在資料庫裡建立資料表，通常之後的資料都是用新增或更新

但是比較簡略的方式就是重新建立資料表

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

接著再重新產生資料表

備註：

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

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

最後是寫入SQLite資料庫

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
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

Tags: