PythonによるSQLite上での重複の削除

Question

前提・実現したいこと

こんにちは。
Python上でCSVファイルを読み込み、SQLite3にデータベースを作成しています。
その際、重複した行を削除しようとしているのですが、以下のSelect Distinct（変数2つ）ではうまくいきません。
読み込むCSVは毎日更新されるので、Primary Keyによる制限をかけない方針です。
（制限をかけると、CSVファイルそのものが読み込まれないという理解です。）
←理解が間違っておりました。
初めてSQLを使ったため、的外れな質問であれば恐縮です。
よろしくお願いいたします。

該当のソースコード

conn = sqlite3.connect('database_name')
sql = """
SELECT
    DISTINCT `column_name1`, 
    ~~DISTINCT~~ `column_name2`
FROM
    'table_name'
"""
conn.execute(sql)

【追記】

・CSVはForループで順次読み込まれます
・重複を許してSQLに格納したのち、重複したものを消す手法は試しましたが、Lockされてしまい断念しました。

【追記】該当のソースコード

import sqlite3
db_file_name = database_name
conn = sqlite3.connect(db_file_name)
sql1 = """
delete   from table_name
where    rowid not in
         (
         select  min(rowid)
         from    prices
         group by
                 column_name1
         ,       column_name2
         )
"""
conn.execute(sql1)

Answer 1

Sqliteにはrowidと言う行を識別するint型の特殊なカラムがあります。

select
    max(rowid),
    column_name1,
    column_name2
from table_name
group by
    column_name1,
    column_name2

とすると(column_name1, column_name2)の組み合わせについて最新の行が一行だけ取得できます。これを応用して古い重複行を削除したければmax(rowid)を含まない行を削除すれば良いわけです。

delete from table_name
where rowid not in (
    select
        max(rowid)
    from table_name
    group by
        column_name1,
        column_name2
)

Comments

[Shu0101]

ありがとうございます。
実は、すべての重複を許してからSQLに格納して削除する方法は試したのですが、Pythonからだとdata baseがlockされてしまい断念しました（SQLite上ではうまく作動しました）。
せっかくご教授いただいたのに申し訳ありません。

[Kenji.Noguchi]

Pythonでは動かず、sqliteコマンドで動くならPythonの方になんらかの問題があると思いませんか？例えば他のプロセスでDBを開いているとか。Sqliteは複数の同時アクセスを許さないので。

[Shu0101]

早々にお返事ありがとうございます。
当該のPythonコードを追加しております。
他のプロセスは開いておらず、Jupyter Labで独立させてこの部分のみをRunしているので、他のプロセスでDBを開いているという訳ではないと思います。
このままRunすると、data base is lockedと出るので、断念したという経緯です。

[Kenji.Noguchi]

"lsof | grep データベースのファイ名"とすれば分かります。

Answer 2

（制限をかけると、CSVファイルそのものが読み込まれないという理解です。）

この理解は間違っています。CSVのインポート時に重複を除くためにUNIQUE制約をつけて下さい。

例えば下記のようなスキーマのデータベースを作成します。

CREATE TABLE hoge(
  date TEXT,
  type TEXT,
  value INT,
  UNIQUE(date,type,value)
);

$ sqlite3 test.db < init.sql

次の2つのCSVをインポートしてみます。1つは予め重複のあるCSV、もう一つは既に存在するレコードを持つCSVです。

test1.csv

2020-06-12,A,1
2020-06-12,A,1
2020-06-13,B,5
2020-06-14,C,3

test2.csv

2020-06-13,B,5
2020-06-17,D,3

この状態でインポートしてみます。

$ sqlite3 -separator , test.db ".import test1.csv hoge"
test1.csv:2: INSERT failed: UNIQUE constraint failed: hoge.date, hoge.type, hoge.value

警告が表示されましたがデータベースの中身を見てみると

$ sqlite3 test.db "SELECT * FROM hoge"
2020-06-12|A|1
2020-06-13|B|5
2020-06-14|C|3

と正しく重複が除かれたデータベースができています。続いて新しいCSVをインポートして見ます。

$ sqlite3 -separator , test.db ".import test2.csv hoge"
test2.csv:1: INSERT failed: UNIQUE constraint failed: hoge.date, hoge.type, hoge.value

やはり警告が表示されましたが、データベースを見てみると

$ sqlite3 test.db "SELECT * FROM hoge"
2020-06-12|A|1
2020-06-13|B|5
2020-06-14|C|3
2020-06-17|D|3

と、新たに意図したレコードが追加されています。

pythonによる方法(回答欄での質問に対する返事)

PRIMARY KEYやUNIQUE制約を持ったテーブルに対して、pythonを使ってCSVファイルを追記する方法を示します。
重要なことはINSERTの代わりにINSERT OR IGNOREを用いることです。

import sqlite3


def insert_csv(conn, filename):
    with open(filename, "r") as f:
        items = []
        for line in f:
            cols = line.strip().split(",")
            items.append(cols)
        cur = conn.cursor()
        cur.executemany(
            "INSERT OR IGNORE INTO hoge (date,type,value) VALUES (?,?,?)",
            items,
        )
        conn.commit()


def main():
    conn = sqlite3.connect("test.db")
    insert_csv(conn, "test1.csv")
    insert_csv(conn, "test2.csv")
    conn.close()


if __name__ == "__main__":
    main()

Comments

[Shu0101]

ご丁寧な回答に感謝いたします。ありがとうございます。
説明不足で大変申し訳ないのですが、現在やりたいこととしては、PythonのForループで順々にCSVファイルを読み込もうとしています。
いただいた例に即しますと、test2に加え、test3も順に読み込みます。
そうしますと、test2が読み込まれた場合はご回答のように上書きされますが、その時点でIntegrityErrorが出てしまい、test3をInputする前に止まってしまいます。
TryとExceptでIntegrityErrorをスルーしようとしましたが、改善しませんでした。

ループの作り方が悪い可能性は否定できませんが、上記事象についてアドバイスいただけますと幸甚です。
よろしくお願いいたします。

[yymmt]

例外をキャッチして無視できない理由は分かりませんが、行いたいことはpythonでcsvを一行ずつ読み込んでINSERT分を使って追加する、ということで良いでしょうか?ソースコードを示したいので、別途解答欄にてお答えします。

[Shu0101]

ありがとうございます、解決しました！
ご理解の通りです。
INSERT OR IGNOREにすることで、例外処理をせずに済みました。
ベストアンサーとさせていただきます。

ちなみに、IntegrityErrorが例外処理できない件は、海外のサイトでも議論されているようですね。
https://stackoverrun.com/ja/q/7449325

Answer 3

DISTINCT は最初の1つだけ記述してください。
DISTINCTは重複データを除外して取得する機能で、重複データを削除する機能ではありません。

Comments

[Shu0101]

ありがとうございます。
勘違いしておりました。。。
それでは、データベースに保存後の重複削除の方法はあるのでしょうか？
簡便な方法があればご教授いただけますと幸いです。