アメグラ2号のブログ

1980年代後半の古き良きビデオゲームのほか、オッサンの個人的備忘録

python(sqlite3) WEBスクレイピング~DB記録 

f:id:game-allergy:20210331143049p:plain

前回はWEBスクレイピングができたので、取得した情報をデータベースに格納してみる。なんだかんだで、データベースへの出し入れは必須だと思うのでいまのうちに整理しておきたい。

流れは、

    DB作成
    テーブル作成
    WEBスクレイピング
    DBへ格納

最後にDB内のデータ確認、、、こんなところか。

◆DB作成

#===============================
# DBの作成
#===============================
import sqlite3

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)
connection.close()

print('--------------------')
print('end of line')
input()

◆テーブル作成

#===============================
# DB内のテーブル作成
#===============================
import sqlite3

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)

cur = connection.cursor()
sql = 'CREATE TABLE WeatherTable(id INTEGER PRIMARY KEY AUTOINCREMENT, hour STRING, weather STRING, temp DOUBLE, windspeed INTEGER, windblow STRING)'
cur.execute(sql)

connection.commit()
connection.close()

print('--------------------')
print('end of line')
input()

"""
id INTEGER PRIMARY KEY AUTOINCREMENT,
hour STRING,
weather STRING,
temp DOUBLE,
windspeed INTEGER,
windblow STRING
"""

データベースに登録するテーブルは、6個のカラムに。Id, hour, weather, temp, windblow, windspeedで、それぞれTenki.jpから取得する情報をもとに型を設定。例えばhourは時刻だけど、「01」「02」「03」…と毎時刻のことだけを表現していたのでこれは単なる文字列だろってことでstringに。
id はプライマリキーにしてオートインクリメントにしているのは、意識せずに数字が自動的に繰り上がってくれて番号をふってくれるので楽ちんだから。記録するたびに自分で連番をふるのは面倒でしょってことで。

◆WEBスクレイピング~DB記録

#===============================
# WEBスクレイピング
# tenki.jpの天気予報情報を取得
# 取得した情報をDBへ格納する
#===============================
import requests
import bs4
import sqlite3

#===============================
# 特定WEBサイトから情報を取得
#===============================
# 情報取得したいURL
URL = 'https://tenki.jp/forecast/3/17/4610/14100/1hour.html'
# URLへアクセス
r = requests.get(URL)
# 解析しやすいデータに
s = bs4.BeautifulSoup(r.text, 'html.parser')
#------------------------------
# データ収集
# 当日含む3日分(24*3=72)
# 要素数・・・72こ
#------------------------------
# 気温情報の取得
list_temp= []
for j in s.find_all('tr' , class_ ='temperature'):
    for i in j.find_all('span' ):
        list_temp.append(i.string)

# 天気情報(晴れとか)の取得
list_weather = []
for j in s.find_all('tr' , class_ ='weather'):
    for i in j.find_all('p'):
        list_weather.append(i.string)

# 時刻の取得
list_hour = []
for j in s.find_all('tr' , class_ ='hour'):
    for i in j.find_all('span'):
        list_hour.append(i.string)

# 風向きの取得
list_windblow = []
for j in s.find_all('tr' , class_ ='wind-blow'):
    for i in j.find_all('p'):
        list_windblow.append(i.string)

# 風速の取得
list_windspeed = []
for j in s.find_all('tr' , class_ ='wind-speed'):
    for i in j.find_all('span'):
        list_windspeed.append(i.string)

#------------------------------
# 各情報を2次元配列へ格納する
#------------------------------
List_all =[list_hour, list_weather, list_temp, list_windspeed,list_windblow]

"""
備考;
List_all =[list_hour, list_weather, list_temp, list_windblow, list_windspeed]
上記は、「5 × 72」の配列
[0]list_hour[0-71]             --> List_all [0][0], [0][1], [0][2], [0][3]…
[1]list_weather[0-71]        --> List_all [1][0], [1][1], [1][2], [1][3]…
[2]list_temp[0-71]             --> List_all [2][0], [2][1], [2][2], [2][3]…
[3]list_windspeed[0-71]       --> List_all [3][0], [3][1], [3][2], [3][3]…
[4]list_windblow[0-71]     --> List_all [4][0], [4][1], [4][2], [4][3]…
"""


#------------------------------
# 確認用
#------------------------------
for i in range(0,72,1):
        print(List_all[0][i], List_all[1][i], List_all[2][i], List_all[3][i], List_all[4][i])


#===============================
# DB接続~DBへデータ格納
#===============================

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)

cur = connection.cursor()
sql1 = 'INSERT INTO WeatherTable(hour, weather, temp, windspeed, windblow) VALUES(?,?,?,?,?)'


for i in range(0,72,1):
    cur.execute(sql1,(List_all[0][i], List_all[1][i], List_all[2][i], List_all[3][i], List_all[4][i]))

connection.commit()
cur.close()
connection.close()


input()

"""
DB備考;
DB name:TEST.db
table name:WeatherTable

テーブル作成時のフィールド名;
id INTEGER PRIMARY KEY AUTOINCREMENT,
hour STRING,
weather STRING,
temp DOUBLE,
windblow STRING,
windspeed INTEGER

"""

最初は書籍の通りにスクリプトを書いてみたが、内容は1つずつ要素を取得してSQLで書き込んで…のfor文だった。え~、これだと要素を見つけた時点でSQL書き込んでしまうので、複数の要素を配列としてデータベースに保管するにはなんだかな~ってなった。
ってことだと、、、どうしたらいいか???
とりあえず要素を取得してリストに保管するか。
気づいてみればスクレイピングの情報取得の仕方がかなりアレな感じになってしまった…わざわざ気温、時刻、風速…とか別個で取得してしまったよ。。。3日分つまり72時間で、1時間ごとのデータなので各データは72個ある。気温が72個、時刻が72個、風速が72個…あ〜うざったい、なんでバラバラやねん!ってことで、各データはリストという1次元配列なので、それらを束ねて2次元配列にし、それをfor文でぶん回してsqlで叩き込んでいく感じにしてみた。Excelの表を1行ずつ書き込んでいるイメージだね。
本来ならHTMLを1回読み込んで、find_allで要素をなめていくときに、取得したい情報をSelect?Switch?で各リストに保管してけばキレイになるんだろね…オレのみたいに何回もfind_allやるのはなんだかな~なんだろうなぁ…。でも見つけたい要素数が72コと少ないからこんなんでいいんでしょ。(シロウト発想)

◆テーブル中身の確認(単純に)

#===============================
# DB内テーブル中身の確認
#===============================
import sqlite3

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)

cur = connection.cursor()
sql1 = 'SELECT * FROM WeatherTable'
cur.execute(sql1)

print(cur.fetchall())

cur.close()
connection.close()

print('--------------------')
print('end of line')
input()

◆テーブル中身の確認(pandas利用)

#===============================
# DB内テーブル中身の確認(pandas)
#===============================
import sqlite3
import pandas as pd

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)
cur = connection.cursor()

sql1 = 'SELECT * FROM WeatherTable'
df = pd.read_sql(sql1, connection)


# ディスプレイ表示省略しない
pd.set_option("display.max_colwidth", None)
pd.set_option("display.max_rows", None)

# ディスプレイに表示
print(df)

cur.close()
connection.close()

print('--------------------')
print('end of line')
input()

◆実行結果

f:id:game-allergy:20210412221026j:plain

SQLで単純にSELECT * FROM・・・とかで引っ張っても、ディスプレイで読みづらい状態だとさすがに萎える…そのためpandsを利用したほうが断然分かりやすい。人間の目って横にも動くけど、上から下に見るほうが比較しやすいというか、把握しやすいのだと思う。(なんだろ、人間工学的な話になりそう・・・。

いや~表形式のデータって見やすいよね、縦に流して見ていくとほんとに見やすいな・・・あれ??風速10m/Sって????そんな嵐なの?明日って??!


そうだ、DBのテーブル削除を載せておこう。何回もテストしているとデータ量がどんどこ増えていくので…。

◆DBテーブル削除

import sqlite3

dbname = 'TEST.db'
connection = sqlite3.connect(dbname)

cur = connection.cursor()
sql = 'DROP TABLE WeatherTable'
cur.execute(sql)

connection.commit()
connection.close()

print('--------------------')
print('end of line')
input()