Python3でsqlite3モジュールを使用する方法
序章
SQLite は、自己完結型のファイルベースのSQLデータベースです。 SQLiteはPythonにバンドルされており、追加のソフトウェアをインストールしなくても、どのPythonアプリケーションでも使用できます。
このチュートリアルでは、Python3のsqlite3モジュールについて説明します。 SQLiteデータベースへの接続を作成し、そのデータベースにテーブルを追加し、そのテーブルにデータを挿入し、そのテーブルのデータを読み取って変更します。
このチュートリアルでは、主に、架空の水族館に魚を追加したり、水族館から魚を削除したりするときに変更する必要のある魚の在庫を扱います。
前提条件
このチュートリアルを最大限に活用するには、Pythonでのプログラミングにある程度精通し、SQLの基本的な背景を理解することをお勧めします。
必要な背景情報については、次のチュートリアルを確認できます。
ステップ1—SQLiteデータベースへの接続を作成する
SQLiteデータベースに接続すると、最終的にコンピューター上のファイルに存在するデータにアクセスします。 SQLiteデータベースは、さまざまな目的に使用できるフル機能のSQLエンジンです。 今のところ、架空の水族館で魚の在庫を追跡するデータベースを検討します。
Pythonを使用してSQLiteデータベースに接続できます sqlite3
モジュール:
import sqlite3
connection = sqlite3.connect("aquarium.db")
import sqlite3
Pythonプログラムにアクセスを許可します sqlite3
モジュール。 The sqlite3.connect()
関数は、ファイルに保持されているSQLiteデータベースとの対話に使用するConnectionオブジェクトを返します aquarium.db
. The aquarium.db
ファイルはによって自動的に作成されます sqlite3.connect()
もしも aquarium.db
私たちのコンピュータにはまだ存在していません。
作成に成功したことを確認できます connection
実行によるオブジェクト:
print(connection.total_changes)
このPythonコードを実行すると、次のような出力が表示されます。
Output0
connection.total_changes
によって変更されたデータベース行の総数です connection
. SQLコマンドをまだ実行していないので、0 total_changes
正しい。
いつでもこのチュートリアルを再開したい場合は、削除することができます aquarium.db
私たちのコンピューターからのファイル。
注:特別な文字列を渡すことで、厳密にメモリに(ファイルではなく)存在するSQLiteデータベースに接続することもできます。 ":memory:"
の中へ sqlite3.connect()
. 例えば、 sqlite3.connect(":memory:")
. A ":memory:"
SQLiteデータベースは、Pythonプログラムが終了するとすぐに消えます。 これは、一時的なサンドボックスでSQLiteで何かを試して、プログラムの終了後にデータを保持する必要がない場合に便利です。
ステップ2—SQLiteデータベースへのデータの追加
これで、 aquarium.db
SQLiteデータベースからデータの挿入と読み取りを開始できます。
SQLデータベースでは、データはテーブルに格納されます。 テーブルは列のセットを定義し、定義された各列のデータを含む0個以上の行を含みます。
次の名前のテーブルを作成します fish
次のデータを追跡します。
名前 | 種族 | タンク番号 |
---|---|---|
サミー | 鮫 | 1 |
ジェイミー | イカ | 7 |
The fish
テーブルはの値を追跡します name
, species
、 と tank_number
水族館の魚ごとに。 2つの例 fish
行が一覧表示されます。 shark
名前付き Sammy
、および1行 cuttlefish
名前付き Jamie
.
これを作成できます fish
SQLiteのテーブルを使用して connection
ステップ1で作成しました。
cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
connection.cursor()
カーソルオブジェクトを返します。 Cursor
オブジェクトを使用すると、SQLiteデータベースにSQLステートメントを送信できます。 cursor.execute()
. The "CREATE TABLE fish ..."
stringは、という名前のテーブルを作成するSQLステートメントです。 fish
前に説明した3つの列を使用します。 name
タイプの TEXT
、タイプの種 TEXT
、 と tank_number
タイプの INTEGER
.
テーブルを作成したので、データの行をテーブルに挿入できます。
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")
私たちは cursor.execute()
2回:サメの列を挿入するために1回 Sammy
タンク内 1
、そして一度イカの列を挿入します Jamie
タンク内 7
. "INSERT INTO fish VALUES ..."
テーブルに行を追加できるSQLステートメントです。
次のセクションでは、SQLを使用します SELECT
挿入したばかりの行を検査するステートメント fish
テーブル。
ステップ3—SQLiteデータベースからのデータの読み取り
ステップ2では、SQLiteテーブルに2つの行を追加しました。 fish
. これらの行は、 SELECT
SQLステートメント:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
このコードを実行すると、次のような出力が表示されます。
Output[('Sammy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]
The cursor.execute()
関数は実行します SELECT
の値を取得するステートメント name
, species
、 と tank_number
の列 fish
テーブル。 fetchall()
のすべての結果を取得します SELECT
声明。 私たちが print(rows)
2つのタプルのリストが表示されます。 各タプルには3つのエントリがあります。 から選択した列ごとに1つのエントリ fish
テーブル。 2つのタプルには、ステップ2で挿入したデータが含まれています。 Sammy
the shark
、および1つのタプル Jamie
the cuttlefish
.
の行を取得したい場合 fish
特定の基準セットに一致するテーブルでは、 WHERE
句:
target_fish_name = "Jamie"
rows = cursor.execute(
"SELECT name, species, tank_number FROM fish WHERE name = ?",
(target_fish_name,),
).fetchall()
print(rows)
これを実行すると、次のような出力が表示されます。
Output[('Jamie', 'cuttlefish', 7)]
前の例と同様に、 cursor.execute(<SQL statement>).fetchall()
のすべての結果を取得できます SELECT
声明。 The WHERE
の節 SELECT
の値が name
は target_fish_name
. 使用していることに注意してください ?
私たちの代わりに target_fish_name
変数に SELECT
声明。 1つの行のみに一致することを期待しており、実際、次の行のみが表示されます。 Jamie
the cuttlefish
戻ってきた。
警告:Python文字列操作を使用してSQLステートメント文字列を動的に作成しないでください。 Python文字列操作を使用してSQLステートメント文字列をアセンブルすると、SQLインジェクション攻撃に対して脆弱になります。 SQLインジェクション攻撃は、データベースに保存されているデータを盗んだり、変更したり、変更したりするために使用される可能性があります。 常に使用してください ?
SQLステートメントのプレースホルダー。Pythonプログラムの値を動的に置き換えます。 値のタプルを2番目の引数としてに渡します Cursor.execute()
値をSQLステートメントにバインドします。 この置換パターンは、こことこのチュートリアルの他の部分でも示されています。
ステップ4—SQLiteデータベースのデータを変更する
SQLiteデータベースの行は、次を使用して変更できます UPDATE
と DELETE
SQLステートメント。
たとえば、サメのサミーがタンク番号2に移動したとします。 サミーの行を変更できます fish
この変更を反映するテーブル:
new_tank_number = 2
moved_fish_name = "Sammy"
cursor.execute(
"UPDATE fish SET tank_number = ? WHERE name = ?",
(new_tank_number, moved_fish_name)
)
発行します UPDATE
変更するSQLステートメント tank_number
の Sammy
その新しい価値に 2
. The WHERE
の節 UPDATE
ステートメントは、の値のみを変更することを保証します tank_number
行が持っている場合 name = "Sammy"
.
次のように実行すると SELECT
ステートメント、更新が正しく行われたことを確認できます。
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
これを実行すると、次のような出力が表示されます。
Output[('Sammy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]
の行に注意してください Sammy
今の値は 2
そのために tank_number
桁。
サメのサミーが野生に放たれ、水族館に収容されなくなったとしましょう。 サミーはもはや水族館に住んでいないので、 Sammy
からの行 fish
テーブル。
発行A DELETE
行を削除するSQLステートメント:
released_fish_name = "Sammy"
cursor.execute(
"DELETE FROM fish WHERE name = ?",
(released_fish_name,)
)
発行します DELETE
行を削除するSQLステートメント Sammy
the shark
. The WHERE
の節 DELETE
ステートメントは、その行に次の場合にのみ行を削除することを保証します name = "Sammy"
.
次のように実行すると SELECT
ステートメント、削除が正しく行われたことを確認できます。
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
このコードを実行すると、次のような出力が表示されます。
Output[('Jamie', 'cuttlefish', 7)]
の行に注意してください Sammy
the shark
今はなくなって、そして Jamie
the cuttlefish
残っています。
ステップ5—使用 with
自動クリーンアップのステートメント
このチュートリアルでは、2つの主要なオブジェクトを使用して "aquarium.db"
SQLiteデータベース:接続オブジェクトという名前 connection
、およびカーソルオブジェクトという名前 cursor
.
Pythonファイルの操作が終了したら、Pythonファイルを閉じる必要があるのと同じように、 Connection
と Cursor
オブジェクトは、不要になったときにも閉じる必要があります。
使用できます with
自動的に閉じるのに役立つステートメント Connection
と Cursor
オブジェクト:
from contextlib import closing
with closing(sqlite3.connect("aquarium.db")) as connection:
with closing(connection.cursor()) as cursor:
rows = cursor.execute("SELECT 1").fetchall()
print(rows)
closing
contextlibモジュールによって提供される便利な関数です。 いつ with
ステートメントが終了し、 closing
を保証します close()
渡されたオブジェクトに対して呼び出されます。 The closing
この例では、関数が2回使用されています。 一度確認する Connection
によって返されるオブジェクト sqlite3.connect()
は自動的に閉じられ、2回目は Cursor
によって返されるオブジェクト connection.cursor()
自動的に閉じられます。
このコードを実行すると、次のような出力が表示されます。
Output[(1,)]
以来 "SELECT 1"
は、値が次の単一の列を持つ単一の行を常に返すSQLステートメントです。 1
、単一のタプルを表示するのは理にかなっています 1
コードによって返される唯一の値として。
結論
The sqlite3
モジュールはPython標準ライブラリの強力な部分です。 追加のソフトウェアをインストールしなくても、フル機能のオンディスクSQLデータベースを操作できます。
このチュートリアルでは、 sqlite3
モジュールを使用してSQLiteデータベースに接続し、そのデータベースにデータを追加し、そのデータベースのデータを読み取って変更します。 その過程で、SQLインジェクション攻撃のリスクと、contextlib.closingを使用して自動的に呼び出す方法についても学びました。 close()
のPythonオブジェクト with
ステートメント。
ここから、 SQLiteとMySQLとPostgreSQL:リレーショナルデータベース管理システムの比較のSQLデータベースについて詳しく知ることができます。