以前、SQLite3の使い方を紹介しましたが、その応用です。
自分がどんなサイトにアクセスしているかを自分で集計したくなったのでその方法を調べました。(firefoxなどもそうらしいのですが) Chromeはアクセス履歴をSQLiteのDB(ファイル)に保存しています。そのため、該当ファイルに接続すればSQLで集計ができます。
Mac の場合、以下のパスに履歴ファイルがあります。拡張子がないので紛らわしいですが、Historyがファイル名で、Defaultまでがディレクトリパスです。
/Users/{ユーザー名}/Library/Application\ Support/Google/Chrome/Default/History
人によってはDefault 以外のディレクトリにあることもあるそうなので、もしそれらしいファイルがなかったら付近のディレクトリの中を探してみてください。例えば、Default/History ではなく、System Profile/Historyや、Guest Profile/Historyに存在する環境も見たことがあります。
ロックがかかったりするとよくないので、このファイルに直接繋ぐのではなく、どこかにコピーを取って、その中を確認しましょう。
# カレントディレクトリにコピーする
$ cp /Users/{ユーザー名}/Library/Application\ Support/Google/Chrome/Default/History .早速接続してみましょう。とりあえずどんなテーブルがあるのか見てみます。
import sqlite3
import pandas as pd
# データベースに接続
con = sqlite3.connect('History')
con.row_factory = sqlite3.Row
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master")
pd.DataFrame(
cursor.fetchall(),
columns=[row[0] for row in cursor.description],
)
"""
name
0 meta
1 sqlite_autoindex_meta_1
2 urls
3 sqlite_sequence
4 visits
5 visit_source
6 visits_url_index
7 visits_from_index
8 visits_time_index
9 visits_originator_id_index
10 keyword_search_terms
11 keyword_search_terms_index1
12 keyword_search_terms_index2
13 keyword_search_terms_index3
14 downloads
15 downloads_url_chains
# 多いので以下略。自分の環境では合計35テーブルありました。
"""目当てのアクセス履歴が保存されているテーブルは、visitsです。ただ、このテーブルはアクセスしたURLのパスそのものは含んでおらず、URLはurlsテーブルに入っていて、visitsテーブルにはurlsテーブルのidが入ってます。
urlsテーブルから1行だけ取り出してみます。(転置してprintしました。)
cursor.execute("""
SELECT
*
FROM
urls
WHERE
url = 'https://analytics-note.xyz/'
""")
print(pd.DataFrame(
cursor.fetchall(),
columns=[row[0] for row in cursor.description],
).T)
"""
0
id 53
url https://analytics-note.xyz/
title 分析ノート
visit_count 43
typed_count 0
last_visit_time 13327924159724978
hidden 0
"""idが53らしいですね。URLとページタイトルが入っています。あと、visit_countとして訪問回数入ってます。(思ったより少ないです。他のPCやスマホで見てることが多いからでしょうか。)
次は、visitsテーブルも見てみます。urlという列にさっきのurlテーブルのidが入ってる(列名をurls_idとかにして欲しかった)ので、それで絞り込んでいます。
cursor.execute("""
SELECT
*
FROM
visits
WHERE
url = 53
ORDER BY
visit_time DESC
LIMIT
1
""")
print(pd.DataFrame(
cursor.fetchall(),
columns=[row[0] for row in cursor.description],
).T)
"""
0
id 8774
url 53
visit_time 13327924159724978
from_visit 0
transition 805306370
segment_id 9
visit_duration 0
incremented_omnibox_typed_score 0
opener_visit 0
originator_cache_guid
originator_visit_id 0
originator_from_visit 0
originator_opener_visit 0
is_known_to_sync 0
"""visit_timeでアクセスした時刻がわかる様ですね。
ちなみに、UNIXタイムスタンプ等に比べて非常にでっかい数字が入っていますが、これはChromeの仕様で、1601年1月1日(UTC)からの経過時間をマイクロ秒(μ秒、100万分の1秒)単位で計測したものだそうです。(この情報は他サイトで入手したのですが、できればChromeの公式ドキュメントか何かで確認したいところです。概ね正しそうなのですが、自分の環境だと数分ずれていて違和感あります。)
さて、テーブルの構造がわかったので具体的なSQL構築していきます。たとえば、直近1000アクセス分の情報を見る場合は以下の様なクエリで良いのではないでしょうか。
sql = """
SELECT
visits.visit_time,
urls.url,
urls.title
FROM
visits
LEFT OUTER JOIN
urls
ON
visits.url = urls.id
ORDER BY
visits.visit_time DESC
LIMIT 1000;
"""
cursor.execute(sql)
df = pd.DataFrame(
cursor.fetchall(),
columns=[row[0] for row in cursor.description],
)仕上げに、visit_time がこのままだと使いにくすぎるので、これを人が読めるように変換します。手順は以下の通りです。
- 10^6で割ってマイクロ秒を秒に変換する。
- 1601年1月1日のタイムスタンプを足して現在時刻のタイムスタンプに変換する。
- 時刻型に戻す。
- 9時間足して日本時間にする。
順番にやっていくとこんなコードになります。
from datetime import datetime
from datetime import timedelta
df["アクセス時刻"] = df["visit_time"].apply(
lambda x: x/10**6
).apply(
lambda x: x+datetime(1601, 1, 1).timestamp()
).apply(
datetime.fromtimestamp
).apply(
lambda x: x+timedelta(hours=9)
)もっといいやり方はいくらでもある気がする(1601/1/1のタイムスタンプとか9時間分の秒数とか事前に定数として持っておいてマイクロを取った時に足してしまうなど)のですが、可読性重視でいけばこの書き方で良いと思います。