【Python】SQLite の使い方【sqlite3】

Python の sqlite3 で、データベースにデータを『追加』するコード例と、データを『取得』するコード例を書きました。

あと、データを『削除』するコード例も書きました。

SQLite は、SSD や HDD の容量を目一杯使って、たくさんのデータを読み書きすることができました。

また、データをディスクに置いたままで、Python の辞書 (dict) のように、狙ったデータをどんどん参照することができました。

ほかにも、SQLite の公式サイトを見たら、『同時読み込み数に制限はない』とか、『書き込みは同時に1つしかできない』とか、いろいろな説明がありました。

High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.

(SQLite) Appropriate Uses For SQLite – High Concurrency

SQLite は、『決算分析システム』や『テキスト分析システム』を作るときに、とても役に立ちました。

小さなデータでも、大きなデータでも、SQLite は有用でした。

バイナリデータも保存できたので、pickle.dumps() で pickle 化したデータを、gzip.compress() で圧縮して、SQLite DB に保存する、といったこともできました。

SQLite データベースの閲覧ソフト

SQLite データベース (SQLite DB) の中身を見るときは、『DB Browser for SQLite』というソフトが便利でした。

(sqlitebrowser.org) DB Browser for SQLite

これで、SQLite データベースの中身を、Excel や LibreOffice Calc のような画面で、見たり編集したりすることができるようになりました。

行数(レコード数)が 100 万件を超えた SQLite DB とか、ファイルサイズが 1 GB を超えた SQLite DB でも、サクッと開いて、中身を見ることができました。

データベースの中身が『意図した通り』にできているかを、簡単にチェックすることができました。

Python マニュアル

コード例で使用した Python 機能のマニュアルの場所です。

SQLite マニュアル

コード例で使用した SQLite 機能のマニュアルの場所です。

データを『追加 (INSERT, REPLACE)』するコード例

SQLite データベースに、データを『追加』する Python コード例です。

コード例では割愛かつあいしましたが、データを一括で追加するときは、(Python) executemany(sql, seq_of_parameters) で追加することもできました。

c.executemany() のほうは、データの加工や取捨選択をする必要がないときに、便利でした。

"""
insert_main.py インサート (INSERT, REPLACE)
SQLite DB に、データを『追加』するコード例です。
"""

import sqlite3
from traceback import format_exception_only

def main():
    print('start\n')

    # データを用意します。
    src_datas = [
        # (例) [0]n [1]text
        [101, 'あいうえお'],
        [102, 'かきくけこ'],
        [103, 'さしすせそ'],
        [104, 'たちつてと'],
        [105, 'なにぬねの'],
    ]

    # SQLite DB のファイルパスを決めます。
    db_file = r'F:\project\sample\datas.db'
    print(f'(db_file) {db_file}\n')

    # SQLite DB に接続します。
    # conn は connection の略です。
    conn = sqlite3.connect(db_file)
    print(f'type(conn) {type(conn)}')

    # カーソルを取得します。
    # c は cursor の略です。
    c = conn.cursor()
    print(f'type(c) {type(c)}\n')
    try:
        # テーブルが無ければ作ります。
        # PRIMARY KEY か UNIQUE を指定した列は、
        # SELECT が高速になりました。
        c.execute('''
            CREATE TABLE IF NOT EXISTS datas (
            n INTEGER PRIMARY KEY,
            text TEXT)
            ''')

        # (必要なら) PRIMARY KEY や UNIQUE をつけていない列でも、
        # INDEX を作ったら、その列の SELECT が高速になりました。
        c.execute('CREATE INDEX IF NOT EXISTS d_text ON datas(text)')

        # SQLite DB に、データを追加していきます。
        for (n, text) in src_datas:
            # REPLACE を使用したら、PRIMARY KEY か UNIQUE の列で
            # データが重複したときに、上書きして続行してくれました。
            c.execute('REPLACE INTO datas VALUES (?,?)', (n, text))

            # そうではなくて、上書きせずに、無視して続行したいときは、
            # INSERT OR IGNORE を使用したらできました。
            c.execute('INSERT OR IGNORE INTO datas VALUES (?,?)', (n, text))

            # 重複を検知したいときは、
            # 普通の INSERT を使用したらできました。
            try:
                c.execute('INSERT INTO datas VALUES (?,?)', (n, text))
            except sqlite3.IntegrityError as e:
                print(f'{format_exception_only(type(e), e)[0].rstrip()} {(n, text)}')

        conn.commit()
        print('\nコミットしました。')
    finally:
        c.close()
        print('\nカーソル c を閉じました。')

        conn.close()
        print('接続 conn を閉じました。')

    print('\nend')
    return

if __name__ == '__main__':
    main()

実行結果

データベースを作ったときの、コマンドプロンプトの表示です。

意図した通り、普通の INSERT で追加したときは、PRIMARY KEYUNIQUE の列で『データの重複』が発生したときに、エラーが発生していました。

start

(db_file) F:\project\sample\datas.db

type(conn) <class 'sqlite3.Connection'>
type(c) <class 'sqlite3.Cursor'>

sqlite3.IntegrityError: UNIQUE constraint failed: datas.n (101, 'あいうえお')
sqlite3.IntegrityError: UNIQUE constraint failed: datas.n (102, 'かきくけこ')
sqlite3.IntegrityError: UNIQUE constraint failed: datas.n (103, 'さしすせそ')
sqlite3.IntegrityError: UNIQUE constraint failed: datas.n (104, 'たちつてと')
sqlite3.IntegrityError: UNIQUE constraint failed: datas.n (105, 'なにぬねの')

コミットしました。

カーソル c を閉じました。
接続 conn を閉じました。

end

スクリーンショット

作成したデータベースを、『DB Browser for SQLite』で開いた時のスクリーンショットです。

意図した通り、REPLACE で追加したデータが入っていました。

データを『取得 (SELECT)』するコード例

SQLite データベースから、データを『取得』する Python コード例です。

"""
select_main.py セレクト (SELECT)
SQLite DB から、データを『取得』するコード例です。
"""

import sqlite3

def main():
    print('start\n')

    # SQLite DB のファイルパスを決めます。
    db_file = r'F:\project\sample\datas.db'
    print(f'(db_file) {db_file}\n')

    # SQLite DB に接続します。
    conn = sqlite3.connect(db_file)
    print(f'type(conn) {type(conn)}')

    # カーソルを取得します。
    c = conn.cursor()
    print(f'type(c) {type(c)}\n')
    try:
        print('# SQLite DB から、すべてのデータを取得します。')
        c.execute('SELECT * FROM datas')
        src_datas = c.fetchall()
        for (n, text) in src_datas:
            print(f'n:{n} text:{text}')

        print('\n# "かきくけこ" のデータだけ取得します。')
        c.execute('SELECT * FROM datas WHERE text=?', ('かきくけこ',))
        src_data = c.fetchone()
        if src_data:
            (n, text) = src_data
            print(f'n:{n} text:{text}')

        print('\n# 大量のデータを取得するときは、少しずつ読み込みます。')
        c.execute('SELECT * FROM datas')
        while True:
            # 試しに、2 件ずつ読み込んでみます。
            # 実際には、物理メモリの空きと相談しながら、
            # 1000 件ずつとか、1 万件ずつとか、読み込みます。
            src_datas = c.fetchmany(size=2)
            print(f'\n{len(src_datas)} 件、読み込みました。')

            # 結果が空 (0 件) になっていたら、while 文を終了します。
            if len(src_datas) == 0:
                print('while 文を break しました。')
                break

            # 結果を表示します。
            for (n, text) in src_datas:
                print(f'n:{n} text:{text}')

            # while 文の先頭に戻ります。
    finally:
        c.close()
        print('\nカーソル c を閉じました。')

        conn.close()
        print('接続 conn を閉じました。')

    print('\nend')
    return

if __name__ == '__main__':
    main()

実行結果

SQLite DB から、データを取得した時の、コマンドプロンプトの表示です。

すべてのデータを『一括』で取得することもできましたし、『狙ったデータだけ』を取得することもできました。

また、すべてのデータを、『少しずつ』取得することもできました。

start

(db_file) F:\project\sample\datas.db

type(conn) <class 'sqlite3.Connection'>
type(c) <class 'sqlite3.Cursor'>

# SQLite DB から、すべてのデータを取得します。
n:101 text:あいうえお
n:102 text:かきくけこ
n:103 text:さしすせそ
n:104 text:たちつてと
n:105 text:なにぬねの

# "かきくけこ" のデータだけ取得します。
n:102 text:かきくけこ

# 大量のデータを取得するときは、少しずつ読み込みます。

2 件、読み込みました。
n:101 text:あいうえお
n:102 text:かきくけこ

2 件、読み込みました。
n:103 text:さしすせそ
n:104 text:たちつてと

1 件、読み込みました。
n:105 text:なにぬねの

0 件、読み込みました。
while 文を break しました。

カーソル c を閉じました。
接続 conn を閉じました。

end

データを『削除 (DELETE)』するコード例

SQLite データベースから、データを『削除』する Python コード例です。

ところで、データを削除しても、SQLite DB のファイルサイズは、変化しませんでした。

SQLite DB のファイルサイズを小さくするときは、VACUUM を実行する必要がありました。

やり方ですが、『SQLite DB を VACUUM するコード例』のせつに、Python コード例を書きました。

"""
delete_main.py デリート (DELETE)
SQLite DB から、データを『削除』するコード例です。
"""

import sqlite3

def main():
    print('start\n')

    # SQLite DB のファイルパスを決めます。
    db_file = r'F:\project\sample\datas.db'
    print(f'(db_file) {db_file}\n')

    # SQLite DB に接続します。
    conn = sqlite3.connect(db_file)
    print(f'type(conn) {type(conn)}')

    # カーソルを取得します。
    c = conn.cursor()
    print(f'type(c) {type(c)}\n')
    try:
        print('# SQLite DB から、すべてのデータを取得します。')
        c.execute('SELECT * FROM datas')
        src_datas = c.fetchall()
        for (n, text) in src_datas:
            print(f'n:{n} text:{text}')

        c.execute('DELETE FROM datas WHERE text=?', ('かきくけこ',))
        print('\n# text="かきくけこ" のデータを『削除』しました。')

        c.execute('DELETE FROM datas WHERE n=?', (105,))
        print('\n# n=105 のデータを『削除』しました。')

        conn.commit()
        print('\n# コミットしました。')

        print('\n# もう一度、すべてのデータを取得します。')
        c.execute('SELECT * FROM datas')
        src_datas = c.fetchall()
        for (n, text) in src_datas:
            print(f'n:{n} text:{text}')

        # もし、削除済みの行を DELETE したらどうなるのか?
        # ⇒ 問題無かった。
        c.execute('DELETE FROM datas WHERE text=?', ('かきくけこ',))
        print('\n# 削除済みの行 text="かきくけこ" を DELETE しても、')
        print('# エラーは出ませんでした。')
    finally:
        c.close()
        print('\nカーソル c を閉じました。')

        conn.close()
        print('接続 conn を閉じました。')

    print('\nend')
    return

if __name__ == '__main__':
    main()

実行結果

SQLite DB から、データを削除した時の、コマンドプロンプトの表示です。

意図した通り、狙ったデータを削除することができました。

start

(db_file) F:\project\sample\datas.db

type(conn) <class 'sqlite3.Connection'>
type(c) <class 'sqlite3.Cursor'>

# SQLite DB から、すべてのデータを取得します。
n:101 text:あいうえお
n:102 text:かきくけこ
n:103 text:さしすせそ
n:104 text:たちつてと
n:105 text:なにぬねの

# text="かきくけこ" のデータを『削除』しました。

# n=105 のデータを『削除』しました。

# コミットしました。

# もう一度、すべてのデータを取得します。
n:101 text:あいうえお
n:103 text:さしすせそ
n:104 text:たちつてと

# 削除済みの行 text="かきくけこ" を DELETE しても、
# エラーは出ませんでした。

カーソル c を閉じました。
接続 conn を閉じました。

end

以上です。

タイトルとURLをコピーしました