Pythonでやってみた10:DBデータ管理_担当者情報の一覧表示 Streamlit×FastAPI×SQLAlchemy
概要
業務でのデータ扱いに関して”あるある”を改善できるか模索中です。
SQLを使用したDBによる共有化・高速化×サーバー連動のためのAPI×可視化・BIツールのWebアプリ開発で何かできるか検討しました。
第1弾として、発注したい業者の担当者を共有化するためのシステム開発にトライアルしました。
1.DBおよびWebアプリ設計
複数の機能を実装していくため各工程で設計を実施します。
1-1.DB設計
DB設計の思想は下記の通りにしました。
1-2.UI設計(フロントエンド)
Webアプリによるフロントエンド(UI設計)の思想は下記の通りです。
1-3.全体イメージ図
今回はすべてローカルで実行しますがDBサーバーを別で用意することを想定してDB操作はFastAPIを用いたAPI実装したいと思います。
よってDB接続はAPI操作のみで実行でき”from sql_app import xx”での操作(同じPC内での操作)はできないものと想定して実装します。
2.作成要領
ほぼ自分の備忘録に近い内容です。(本職の方がいれば指導してほしい)
3.Webアプリ画面の作成(Streamlit)
ブラウザベースでデータを見れるようにフロントエンドのWebアプリ開発としてStreamlitを使用します。
3-1.ページ選択用アプリのパッケージ作成
今回は検索・登録・削除の3つの画面を作成していくため、各ページのモジュールをまとめてパッケージにしました。
現段階では動作はしませんが”webapp.py”は下記の通り記載することでサイドバーから"subwebapp"パッケージ内の好きなページを選択できます。
[webapp.py]
import streamlit as st
import sys, os
from subwebapp import app_create, app_delete, app_show
#サイドバーで選択するページの設定
PAGES ={
"データ検索(READ)": app_show,
"データ登録(CREATE)": app_create,
"データ削除(DELETE)": app_delete
}
st.sidebar.title("ページ設定")
selection = st.sidebar.radio("表示するページを設定してください。", list(PAGES.keys()))
page = PAGES[selection]
page.app()
[subwebapp/__init__.py] ※空ファイル
[subwebapp/utils.py]
ROOT_API = "http://127.0.0.1:8000" #APIのエンドポイント
3-2.データ入力フォームの作成:st.form()
DBに登録したいデータを入力する場合はブラウザ操作中に毎回更新するよりデータ入力後にまとめて更新する方が便利です。Streamlitでは「st.form()」を用いて機能を実装します。
3-3.データテーブル表示:st.table(), st.dataframe()
DBデータは構造化データのためテーブル形式で取得できます。テーブルをWebアプリに実装するにはst.tableやst.dataframe()を使用します。(公式Doc)
3-4.アプリのイメージ図
検索、作成、削除画面のイメージはそれぞれ下記としてコードを実装していきます。この時点ではボタン機能の実装はないため、完成コードを最終章にまとめて記載しました。
4.DBおよびAPI作成(SQL Alchemy・FastAPI)
DBおよびAPIを操作するパッケージを作成していきます。内容は基本的にはFastAPIの公式Docsを参照しております。
4-1.DB/APIパッケージ作成
DB・API操作を実施するパッケージの作成をします。ファイル構成はFastAPI記事の「SQL (Relational) Databases」を参考にしたいと思います。
一部は個人的に理解しにくいので勝手に名前を変えておりますが、基本的には記事と名前をそろえて作成する方が他の人が見やすいと思います。
4-2.SQL AlchemyのSesssion管理:database_DB.py
SQL AlchemyのSession管理およびテーブル作成のためのBaseクラスの作成用モジュールを作成していきます。
[sql_app/database_DB.py]
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#SQLAlchemyのDB作成・接続のためのURL作成
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db" #同じディレクトリにsql_app.dbを作成
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db" #PostgreSQLを使用したい人向け
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False} #SQLiteを使用するため引数:connect_args={"check_same_thread": False}を設定
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base() #DBのテーブル作成時に継承させるためのクラス
4-3.SQL AlchemyのDBテーブル作成:tables_DB.py
次にSQL AlchemyのDB用テーブルのクラスをtables_DBに記載します。
[sql_app/tables_DB.py]
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database_DB import Base #同じディレクトリのdatabase_DB.pyのBaseクラスをインポート
class Industry(Base):
__tablename__ = "industry"
id = Column(Integer, primary_key=True, index=True)
industry = Column(String, unique=True, index=True) #重複無し
ordercompany = relationship("Ordercompany", back_populates="industry") #親テーブル側の外部キーを設定
class Ordertype(Base):
__tablename__ = "ordertype"
id = Column(Integer, primary_key=True, index=True)
ordertype = Column(String, unique=True, index=True) #重複無し
ordercompany = relationship("Ordercompany", back_populates="ordertype") #親テーブル側の外部キーを設定
class Ordercompany(Base):
__tablename__ = "ordercompany"
id = Column(Integer, primary_key=True, index=True)
id_industry = Column(Integer, ForeignKey("industry.id")) #外部キーでID管理
id_ordertype = Column(Integer, ForeignKey("ordertype.id")) #外部キーでID管理
companyname = Column(String, index=True)
personname = Column(String)
phonenum = Column(String)
email = Column(String)
producttype = Column(String)
project = Column(String)
remark = Column(String)
industry = relationship("Industry", back_populates="ordercompany") #relationshipsにより、industryテーブルと関連付け
ordertype = relationship("Ordertype", back_populates="ordercompany") #relationshipsにより、ordertypeテーブルと関連付け
【参考説明】
「from .database_DB import Base」は先ほど作成したモジュール内のBaseをimportしています。
4-4.FastAPIの型ヒントを定義:schemas_API.py
FastAPIでPOST処理を受け付ける時のデータの型ヒントを定義します。tables_DBにおけるSQL Alchemyのデータ型はBaseを継承しましたが、FastAPIではPydanticを継承して使用します。
[sql_app/schemas_API.py]
from typing import List, Optional #python 3.9以上ではListは省略可, 10以上ではこの行は省略可※その代わり下記記載が変わる
from pydantic import BaseModel
#Industry(業種)クラス
class IndustryBase(BaseModel):
industry: str
class IndustryCreate(IndustryBase):
pass
class Industry(IndustryBase):
id: int
class Config:
orm_mode = True #SQLAlchemyのORMを受け入れる
#Ordertype(発注形態)クラス
class OrdertypeBase(BaseModel):
ordertype: str
class OrdertypeCreate(OrdertypeBase):
pass
class Ordertype(OrdertypeBase):
id: int
class Config:
orm_mode = True
#Ordercompany(発注業者管理)(メイン)
class OrdercompanyBase(BaseModel):
companyname: str
personname: str
phonenum: str
email: str
producttype: str
project: str
remark: Optional[str] = None
# remark: str | None = None #Python3.10以上の記載方法
class OrdercompanyCreate(OrdercompanyBase):
pass
class Ordercompany(OrdercompanyBase):
id: int
id_industry: int #Industryクラスの外部キー
id_ordertype: int #Ordertypeクラスの外部キー
class Config:
orm_mode = True
#DELETE操作専用
class DeleteOrderCompany(BaseModel):
id: int
password: str
4-5.DBのCRUD操作:crud_DB.py
crud_DB.pyにはDB操作を記載します。実装内容は下記の通りです。
DB操作ではDBテーブル用クラス情報がある「tables_DB.py」とFastAPIでの型ヒントを記載した「schemas_API.py」をインポートして使用します。
[sql_app/crud_DB.py]
from sqlalchemy.orm import Session #型ヒント用にSQLAlchemyのSessionクラス
from . import tables_DB #SQLAlchemyのテーブル
from . import schemas_API #APIの型ヒント
#READ操作
def get_industry(db: Session, skip: int=0, limit: int=100):
return db.query(tables_DB.Industry).offset(skip).limit(limit).all()
def get_ordertype(db: Session, skip: int=0, limit: int=100):
return db.query(tables_DB.Ordertype).offset(skip).limit(limit).all()
def get_ordercompanies(db: Session, skip: int =0, limit: int = 100):
return db.query(tables_DB.Ordercompany).offset(skip).limit(limit).all()
#CREATE操作
def create_industry(db: Session, industry: schemas_API.IndustryCreate):
db_industry = tables_DB.Industry(
industry = industry.industry,
)
db.add(db_industry)
db.commit()
db.refresh(db_industry)
return db_industry
def create_ordertype(db: Session, ordertype: schemas_API.OrdertypeCreate):
db_ordertype = tables_DB.Ordertype(**ordertype.dict()) #省略型の記法
db.add(db_ordertype)
db.commit()
db.refresh(db_ordertype)
return db_ordertype
def create_ordercompany(db: Session, ordercompany: schemas_API.OrdercompanyCreate, id_industry: int, id_ordertype: int):
#DB登録用のインスタンスを生成
db_ordercompany = tables_DB.Ordercompany(
**ordercompany.dict(),
id_industry = id_industry, #Pydanticで定義されていないキー
id_ordertype = id_ordertype #Pydanticで定義されていないキー
)
# db_ordercompany = tables_DB.Ordercompany(**ordercompany.dict()) #省略型の記法
db.add(db_ordercompany) #DB(session)に追加
db.commit() #DBに反映
db.refresh #インスタンスを更新->DBから新しい値を取得可能
return db_ordercompany
#DELETE操作
def delete_ordercompany(db: Session, datas: schemas_API.DeleteOrderCompany):
if datas.password == 'KIYO':
db_ordercompany = db.query(tables_DB.Ordercompany).filter(tables_DB.Ordercompany.id == datas.id).first()
db.delete(db_ordercompany)
db.commit()
return db_ordercompany
else:
return 'パスワードが間違っています。'
【備忘録】
「SQLAlchemyでmigrationにAlembicを使用する」と記載ある。今回は使う必要性はなさそうだけど今後学習が必要かもしれないので参考を記載。
4-6.API開発ーGET/POSTメソッド:main_API.py
最後にFastAPIを用いてAPI実装をします。
[sql_app/main_API.py]
import string
from typing import List #Python3.6 ver.
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session #SQL AlchemyのDB型
from . import crud_DB, tables_DB, schemas_API
from .database_DB import SessionLocal, engine
tables_DB.Base.metadata.create_all(bind=engine) #テーブル作成
app = FastAPI() #FastAPIのapp作成
# Dependency
def get_db():
db = SessionLocal() #DB接続
try:
yield db #DBリクエスト
finally:
db.close() #エラーが出ても必ずDB切断
#GETメソッド->特定のパラメータが不要なためDBのREAD操作を実行
@app.get("/industry/", response_model=List[schemas_API.Industry]) #response_modelで戻り値の型を指定
def read_industry(skip: int =0, limit: int =100, db: Session = Depends(get_db)):
return crud_DB.get_industry(db, skip=skip, limit=limit)
@app.get("/ordertype/", response_model=List[schemas_API.Ordertype])
def read_ordertype(skip: int =0, limit: int =100, db: Session = Depends(get_db)):
return crud_DB.get_ordertype(db, skip=skip, limit=limit)
@app.get("/ordercompany/", response_model=List[schemas_API.Ordercompany])
def read_ordercompany(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
ordercompany = crud_DB.get_ordercompanies(db, skip=skip, limit=limit)
return ordercompany
#POSTメソッド
@app.post("/industry/", response_model=schemas_API.Industry)
def create_industry(industry: schemas_API.IndustryCreate, db: Session = Depends(get_db)):
return crud_DB.create_industry(db, industry)
@app.post("/ordertype/", response_model=schemas_API.Ordertype)
def create_ordertype(ordertype: schemas_API.OrdertypeCreate, db: Session = Depends(get_db)):
return crud_DB.create_ordertype(db, ordertype)
#POST-CREATE操作
@app.post("/ordercompany/", response_model=schemas_API.Ordercompany)
def create_ordercompany(id_industry: int, id_ordertype:int, ordercompany: schemas_API.OrdercompanyCreate, db: Session = Depends(get_db)):
return crud_DB.create_ordercompany(db=db, ordercompany=ordercompany, id_industry=id_industry, id_ordertype=id_ordertype)
#POST-DELETE操作
@app.post("/ordercompany/delete/")
def delete_ordercompany(datas: schemas_API.DeleteOrderCompany , db: Session = Depends(get_db)):
return crud_DB.delete_ordercompany(db, datas)
【def vs async defの選択】
async defは非同期処理を実現しますが、公式Docsより「SQL Alchemyは直接”await”使用に対して互換性がない」との記載があるためdefで作成しました(十分に意味が理解できていないため紹介のみ)。
5.DB登録/Webアプリの機能実装
5-1.システム起動(Webアプリ/API)
ターミナルを2つ用意してStreamlit/FastAPIをそれぞれ起動します。
[Terminal_1] webappモジュールを起動
streamlit run webapp.py
[Terminal_2] sql_appパッケージ内のmain_APIモジュールにあるappを起動
uvicorn sql_app.main_API:app --reload
main_APIを起動すると「tables_DB.Base.metadata.create_all(bind=engine) 」も合わせて実行されるためDB作成・接続が実行されます。
5-2.マスタテーブルの登録
WebアプリとDB/APIライブラリは同じディレクトリにありますが、あくまで別サーバーを想定します。よってDB操作はすべてAPIで実施します。
今回は使用者がマスタデータを追加できない(ブラウザ上で操作できない)ようにするため、個別でマスターデータを追加します。今回は適当にJupyter(下図のipynbファイル)で処理しました。
まずrequestライブラリを用いてGETメソッドによるDBデータ取得(READ)を実施して①データが空であること、②通信できていることを確認します。
[IN]
import requests
import json
root_api = "http://127.0.0.1:8000"
res = requests.get(root_api + "/industry/")
print(res.status_code) #200なら通信成功
print(res.json())
[Out]
200
[]
次にPOSTメソッドでDBのCREATE操作をするAPIを叩くことでデータ登録・確認をします。※for文で回すために冗長なコードになってますが{"industry": data}を1回ずつ処理しても登録できます。
[In]
import requests
import json
industry = {1:"メーカー",
2:"商社"}
datas = [{"industry": v} for v in industry.values()] #[{'industry': 'メーカー'}, {'industry': '商社'}]
for data in datas:
res = requests.post(root_api + "/industry/", data=json.dumps(data))
# print(res.status_code) #通信確認用
root_api = "http://127.0.0.1:8000"
res = requests.get(root_api + "/industry/")
# print(res.status_code) #200なら通信成功
print(res.json())
[Out]
#response_model=List[schemas_API.Industry]で出力される
[{'industry': 'メーカー', 'id': 1}, {'industry': '商社', 'id': 2}]
上を参考にして”Ordertype”のマスタデータも登録します。
[In]
import requests
import json
ordertype={1:"直接発注",
2:"商社経由"}
datas = [{"ordertype": v} for v in ordertype.values()] #[{'ordertype': '直接発注'}, {'ordertype': '商社経由'}]
for data in datas:
res = requests.post(root_api + "/ordertype/", data=json.dumps(data))
root_api = "http://127.0.0.1:8000"
res = requests.get(root_api + "/ordertype/")
print(res.status_code) #200なら通信成功
print(res.json())
datas_ordertype = {i['id']: i['ordertype'] for i in res.json()}
datas_ordertype
[Out]
200
[{'ordertype': '直接発注', 'id': 1}, {'ordertype': '商社経由', 'id': 2}] #response_model=List[schemas_API.Ordertype]
{1: '直接発注', 2: '商社経由'}
【備忘録:処理動作の確認用※実装はWebアプリにする】
APIの戻り値はListであるため{id:data}の辞書形式に変換します。またデータをidで登録するためにkey:valueを逆にした辞書も作成します。
[In]
datas_industry = {i['id']: i['industry'] for i in res.json()}
industry2id = {v: k for k, v in datas_industry.items()}
datas_industry , industry2id
[Out]
{1: 'メーカー', 2: '商社'}
{'メーカー': 1, '商社': 2}
5-3.Webアプリの機能実装
3章ではUIのみ実装(タイトル/見出し、フォームの入力画面、ボタン)しましたが、最後に機能を実装していきます。
上記をまとめると結果として下記コードになりました。
[subwebapp/app_show]
def app():
import streamlit as st
import pandas as pd
import requests
import json
from .utils import ROOT_API #app()の中に入れないとエラー
st.title('メーカー・担当者管理システム')
st.markdown('## データ検索')
#IndustryとOrdertypeのマスタデータを辞書型で取得-API使用
res = requests.get(ROOT_API + "/industry/")
industry = {i['id']: i['industry'] for i in res.json()} #{1:"メーカー", 2:"商社"}
res = requests.get(ROOT_API + "/ordertype/")
ordertype = {i['id']: i['ordertype'] for i in res.json()} #{1:"直接発注", 2:"商社経由"}
#Ordercompanyデータの表示
ROOT_API = "http://127.0.0.1:8000"
res = requests.get(ROOT_API + "/ordercompany/")
#データなし(空リスト出力時はエラーになるためif文)
if not res.json():
st.write('DBに登録されているデータは存在しません。')
else:
#ID(整数)が入っているデータを値に変換
df = pd.DataFrame(res.json())
df['id_industry'] = df['id_industry'].map(industry)
df['id_ordertype'] = df['id_ordertype'].map(ordertype)
#dataframeのカラム名を変更
renames = {
'id': 'ID',
'id_industry': '業種',
'id_ordertype': '発注形態',
'companyname': '会社名',
'personname': '担当社',
'phonenum': '連絡先(E-mail)',
'email': '連絡先(TEL)',
'producttype': '取扱い製品',
'project': 'プロジェクト名',
'remark': '備考'
}
df.rename(columns=renames, inplace=True) #カラム名変更, inplaceでdfを上書き
#カラムの列順を変更
colnums = list(range(len(df.columns)))
colnums = colnums[-3:] + colnums[:-3] #output->[7, 8, 9, 0, 1, 2, 3, 4, 5, 6]
df = df.iloc[:, colnums] #後ろ3の列を前に移動
#データ表示
st.table(df.set_index('ID'))
[subwebapp/app_create]
import streamlit as st
import pandas as pd
import requests
import json
from .utils import ROOT_API
def app():
st.title('メーカー・担当者管理システム')
st.markdown('## データ入力画面')
#IndustryとOrdertypeのマスタデータを辞書型で取得-API使用
res = requests.get(ROOT_API + "/industry/")
industry = {i['id']: i['industry'] for i in res.json()} #{1:"メーカー", 2:"商社"}
industry2id = {v: k for k, v in industry.items()} #key:valueを反転->値からidを取得
res = requests.get(ROOT_API + "/ordertype/")
ordertype = {i['id']: i['ordertype'] for i in res.json()} #{1:"直接発注", 2:"商社経由"}
ordertype2id = {v: k for k, v in ordertype.items()} #key:valueを反転->値からidを取得
#データフォームUIの作成
with st.form("input_data"):
in_industry : str = st.selectbox("業種", list(industry.values())) #業種選択
in_ordertype : str = st.selectbox("発注形態", list(ordertype.values())) #業種選択
in_companyname : str = st.text_input(label="会社名", value="株式会社")
in_personname : str = st.text_input(label="担当者")
in_TEL : str = st.text_input(label="TEL(個人がない場合は代表)")
in_Email : str = st.text_input(label="E-mail(個人がない場合は代表)", value="@.co.jp")
in_productname : str = st.text_input(label="取扱い製品")
in_projectname : str = st.text_input(label="関連PJ(依頼時のPJ)")
in_remark : str = st.text_area(label="備考(未入力でもよい)", value="無し") #初期値は"無し"という文字列
data = {
'companyname' : in_companyname,
'personname' : in_personname,
'phonenum' : in_TEL,
'email' : in_Email,
'producttype' : in_productname,
'project' : in_projectname,
'remark' : in_remark
}
# Every form must have a submit button.
submitted = st.form_submit_button("データ登録ボタン")
if submitted:
params = {
'id_industry' : industry2id[in_industry],
'id_ordertype' : ordertype2id[in_ordertype]}
res = requests.post(ROOT_API + "/ordercompany/",
data=json.dumps(data),
params=params)
if res.status_code == 200:
st.write('データを登録しました。')
st.json(res.json())
[subwebapp/app_delete]
from email.policy import default
import streamlit as st
import streamlit as st
import pandas as pd
import requests
import json
def app():
st.title('メーカー・担当者管理システム')
st.markdown('## データ削除画面')
#Ordercompanyデータの表示
ROOT_API = "http://127.0.0.1:8000"
res = requests.get(ROOT_API + "/ordercompany/")
#データなし(空リスト出力時はエラーになるためif文)
if not res.json():
st.write('DBに登録されているデータは存在しません。')
else:
df = pd.DataFrame(res.json())
#カラムの列順を変更
colnums = list(range(len(df.columns)))
colnums = colnums[-3:] + colnums[:-3] #output->[7, 8, 9, 0, 1, 2, 3, 4, 5, 6]
df = df.iloc[:, colnums] #後ろ3の列を前に移動
st.markdown('### データ選択')
with st.form("show_data"):
id_select = st.selectbox("削除するデータを選択", list(df['id'])) #業種選択
submitted = st.form_submit_button("データ選択")
if submitted:
df = df[df['id']==id_select] #選択したID
st.dataframe(df)
with st.form("delete_data"):
st.write(f'削除するデータid:{id_select}')
text_pass = st.text_input("パスワード入力", type="password") #パスワード入力
# Every form must have a submit button.
submitted = st.form_submit_button("データ削除")
if submitted:
password = 'KIYO'
if text_pass == password:
params = {'id': id_select,
'password': password}
res = requests.post(ROOT_API + "/ordercompany/delete/",
data=json.dumps(params))
if res.status_code==200:
st.write(f"データid:{id_select}を削除しました。")
st.write(res.json())
else:
st.write('パスワードが間違っています。')
6.動作確認
最後にシステムを起動させた状態で動作チェックします。
7.全コード
7-1.フロントエンド:Webアプリ側
[webapp.py]
import streamlit as st
import sys, os
from subwebapp import app_create, app_delete, app_show
#サイドバーで選択するページの設定
PAGES ={
"データ検索(READ)": app_show,
"データ登録(CREATE)": app_create,
"データ削除(DELETE)": app_delete
}
st.sidebar.title("ページ設定")
selection = st.sidebar.radio("表示するページを設定してください。", list(PAGES.keys()))
page = PAGES[selection]
page.app()
[subwebapp/__init__.py] ※空ファイル
[subwebapp/utils.py]
ROOT_API = "http://127.0.0.1:8000" #APIのエンドポイント
[subwebapp/app_create.py]
import streamlit as st
import pandas as pd
import requests
import json
from .utils import ROOT_API
def app():
st.title('メーカー・担当者管理システム')
st.markdown('## データ入力画面')
#IndustryとOrdertypeのマスタデータを辞書型で取得-API使用
res = requests.get(ROOT_API + "/industry/")
industry = {i['id']: i['industry'] for i in res.json()} #{1:"メーカー", 2:"商社"}
industry2id = {v: k for k, v in industry.items()} #key:valueを反転->値からidを取得
res = requests.get(ROOT_API + "/ordertype/")
ordertype = {i['id']: i['ordertype'] for i in res.json()} #{1:"直接発注", 2:"商社経由"}
ordertype2id = {v: k for k, v in ordertype.items()} #key:valueを反転->値からidを取得
#データフォームUIの作成
with st.form("input_data"):
in_industry : str = st.selectbox("業種", list(industry.values())) #業種選択
in_ordertype : str = st.selectbox("発注形態", list(ordertype.values())) #業種選択
in_companyname : str = st.text_input(label="会社名", value="株式会社")
in_personname : str = st.text_input(label="担当者")
in_TEL : str = st.text_input(label="TEL(個人がない場合は代表)")
in_Email : str = st.text_input(label="E-mail(個人がない場合は代表)", value="@.co.jp")
in_productname : str = st.text_input(label="取扱い製品")
in_projectname : str = st.text_input(label="関連PJ(依頼時のPJ)")
in_remark : str = st.text_area(label="備考(未入力でもよい)", value="無し") #初期値は"無し"という文字列
data = {
'companyname' : in_companyname,
'personname' : in_personname,
'phonenum' : in_TEL,
'email' : in_Email,
'producttype' : in_productname,
'project' : in_projectname,
'remark' : in_remark
}
# Every form must have a submit button.
submitted = st.form_submit_button("データ登録ボタン")
if submitted:
params = {
'id_industry' : industry2id[in_industry],
'id_ordertype' : ordertype2id[in_ordertype]}
res = requests.post(ROOT_API + "/ordercompany/",
data=json.dumps(data),
params=params)
if res.status_code == 200:
st.write('データを登録しました。')
st.json(res.json())
[subwebapp/app_delete.py]
from email.policy import default
import streamlit as st
import streamlit as st
import pandas as pd
import requests
import json
def app():
st.title('メーカー・担当者管理システム')
st.markdown('## データ削除画面')
#Ordercompanyデータの表示
ROOT_API = "http://127.0.0.1:8000"
res = requests.get(ROOT_API + "/ordercompany/")
#データなし(空リスト出力時はエラーになるためif文)
if not res.json():
st.write('DBに登録されているデータは存在しません。')
else:
df = pd.DataFrame(res.json())
#カラムの列順を変更
colnums = list(range(len(df.columns)))
colnums = colnums[-3:] + colnums[:-3] #output->[7, 8, 9, 0, 1, 2, 3, 4, 5, 6]
df = df.iloc[:, colnums] #後ろ3の列を前に移動
st.markdown('### データ選択')
with st.form("show_data"):
id_select = st.selectbox("削除するデータを選択", list(df['id'])) #業種選択
submitted = st.form_submit_button("データ選択")
if submitted:
df = df[df['id']==id_select] #選択したID
st.dataframe(df)
with st.form("delete_data"):
st.write(f'削除するデータid:{id_select}')
text_pass = st.text_input("パスワード入力", type="password") #パスワード入力
# Every form must have a submit button.
submitted = st.form_submit_button("データ削除")
if submitted:
password = 'KIYO'
if text_pass == password:
params = {'id': id_select,
'password': password}
res = requests.post(ROOT_API + "/ordercompany/delete/",
data=json.dumps(params))
if res.status_code==200:
st.write(f"データid:{id_select}を削除しました。")
st.write(res.json())
else:
st.write('パスワードが間違っています。')
[subwebapp/app_show.py]
def app():
import streamlit as st
import pandas as pd
import requests
import json
from .utils import ROOT_API #app()の中に入れないとエラー
st.title('メーカー・担当者管理システム')
st.markdown('## データ検索')
#IndustryとOrdertypeのマスタデータを辞書型で取得-API使用
res = requests.get(ROOT_API + "/industry/")
industry = {i['id']: i['industry'] for i in res.json()} #{1:"メーカー", 2:"商社"}
res = requests.get(ROOT_API + "/ordertype/")
ordertype = {i['id']: i['ordertype'] for i in res.json()} #{1:"直接発注", 2:"商社経由"}
#Ordercompanyデータの表示
ROOT_API = "http://127.0.0.1:8000"
res = requests.get(ROOT_API + "/ordercompany/")
#データなし(空リスト出力時はエラーになるためif文)
if not res.json():
st.write('DBに登録されているデータは存在しません。')
else:
#ID(整数)が入っているデータを値に変換
df = pd.DataFrame(res.json())
df['id_industry'] = df['id_industry'].map(industry)
df['id_ordertype'] = df['id_ordertype'].map(ordertype)
#dataframeのカラム名を変更
renames = {
'id': 'ID',
'id_industry': '業種',
'id_ordertype': '発注形態',
'companyname': '会社名',
'personname': '担当社',
'phonenum': '連絡先(E-mail)',
'email': '連絡先(TEL)',
'producttype': '取扱い製品',
'project': 'プロジェクト名',
'remark': '備考'
}
df.rename(columns=renames, inplace=True) #カラム名変更, inplaceでdfを上書き
#カラムの列順を変更
colnums = list(range(len(df.columns)))
colnums = colnums[-3:] + colnums[:-3] #output->[7, 8, 9, 0, 1, 2, 3, 4, 5, 6]
df = df.iloc[:, colnums] #後ろ3の列を前に移動
#データ表示
st.table(df.set_index('ID'))
7-2.バックエンド:DB・API
[sql_app/__init__.py] ※空ファイル
[sql_app/database_DB.py]
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#SQLAlchemyのDB作成・接続のためのURL作成
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db" #同じディレクトリにsql_app.dbを作成
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db" #PostgreSQLを使用したい人向け
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False} #SQLiteを使用するため引数:connect_args={"check_same_thread": False}を設定
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base() #DBのテーブル作成時に継承させるためのクラス
[sql_app/tables_DB.py]
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database_DB import Base #同じディレクトリのdatabase_DB.pyのBaseクラスをインポート
class Industry(Base):
__tablename__ = "industry"
id = Column(Integer, primary_key=True, index=True)
industry = Column(String, unique=True, index=True) #重複無し
ordercompany = relationship("Ordercompany", back_populates="industry") #親テーブル側の外部キーを設定
class Ordertype(Base):
__tablename__ = "ordertype"
id = Column(Integer, primary_key=True, index=True)
ordertype = Column(String, unique=True, index=True) #重複無し
ordercompany = relationship("Ordercompany", back_populates="ordertype") #親テーブル側の外部キーを設定
class Ordercompany(Base):
__tablename__ = "ordercompany"
id = Column(Integer, primary_key=True, index=True)
id_industry = Column(Integer, ForeignKey("industry.id")) #外部キーでID管理
id_ordertype = Column(Integer, ForeignKey("ordertype.id")) #外部キーでID管理
companyname = Column(String, index=True)
personname = Column(String)
phonenum = Column(String)
email = Column(String)
producttype = Column(String)
project = Column(String)
remark = Column(String)
industry = relationship("Industry", back_populates="ordercompany") #relationshipsにより、industryテーブルと関連付け
ordertype = relationship("Ordertype", back_populates="ordercompany") #relationshipsにより、ordertypeテーブルと関連付け
[sql_app/schemas_API.py]
from typing import List, Optional #python 3.9以上ではListは省略可, 10以上ではこの行は省略可※その代わり下記記載が変わる
from pydantic import BaseModel
#Industry(業種)クラス
class IndustryBase(BaseModel):
industry: str
class IndustryCreate(IndustryBase):
pass
class Industry(IndustryBase):
id: int
class Config:
orm_mode = True #SQLAlchemyのORMを受け入れる
#Ordertype(発注形態)クラス
class OrdertypeBase(BaseModel):
ordertype: str
class OrdertypeCreate(OrdertypeBase):
pass
class Ordertype(OrdertypeBase):
id: int
class Config:
orm_mode = True
#Ordercompany(発注業者管理)(メイン)
class OrdercompanyBase(BaseModel):
companyname: str
personname: str
phonenum: str
email: str
producttype: str
project: str
remark: Optional[str] = None
# remark: str | None = None #Python3.10以上の記載方法
class OrdercompanyCreate(OrdercompanyBase):
pass
class Ordercompany(OrdercompanyBase):
id: int
id_industry: int #Industryクラスの外部キー
id_ordertype: int #Ordertypeクラスの外部キー
class Config:
orm_mode = True
#DELETE操作専用
class DeleteOrderCompany(BaseModel):
id: int
password: str
[sql_app/crud_DB.py]
from sqlalchemy.orm import Session #型ヒント用にSQLAlchemyのSessionクラス
from . import tables_DB #SQLAlchemyのテーブル
from . import schemas_API #APIの型ヒント
#READ操作
def get_industry(db: Session, skip: int=0, limit: int=100):
return db.query(tables_DB.Industry).offset(skip).limit(limit).all()
def get_ordertype(db: Session, skip: int=0, limit: int=100):
return db.query(tables_DB.Ordertype).offset(skip).limit(limit).all()
def get_ordercompanies(db: Session, skip: int =0, limit: int = 100):
return db.query(tables_DB.Ordercompany).offset(skip).limit(limit).all()
#CREATE操作
def create_industry(db: Session, industry: schemas_API.IndustryCreate):
db_industry = tables_DB.Industry(
industry = industry.industry,
)
db.add(db_industry)
db.commit()
db.refresh(db_industry)
return db_industry
def create_ordertype(db: Session, ordertype: schemas_API.OrdertypeCreate):
db_ordertype = tables_DB.Ordertype(**ordertype.dict()) #省略型の記法
db.add(db_ordertype)
db.commit()
db.refresh(db_ordertype)
return db_ordertype
def create_ordercompany(db: Session, ordercompany: schemas_API.OrdercompanyCreate, id_industry: int, id_ordertype: int):
#DB登録用のインスタンスを生成
db_ordercompany = tables_DB.Ordercompany(
**ordercompany.dict(),
id_industry = id_industry, #Pydanticで定義されていないキー
id_ordertype = id_ordertype #Pydanticで定義されていないキー
)
# db_ordercompany = tables_DB.Ordercompany(**ordercompany.dict()) #省略型の記法
db.add(db_ordercompany) #DB(session)に追加
db.commit() #DBに反映
db.refresh #インスタンスを更新->DBから新しい値を取得可能
return db_ordercompany
#DELETE操作
def delete_ordercompany(db: Session, datas: schemas_API.DeleteOrderCompany):
if datas.password == 'KIYO':
db_ordercompany = db.query(tables_DB.Ordercompany).filter(tables_DB.Ordercompany.id == datas.id).first()
db.delete(db_ordercompany)
db.commit()
return db_ordercompany
else:
return 'パスワードが間違っています。'
[sql_app/main_API.py]
import string
from typing import List #Python3.6 ver.
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session #SQL AlchemyのDB型
from . import crud_DB, tables_DB, schemas_API
from .database_DB import SessionLocal, engine
tables_DB.Base.metadata.create_all(bind=engine) #テーブル作成
app = FastAPI() #FastAPIのapp作成
# Dependency
def get_db():
db = SessionLocal() #DB接続
try:
yield db #DBリクエスト
finally:
db.close() #エラーが出ても必ずDB切断
#GETメソッド->特定のパラメータが不要なためDBのREAD操作を実行
@app.get("/industry/", response_model=List[schemas_API.Industry]) #response_modelで戻り値の型を指定
def read_industry(skip: int =0, limit: int =100, db: Session = Depends(get_db)):
return crud_DB.get_industry(db, skip=skip, limit=limit)
@app.get("/ordertype/", response_model=List[schemas_API.Ordertype])
def read_ordertype(skip: int =0, limit: int =100, db: Session = Depends(get_db)):
return crud_DB.get_ordertype(db, skip=skip, limit=limit)
@app.get("/ordercompany/", response_model=List[schemas_API.Ordercompany])
def read_ordercompany(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
ordercompany = crud_DB.get_ordercompanies(db, skip=skip, limit=limit)
return ordercompany
#POSTメソッド
@app.post("/industry/", response_model=schemas_API.Industry)
def create_industry(industry: schemas_API.IndustryCreate, db: Session = Depends(get_db)):
return crud_DB.create_industry(db, industry)
@app.post("/ordertype/", response_model=schemas_API.Ordertype)
def create_ordertype(ordertype: schemas_API.OrdertypeCreate, db: Session = Depends(get_db)):
return crud_DB.create_ordertype(db, ordertype)
#POST-CREATE操作
@app.post("/ordercompany/", response_model=schemas_API.Ordercompany)
def create_ordercompany(id_industry: int, id_ordertype:int, ordercompany: schemas_API.OrdercompanyCreate, db: Session = Depends(get_db)):
return crud_DB.create_ordercompany(db=db, ordercompany=ordercompany, id_industry=id_industry, id_ordertype=id_ordertype)
#POST-DELETE操作
@app.post("/ordercompany/delete/")
def delete_ordercompany(datas: schemas_API.DeleteOrderCompany , db: Session = Depends(get_db)):
return crud_DB.delete_ordercompany(db, datas)
次回からの注意点
エラーで死にそうだったので自分用の備忘録
参考資料
あとがき
とりあえずDB操作はシリーズとして何個か作る予定です。
●筋トレ管理:LINE通知+グラフ化(可視化)
●(取得ではなく)提出した見積もりの価格管理
●メーカーからの見積もり管理(上に乗せられるかな?)
●QAリスト管理表:質問に対する回答をメールベースでなくシステムベースへ変更して、かつデータベース化
作りたいものが増えるほど必要なスキルのハードルが急激に上がるので結構つらい。
この記事が気に入ったらサポートをしてみませんか?