やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3 公開日時とURLで一意チェックする（表制約）

SQLite3 公開日時とURLで一意チェックする（表制約）

SQLite3

　表制約で複数列の一意チェックをする

成果物

情報源

テーブル定義

表制約（unique）

create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);

　UNIQUE(published,url)を追記した。

　制約に名前を付けることもできる。

create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  CONSTRAINT cstr_unique UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);

　だが、SQLite3ではalter table drop constraintコマンドをサポートしていない。名前をつける意味はない。pragma index_info('制約名'), pragma table_info('表名')でも情報を出せない。

コンフリクト

　UNIQUE(published,url) on conflict ...を追記しても良かったが、どうすべきか迷ったのでやめた。

　rollback, abort, fail, ignore, replaceの5つ。使うならignoreか。制約違反のステートメントをスキップして続行する。

create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) on conflict ignore -- 公開日時とURLの両方が被ると同一とみなす
);

　もし公開日時の降順にソートされてinsertされるなら、DB内レコードと重複した時点で古いニュースと判断できる。よって、強制終了するのがよい。スキップして次に移っても、やはり重複してスキップ対象だろうから。

　だが、これはソートされていることが前提。もしソートされていなければ1件ずつ対応すべきだろう。念の為、1件ずつが安全か。

　また、ignoreでは無視されてエラーが表示されない。一意制約違反があったことがわかったほうが良いときもあるだろう。そのときはfailを使うことになる。問題は、エラー表示のON/OFFを切り替えられないことにある。

insert時のコンフリクト

　エラー表示のON/OFFを切り替えるには、insert文を動的に生成し、on conflictの対応をignore/failで切り替えれば良いだろう。

class NewsDb:
    ...
    def __insert_sql(self, conflict=None): 
        return ('insert into news(published,url,title,body) values(?,?,?,?)' + 
                '' (conflict is None) 'on conflict ' + conflict)
    ...
  • conflictがないときは無駄な結合が生じる。レコード数分も
  • conflictの値は確認すべき

例外

　一意制約違反のときはSQLITE_CONSTRAINTを返す。

conflict 意味 戻り値
rollback ロールバック SQLITE_CONSTRAINT
abort 中断 SQLITE_CONSTRAINT
fail 失敗 SQLITE_CONSTRAINT
ignore 無視 -
replace 上書き -

　今回の一意制約違反のときは、ロールバックすべきでない。

　insert ... on conflict ignoreにすることで例外発生を回避できるだろう。failだと例外発生するだろう。もし、例外時は無条件でrollbackしているなら、一意違反でないレコードまでロールバックされてしまう。

　どんな例外が発生するのか？　sqlite3.IntegrityErrorだった。

コード
例外

test.py

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
sql = '''
create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);
insert into news(published, url) values('0','A');
insert into news(published, url) values('0','B');
insert into news(published, url) values('0','A');
insert into news(published, url) values('1','A');
insert into news(published, url) values('1','B');
'''
cur.executescript(sql)

$ python3 test.py 
Traceback (most recent call last):
  File "test.py", line 19, in <module>
    cur.executescript(sql)
sqlite3.IntegrityError: UNIQUE constraint failed: news.published, news.url
fail
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
sql = '''
create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);
insert into news(published, url) values('0','A');
insert into news(published, url) values('0','B');
insert or fail into news(published, url) values('0','A');
insert into news(published, url) values('1','A');
insert into news(published, url) values('1','B');
'''
try: cur.executescript(sql)
finally: print(cur.execute('select count(*) from news;').fetchone()[0])

2
Traceback (most recent call last):
  File "test0.py", line 19, in <module>
    try: cur.executescript(sql)
sqlite3.IntegrityError: UNIQUE constraint failed: news.published, news.url

　一意エラーが発生する前までのinsertは挿入された。だが、一意エラー以降のinsertはは実行されなかった。published順にソートされた状態でinsertされるなら、これでも問題ない。なにせ既存と重複したなら、それ以降は古いニュースだから不要。

ignore

test1.py

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
sql = '''
create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);
insert into news(published, url) values('0','A');
insert into news(published, url) values('0','B');
insert or ignore into news(published, url) values('0','A');
insert into news(published, url) values('1','A');
insert into news(published, url) values('1','B');
'''
try: cur.executescript(sql)
finally: print(cur.execute('select count(*) from news;').fetchone()[0])

python3 test1.py 

4

　一意エラー箇所だけ無視されて、他はすべてinsertされた。

一意制約エラーのとき以外はロールバックする

test2.py

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
sql = '''
create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);
insert into news(published, url) values('0','A');
insert into news(published, url) values('0','B');
insert or fail into news(published, url) values('0','A');
insert into news(published, url) values('1','A');
insert into news(published, url) values('1','B');
'''
try: cur.executescript(sql)
except sqlite3.IntegrityError: # おそらく制約違反
    import traceback
    traceback.print_exc()
except: # それ以外
    import traceback
    traceback.print_exc()
    self.conn.rollback() # ロールバックする
finally: print(cur.execute('select count(*) from news;').fetchone()[0])

Traceback (most recent call last):
  File "test2.py", line 19, in <module>
    try: cur.executescript(sql)
sqlite3.IntegrityError: UNIQUE constraint failed: news.published, news.url
2

　これで今回つくった一意制約エラーのときはロールバックせず、それ以外の例外のときはロールバックするようになった。

　公式文書によると以下のようなエラーがある。

Error 意味
sqlite3.Warning 警告
sqlite3.Error このモジュールにおける他の例外クラスの基底クラス
sqlite3.DatabaseError データベースに関連するエラーに対して発生した例外
sqlite3.IntegrityError データベースのリレーショナル整合性が影響を受けるときに発生する例外
sqlite3.ProgrammingError プログラミングエラーに対して発生する例外（テーブルが既存・見つからない、SQL構文エラー、指定されたパラメーターの数が間違っている等）

　どうやら細かい切り分けはできていなさそう。たとえば外部エラーなど他の制約エラーが起こったときもsqlite3.IntegrityErrorが発生する。今回の一意制約違反である場合だけにしたいのだが、どうすればいい？

一意制約エラーのとき以外はロールバックする２

test3.py

except sqlite3.IntegrityError as err_sql_integ: # おそらく制約違反
    import traceback
    import sys
    msg = str(err_sql_integ.with_traceback(sys.exc_info()[2]))
    traceback.print_exc()
    if 'UNIQUE' in msg and 'published' in msg and 'url' in msg: pass
    else: self.conn.rollback() # ロールバックする

　他のIntegrityErrorのときはロールバックする。

　でも、メッセージの内容ってSQLite3の実装や、テーブル定義によって変わってしまうのでは？

　たとえば表制約に名前をつけたとき、メッセージ内容が変わってしまわないか？

create table if not exists news(
  id         integer primary key,
  published  text,
  url        text,
  title      text,
  body       text,  -- URL先から本文だけを抽出したプレーンテキスト
  CONSTRAINT cstr_unique UNIQUE(published,url) -- 公開日時とURLの両方が被ると同一とみなす
);

sqlite3.IntegrityError: UNIQUE constraint failed: news.published, news.url

　大丈夫だった。制約名の有無にかかわらず、エラーメッセージは同じ。

　でも、バージョンによる違いとかあるかもしれない。でも、たぶんUNIQUE,published,urlの3単語に絞ったから大丈夫だろう。

結局どうする？

try: 挿入処理（`insert or fail ...`）
except sqlite3.IntegrityError as err_sql_integ: # おそらく制約違反
    import traceback
    import sys
    msg = str(err_sql_integ.with_traceback(sys.exc_info()[2]))
    traceback.print_exc()
    if 'UNIQUE' in msg and 'published' in msg and 'url' in msg: pass
    else: self.conn.rollback() # ロールバックする
except: # それ以外
    import traceback
    traceback.print_exc()
    self.conn.rollback() # ロールバックする

　挿入データはpublishedの降順にソートされているものとする。

所感

　すごい汚いコード。エラー条件分岐をメッセージのテキストで行うとか、クソすぎて不安しかない。あと、exceptの内容かぶってる。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで