PythonでSQLiteを操作する方法 – 初心者向けハンドブック

SQLiteは最も人気のある関係データベース管理システム（RDBMS）の一つです。軽量で、システム上で多くのスペースを占めません。

最も良い特徴の一つは、サーバーレスであることです。これは、別のサーバーをインストールもしくは管理する必要がないことを意味します。

代わりに、あなたのコンピュータ上の単一のファイルに全てを保存します。また、設定は不要で、複雑な設定プロセスはありません。これは初心者や小規模のプロジェクトに最適です。

SQLiteは、使い易く、迅速で、より大きなデータベースが行えるほぼすべてのタスクを処理できる一方で、余計なソフトウェアの管理の手間を省くので、中小規模のアプリケーションには理想的な選択です。個人的なプロジェクトを構築するか、新しいアプリのプロトタイピングを行っている場合、SQLiteはすぐに物事を始めるのに優れた選択肢です。

Python環境の設定方法
SQLiteデータベースの作成方法
データベーステーブルの作成方法
データをテーブルに插入する方法
データのクエリ方法
データの更新と削除方法
トランザクションの使用方法
SQLiteのクエリパフォーマンスを索引付けで最適化する方法
エラーと例外を処理する方法
データのエクスポートとインポート [ボーナスセクション]
終わる

このチュートリアルは、データベースの使い方を学ぶ人に最適で、複雑な設定をすることなく始めることができます。

Python環境の設定方法

SQLiteを使う前に、Python環境が準備されていることを確認しましょう。以下は、すべてを設定する方法です。

Pythonのインストール

まだPythonをシステムにインストールしていない場合、公式のPythonウェブサイトから下载できます。OSごとのインストール手順（Windows、macOS、Linux）に従ってください。

Pythonがインストールされていることを確認するには、ターミナル（またはコマンドプレート）を開き、以下のように入力します。

python --version

これはインストールされているPythonの現在のバージョンを表示します。インストールされていない場合は、Pythonウェブサイトの指示に従ってください。

SQLite3モジュールのインストール

良いニュースは、SQLite3はPythonに組み込まれていることです！標準のPythonライブラリに含まれているので、別途インストールする必要はありません。

How to Create a Virtual Environment (Optional but Recommended)

依存関係を整理しておくために、プロジェクトごとに仮想環境を作るのは良いアイデアです。

仮想環境を作成するには、以下のステップに従ってください:

まず、ターミナルかコマンドプロンプトを開き、プロジェクトを作成したいディレクトリに移動します。
仮想環境を作成するために次のコマンドを実行します:

。

python -m venv env

ここで、envは仮想環境の名前です。

Activate the virtual environment:

# Use the command for Windows
env\Scripts\activate

# Use the command for macOS/Linux:
env/bin/activate

Activate the virtual environmentの後、ターミナルのプロンプトが変わり、仮想環境の名前が表示されていることに気づくでしょう。

必要なライブラリのインストール

このプロジェクトにはいくつか追加のライブラリが必要です。具体的には、以下のように使用します。

pandas: これはデータを表形式で処理し、表示するためのオプションのライブラリであり、高度な使用法において有用です。
faker: このライブラリは、随時生成する架空のデータを生成するために使用します。例えば、ランダムな名前や住所など、データベースにテスト用に挿入することができます。

pandasとfakerをインストールするには、以下のコマンドを実行します。

pip install pandas faker

これにより、pandasとfakerが仮想環境にインストールされます。これで、您的環境は設定され、PythonでSQLiteデータベースを作成および管理する準備が整いました。

SQLiteデータベースを作成する方法

データベースは、データを構造化して簡単にアクセス、更新、および組織化する方法を提供する構造化された方法です。これは、シンプルなアプリから複雑なシステムにおいて、効率的に大量のデータを格納するデジタルの分類システムです。データベースは、データをテーブル形式で組織化します。行および列は個別のレコードとその属性を表現するものです。

SQLiteデータベースの仕組み

多くのデータベースシステムとは異なり、SQLiteはサーバーなしのデータベースです。これは、サーバーの設定または管理を必要としないため、軽量で簡単に使用できます。すべてのデータは、コンピュータ上の单純なファイルに格納され、簡単に移動、共有、またはバックアップできます。シンプルさにもかかわらず、SQLiteは多くの一般的なデータベース業務を処理することができる強力な機能を提供しており、モバイルアプリ、嵌入式システム、小さい到大きさのプロジェクトで広く使用されています。

新しいSQLiteデータベースを作成する方法

ここで、Pythonのsqlite3ライブラリを使用して、新しいSQLiteデータベースとその操作方法を学びましょう。

データベースに接続する

sqlite3は既にインストールされているため、Pythonスクリプトでのインポートだけが必要です。新しいデータベースを作成するか既存のデータベースに接続するために、sqlite3.connect()メソッドを使用します。このメソッドはデータベースファイルの名前を引数として取ります。ファイルが存在しない場合、SQLiteは自動的にそれを作成します。

import sqlite3

# SQLiteデータベースに接続する(存在しない場合は作成します)
connection = sqlite3.connect('my_database.db')

この例で、スクリプトと同じディレクトリにmy_database.dbという名前のファイルが作成されます。ファイルが既に存在する場合、SQLiteはそれに接続するだけです。

カーソルを作成する

接続を取得した後、次のステップはカーソルオブジェクトの作成です。カーソルはデータベース上でSQLコマンドとクエリの実行を責任があります。

# カーソルオブジェクトを作成します
cursor = connection.cursor()

接続を閉じる

データベースで作業を終えたら、リソースを解放するために接続を閉じることが重要です。以下のコマンドで接続を閉じることができます:

# データベース接続を閉じる
connection.close()

ただし、すべての操作が完了した後に接続を閉じるべきです。

Pythonスクリプトを実行すると、現在の作業ディレクトリにmy_database.dbというファイルが作成されます。これで最初のSQLiteデータベースが作成されました！

コンテキストマネージャを使用して接続を開くと閉じる方法

Pythonは、withステートメント（コンテキストマネージャとしても知られています）を使用して、データベース接続を効率的かつクリーンに処理する方法を提供しています。withステートメントは接続を自動的に開いて閉じ、データベース操作中にエラーが発生しても接続が適切に閉じられるようにします。これにより、connection.close()を手動で呼び出す必要がなくなります。

データベース接続を処理するためにwithステートメントを使用する方法は次のとおりです:

import sqlite3

# ステップ1: 'with'を使用してデータベースに接続（または作成）し、完了したら自動的に閉じる
with sqlite3.connect('my_database.db') as connection:

    # ステップ2: データベースとやり取りするカーソルオブジェクトを作成
    cursor = connection.cursor()

    print("Database created and connected successfully!")

# connection.close()を呼び出す必要はありません。自動で行われます！

これからは、データベース接続を効率的に管理するために、今後のコード例でwithステートメントを使用します。これにより、コードが簡潔になり、メンテナンスが容易になります。

データベーステーブルの作成方法

SQLiteデータベースを作成して接続したので、次のステップはデータベース内にテーブルを作成することです。テーブルは、データを行（レコード）と列（属性）に整理して保存する場所です。この例では、学生に関する情報を保存するためのStudentsというテーブルを作成し、今後のセクションで再利用します。

テーブルを作成するために、SQLのCREATE TABLEステートメントを使用します。このコマンドは、列名と各列のデータ型を含むテーブルの構造を定義します。

次のフィールドを持つStudentsテーブルを作成するための簡単なSQLコマンドを示します：

id: 各学生の一意の識別子（整数）。
name: 学生の名前（テキスト）。
age: 学生の年齢（整数）。
email: 学生のメールアドレス（テキスト）。

このテーブルを作成するためのSQLコマンドは次のようになります：

CREATE TABLE Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

私はこの文言を翻訳するために、PythonでSQLiteデータベースに接続し、SQL命令を実行する方法を示します。

import sqlite3

# 'with' を使用して、SQLiteデータベースに接続し、完了時に自動的に接続を閉じる
with sqlite3.connect('my_database.db') as connection:

    # カーソルオブジェクトを作成する
    cursor = connection.cursor()

    # Students テーブルを作成するSQL命令を書く
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );
    '''

    # SQL命令を実行する
    cursor.execute(create_table_query)

    # 変更を適用する
    connection.commit()

    # 確認メッセージを表示する
    print("Table 'Students' created successfully!")

IF NOT EXISTS: これは、テーブルが既に存在しない場合にのみテーブルを作成し、テーブルが以前に作成されている場合のエラーを防ぐようにします。
connection.commit(): これは、データベースに変更を適用する

上記のPythonコードを実行すると、my_database.dbデータベースファイルにStudentsテーブルが作成されます。また、端末にテーブルの作成が成功したことを確認するメッセージも表示されます。

もしVisual Studio Codeを使用している場合、SQLite Viewer拡張機能をインストールすることでSQLiteデータベースを表示することができます。

SQLiteおよびPythonへのデータ型のマッピング

SQLiteはいくつかのデータ型をサポートしており、テーブルを定義する際に理解する必要があります。以下は、一般的なSQLiteデータ型とそれらがPython型にどうマップされるかの簡単な概要です：

SQLite Data Type	Description	Python Equivalent
INTEGER	整数	`int`
TEXT	テキスト文字列	`str`
REAL	浮動小数点数	`float`
BLOB	バイナリデータ（画像、ファイルなど）	`bytes`
NULL	値のないことやデータの欠損を表す	`None`

私たちのStudentsテーブルでは以下の通りです：

idはINTEGER型で、Pythonのintにマップされます。
nameとemailはTEXT型で、Pythonのstrにマップされます。
年齢もINTEGER型であり、Pythonのintに対応しています。

テーブルにデータを插入する方法

私たちの生徒テーブルを作成した後、データベースにデータを插入する時間です。この節で、PythonとSQLiteを使用して单一のレコードおよび複数のレコードを插入する方法を説明し、SQL注入など一般的な安全対策を避ける方法について説明します。

单一のレコードを插入する方法

データベースにデータを插入するには、INSERT INTO SQL命令を使用します。まず、私たちの生徒テーブルに单一のレコードを插入しましょう。

单一のレコードを插入するための基本的なSQLスyntaxは以下の通りです:

INSERT INTO Students (name, age, email) 
VALUES ('John Doe', 20, '[email protected]');

しかし、Pythonスクリプトで直接SQLを書いて、硬コードされた値を使用する代わりに、私たちはパラメータ化されたクエリを使用して、コードをより安全で柔軟なものにします。パラメータ化されたクエリはSQL注入を防ぐのを助け、これは悪意のあるユーザーが有害な入力を渡して、SQLクエリを操作することができる一般的な攻撃です。

以下は、パラメータ化されたクエリを使用して生徒テーブルに单一のレコードを插入する方法です。

import sqlite3

# 'with' を使用して自動的に接続を開くと閉じる
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # 学生テーブルにレコードを挿入する
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    student_data = ('Jane Doe', 23, '[email protected]')

    cursor.execute(insert_query, student_data)

    # 変更を自動的にコミットする
    connection.commit()

    # connection.close(); を呼び出す必要はない！ automaticaly!
    print("Record inserted successfully!")

「?」プレースホルダーは、テーブルに挿入する値を表しています。実際の値は、cursor.execute() メソッドに渡されるタプル (student_data) として指定されます。

複数のレコードを插入する方法

一度に複数のレコードを插入したい場合、Python で executemany() メソッドを使用することができます。このメソッドは、各レコードを表すタプルのリストを受け取ります。

私たちの例をより動的にするために、Faker ライブラリを使用して随机な学生データを生成することができます。これはテストと実際の世界のシミュレーションに有用です。

from faker import Faker
import sqlite3

# Faker を初期化する
fake = Faker(['en_IN'])

# 'with' を使用して自動的に接続を開くと閉じる
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # 学生テーブルにレコードを挿入する
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    students_data = [(fake.name(), fake.random_int(
        min=18, max=25), fake.email()) for _ in range(5)]

    # 複数のレコード用のクエリを実行する
    cursor.executemany(insert_query, students_data)

    # 変更をコミットする
    connection.commit()

    # 確認メッセージを印刷する
    print("Fake student records inserted successfully!")

このコードでは:

Faker() は学生のためにランダムな名前、年齢、メールアドレスを生成します。ロケール([‘en_IN’])を渡すことは任意です。
cursor.executemany(): このメソッドを使用すると、複数のレコードを一度に挿入できるため、コードがより効率的になります。
students_data: 各タプルが1人の学生のデータを表すタプルのリスト。

一般的な問題の対処法：SQLインジェクション

SQLインジェクションは、攻撃者が悪意のある入力を提供することでSQLクエリを挿入または操作できるセキュリティ脆弱性です。例えば、攻撃者は'; DROP TABLE Students; --のようなコードを注入してテーブルを削除しようとするかもしれません。

パラメータ化クエリ（上述の例のように）を使用することで、この問題を避けることができます。パラメータ化クエリの?プレースホルダーは、入力値がデータとして扱われ、SQLコマンドの一部として扱われないことを保証します。これにより、悪意のあるコードが実行されることは不可能になります。

データのクエリ方法

データをStudentsテーブルに挿入した後、次にテーブルからデータを取得する方法を学びましょう。Pythonでデータを取得する異なる方法を探検します。これにはfetchone()、fetchall()、およびfetchmany()が含まれます。

テーブルからデータを照会するためには、SELECT文を使用します。Studentsテーブルの全ての列を選択する単純なSQLコマンドは以下の通りです：

SELECT * FROM Students;

このコマンドはStudentsテーブルの全てのレコードと列を取得します。これをPythonで実行し、結果を取得することができます。

全てのレコードを取得する方法

以下のようにStudentsテーブルから全てのレコードを取得することができます：

import sqlite3

# SQLiteデータベースに接続するために'with'を使用
with sqlite3.connect('my_database.db') as connection:

    # カーソルオブジェクトを作成
    cursor = connection.cursor()

    # Studentsテーブルから全てのレコードを選択するSQLコマンドを記述
    select_query = "SELECT * FROM Students;"

    # SQLコマンドを実行
    cursor.execute(select_query)

    # 全てのレコードを取得
    all_students = cursor.fetchall()

    # 結果をターミナルに表示
    print("All Students:")
    for student in all_students:
        print(student)

この例では、fetchall()メソッドはクエリによって返される全ての行をタプルのリストとして取得します。

All Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
(4, 'Hemani Shukla', 18, '[email protected]')
(5, 'Warda Kara', 20, '[email protected]')
(6, 'Mitali Nazareth', 19, '[email protected]')

単一のレコードを取得する方法

1つのレコードだけを取得したい場合、fetchone()メソッドを使用できます。

import sqlite3

SQLiteデータベースに接続するには 'with' を使用してください
with sqlite3.connect('my_database.db') as connection:

    カーソルオブジェクトを作成します
    cursor = connection.cursor()

    Studentsテーブルから全てのレコードを選択するSQLコマンドを書きます
    select_query = "SELECT * FROM Students;"

    SQLコマンドを実行します
    cursor.execute(select_query)

    1つのレコードを取得します
    student = cursor.fetchone()

    結果を表示します
    print("First Student:")
    print(student)

出力:

First Student:
(1, 'Jane Doe', 23, '[email protected]')

複数のレコードを取得する方法

特定の数のレコードを取得するには、fetchmany(size)を使用できます:

import sqlite3

SQLiteデータベースに接続するには 'with' を使用します
with sqlite3.connect('my_database.db') as connection:

    カーソルオブジェクトを作成します
    cursor = connection.cursor()

    Studentsテーブルから全てのレコードを選択するSQLコマンドを書きます
    select_query = "SELECT * FROM Students;"

    SQLコマンドを実行します
    cursor.execute(select_query)

    3つのレコードを取得します
    three_students = cursor.fetchmany(3)

    結果を表示します
    print("Three Students:")
    for student in three_students:
        print(student)

出力:

Three Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')

`pandas`を使用したより良いデータ表示方法

より良いデータ表示のために、pandasライブラリを使用して、クエリ結果からDataFrameを作成できます。これにより、データの操作と可視化が容易になります。

以下は、全てのレコードを取得して、pandas DataFrameとして表示する方法です。

import sqlite3
import pandas as pd

# 'with'を使用してSQLiteデータベースに接続する
with sqlite3.connect('my_database.db') as connection:
    # Studentsテーブルからすべてのレコードを選択するSQLコマンドを書く
    select_query = "SELECT * FROM Students;"

    # pandasを使用してSQLクエリを直接DataFrameに読み取る
    df = pd.read_sql_query(select_query, connection)

# DataFrameを表示する
print("All Students as DataFrame:")
print(df)

Output:

All Students as DataFrame:
   id                  name  age                        email
0   1              Jane Doe   23             [email protected]
1   2  Bahadurjit Sabharwal   18  [email protected]
2   3           Zayyan Arya   20  [email protected]
3   4         Hemani Shukla   18    [email protected]
4   5            Warda Kara   20           [email protected]
5   6       Mitali Nazareth   19          [email protected]

`pd.read_sql_query()`関数はSQLクエリを実行し、直接結果をpandas DataFrameとして返す。

データの更新と削除

この節で、Python内でSQLコマンドを使用してStudentsテーブルの既存レコードを更新し、レコードを削除する方法を学びます。これは、データの管理と維持を効果的に行うために重要です。

既存のレコードの更新

データベース内の既存レコードを変更するには、SQLのUPDATEコマンドを使用します。このコマンドは、特定の条件に基づいて1行または複数行の特定の列の値を変更することができます。

例えば、学生の年齢を更新したい場合、SQLコマンドは以下のようになります:

UPDATE Students 
SET age = 21 
WHERE name = 'Jane Doe';

次に、Pythonコードを使用してStudentsテーブルに特定の学生の年齢を更新しましょう。

import sqlite3

SQLiteデータベースに接続するために 'with' を使用する
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    学生の年齢を更新するSQLコマンド
    update_query = '''
    UPDATE Students 
    SET age = ? 
    WHERE name = ?;
    '''

    更新するデータ
    new_age = 21
    student_name = 'Jane Doe'

    データとともにSQLコマンドを実行する
    cursor.execute(update_query, (new_age, student_name))

    更新を保存するために変更をコミットする
    connection.commit()

    確認メッセージを印刷する
    print(f"Updated age for {student_name} to {new_age}.")

この例では、SQL注入を防ぐためにパラメータ化されたクエリを使用しました。

テーブルからレコードを削除する方法

データベースからレコードを削除するには、SQLのDELETEコマンドを使用します。このコマンドを使用すると、指定された条件に基づいて1行以上の行を削除できます。

たとえば、’Jane Doe’という学生を削除したい場合、SQLコマンドは以下のようになります。

DELETE FROM Students 
WHERE name = 'Jane Doe';

withステートメントを使用して、Studentsテーブルから特定の学生を削除するPythonコードを書いてみましょう。

import sqlite3

'with'を使用してSQLiteデータベースに接続する
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    学生を削除するSQLコマンド
    delete_query = '''
    DELETE FROM Students 
    WHERE name = ?;
    '''

    削除される学生の名前
    student_name = 'Jane Doe'

    データとともにSQLコマンドを実行する
    cursor.execute(delete_query, (student_name,))

    削除を保存するために変更をコミットする
    connection.commit()

    確認メッセージを印刷する
    print(f"Deleted student record for {student_name}.")

重要な考慮事項

条件: 更新または削除レコード時にWHERE句を常に使用して、テーブル内のすべての行を修正または削除するのを避けます。WHERE句を使用しない場合、その命令はテーブル内のすべての行に影響を与えます。
バックアップ: プロダクション環境において特に、更新または削除を行う前にデータベースのバックアップを取るのは良い慣習です。

トランザクションの使用方法

トランザクションは、一つまたは複数のSQL操作のシーケンスを単一の作业単位として处理します。データベースのコンTEXTでは、トランザクションによって複数の操作がすべて成功するか、すべて失敗するかの場合があります。これにより、データベースはエラーや予期しない問題に直面しても一致性を保ちます。

例えば、二つの銀行口座間でお金を移転する場合、口座Aからの支出と口座Bへのクレジットが一緒に成功または失敗することを望んでいます。操作の一つが失敗した場合、他の操作は実行しないで一致性を维持するべきです。

トランザクションを使用する理由？

原子性: トランザクションは、一連の操作が単一のユニットとして扱われることを保証します。1つの操作が失敗すると、その操作はデータベースに適用されません。
一貫性: トランザクションは、すべてのルールと制約が遵守されることにより、データベースの整合性を維持するのに役立ちます。
分離性: 各トランザクションは他と独立して操作し、意図しない干渉を防ぎます。
耐久性: トランザクションがコミットされると、変更はシステム障害の場合でも永続的です。

トランザクションを使用するタイミング?

トランザクションを使用するべきタイミングは次のときです:

成功または失敗が一緒になる複数の関連操作を実行する場合。
整合性と完全性が必要な重要なデータを変更する場合。
金融取引やデータ移行など、失敗する可能性のある操作を行います。

Pythonでのトランザクション管理方法

SQLiteでは、トランザクションを管理するためにBEGIN、COMMIT、およびROLLBACKコマンドを使用します。しかし、Pythonでsqlite3モジュールを使用する場合、通常は接続オブジェクトを通じてトランザクションを管理します。

トランザクションの開始

トランザクションは、SQL文を実行する際に暗黙的に開始されます。明示的にトランザクションを開始するには、BEGINコマンドを使用します。

cursor.execute("BEGIN;")

しかし、通常は手動でトランザクションを開始する必要はありません。SQLiteはSQL文を実行すると自動的にトランザクションを開始します。

トランザクションのコミット方法

トランザクション中に行われたすべての変更を保存するには、commit()メソッドを使用します。これにより、データベース内のすべての変更が恒久的になります。

connection.commit()

上記の例で既にcommit()メソッドを使用しました。

トランザクションのロールバック

何か問題が発生し、トランザクション中に行われた変更を元に戻したい場合は、rollback()メソッドを使用します。これにより、トランザクション開始以降のすべての変更が元に戻ります。

connection.rollback()

Pythonでのトランザクション使用例

実際のシーンでトランザクションの使用を説明するために、新しいテーブルを作成し、顧客アカウントを管理するCustomersと名付けます。この例では、各顧客にbalanceがあると仮定します。このテーブルに2人の顧客を追加し、彼らの間で资金の振り分け操作を行いましょう。

まず、Customersテーブルを作成し、2人の顧客を追加しましょう。

import sqlite3

#Customersテーブルを作成し、2人の顧客を追加する
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    #Customersテーブルを作成する
    create_customers_table = '''
    CREATE TABLE IF NOT EXISTS Customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        balance REAL NOT NULL
    );
    '''
    cursor.execute(create_customers_table)

    #2人の顧客を追加する
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))

    connection.commit()

次に、AshutoshとKrishnaの間で资金の振り分け操作を行いましょう。

import sqlite3


def transfer_funds(from_customer, to_customer, amount):
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        try:
            #トランザクションを開始する
            cursor.execute("BEGIN;")

            #送信者から金额を引く
            cursor.execute(
                "UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
            #受信者に金额を加える
            cursor.execute(
                "UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))

            #変更をコミットする
            connection.commit()
            print(
                f"Transferred {amount} from {from_customer} to {to_customer}.")

        except Exception as e:
            #エラーが発生した場合、トランザクションを取り消す
            connection.rollback()
            print(f"Transaction failed: {e}")


#使用例
transfer_funds('Ashutosh', 'Krishna', 80.0)

この例では、まずCustomersテーブルを作成し、AshutoshとKrishnaの2人の顧客を插入しました。Ashutoshには100の余金、Krishnaには50の余金がありました。その後、AshutoshからKrishnaに80の資金を移転しました。トランザクションを使用することで、Ashutoshのアカウントからの debitingとKrishnaのアカウントへのcreditingが单一の原子操作として実行され、エラーが発生した場合でもデータの Integrityを維持できます。转账が失敗した場合（たとえば余金が足りない場合）、トランザクションは回滚され、両方のアカウントが変更されません。

SQLiteのクエリパフォーマンスをインデックスを使用して最適化する方法

インデックスは、データベース内でクエリパフォーマンスを向上させる強力な技術です。インデックスは特定の列値に基づいて行の位置を保存するデータ構造に似ています。本の最後にある索引は、素早く特定のトピックを探すことができます。

インデックスなしで、SQLiteは全ての行をテーブルについて順番にスキャンする必要があり、データセットが成長するにつれて效率が低下することがあります。インデックスを使用すると、SQLiteは必要な行に直接飛び込むことができ、クエリ実行の速度を大幅に上昇させます。

伪のデータをデータベースに追加する方法

索引を使用する影響を効果的にテストするために、大規模なデータセットが必要です。手動でレコードを追加する代わりに、fakerライブラリを使用して素早く伪のデータを生成することができます。この節では、10,000の伪のレコードを生成し、Studentsテーブルに插入します。これはデータベースが大きくなり、クエリパフォーマンスが重要になる実際の世界のシナリオをシミュレートします。

以下のように、executemany() メソッドを使用してレコードを插入します。

import sqlite3
from faker import Faker

# Fakerライブラリを初期化
fake = Faker(['en_IN'])


def insert_fake_students(num_records):
    """Generate and insert fake student data into the Students table."""
    fake_data = [(fake.name(), fake.random_int(min=18, max=25),
                  fake.email()) for _ in range(num_records)]

    # データベース接続を処理するために 'with' を使用
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # 伪のデータをStudentsテーブルに插入
        cursor.executemany('''
        INSERT INTO Students (name, age, email) 
        VALUES (?, ?, ?);
        ''', fake_data)

        connection.commit()

    print(f"{num_records} fake student records inserted successfully.")


# 10,000の伪のレコードをStudentsテーブルに插入
insert_fake_students(10000)

このスクリプトを実行すると、10,000の伪の学生レコードがStudentsテーブルに追加されます。次の節で、データベースをクエリーし、インデックスを持った場合と持たない場合の性能を比較します。

インデックスを使用しないクエリー方法

この節で、Studentsテーブルにインデックスを使用せずにクエリーし、SQLiteが何もOptimizationを行わない場合のパフォーマンスを観察します。これは後でインデックスを追加した場合と比較する基準になります。

インデックスを使用しない場合、SQLiteは全テーブルスキャンを行います。つまり、一致する結果を見つけるためにテーブルのすべての行を確認する必要があります。小さなデータセットにとってこれは耐えることができますが、レコード数が増えるにつれて、搜索に必要な時間は劇的に増加します。特定の学生の名前を持つ学生を探すための基本的なSELECTクエリーを実行し、Pythonのtimeモジュールを使用してクエリの実行時間を記録して性能を測定してみましょう。

まず、Studentsテーブ尔に特定の名前の学生を探すクエリーを実行します。Pythonのtimeモジュールを使用してクエリの実行時間を記録します。

import sqlite3
import time


def query_without_index(search_name):
    """Query the Students table by name without an index and measure the time taken."""

    データベースに 'with' を使用して接続する
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        開始時間を計測する
        start_time = time.perf_counter_ns()

        名前を指定して生徒を探すSELECTクエリを実行する
        cursor.execute('''
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        全結果を取得する（実際には1つか数個だけ）
        results = cursor.fetchall()

        終了時間を計測する
        end_time = time.perf_counter_ns()

        かかった総時間を計算する
        elapsed_time = (end_time - start_time) / 1000

        結果とかかった時間を表示する
        print(f"Query completed in {elapsed_time:.5f} microseconds.")
        print("Results:", results)


例：名前を指定して生徒を検索
query_without_index('Ojasvi Dhawan')

以下が出力結果：

Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]

上記のスクリプトを実行することで、インデックスを持たないStudentsテーブルを検索する時間がわかります。たとえば、テーブル内に10,000のレコードがある場合、テーブルのサイズとハードウェアによっては1000-2000マイクロ秒かかることがあります。小さなデータセットに対してはあまり遅くはないかもしれませんが、レコードが追加されるにつれてパフォーマンスは劣化します。

time.perf_counter_ns()を使用してクエリ実行にかかった時間をナノ秒単位で計測します。このメソッドは小さい時間間隔のベンチマークには非常に正確です。読みやすいようにマイクロ秒(us)に変換します。

クエリプランの紹介

データベースを操作する際に、クエリの実行方法を理解することが性能の瓶颈を特定し、コードを最適化するために役立つ。SQLiteは、これを行うための有用なツールEXPLAIN QUERY PLANを提供しています。これを使用することで、SQLiteがデータを取得する際の手順を分析することができます。

この節では、EXPLAIN QUERY PLANを使用して、クエリの内部工作机制を視覚化し、理解する方法を紹介します。特に、索引が存在しない場合、SQLiteが完全なテーブルスキャンを行います。

ここで、EXPLAIN QUERY PLANを使用して、Studentsテーブルから索引を持たない場合にSQLiteがデータを取得する方法を見ます。学生の名前に基づいて名学生を搜索し、クエリ計画は、一致する行を見つけるSQLiteの手順を揭示します。

import sqlite3


def explain_query(search_name):
    """Explain the query execution plan for a SELECT query without an index."""

    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # EXPLAIN QUERY PLANを使用して、クエリの実行方法を分析する
        cursor.execute('''
        EXPLAIN QUERY PLAN
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # クエリ計画を取得し、表示する
        query_plan = cursor.fetchall()

        print("Query Plan:")
        for step in query_plan:
            print(step)


# 例: 名前に基づくクエリ計画の分析
explain_query('Ojasvi Dhawan')

このコードを実行すると、SQLiteはクエリを実行する方法の詳細を返します。以下は、出力の例です。

Query Plan:
(2, 0, 0, 'SCAN Students')

これは、SQLiteが提供された値に一致するStudentsテーブルの全ての行をスキャンする必要があること（完全なテーブルスキャン）を示しています。name列に索引がありませんので、SQLiteはテーブルの各行を確認する必要があります。

索引の作成方法

Creating an index on a column allows SQLite to find rows more quickly during query operations. Instead of scanning the entire table, SQLite can use the index to jump directly to the relevant rows, significantly speeding up queries—especially those involving large datasets.

To create an index, use the following SQL command:

CREATE INDEX IF NOT EXISTS index-name ON table (column(s));

この例では、Studentsテーブルのname列にインデックスを作成します。以下がその方法です（Pythonを使用）：

import sqlite3
import time


def create_index():
    """Create an index on the name column of the Students table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # name列にインデックスを作成するSQLコマンド
        create_index_query = '''
        CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
        '''

        # 開始時間の計測
        start_time = time.perf_counter_ns()

        # SQLコマンドを実行してインデックスを作成
        cursor.execute(create_index_query)

        # 開始時間の計測
        end_time = time.perf_counter_ns()

        # 変更をコミット
        connection.commit()

        print("Index on 'name' column created successfully!")

        # 計算した合計時間
        elapsed_time = (end_time - start_time) / 1000

        # 結果と所要時間の表示
        print(f"Query completed in {elapsed_time:.5f} microseconds.")


# インデックス作成の関数を呼び出す
create_index()

Output:

Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.

インデックスの作成にはこれらの時間（102768.6マイクロ秒）がかかりますが、これは一度の操作です。複数のクエリを実行する際には、大幅な速度向上が得られます。以下のセクションでは、このインデックスによるパフォーマンス改善を観察するために、データベースに再度クエリを投げます。

How to Query with Indexes

この節では、先ほど実行した同じSELECTクエリを行いますが、ここではStudentsテーブルのname列に作成した索引を利用します。索引による性能改善を観察するため、実行時間を計測と記録します。

import sqlite3
import time


def query_with_index(student_name):
    """Query the Students table using an index on the name column."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQLコマンド：名前に基づいて学生を選択する
        select_query = 'SELECT * FROM Students WHERE name = ?;'

        # 実行時間を計測する
        start_time = time.perf_counter_ns()  # タイマーを開始する

        # 与えられた学生名でクエリを実行する
        cursor.execute(select_query, (student_name,))
        result = cursor.fetchall()  # すべての結果を取得する

        end_time = time.perf_counter_ns()  # タイマーを終了する

        # 経過時間（ミクロ秒）を計算する
        execution_time = (end_time - start_time) / 1000

        # 結果と実行時間を表示する
        print(f"Query result: {result}")
        print(f"Execution time with index: {execution_time:.5f} microseconds")


# 例：名前に基づいて学生を探索する
query_with_index('Ojasvi Dhawan')

出力内容は以下の通りです：

Query result: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
Execution time with index: 390.70000 microseconds

索引を持っていない場合と比べて、実行時間が显著に短縮されることがわかります。

次に、Studentsテーブルのname列に索引idx_nameがあるクエリの実行計画を分析しましょう。同じスクリプトを再実行すると、以下の出力が得られます：

Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')

現在の計画では、クエリが索引idx_nameを使用しており、スキャンする必要のある行数を大幅に減らしています。これにより、より速いクエリ実行が実現します。

パフォーマンス結果の比較

では、インデックスを伴う場合と無しの場合に得られたパフォーマンス結果を要約しましょう。

実行時間の比較

クエリータイプ	実行時間（マイクロ秒）
インデックスなし	1578.1
インデックスあり	390.7

パフォーマンス改善の要約

インデックスを持つクエリーは、インデックスを持たないクエリーに比べて約4.04倍速いです。
インデックスを追加後、実行時間は約75.24%改善されました。

インデックス使用のベストプラクティス

インデックスはSQLiteデータベースのパフォーマンスを大幅に向上させることができますが、それらは慎重に使用する必要があります。以下は、インデックスを使用する際に考慮するベストプラクティスです：

インデックスの使用時期と理由

频繁なクエリーカラム： SELECTクエリーでよく使われるカラムにインデックスを使用します。特に、WHERE、JOIN、ORDER BY節で使われるカラムにはインデックスを付けることができます。これは、これらのカラムにインデックスを付けることで、クエリーの実行時間を劇的に短縮するからです。
ユニーク性約束: ユーザー名や電子メールアドレスなど、ユニークな値を保持する必要がある列がある場合、インデックスの作成によりこの約束を効率的に強制することができます。
大きなデータセット: レコード数が多いテーブルにとって、インデックスは益が出ます。インデックスを使用することで、より迅速な探索が可能で、データの成長に伴ってパフォーマンスを維持するために重要です。
複合索引: 複数の列を基にするSELECTクエリによるフィルタリングやソートを行う場合、複合索引を考慮すること。たとえば、よく生徒の名前と年齢の両方で探索を行う場合、両方の列に索引を作成することで、このようなクエリを最適化することができます。

インデックスの潜在的なデメリット

インデックスは大きな利点を提供しますが、いくつかの潜在的なデメリットがあります：

挿入/更新操作の遅延: インデックスを持つテーブルに記録を挿入または更新する場合、SQLiteはインデックスも更新する必要があり、これにより操作が遅くなることがあります。これは、各挿入や更新にはインデックス構造を维持するための追加のオーバーヘッドが必要だからです。
ストレージ要件の増加: インデックスは追加のディスクスペースを消費します。大きなテーブルの場合、ストレージコストはかなりなりうるです。特にストレージ資源が限られたシステム向けのデータベーススキーマ設計を行う際に、これを考慮する必要があります。
複雑な索引管理: 索引の数が多すぎるとデータベース管理が複雑化する可能性があり、重複した索引を持つことがあり、性能を向上させる代わりに性能を劣化させる可能性があります。索引を定期的に評価し、最適化するのは良い慣習です。

索引はデータベースクエリを最適化する強力なツールですが、慎重に考慮する必要があります。読み取り性能の改善と書き込み操作の潜在的なオーバーヘッドの間のバランスをとることが鍵です。これを実現するためのいくつかの戦略があります:

クエリの性能を監視する: SQLiteのEXPLAIN QUERY PLANを使用して、索引を使用している場合と使用していない場合のクエリのパフォーマンスを分析します。これは、どの索引が有効であり、どの索引が不要であるかを特定するのを助けることができます。
定期的なメンテナンス: 定期的に索引を評価し、まだ必要であるかを確認します。重複したまたは稀に使用される索引を削除し、データベース操作を流暢にすることができます。
テストと評価: プロダクション環境で索引を実施する前に、読み書き操作にそれが及ぼす影響を理解するために十分なテストを行います。

これらのベストプracticeに従って、索引の利点を利用することができ、潜在的な欠点を最小限に抑えることができ、最終的にはSQLiteデータベースのパフォーマンスと効率を向上させます。

エラーと例外の処理方法

この節で、PythonでSQLiteを使用している際のエラーと例外の処理方法について話します。正しいエラー処理は、データベースの Integrityを保ち、アプリケーションが予期した動作を行うことが重要です。

SQLite操作中の一般的なエラー

SQLiteデータベースとやり取りする際には、いくつかの一般的なエラーが発生し得ます。

制約違反: これは、データベースの制約を違反しているデータを挿入または更新しようとしたときに発生します。たとえば、重複した主キーを挿入しようとすると誤りが発生します。
データ型不整合: 正しくないデータ型のデータを挿入しようとすると(たとえば、数値を期待されている場所に文字列を挿入するなど)エラーが発生する可能性があります。
データベースロックエラー: 別のプロセスや接続による書き込み中のデータベースにアクセスしようとすると、”データベースがロックされています”というエラーが発生します。
構文エラー: SQLの構文ミスは、コマンドを実行しようとしたときにエラーになります。

Pythonの例外処理の使い方

Pythonの内蔵例外処理機構（tryとexcept）は、SQLite操作中のエラー管理に欠かせない。これらの構文を使用することで、プログラムを壊してしまわない限り、例外を捕まえて適切に対応できます。

以下は、データベースにデータを插入する際のエラー処理の基本的な例です：

import sqlite3


def add_customer_with_error_handling(name, balance):
    """Add a new customer with error handling."""
    try:
        with sqlite3.connect('my_database.db') as connection:
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
            connection.commit()
            print(f"Added customer: {name} with balance: {balance}")

    except sqlite3.IntegrityError as e:
        print(f"Error: Integrity constraint violated - {e}")

    except sqlite3.OperationalError as e:
        print(f"Error: Operational issue - {e}")

    except Exception as e:
        print(f"An unexpected error occurred: {e}")


# 例の使用
add_customer_with_error_handling('Vishakha', 100.0)  # 有効
add_customer_with_error_handling('Vishakha', 150.0)  # 重複のエントリー

この例では：

我们捕获IntegrityError，该异常用于诸如唯一性约束违反之类的违规行为。
我们捕获OperationalError，用于处理一般数据库相关问题（如数据库锁定错误）。
我们还具有一个通用的except块，用于处理任何意外的异常。

出力：

Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name

データベースの整合性を保証するベストプractices

トランザクションの使用: 複数の関連する操作を行う場合は常にトランザクションを使用します（前のセクションで説明しました）。これにより、すべての操作が成功するかどうか、または成功しないかどうかが保証され、一貫性が保たれます。
入力データの検証: SQLコマンドを実行する前に、入力データを検証して期待される基準（例：正しい型、許容範囲内）を満たしているかどうかを確認します。
特定の例外のキャッチ: 異なるタイプのエラーを適切に処理するために常に特定の例外をキャッチします。これにより、エラーハンドリングとデバッグがより明確になります。
エラーのログ記録: エラーを単にコンソールに出力するのではなく、ファイルや監視システムにログ記録することを検討してください。これにより、本番環境での問題の追跡が容易になります。
優雅な劣化: エラーを優雅に処理するようにアプリケーションを設計します。操作が失敗した場合、アプリケーションをクラッシュさせるのではなく、ユーザーに意味のあるフィードバックを提供します。
定期的なデータバックアップ: クリティカルな障害やデータの破損の場合にデータ損失を防ぐために、定期的にデータベースのバックアップを取ります。
プリペアドステートメントの使用: プリペアドステートメントはSQLインジェクション攻撃を防ぐのに役立ち、繰り返しのクエリに対してもより良いパフォーマンスを提供できます。

データのエクスポートとインポート方法 [ボーナスセクション]

このセクションでは、SQLiteデータベースからCSVやJSONなどの一般的な形式にデータをエクスポートし、Pythonを使用してこれらの形式からSQLiteにデータをインポートする方法を学びます。データ共有、バックアップ、他のアプリケーションとの統合に役立ちます。

SQLiteからCSVへのデータのエクスポート

Pythonの組込みライブラリを使用して、CSV（カンマ区切り値）ファイルへのデータエクスポートは簡単です。CSVファイルはデータの保存と交換に広く使用されており、データをエクスポートする便利な形式となっています。

以下は、SQLite テーブルからCSV ファイルにデータをエクポートする方法です。

import sqlite3
import csv

def export_to_csv(file_name):
    """Export data from the Customers table to a CSV file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # すべての顧客データを取得するためのクエリを実行
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # データをCSVに書き込む
        with open(file_name, 'w', newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(['ID', 'Name', 'Balance'])  # ヘッダーを書き込む
            csv_writer.writerows(customers)  # データ行を書き込む

        print(f"Data exported successfully to {file_name}.")

# 例の使用方法
export_to_csv('customers.csv')

JSONにデータをエクポートする方法

同様に、JSON (JavaScript Object Notation) ファイルにデータをエクポートすることができます。これは、特にWeb 应用程序でデータ変換のために人気のある形式です。

以下は、データをJSONにエクポートする例です。

import json
import sqlite3


def export_to_json(file_name):
    """Export data from the Customers table to a JSON file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # すべての顧客データを取得するためのクエリを実行
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # データを辞書のリストに変換する
        customers_list = [{'ID': customer[0], 'Name': customer[1],
                           'Balance': customer[2]} for customer in customers]

        # データをJSONに書き込む
        with open(file_name, 'w') as json_file:
            json.dump(customers_list, json_file, indent=4)

        print(f"Data exported successfully to {file_name}.")


# 例の使用方法
export_to_json('customers.json')

CSVからSQLiteにデータをインポートする方法

CSV ファイルからSQLite データベースにデータをインポートすることもできます。これは、既存のデータセットをデータベースに配置するために有用です。

以下は、CSV ファイルからデータをインポートする方法です。

import csv
import sqlite3


def import_from_csv(file_name):
    """Import data from a CSV file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # 読み込むためにCSVファイルを開く
        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # ヘッダー行をスキップする

            # Customersテーブルに各行を挿入する
            for row in csv_reader:
                cursor.execute(
                    "INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# 使用例
import_from_csv('customer_data.csv')

JSONからSQLiteにデータをインポートする方法

同様に、JSONファイルからデータをインポートするのは簡単です。JSONファイルを読み込み、SQLiteテーブルにデータを挿入することができます。

import json
import sqlite3


def import_from_json(file_name):
    """Import data from a JSON file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # 読み込むためにJSONファイルを開く
        with open(file_name, 'r') as json_file:
            customers_list = json.load(json_file)

            # Customersテーブルに各顧客を挿入する
            for customer in customers_list:
                cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# 使用例
import_from_json('customer_data.json')

まとめ

以上です！このガイドでは、PythonでSQLiteを扱うための基本を紹介し、環境のセットアップからクエリやデータの操作、情報のエクスポートやインポートまでをカバーしました。

さて、新しい知識を実践する時が来ました！SQLiteとPythonを使ってプロジェクトを作ることをお勧めします。

プロジェクトが完成したら、Twitterでシェアして、私にタグをつけてください！あなたが作ったものを見て、あなたの功績を称えたいと思います。

このチュートリアルのすべてのコードはGitHubにあります。これに跟隨していただき、ありがとうございます。

freeCodeCampの記事に免费的なTOC Generatorツールを使用して目次を生成します。