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