このチュートリアルでは、Python の sqlite3 インタフェースと組み合わせて SQLite を使用することについて説明します。SQLiteは、ほとんどの標準的なPythonのインストールにバンドルされている、単一ファイルのリレーショナルデータベースです。SQLiteはしばしば小規模なアプリケーション、特に携帯電話やタブレット、スマート家電、計測器などの組み込みシステムやデバイスのための技術として選ばれています。しかし、中小規模のWebアプリケーションやデスクトップアプリケーションに使用されているのを耳にすることも珍しくありません。
データベースの作成と接続
新しいSQLiteデータベースを作成するのは、Python標準ライブラリのsqlite3モジュールを使って接続を作成するのと同じくらい簡単です。接続を確立するために必要なことは、sqlite3モジュールの connect(...) メソッドにファイルのパスを渡すことだけです。もしファイルで表されるデータベースが存在しなければ、そのパスでデータベースが作成されます。
import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')
日常的なデータベースプログラミングでは、常にデータベースへの接続を作成することになるでしょう。したがって、この単純な接続文を再利用可能な汎用関数にまとめるのは良い考えです。
# db_utils.py
import os
import sqlite3
# create a default path to connect to and create (if necessary) a database
# called 'database.sqlite3' in the same directory as this script
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')
def db_connect(db_path=DEFAULT_PATH):
con = sqlite3.connect(db_path)
return con
テーブルの作成
データベースのテーブルを作成するためには、格納するデータの構造について知っておく必要があります。リレーショナルデータベースのテーブルを定義するには、多くの設計上の考慮事項があり、それについて書かれた本がたくさんあります。私はこの実践の詳細には立ち入らず、読者のさらなる調査に委ねることにします。
しかし、Pythonを使ったSQLiteデータベースプログラミングの議論を助けるために、本の売り上げに関する以下のデータをすでに収集している架空の書店のためにデータベースを作成する必要があるという前提で作業することにします。
| 顧客│日付│商品│価格│。
| — | — | — | — |
| アラン・チューリング｜1944年2月22日｜組合せ論入門｜7.99｜（日本経済新聞社
| ドナルド・クヌース｜1967年7月3日｜短編小説の書き方｜17.99円
| ドナルド・クヌース｜1967年7月3日｜データ構造とアルゴリズム｜11.99｜（英語版のみ
| エドガー・コッド｜1969年1月12日｜集合論特論｜16.99ドル
このデータを調べてみると、顧客、製品、注文に関する情報が含まれていることがわかる。この種のトランザクションシステムのデータベース設計の一般的なパターンは、注文を2つの追加テーブル、注文と品目（注文詳細と呼ばれることもあります）に分割して、より高い正規化を実現することです。
Python インタープリタで、前に定義した db_utils.py モジュールと同じディレクトリに、customers と products テーブルを作成する SQL を次のように入力します。
>>> from db_utils import db_connect
>>> con = db_connect() # connect to the database
>>> cur = con.cursor() # instantiate a cursor obj
>>> customers_sql = """
... CREATE TABLE customers (
... id integer PRIMARY KEY,
... first_name text NOT NULL,
... last_name text NOT NULL)"""
>>> cur.execute(customers_sql)
>>> products_sql = """
... CREATE TABLE products (
... id integer PRIMARY KEY,
... name text NOT NULL,
... price real NOT NULL)"""
>>> cur.execute(products_sql)
上記のコードでは、接続オブジェクトを作成し、それを使ってカーソル・オブジェクトをインスタンス化しています。カーソルオブジェクトはSQLiteデータベース上でSQL文を実行するために使用されます。
カーソルを作成した後、私は customer テーブルを作成する SQL を書き、プライマリキーと姓名のテキストフィールドを与えて、それを customers_sql という変数に代入しました。そして、カーソルオブジェクトの execute(...) メソッドを呼び出し、変数 customers_sql を渡しました。それから、同じように products テーブルを作成します。
組み込みの SQLite メタデータテーブルである sqlite_master テーブルに問い合わせることで、上記のコマンドが成功したかどうかを確認することができます。
現在接続しているデータベースのすべてのテーブルを見るには、 sqlite_master テーブルの name カラムに問い合わせ、 type を “table” に指定してください。
>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
<sqlite3.cursor 0x104ff7ce0="" at="" object="">
>>> print(cur.fetchall())
[('customers',), ('products',)]
テーブルのスキーマを見るには、同じテーブルの sql カラムにクエリを発行してください。
>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table'
… AND name='customers'""")
<sqlite3.cursor 0x104ff7ce0="" at="" object="">
>>> print(cur.fetchone()[0])
CREATE TABLE customers (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL)
次に定義するテーブルは orders テーブルで、これは外部キーと購入日を介して顧客と注文を関連付けます。SQLite は実際の日付/時刻データ型 (あるいは SQLite の用語と一致するデータクラス) をサポートしていないので、すべての日付はテキスト値として表現されることになります。
>>> orders_sql = """
... CREATE TABLE orders (
... id integer PRIMARY KEY,
... date text NOT NULL,
... customer_id integer,
... FOREIGN KEY (customer_id) REFERENCES customers (id))"""
>>> cur.execute(orders_sql)
最後に定義するテーブルは、各注文に含まれる商品の詳細な会計処理を行う line items テーブルです。
lineitems_sql = """
... CREATE TABLE lineitems (
... id integer PRIMARY KEY,
... quantity integer NOT NULL,
... total real NOT NULL,
... product_id integer,
... order_id integer,
... FOREIGN KEY (product_id) REFERENCES products (id),
... FOREIGN KEY (order_id) REFERENCES orders (id))"""
>>> cur.execute(lineitems_sql)
データの読み込み
このセクションでは、作成したばかりのテーブルにサンプルデータを INSERT する方法を説明します。なぜなら、商品がなければ、販売を行うことができず、したがって、商品と注文を関連付ける外部キーがないからです。サンプルデータを見てみると、4つの商品があることがわかります。
- 組合せ論入門 ($7.99)
- 短編小説の書き方ガイド (17.99ドル)
- データ構造とアルゴリズム (11.99ドル)
- 集合論特論 ($16.99)
INSERT文の実行のワークフローは、簡単に言うと
-
- データベースに接続する
- カーソルオブジェクトを作成する
- パラメータ化された挿入SQL文を書き、変数に格納する
- カーソルオブジェクトのexecuteメソッドを呼び出し、SQL変数とテーブルに挿入される値をタプルとして渡します。
この一般的なアウトラインをもとに、もう少しコードを書いてみましょう。
>>> con = db_connect()
>>> cur = con.cursor()
>>> product_sql = "INSERT INTO products (name, price) VALUES (?, ?)"
>>> cur.execute(product_sql, ('Introduction to Combinatorics', 7.99))
>>> cur.execute(product_sql, ('A Guide to Writing Short Stories', 17.99))
>>> cur.execute(product_sql, ('Data Structures and Algorithms', 11.99))
>>> cur.execute(product_sql, ('Advanced Set Theory', 16.99))
上記のコードはごく当たり前のように見えますが、重要なことがいくつかありますので、少し説明します。insert文は?の部分を除いて標準的なSQL構文に従っています。この ? は、実は「パラメタライズドクエリ」と呼ばれるもののプレースホルダです。
パラメタライズドクエリは、Pythonのsqlite3モジュールのような、現代の高レベルプログラミング言語に対する基本的にすべてのデータベースインターフェイスの重要な機能です。このタイプのクエリは、何度も繰り返されるクエリの効率を向上させるのに役立ちます。おそらくもっと重要なことは、カーソルオブジェクトのexecuteメソッドを呼び出す際に渡される?プレースホルダの代わりになる入力をサニタイズし、SQLインジェクションにつながる悪意のある入力を防ぐことです。以下は、人気ブログxkcd.comに掲載された、SQLインジェクションの危険性を説明するコミックです。
残りのテーブルにデータを入力するために、少し変わったパターンに従います。顧客の姓名と購入日の組み合わせで識別される各注文のワークフローは、次のようになります。
-
- 新しい顧客を customers テーブルに挿入し、その主キー ID を取得する。
- 顧客 ID と購入日を基に注文書を作成し、その主キー ID を取得します。
- 注文の各製品について、その主キー ID を決定し、注文と製品を関連付ける行 項目を作成します。
このように、商品を簡単に検索することができます。SELECT SQL文の仕組みについては、後ほど詳しく説明します。
>>> cur.execute("SELECT id, name, price FROM products")
>>> formatted_result = [f"{id:<5}{name:<35}{price:>5}" for id, name, price in cur.fetchall()]
>>> id, product, price = "Id", "Product", "Price"
>>> print('
'.join([f"{id:<5}{product:<35}{price:>5}"] + formatted_result))
Id Product Price
1 Introduction to Combinatorics 7.99
2 A Guide to Writing Short Stories 17.99
3 Data Structures and Algorithms 11.99
4 Advanced Set Theory 16.99
最初の注文は 1944 年 2 月 22 日、Alan Turing が組合せ論入門を $7.99 で購入したものです。
カーソルオブジェクトの lastrowid フィールドにアクセスして、チューリングさんの主キー ID を決定します。
>>> customer_sql = "INSERT INTO customers (first_name, last_name) VALUES (?, ?)"
>>> cur.execute(customer_sql, ('Alan', 'Turing'))
>>> customer_id = cur.lastrowid
>>> print(customer_id)
1
次に注文項目を作成して、新しい注文IDの値を収集し、チューリングさんが注文した商品と一緒に行項目に関連付けることができます。
>>> order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
>>> date = "1944-02-22" # ISO formatted date
>>> cur.execute(order_sql, (date, customer_id))
>>> order_id = cur.lastrowid
>>> print(order_id)
1
>>> li_sql = """INSERT INTO lineitems
... (order_id, product_id, quantity, total)
... VALUES (?, ?, ?, ?)"""
>>> product_id = 1
>>> cur.execute(li_sql, (order_id, 1, 1, 7.99))
残りのレコードは、ドナルド・クヌースへの注文を除いて、まったく同じようにロードされます。しかし、このようなタスクの繰り返しの性質から、これらの機能を再利用可能な関数にラップする必要性が叫ばれています。db_utils.py モジュールに次のコードを追加してください。
def create_customer(con, first_name, last_name):
sql = """
INSERT INTO customers (first_name, last_name)
VALUES (?, ?)"""
cur = con.cursor()
cur.execute(sql, (first_name, last_name))
return cur.lastrowid
def create_order(con, customer_id, date):
sql = """
INSERT INTO orders (customer_id, date)
VALUES (?, ?)"""
cur = con.cursor()
cur.execute(sql, (customer_id, date))
return cur.lastrowid
def create_lineitem(con, order_id, product_id, qty, total):
sql = """
INSERT INTO lineitems
(order_id, product_id, quantity, total)
VALUES (?, ?, ?, ?)"""
cur = con.cursor()
cur.execute(sql, (order_id, product_id, qty, total))
return cur.lastrowid
これで、効率的に作業できるようになりました。
新しい関数にアクセスできるようにするには、Python インタープリタを exit() して、リロードする必要があります。
>>> from db_utils import db_connect, create_customer, create_order, create_lineitem
>>> con = db_connect()
>>> knuth_id = create_customer(con, 'Donald', 'Knuth')
>>> knuth_order = create_order(con, knuth_id, '1967-07-03')
>>> knuth_li1 = create_lineitem(con, knuth_order, 2, 1, 17.99)
>>> knuth_li2 = create_lineitem(con, knuth_order, 3, 1, 11.99)
>>> codd_id = create_customer(con, 'Edgar', 'Codd')
>>> codd_order = create_order(con, codd_id, '1969-01-12')
>>> codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)
ソフトウェア職人の学生として、もう一つアドバイスしなければならないことがあります。複数のデータベース操作（この場合はINSERT）を行って、実際には1つの累積的なタスク（つまり注文の作成）を達成する場合、サブタスク（顧客、注文、行項目の作成）を1つのデータベーストランザクションにラップして、成功時にコミットするか、途中でエラーが発生した場合にロールバックできるようにするとよいでしょう。
これは次のようなものです。
try:
codd_id = create_customer(con, 'Edgar', 'Codd')
codd_order = create_order(con, codd_id, '1969-01-12')
codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)
# commit the statements
con.commit()
except:
# rollback all database actions since last commit
con.rollback()
raise RuntimeError("Uh oh, an error occurred ...")
このセクションの最後に、データベースの既存のレコードをUPDATEする方法を簡単に説明したいと思います。ここでは、Guide to Writing Short Stories の価格を 10.99 (セール中) に更新してみましょう。
>>> update_sql = "UPDATE products SET price = ? WHERE id = ?"
>>> cur.execute(update_sql, (10.99, 2))
データベースへの問い合わせ
一般的に、データベースに対して行われる最も一般的な操作は、SELECT文によってデータベースに格納されているデータの一部を取得することです。この節では、sqlite3インタフェースを使用して簡単なSELECT問い合わせを実行する方法を示します。
customersテーブルの基本的な複数行の問い合わせを行うには、カーソルオブジェクトの execute(...) メソッドにSELECT文 を渡します。その後、同じカーソルオブジェクトの fetchall() メソッドを呼び出すことで、クエリの結果を反復処理することができます。
>>> cur.execute("SELECT id, first_name, last_name FROM customers")
>>> results = cur.fetchall()
>>> for row in results:
... print(row)
(1, 'Alan', 'Turing')
(2, 'Donald', 'Knuth')
(3, 'Edgar', 'Codd')
その代わりに、データベースから1つのレコードを取得したいとします。例えば、Donald Knuth の ID が 2 であるような、より具体的なクエリを書いて、カーソルオブジェクトの fetchone() メソッドを呼び出すことでこれを実行することができます。
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> print(result)
(2, 'Donald', 'Knuth')
各結果の個々の行がタプルの形式であることがわかりますか？タプルはプログラミングの用途によっては非常に便利なPythonicのデータ構造ですが、多くの人はデータ検索のタスクになると少し邪魔になると感じています。そこで、より柔軟な方法でデータを表現する方法があります。必要なことは、接続オブジェクトの row_factory メソッドを sqlite3.Row のようなより適切なものに設定することだけです。これにより、位置やキーワードの値によって行の個々のアイテムにアクセスできるようになります。
>>> import sqlite3
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> id, first_name, last_name = result['id'], result['first_name'], result['last_name']
>>> print(f"Customer: {first_name} {last_name}'s id is {id}")
Customer: Donald Knuth's id is 2
結論
この記事では、ほとんどのPythonに同梱されている軽量な単一ファイルSQLiteデータベースに対するsqlite3 Pythonインターフェースの最も重要な特徴と機能性について、簡単なデモンストレーションを行ってみました。しかし、データベースプログラミングの複雑さは一般的に企業レベルで最もセキュリティホールが発生しやすいものの1つであり、そのような仕事をする前にさらなる知識が必要であることを新参者に注意しています。
いつものように、私は読んでくださってありがとうございますし、以下のコメントや批判を歓迎します。
。