SQLAlchemyを使ってSAP HANAをPythonからORM経由で扱う

SAP HANA

2023.11.09

この記事を書いたメンバー：

大友佑介

SQLAlchemyを使ってSAP HANAをPythonからORM経由で扱う

こんにちは。大友（@yomon8)です。

普段全くSAP触らないくせにSAPネタを3連投目です。

以下の記事を書く中で hdbcliというSAP製ライブラリを利用して、SAP HANAに接続して操作してみたのですが、良い意味でSAP製品らしくなく他のDBと同じように使うことができました。

そこで、もしかしてPythonの有名なORMライブラリであるSQLAlchemyからも使えるのでは無いかと思って調べて見たら、SAPから正にSQL AlchemyのHANA用のDialectがありました。この記事ではこちらを触ってみます。

テスト用のスキーマの作成

前回こちらの記事で立ち上げたDocker上のHANAを使っていきます。

テーブル作ったり削除したりするので、専用のスキーマ作ります。LinuxにSSHでログインしたら、以下のコマンドでDockerに入ります。

 $ sudo docker exec -it a4h /bin/bash
vhcala4hci:/ # 
vhcala4hci:/ # su - hdbadm
hdbadm@vhcala4hci:/usr/sap/HDB/HDB02> hdbsql -u SYSTEM -p Htods70334  -n localhost:30215

そしてhdbadmユーザにスイッチして、hdbsqlコマンドでDBに接続します。-pはパスワードなのですが、こちらで使っているコンテナイメージのものはDocker Hubのリンク先に記載されています。

vhcala4hci:/ # su - hdbadm
hdbadm@vhcala4hci:/usr/sap/HDB/HDB02> hdbsql -u SYSTEM -p xxxxx  -n localhost:30215

demoというデモ用のユーザを作成します。同時にDEMOというスキーマも作成されます。確認したら一旦\qコマンドで抜けます。

hdbsql HDB=> CREATE USER demo PASSWORD Init1234;
hdbsql HDB=> \ds DEMO
Schema name,Owner name
"DEMO","DEMO"

hdbsql HDB=> \q

作成したユーザでログインしてみます。この際に新たなパスワードを聞かれるので、今回は「Demo01234」としました。こちらでもログイン確認してみます。

hdbadm@vhcala4hci:/usr/sap/HDB/HDB02> hdbsql -u demo -p Init1234  -n localhost:30215

# 変更したパスワードでログイン確認
hdbadm@vhcala4hci:/usr/sap/HDB/HDB02> hdbsql -u demo -p Demo01234  -n localhost:30215

Python準備

今回の内容は以下のリポジトリに入れています。hana_orm_sqlalchemy.ipynbファイルです。

まず、sqlalchemy-hanaのパッケージを入れておきます。poetry使っているので以下のコマンドです。

 poetry add sqlalchemy-hana

.envファイルの中身は以下のように設定します。

HDB_HOST="ホスト名やIPアドレス"
HDB_PORT=30215
HDB_USER="demo"
HDB_PASSWORD="Dem01234"

Notebookの冒頭で接続のための情報を.envファイルから読み込んでいます。

import os

load_dotenv()
hdb_user = os.environ["HDB_USER"]
hdb_password = os.environ["HDB_PASSWORD"]
hdb_host = os.environ["HDB_HOST"]
hdb_port = os.environ["HDB_PORT"]

接続用のオブジェクトを生成します。SQL Alchemyを使ったことある人ならお馴染みかと思います。説明はコード内に書いてます。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# データベースエンジンの作成（ここではSAP HANAをdialectに指定）
# echo = TrueでSQL出力
engine = create_engine(f"hana+hdbcli://{HDB_USER}:{HDB_PASSWORD}@{HDB_HOST}:{HDB_PORT}",
                       echo=True,
                       execution_options={"supports_statement_cache":False})


# セッションの作成
Session = sessionmaker(bind=engine)
session = Session()

DDL操作（CREATE TABLE)

簡単なユーザテーブルを作成してみます。

from sqlalchemy import Column, Integer, String, Sequence
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles

@compiles(CreateColumn, 'hana')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace('NOT NULL', 'NOT NULL GENERATED BY DEFAULT AS IDENTITY')
    return text

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "USER"
    __table_args__ = {
        "schema" : "DEMO",
        "comment" : "デモ用のユーザーテーブル"
    }

    id = Column('id', Integer, Sequence('id_seq'), primary_key=True)
    name = Column('name', String(200))
    age = Column('age', Integer)
    email = Column('email',String(200))


# # テーブルの作成
Base.metadata.create_all(bind=engine)

以下のようにSQLが発行されてテーブルが作成されました。

2023-11-09 07:04:51,353 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-09 07:04:51,355 INFO sqlalchemy.engine.Engine SELECT 1 FROM SYS.TABLES WHERE SCHEMA_NAME=? AND TABLE_NAME=?
2023-11-09 07:04:51,356 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00138s] ('DEMO', 'USER')
2023-11-09 07:04:51,378 INFO sqlalchemy.engine.Engine SELECT 1 FROM SYS.SEQUENCES WHERE SCHEMA_NAME=? AND SEQUENCE_NAME=?
2023-11-09 07:04:51,380 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00134s] ('DEMO', 'ID_SEQ')
2023-11-09 07:04:51,405 INFO sqlalchemy.engine.Engine CREATE SEQUENCE id_seq
2023-11-09 07:04:51,407 INFO sqlalchemy.engine.Engine [no key 0.00152s] ()
2023-11-09 07:04:51,436 INFO sqlalchemy.engine.Engine 
CREATE TABLE "DEMO"."USER" (
    id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
    name VARCHAR(200), 
    age INTEGER, 
    email VARCHAR(200), 
    PRIMARY KEY (id)
)




2023-11-09 07:04:51,437 INFO sqlalchemy.engine.Engine [no key 0.00074s] ()
2023-11-09 07:04:51,470 INFO sqlalchemy.engine.Engine COMMENT ON TABLE "DEMO"."USER" IS 'デモ用のユーザーテーブル'
2023-11-09 07:04:51,472 INFO sqlalchemy.engine.Engine [no key 0.00155s] ()
2023-11-09 07:04:51,500 INFO sqlalchemy.engine.Engine COMMIT

DML操作(CRUD)

次に簡単なCRUD操作を試していきます。

Create(INSERT)

まずは上記で作成したモデルを使ってテストユーザをINSERTしていきます。

 sample_users = [
    User(name="Alice", age=25, email="alice@example.com"),
    User(name="Bob", age=30, email="bob@example.com"),
    User(name="Carol", age=22, email="carol@example.com"),
    User(name="Dave", age=20, email="dave@example.com"),
    User(name="Eve", age=35, email="eve@example.com"),
    User(name="Frank", age=28, email="frank@example.com"),
    User(name="Grace", age=40, email="grace@example.com"),
    User(name="Hank", age=23, email="hank@example.com"),
    User(name="Ivy", age=31, email="ivy@example.com"),
    User(name="John", age=27, email="john@example.com")
]
# サンプルデータをデータベースに追加
session.add_all(sample_users)
session.commit()
session.close()

ログの一部を貼り付けますが、SQL発行されてます。

2023-11-09 07:05:39,032 INFO sqlalchemy.engine.Engine SELECT id_seq.NEXTVAL FROM DUMMY
2023-11-09 07:05:39,032 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-11-09 07:05:39,064 INFO sqlalchemy.engine.Engine INSERT INTO "DEMO"."USER" (id, name, age, email) VALUES (?, ?, ?, ?)
2023-11-09 07:05:39,065 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.03363s] (1, 'Alice', 25, 'alice@example.com')
2023-11-09 07:05:39,100 INFO sqlalchemy.engine.Engine SELECT id_seq.NEXTVAL FROM DUMMY
2023-11-09 07:05:39,101 INFO sqlalchemy.engine.Engine [raw sql] ()
...

Read(SELECT)

では登録したユーザをSELECTしてみます。

 # SELECT FROM USER;
all_users = session.query(User).all()
for user in all_users:
    print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}, Email: {user.email}')

ログは以下の通りです。上手く登録できているようです。

2023-11-09 07:06:28,514 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-09 07:06:28,515 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER"
2023-11-09 07:06:28,516 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00064s] ()
ID: 1, Name: Alice, Age: 25, Email: alice@example.com
ID: 2, Name: Bob, Age: 30, Email: bob@example.com
ID: 3, Name: Carol, Age: 22, Email: carol@example.com
ID: 4, Name: Dave, Age: 20, Email: dave@example.com
ID: 5, Name: Eve, Age: 35, Email: eve@example.com
ID: 6, Name: Frank, Age: 28, Email: frank@example.com
ID: 7, Name: Grace, Age: 40, Email: grace@example.com
ID: 8, Name: Hank, Age: 23, Email: hank@example.com
ID: 9, Name: Ivy, Age: 31, Email: ivy@example.com
ID: 10, Name: John, Age: 27, Email: john@example.com

WHERE句を使ってAliceユーザを引いてみます。

# SELECT FROM USER WHERE name == ?;
user_name = "Alice"
user_by_name = session.query(User).filter(User.name == user_name).first()
if user_by_name:
    print(f"""
          ID: {user_by_name.id}, 
          Name: {user_by_name.name}, 
          Age: {user_by_name.age}, 
          Email: {user_by_name.email}
          """)

問題無くSELECTできています。

 2023-11-09 07:07:02,927 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER" 
WHERE "DEMO"."USER".name = ?
 LIMIT ?
2023-11-09 07:07:02,929 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00273s] ('Alice', 1)


          ID: 1, 
          Name: Alice, 
          Age: 25, 
          Email: alice@example.com

Update(UPDATE)

次にデータ更新。

 email = "bob@example.com"
target_user = session.query(User).filter(User.email == email).first()
if target_user:
    target_user.name = "Update"
    session.commit()


target_user = session.query(User).filter(User.email == email).first()
if target_user:
    print(f"""
          ID: {target_user.id}, 
          Name: {target_user.name}, 
          Age: {target_user.age}, 
          Email: {target_user.email}
          """)

ちゃんとUpdateできています。

2023-11-09 07:08:57,430 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER" 
WHERE "DEMO"."USER".email = ?
 LIMIT ?
2023-11-09 07:08:57,433 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00306s] ('bob@example.com', 1)
2023-11-09 07:08:57,467 INFO sqlalchemy.engine.Engine UPDATE "DEMO"."USER" SET name=? WHERE "DEMO"."USER".id = ?
2023-11-09 07:08:57,468 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00065s] ('Update', 2)
2023-11-09 07:08:57,496 INFO sqlalchemy.engine.Engine COMMIT
2023-11-09 07:08:57,513 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-09 07:08:57,514 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER" 
WHERE "DEMO"."USER".email = ?
 LIMIT ?
2023-11-09 07:08:57,515 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00079s] ('bob@example.com', 1)


          ID: 2, 
          Name: Update, 
          Age: 30, 
          Email: bob@example.com

Delete(DELETE)

最後にDELETEです。

name = "Carol"
target_user = session.query(User).filter(User.name == name).first()
if target_user:
    session.delete(target_user)
    session.commit()


target_user = session.query(User).filter(User.name == name).first()
if not target_user:
    print(f"User {name} not found!")

指定したユーザを削除できてそうです。

2023-11-09 07:10:42,339 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER" 
WHERE "DEMO"."USER".name = ?
 LIMIT ?
2023-11-09 07:10:42,341 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00190s] ('Carol', 1)
2023-11-09 07:10:42,362 INFO sqlalchemy.engine.Engine DELETE FROM "DEMO"."USER" WHERE "DEMO"."USER".id = ?
2023-11-09 07:10:42,362 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00053s] (3,)
2023-11-09 07:10:42,384 INFO sqlalchemy.engine.Engine COMMIT
2023-11-09 07:10:42,399 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-11-09 07:10:42,400 INFO sqlalchemy.engine.Engine SELECT "DEMO"."USER".id AS "DEMO_USER_id", "DEMO"."USER".name AS "DEMO_USER_name", "DEMO"."USER".age AS "DEMO_USER_age", "DEMO"."USER".email AS "DEMO_USER_email" 
FROM "DEMO"."USER" 
WHERE "DEMO"."USER".name = ?
 LIMIT ?
2023-11-09 07:10:42,401 INFO sqlalchemy.engine.Engine [dialect hana+hdbcli does not support caching 0.00053s] ('Carol', 1)
User Carol not found!