【python】BeautifulSoupとSQLiteを使ってデータベースを作ってみる
SQLiteの入門として書いた記事が最近伸びてきているみたいなのでまたもやSQLiteの記事を書いてみる。
▼【python】データベースの入門としてSQLiteを使ってみた - 僕の世界観を変えてみる
今回はBeautifulSoupを使ってスクレイピングしたデータをSQLiteでテーブルに入れてデータベースを作っていこうという記事です。
エディタはVisual Studio Code(VScode)を使いました。
シンタックスハイライトの種類が色々あるのとエディタ内にターミナルが表示されているのとデータベースを可視化できるので便利です。
▼Visual Studio Code - Code Editing. Redefined
- 今回のシナリオ
- BeautifulSopuとrequestsでデータを抽出
- 取得したデータをSQLiteでデータベース化する
- vscode上でテーブルを可視化する
- コマンドラインでSQLite
- コード全体
- まとめ
今回のシナリオ
まずデータベースに登録するデータですが、乃木坂46公式サイトに載っているメンバーの名前、読み方、血液型、生年月日、星座、身長にします。
データベースを作る練習にはもってこいの情報です。
使用言語はpython3
使用ライブラリはBeautifulSoup, requests, SQLite3です。
BeautifulSopuとrequestsでデータを抽出
乃木坂46のメンバーページにアクセスします。
デベロッパーツール（F12で起動）を使ってhtmlタグを調べます。
メンバーの名前を抽出
デベロッパーツールのElementsの項目を使って調べて見ると名前は<span class="main">というタグで囲われていて、読みはspan class="sub"というタグで囲われていることがわかります。
しかしよく見ると吉田彩乃クリスティーだけ読みのフォントサイズが他のフォントと比べると小さくなっており、class名もsub2になっています。
吉田彩乃クリスティーだけ個別で取り出せばいいだけなんですが、順番が狂ってしまうのでここでは名前(span class="main")だけを取り出そうと思います。
import requests from bs4 import BeautifulSoup as bs url = "http://www.nogizaka46.com/member/" header = {"User-Agent" : "Mozilla/5.0"} soup = bs(requests.get(url, headers=header).content, 'html.parser') name = [] member_link = [] for i in soup.find_all('div', class_='unit'): for j in i.find_all('span', class_='main'): name.append(j.text) print(name)
['秋元 真夏', '生田 絵梨花', '伊藤 かりん', '伊藤 純奈', '伊藤 理々杏', '井上 小百合', '岩本 蓮加', '梅澤 美波', '衛藤 美彩', '大園 桃子', '北野 日奈子', '久保 史緒里', '齋藤 飛鳥', '斉藤 優里', '阪口 珠美', '桜井 玲香', '佐々木 琴子', '佐藤 楓', '白石 麻衣', '新内 眞衣', '鈴木 絢音', '高山 一実', '寺田 蘭世', '中田 花奈', '中村 麗乃', '西野 七瀬', '樋口 日奈', '星野 みなみ', '堀 未央奈', '松村 沙友理', '向井 葉月', '山崎 怜奈', '山下 美月', '吉田 綾乃クリスティー', '与田 祐希', '渡辺 みり愛', '和田 まあや', '遠藤 さくら', '賀喜 遥香', '掛橋 沙耶香', '金川 紗耶', '北川 悠理', '柴田 柚菜', '清宮 レイ', '田村 真佑', '筒井 あやめ', '早川 聖来', '矢久保 美緒']
URLにアクセスするためにrequestsをインポート
htmlタグからテキストを抽出するためにBeautifulSoupをインポート
url=には公式サイトのメンバーページ
headerにはUserAgentの情報を入れてます。
最近のwebサイトはUserAgentを指定しないとアクセス拒否されるものがほとんどです。
乃木坂公式サイトもこれに該当します。
Mozilla/5.0と書くことでfirefoxでアクセスしているように偽装させています。
requests.getで乃木坂のサイトにリクエストを送っています。
そのの引数でheadersを指定します。
BeautifulSoupの引数でcontentを指定するとテキスト以外のデータを取得できます。
textにするとテキストデータを取得できますが、基本contentでいい気がします。
パーサーは他の記事でも書きましたがlxmlが速いらしいです。
▼【Python3】BeautifulSoupとRequestsを使ったスクレイピング - 僕の世界観を変えてみる
僕は違いがわからないのでデフォルトのhtmlparserにしています。
for文の中ですが、BeautifulSoupを使ってclassがunitのdivタグを一つ一つ取り出してiの中に入れていきます。
続いてdivタグの中にclassがmainのspanタグがあるのでこれを取り出しjに入れます。
最後にnameという空のリストにしまってあげます。
メンバーごとのリンクを抽出
次にメンバーの生年月日や身長などのデータですが、これは個々のページに記載されているのでそれぞれのリンクを取り出す必要が出てきます。
上の画像を見ると<div class="unit">の子タグにaタグがあるのがわかります。
これが個々のリンクになっているためこれもfor文で一緒に取り出すことにします。
name = [] member_link = [] for i in soup.find_all('div', class_='unit'): for j in i.find_all('span', class_='main'): name.append(j.text) for link in i.find_all('a'): member_link.append('http://www.nogizaka46.com/member' + link.get('href').split('.')[1] + '.php')
class名がunitのdivタグの子要素である<a>タグをlinkに一つずつ入れていきます。
aタグだけをprintすると下のような形で抽出されてしまいます。
<a href="./detail/yodayuuki.php"><img alt="与田 祐希" class="yodayuuki" height="150" src="https://img.nogizaka46.com/www/img/dot.gif" width="124"/><span class="main">与田 祐希</span><span class="sub">よだ ゆうき</span><!-- sub2 --></a>
<a>タグの中にあるhrefの情報が欲しい場合はgetを使用します。
link.get('href')という記述をすることでリンクを取得できます。
しかしここで取得できるリンクはURLではなくパスになっています。
なのでURLの形にしてあげる必要があるんです。
link.getで取得した文字の頭にドットが含まれているためこれを取り除きます。
頭の一文字だけ取り除く方法を僕は知らないのでとりあえずsplitしています。
これで頭のドットを取り除けますが最後の.phpも抜けてしまうので文字列で.phpを追加しています。
splitの説明
split('.')[1]というのは./detail/yodayuuki.phpをドットの位置で区切るという意味です。
実行すると['', '/detail/yodayuuki/', 'php']こんな形になります。
必要なのは/detail/yodayuuki/の部分なのでsplitのあとに[1]番目という意味で[1]を入れています。
※この記事書いているときに気づいたんですが、link.textで実行するとaタグ内の文字列を取り出せるので名前とふりがなをとってこれますね。
残りのデータを抽出する
メンバーの個々のリンクを抽出できたのであとはfor文で一人一人のページにアクセスして他の情報を取ってくるだけです。
生年月日や血液型などの情報は<div class="txt">というタグの子要素にある<dl>、さらにその子要素の<dd>に書かれています。
これらも同じようにfor文で回して取っていきましょう。
yomi = [] birth = [] blood = [] seiza = [] height = [] for link in member_link: profile_soup = bs(requests.get(link, headers=header).content, 'html.parser') for h2 in profile_soup.find_all('h2'): for span in h2.find_all('span'): yomi.append(span.text) for dl in profile_soup.find_all('dl'): for dd in dl.find_all('dd')[0]: birth.append(dd) for dd in dl.find_all('dd')[1]: blood.append(dd) for dd in dl.find_all('dd')[2]: seiza.append(dd) for dd in dl.find_all('dd')[3]: height.append(dd) time.sleep(1)
星座は英語でなんだかわからなかったのでseizaにしています。
またyomiと書いてありますが今思えばふりがなですよね。
コードの解説ですが、まずはデータを入れるための空のリストをそれぞれ作成します。
メンバーそれぞれのリンクにアクセスするには同じようにrequests.getでアクセスできます。
アクセスの後にhtmlをパースするために同様にBeautifulSoupを使用します。
やっていることは同じです。
それを４８人分行う必要があるためfor文で回しているだけです。
まずyomiですがこれは<h2>タグで囲まれていてさらに<span>でも囲まれています。
ってことでBeautifulSoupのfind_allで<h2>タグを取ってきて、さらにその子要素の<span>も同様にfind_allで取り出します。
文字列だけが必要なので.textを付け加えた物をyomiリストに格納します。
次に生年月日などのデータですが、これはぱっと見<dl>タグを使っている部分は一つしかないのですぐに<dl>を指定しても良さそうです。
なので<dl>をfind_allで取り出し、<dd>には生年月日、血液型、星座、身長の４つのデータが入っているため、dd[0]とすると生年月日を取り出すことができ、dd[1]で血液型、dd[2]で星座という風に取り出すことが可能です。
ってことでdd[0]をbirthリストに入れて残りもそれぞれのリストに格納していきます。
で一人のデータを取得した後にはtime.sleep(1)で１秒間のインターバルを設けます。
サーバに負荷をかけないようにするためです。
ちなみにtime関数はimport timeでインポートしないと使えません。
取得したデータをSQLiteでデータベース化する
あとはSQLiteを使ってデータベースを作ります。
import sqlite3 con = sqlite3.connect('nogizaka.db') cur = con.cursor() cur.executescript(""" drop table if exists member_data; CREATE TABLE member_data(name, yomi, birth, blood, seiza, height)""") for a,b,c,d,e,f in zip(name, yomi, birth, blood, seiza, height): cur.execute("INSERT INTO member_data VALUES(?,?,?,?,?,?);",(a,b,c,d,e,f)) con.commit()
sqlite入門の記事でも書いたものと変わりません。
conにデータベースにアクセスするためのコードを入れます。
このときnogizaka.dbが存在すれば接続して、存在しなければ新しく作成します。
SQLにはカーソルという概念があります。
このカーソルを使ってデータの挿入や参照を行います。
次にデータベースの中にテーブルを作成します。
作成にはexecuteメソッドを使用しますが、テーブルがすでに存在する場合はエラーが起きてしまうので今回はexecutescriptを使用します。
drop table if exists member_data;でmember_dataというテーブルが存在する場合、既存のテーブルを削除して新規で作成します。
CREATE TABLE member_data(name, yomi, birth, blood, seiza, height)で名前、読み、生年月日、血液型、星座、身長の項目のmember_dataというテーブルを作成します。
まだ目には見えませんが上記のコードで画像のようなテーブルを作成したことになります。
あとはテーブルに個々のデータを追加していきます。
name, yomi, birth, blood, seiza, heightはリストなのでfor文で一つ一つ取り出していきます。
通常for文は一つしか取り出せませんが、zipを使うと複数のリストを呼び出すことができます。
この場合nameをaに入れ、yomiをbに入れる、ってな具合に回していきます。
そしてリストから取り出したデータをプレースホルダーを使ってテーブルに追加していきます。
それがcur.execute("INSERT INTO member_data VALUES(?,?,?,?,?,?);",(a,b,c,d,e,f))の部分です。
なぜ？を使うのかというとsqlインジェクションという攻撃というか脆弱性というか、それらから守るために使います。
今回の場合は必要ありませんが、webアプリを作る際には必要になるのでプレースホルダーを使うように習慣づけておくといいかもしれませんね。
最後にcommitして保存します。
これでデータベースの完成です。
vscode上でテーブルを可視化する
vscodeには色々な拡張機能があります。
今回はsqliteで作成したデータベースをエクセルのように罫線で囲われたテーブルのように表示する拡張機能を使っていきます。
vscodeを開いて左側にある四角いアイコンExtensionsをクリックします。
検索バーでsqliteと検索すると一番上に出てくる拡張機能をインストールします。
vscodeを再起動すると使用できるようになります。
作成したデータベースを右クリックしてopen database in explorerをクリックします。
するとexplorerの下部にsqlite explorerが追加されるはずです。
データベースを開いてテーブルを右クリックするとshow tableが出てくるのでクリックします。
そうするとデータベースが作業画面に表示されます。
コマンドラインでSQLite
ここまできたら条件を指定してテーブルからデータを取り出してみたいですよね。
毎回pythonでコードを書いて実行するのもめんどくさいのでコマンドラインで動かしてみたいと思います。
windowsの場合はsqliteのexeをインストールすればコマンドプロンプトで使えますがmacだとすぐに使えるようになっています。
今回はmacで操作します。
vscode上でshift+control+@を押すとターミナルを開くことができます。
開いたらsqlite3と打って実行します。
データベースを開く
データベースを開くときは.openを使用します。
.open nogizaka.db .database main: /Users/shoot/Documents/python/nogizaka.db
データベースが開けたか確認するには.databaseを実行します。
するとデータベースまでのパスが表示されるはずです。
次に.tablesを実行してテーブルが表示されるか確認します。
.tables member_data
複数のテーブルがある場合、確認のために.tablesを使うんだと思います。
ではテーブルも確認できたのでターミナルに表示していきます。
基本はselect * from テーブル名; です。
select * from member_data; 秋元 真夏|あきもと まなつ |1993年8月20日|B型|しし座|154cm 生田 絵梨花|いくた えりか |1997年1月22日|A型|みずがめ座|160cm 伊藤 かりん|いとう かりん |1993年5月26日|O型|ふたご座|153cm 伊藤 純奈|いとう じゅんな |1998年11月30日|A型|いて座|166cm 伊藤 理々杏|いとう りりあ |2002年10月8日|B型|てんびん座|154cm 井上 小百合|いのうえ さゆり |1994年12月14日|B型|いて座|156cm 岩本 蓮加|いわもと れんか |2004年2月2日|B型|みずがめ座|157cm 梅澤 美波|うめざわ みなみ |1999年1月6日|A型|やぎ座|170cm 衛藤 美彩|えとう みさ |1993年1月4日|AB型|やぎ座|163cm 大園 桃子|おおぞの ももこ |1999年9月13日|O型|おとめ座
※メンバー全員は表示できないので一部抜粋して表示しています。
さて、表示はされましたがこれだとなんの項目があるのかがわかりませんよね。
sqlの設定を行うshowコマンド
そこで項目を表示するために.showコマンドを使って設定を変更してみます。
.show echo: off eqp: off explain: auto headers: off mode: list nullvalue: "" output: stdout colseparator: "|" rowseparator: "\n" stats: off width: filename: nogizaka.db
このheadersというのが項目になるのでこれをonに変えてあげます。
.headers on
これでもう一度テーブルを表示してみます。
select * from member_data; name|yomi|birth|blood|seiza|height 秋元 真夏|あきもと まなつ |1993年8月20日|B型|しし座|154cm 生田 絵梨花|いくた えりか |1997年1月22日|A型|みずがめ座|160cm 伊藤 かりん|いとう かりん |1993年5月26日|O型|ふたご座|153cm 伊藤 純奈|いとう じゅんな |1998年11月30日|A型|いて座|166cm 伊藤 理々杏|いとう りりあ |2002年10月8日|B型|てんびん座|154cm 井上 小百合|いのうえ さゆり |1994年12月14日|B型|いて座|156cm 岩本 蓮加|いわもと れんか |2004年2月2日|B型|みずがめ座|157cm 梅澤 美波|うめざわ みなみ |1999年1月6日|A型|やぎ座|170cm 衛藤 美彩|えとう みさ |1993年1月4日|AB型|やぎ座|163cm 大園 桃子|おおぞの ももこ |1999年9月13日|O型|おとめ座|156cm 北野 日奈子|きたの ひなこ |1996年7月17日|O型|かに座|158cm 久保 史緒里|くぼ しおり |2001年7月14日|O型|かに座|159cm 齋藤 飛鳥|さいとう あすか |1998年8月10日|O型|しし座|158cm
これで項目が表示されてなんのデータなのかがわかるようになりました。
と言ってもインデントがずれているので見づらいのも事実。
なので次はインデントを合わせるためにmodeを変更します。
showコマンドで表示した時にmode:listとなっていた部分をcolumnに変更するとテーブルを整列させてくれます。
.mode column
modeを変更したのでもう一度テーブルを表示してみましょう。
select * from member_data; name yomi birth blood seiza height ---------- ---------- ---------- ---------- ---------- ---------- 秋元 真夏 あきもと まなつ 1993年8月20日 B型 しし座 154cm 生田 絵梨花 いくた えりか 1997年1月22日 A型 みずがめ座 160cm 伊藤 かりん いとう かりん 1993年5月26日 O型 ふたご座 153cm 伊藤 純奈 いとう じゅんな 1998年11月30 A型 いて座 166cm 伊藤 理々杏 いとう りりあ 2002年10月8日 B型 てんびん座 154cm 井上 小百合 いのうえ さゆり 1994年12月14 B型 いて座 156cm 岩本 蓮加 いわもと れんか 2004年2月2日 B型 みずがめ座 157cm 梅澤 美波 うめざわ みなみ 1999年1月6日 A型 やぎ座 170cm 衛藤 美彩 えとう みさ 1993年1月4日 AB型 やぎ座 163cm 大園 桃子 おおぞの ももこ 1999年9月13日 O型 おとめ座 156cm 北野 日奈子 きたの ひなこ 1996年7月17日 O型 かに座 158cm 久保 史緒里 くぼ しおり 2001年7月14日 O型 かに座 159cm 齋藤 飛鳥 さいとう あすか 1998年8月10日 O型 しし座 158cm
はい、めちゃくちゃずれてますね。
何が整列できるですか、全く整列できてないじゃないですか！
いやこれは僕も被害者ですよ。
ちゃんとしっかりベストを尽くした結果がこれなんですから。
僕だってもっと綺麗に並んでくれるもんだと期待していたんです。
それがこれですよ、全く。
はい、気を取り直して次に行きます。
余談ですがmodeにはlist, columnの他にhtmlやcsvなどもありhtmlを指定するとtableタブで表示してくれます。
それをhtmlに貼り付ければブログやサイトでも表示できるってわけです。
便利ですね。
条件をつけてデータを参照する
※個人的に見づらいのでmodeをlistに戻しています。
次は条件をつけてデータを取り出してみます。
血液型がO型の人を取り出します。
条件をつけるときはwhereを使います。
select * from member_data where blood="O型"; name|yomi|birth|blood|seiza|height 伊藤 かりん|いとう かりん |1993年5月26日|O型|ふたご座|153cm 大園 桃子|おおぞの ももこ |1999年9月13日|O型|おとめ座|156cm 北野 日奈子|きたの ひなこ |1996年7月17日|O型|かに座|158cm 久保 史緒里|くぼ しおり |2001年7月14日|O型|かに座|159cm 齋藤 飛鳥|さいとう あすか |1998年8月10日|O型|しし座|158cm 斉藤 優里|さいとう ゆうり |1993年7月20日|O型|かに座|157cm 鈴木 絢音|すずき あやね |1999年3月5日|O型|うお座|160cm 西野 七瀬|にしの ななせ |1994年5月25日|O型|ふたご座|159cm 堀 未央奈|ほり みおな |1996年10月15日|O型|てんびん座|160cm 山下 美月|やました みづき |1999年7月26日|O型|しし座|159cm 与田 祐希|よだ ゆうき |2000年5月5日|O型|おうし座|152cm 渡辺 みり愛|わたなべ みりあ |1999年11月1日|O型|さそり座|153cm 和田 まあや|わだ まあや |1998年4月23日|O型|おうし座|160cm 金川 紗耶|かながわ さや |2001年10月31日|O型|さそり座|164cm 清宮 レイ|せいみや れい |2003年8月1日|O型|しし座|162cm 筒井 あやめ|つつい あやめ |2004年6月8日|O型|ふたご座|160cm
実は僕、O型の人とは気が合うんですよ。
一部の文字が含まれる場合
では、8月生まれの人を取り出す場合はどうすればいいでしょう。
select * from member_data where birth="8月"
これだとマッチしません。
8月という一部の文字が”含まれる”場合を参照する場合はlikeを使います。
select * from member_data where birth like "%8月%; name|yomi|birth|blood|seiza|height 秋元 真夏|あきもと まなつ |1993年8月20日|B型|しし座|154cm 齋藤 飛鳥|さいとう あすか |1998年8月10日|O型|しし座|158cm 佐々木 琴子|ささき ことこ |1998年8月28日|A型|おとめ座|163cm 白石 麻衣|しらいし まい |1992年8月20日|A型|しし座|162cm 中田 花奈|なかだ かな |1994年8月6日|A型|しし座|158cm 松村 沙友理|まつむら さゆり |1992年8月27日|B型|おとめ座|164cm 向井 葉月|むかい はづき |1999年8月23日|A型|おとめ座|152cm 賀喜 遥香|かき はるか |2001年8月8日|A型|しし座|166cm 北川 悠理|きたがわ ゆり |2001年8月8日|不明|しし座|163cm 清宮 レイ|せいみや れい |2003年8月1日|O型|しし座|162cm 早川 聖来|はやかわ せいら |2000年8月24日|A型|おとめ座|164cm 矢久保 美緒|やくぼ みお |2002年8月14日|B型|しし座|152cm
whereで項目を指定した後にlikeを使い8月という文字列が含まれる行を取り出しています。
パーセントで囲んだ文字を含むという意味になります。
ちなみに僕が言っている項目というのはカラムと呼ばれています。
あとlikeの他にglobという句もあるようですが僕はわかりません。
大文字小文字も区別できるとかだった気がしますが忘れました。
そして僕は12月生まれです。
データを並び替える
最後に並び替えです。
並び替えにはorder_by カラム名 昇順:降順で指定します。
身長が低い順に並び替えてみます。
select * from member_data order by height asc; name|yomi|birth|blood|seiza|height 向井 葉月|むかい はづき |1999年8月23日|A型|おとめ座|152cm 与田 祐希|よだ ゆうき |2000年5月5日|O型|おうし座|152cm 矢久保 美緒|やくぼ みお |2002年8月14日|B型|しし座|152cm 伊藤 かりん|いとう かりん |1993年5月26日|O型|ふたご座|153cm 渡辺 みり愛|わたなべ みりあ |1999年11月1日|O型|さそり座|153cm 秋元 真夏|あきもと まなつ |1993年8月20日|B型|しし座|154cm 伊藤 理々杏|いとう りりあ |2002年10月8日|B型|てんびん座|154cm 桜井 玲香|さくらい れいか |1994年5月16日|A型|おうし座|155cm 寺田 蘭世|てらだ らんぜ |1998年9月23日|不明|てんびん座|155cm 星野 みなみ|ほしの みなみ |1998年2月6日|B型|みずがめ座|155cm 井上 小百合|いのうえ さゆり |1994年12月14日|B型|いて座|156cm 大園 桃子|おおぞの ももこ |1999年9月13日|O型|おとめ座|156cm 掛橋 沙耶香|かけはし さやか |2002年11月20日|B型|さそり座|156cm 岩本 蓮加|いわもと れんか |2004年2月2日|B型|みずがめ座|157cm 斉藤 優里|さいとう ゆうり |1993年7月20日|O型|かに座|157cm 北野 日奈子|きたの ひなこ |1996年7月17日|O型|かに座|158cm
ascで昇順、descで降順に並び替えることができます。
与田ちゃんが小さいのはわかってましたが、向井も同じ身長だったんですね。
僕は177㎝で大きいほうだと思っていたんですが、最近の子は180㎝が平均なんですか？
コード全体
最後にコード全体を載せておきます。
tqdmというのはプログレスバーです。
進捗情報を表示するのにしようしました。
tqdmの記事はこちら 【Python3】progressbarとtqdmがImportErrorになったのでsys.pathで導いてあげた - 僕の世界観を変えてみる
まとめ
どうだったでしょうか今回の記事は。
僕ね、じつはyoutubeにも動画をあげてるんですよ。
youtubeから動画をダウンロードする方法を書いたんですけど、あれ文字だとわかりづらいかなーと思って。
そしたら低評価ついたんで世の中厳しいなって思いまして、じゃブログでしっかり書いてがんばろうかなと。
そんな思いで書いてみましたよ。
sqlはwebアプリ作る時にも使うかもしれないので勉強しておいて損はないです。
勉強していて楽しかったのでちょっと長めの記事になっちゃいましたが参考にしていただけたら幸いです。
