こんにちは。野中やすおです。
今回は、前回に引き続きSQLAlchemyでデータベースを操作をしてみます。 前回の「今更ながらPythonを使ってsqliteでデータベースを操作してみる」の記事は以下をご覧ください。
こんにちは。野中やすおです。 最近ようやく選挙が終わったのですが、ブランクがだいぶ空き、さっぱりコーディングを忘れてしまっているのでリハビリがてらPythonを使って、sqliteでデータベースを操作をしてみます。 […]
今回の記事では、まずはCREATE、INSERT、UPDATE、DELETE、SELECTを順にメモしてます。
また今回は、customersテーブルに加えて、itemテーブル、purchases_detailsテーブルも追加してみました。
CREATE TABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
from sqlalchemy import ( Table, Column, Integer, String, MetaData, create_engine, ForeignKey, DateTime, ) import os main_path = os.path.dirname(os.path.abspath(__file__)) os.chdir(main_path) engine = create_engine( "sqlite:///test.db", echo=True ) # 接続するデータベース用エンジンを作成 echo = True にしておくと 発行されるSQLがログで出力される meta = MetaData(engine) # customersテーブルを定義 customers = Table( "customers", meta, Column("customer_id", String, primary_key=True), Column("customer_name", String), Column("customer_unit_price", Integer), ) # itemsテーブルを定義 items = Table( "items", meta, Column("item_id", String, primary_key=True), Column("item_name", String), Column("item_price", Integer), ) # purchasesテーブルを定義 purchases = Table( "purchases", meta, Column("purchase_id", Integer, primary_key=True, autoincrement=True), Column("item_id", String, ForeignKey("customers.customer_id")), Column("date", DateTime), ) # purchase_detailsテーブルを定義 purchase_details = Table( "purchases_details", meta, Column( "purchase_id", Integer, ForeignKey("purchases.purchase_id"), primary_key=True, ), Column("item_id", String, ForeignKey("items.item_id"), primary_key=True), Column("item_quantity", Integer), ) meta.create_all() # テーブルを一括で作成 |
補足すると、create_engineで接続するデータベース用エンジンを作成しています。
この例では接続するデータベースは、test.dbで、create_engineのオプションでecho=Trueをつけると発行されるSQLがログで出力されるようになります。
create_allで定義したテーブルを一括して作成することができます。
INSERT TABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
from sqlalchemy.sql import insert from models import engine, customers import sqlalchemy import pandas as pd # customersの値をInsert customer_values = ( { "customer_id": "1", "customer_name": "顧客A", "customer_unit_price": 1000, }, { "customer_id": "2", "customer_name": "顧客B", "customer_unit_price": 2000, }, ) # Insertする方法1 query = customers.insert().values(customer_values) # Insertする方法2 query = insert(customers, values=customer_values) conn = engine.connect() try: result = conn.execute(query) if result.is_insert: print("insert成功") query = "SELECT * FROM customers" df = pd.read_sql(query, conn) print(df) except sqlalchemy.exc.IntegrityError: print("insert失敗") conn.close() |
上記の例では、customersテーブルに値をInsertしています。Insertを行う方法は２つあって１つ目は、
|
1 |
query = customers.insert().values(customer_values) |
とする方法。
もう一つは、
|
1 |
query = insert(customers, values=customer_values) |
とする方法です。
また作成したテーブルをデータフレームで読み込んでいます。print(df)の結果として、ログ上では、以下のように表示されます。
|
1 2 3 |
customer_id customer_name customer_unit_price 0 1 顧客A 1000 1 2 顧客B 2000 |
そして今回エラーハンドリングとしてtry~catch文の中でqueryを実行しています。sqlchemy.exc.IntegrityErrorは、
- プライマリキー制約違反
- 外部キー制約違反
- ユニーク(一意)制約違反
- Not null制約違反
の時に発生するエラーですので、実務ではもう少し細かくエラーハンドリングをするべきだと思います。公式HPもぜひ参考してみてください。
その他、itemテーブル、purchases_detailsテーブルにも同様に値を入れます。
UPDATE TABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
from sqlalchemy.sql import update from models import engine, customers conn = engine.connect() # ("2", "顧客B", 3000)としてアップデートする query = ( update(customers) .where(customers.c.customer_id == "2") .values(customer_name="顧客B", customer_unit_price=3000) ) conn.execute(query) query = "SELECT * FROM customers" df = pd.read_sql(query, conn) print(df) conn.close() |
customers.c.customer_idのcはcolumnの省略を意味していて、customersテーブルの中のカラムの中のcustomer_idを表しています。
updateでは、顧客Bのcustomer_unit_priceを2000⇨3000に修正してみました。こちらも作成したテーブルをデータフレームで読み込んでいます。print(df)の結果として、ログ上では、以下のように表示されます。
|
1 2 3 |
customer_id customer_name customer_unit_price 0 1 顧客A 1000 1 2 顧客B 3000 |
DELETE
|
1 2 3 4 5 6 7 8 |
from sqlalchemy.sql import delete from models import engine, customers conn = engine.connect() query = delete(customers).where(customers.c.customer_id == 2) conn.execute(query) conn.close() |
上記でコードを実行するとcustomersテーブルの中のcustomer_idが２となっている行を削除します。
こちらも作成したテーブルをデータフレームで読み込んでいます。print(df)の結果として、ログ上では、以下のように表示されます。
|
1 2 |
customer_id customer_name customer_unit_price 0 1 顧客A 1000 |
SELECT
|
1 2 3 4 5 6 7 8 |
from models import engine, customers conn = engine.connect() query = customers.select() result = conn.execute(query) print(result.fetchall()) conn.close() |
selectでは、例とごとくcustomersテーブルを選択しています。
今回の記事では、SQLAlchemyでデータベースを操作方法をメモしてみました。
fetchallは、データベースで実行したSQLの結果全てを配列として返すメソッドで、print(result.fetchall())で以下のように出力します。
|
1 |
[('1', '顧客A', 1000)] |
別の記事では、より実践的なSQLAlchemyでデータベースを操作方法を書いていこうと思います。
