[SQLite3]すでに取得済みのデータは新規で追加せず更新だけする

2021-02-21

Pythonでクローラーを作り始めたのですがタイトル通りのことをやりたいと思いました。

Pythonスクリプトでごにょごにょするかと思っていましたが、SQLite側の構文で実装できたので備忘録としてメモ。

何をやりたかったのか

そもそも、何をやりたかったのかを先に紹介しておきます。

Pythonで組んだクローラーで該当サイトを巡回→データが追加されていれば追加 or 変更無ければ新規でデータを追加しない

ということをやりたいと思っていました。これしないと、クローリングする度に同じ情報でデータベースが埋め尽くされてしまうので。

Pythonでスクリプトを組むのではなく、上記の目的はREPLACE(もしくは INSERT OR REPLACE)で実装することができました。別のデータベースではUPSEERTで実装するみたいです。

今回はサンプルを基にこのREPLACEの使い方について紹介していきます。

SQLite3のREPLACEを使う

今回、利用するサンプルコードは最小限の果物屋さんのデータベースと考えてみます。

辞書型で果物の名前と、それに対応する金額を記述しています。

import sqlite3

fruits = [{'name': 'りんご', 'price': 100},
          {'name': 'バナナ', 'price': 200},
          {'name': 'オレンジ', 'price': 300},
          ]


conn = sqlite3.connect('fruits.db')
c = conn.cursor()
c.execute('''
     CREATE TABLE fruits (
     name text primary key,
     price integer
     )
''')

c.executemany('REPLACE INTO fruits VALUES (:name, :price)', fruits)
conn.commit()
conn.close()

実際のコードはこんな感じ。変数fruitsの中に、それぞれの果物や金額を記述しています。

そして、SQLite3でfruits.dbというデータベースを作成し、nameとpriceの受け皿を用意しています。

ここで注目したいのが、name text primary key, の部分です。

primary keyはそのカラム(ここで言うname)を主キーと設定しています。

REPLACE構文を使う際には、何を以って照合するのか？という部分でprimary keyを設定して使います。

もっと噛み砕いて言うと、REPLACEを使用した時にデータベースから同じ果物の名前のデータがあれば追加せず、更新しますよという手続きを行います。

これがWebスクレイピングであれば、URLをprimary keyに設定することになると思います。例えばブログ記事を巡回するクローラーを作成するとすれば、タイトルや本文や更新されることはありますが、URLは基本的に変更されることはありません。

実際にコードを動かす

前置きが長くなってしまいましたが、上記コードを動かしてみます。

すると、同じ階層に「fruits.db」というファイルが作成されます。

実行して中身を見ていきましょう。(MacのSQLite GUIアプリのDB Browser for SQLiteを使用しています)

nameとpriceのカラムにそれぞれ記述した値が格納されていますね。

では、次にりんごの値段を100円から500円に変更してみます。期待結果としては、4番目に新しくりんご500円が追加されるのではなく、既存のりんご(id:1)のpriceの値のみが変更されることです。

import sqlite3


fruits = [{'name': 'りんご', 'price': 500}, # りんごの金額のみ変更
          {'name': 'バナナ', 'price': 200},
          {'name': 'オレンジ', 'price': 300},
          ]


conn = sqlite3.connect('fruits.db')
c = conn.cursor()

# すでにテーブルは作成しているので割愛
# c.execute('''
#     CREATE TABLE fruits (
#     name text primary key,
#     price integer
#     )
# ''')

c.executemany('REPLACE INTO fruits VALUES (:name, :price)', fruits)
conn.commit()
conn.close()

期待結果通りになりました。

もう一個ついでに、りんごの部分をドリアン(500円)に変更してみるとどうなるでしょうか？

import sqlite3


fruits = [{'name': 'ドリアン', 'price': 500}, # りんごからドリアンに変更
          {'name': 'バナナ', 'price': 200},
          {'name': 'オレンジ', 'price': 300},
          ]


conn = sqlite3.connect('fruits.db')
c = conn.cursor()

# すでにテーブルは作成しているので割愛
# c.execute('''
#     CREATE TABLE fruits (
#     name text primary key,
#     price integer
#     )
# ''')

c.executemany('REPLACE INTO fruits VALUES (:name, :price)', fruits)
conn.commit()
conn.close()

結果は既存のprimary key(果物の名前)にない値なので新たにテーブルに追加されました。