Windows で FastAPI で SQL Server のデータを利用する２

# 背景
会社にて、FastAPI で、SQL Server の情報を利用する必要が生じたので、試行した記録

今回は以下
1. SQLAlchemy 
1. ODBC Driver
1. SQL Server アクセス

以下を参考に

https://fastapi.tiangolo.com/ja/tutorial/sql-databases/?h=db#alternative-db-session-with-middleware

# SQLAlchemy インストール

```bash: install SQLAlchemy
pip3 install sqlalchemy
```

# ODBC インストール
## ドライバー一覧の確認
最初は入ってないと思うので、インストール後に確認用
```bash: ドライバー一覧
cat /etc/odbcinst.ini
```

基本は以下

## WSL のバージョン確認
```bash: バージョン確認
lsb_release  -rs
```

## pyodbc のインストール
```bash: pyodbc install
pip3 install pyodbc
```

## ODBC Driver インストール
https://learn.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

```bash: ODBC Install
if ! [[ "18.04 20.04 22.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev
```

curl のとこを実行すると以下な感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/7e2892a1-e052-7bff-c532-a4c449e77765.png)

## curl が上手くいかなかった場合の対処法
prod.list を確認して
https://packages.microsoft.com/config/ubuntu/20.04/prod/pool/main/m/
辺りから、以下をwindows 上でダウンロード
msodbcsql18_18.1.1.1-1_amd64.deb
mssql-tools18_18.1.1.1-1_amd64.deb
/tmp 等にコピーをして、以下のようにして解凍
sudo dpkg -i /tmp/msodbcsql18_18.1.1.1-1_amd64.deb
sudo dpkg -i /tmp/mssql-tools18_18.1.1.1-1_amd64.deb
あとは、上述の<code>sudo apt-get update</code> 以降を実施

:::note warn
解凍順序を逆にするとエラーにが出るので注意
:::

:::note info
Ver 17 にしたいなら、適宜修正。
以下も参考に
:::
https://learn.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16#17

### エクスプローラーから、wsl へのアクセス
```dosbatch: エクスプローラーからwsl オープン
\\wsl$
```


# FastAPI のチュートリアルに従って、sample app を準備する

https://fastapi.tiangolo.com/ja/tutorial/sql-databases/?h=db#file-structure

基本は以下な感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/9da9f912-bb4b-c44f-0098-731e2961fe21.png)

準備したら、DB接続の確認へ

# SQL Server への接続
チュートリアルでは、以下のように、sqlite 用になっているので、これを修正する必要がある

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/31ab80f7-9868-02ee-500b-b433863af5ee.png)

## SQL Server 用の修正
ここからは、以下を参考に

https://docs.sqlalchemy.org/en/14/dialects/mssql.html?highlight=sql+server#module-sqlalchemy.dialects.mssql.pyodbc

```python: SQL Server 用 database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import URL

connection_url = URL.create(
    "mssql+pyodbc",
    username="user",
    password="pass",
    host=r"USER\SQLEXPRESS",
    port=1433,
    database="testDB",
    query={
        "driver": "ODBC Driver 18 for SQL Server", # 17 でもOK
        "autocommit": "True",
        "LongAsMax": "Yes",
    },
)
engine = create_engine(
    connection_url,
    encoding="utf-8",
    echo=True,
    connect_args={"TrustServerCertificate": "yes"},
    pool_pre_ping=True,
    pool_recycle=3600,
    pool_timeout=30,
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()
```
:::note info
接続情報は適宜修正のこと
:::

# 実際の動作確認
```bash: uvicorn 起動
uvicorn fastapi_sample_app.main:app --reload
```
:::note warn
以下のように、フォルダ内で実行すると、下記のようなエラーが出るので注意
```bash: app フォルダで起動
uvicorn main:app --reload
```

> attempted relative import with no known parent package
:::

## 成功時
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/b8990136-8bfa-5e4f-fdb3-dcc647889494.png)

## 失敗の対処記録
### SQL Server にログイン出来ない
原因
- SQL Server Express で SQL Server 認証モードを追加してなかった :sweat_smile: 

対処
- 以下設定変更
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/cb3b122f-333a-aa94-1ad6-5f13732a0a19.png)

### uvicorn 起動時にエラー No Kerberos credentials available
> sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (851968) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/dbapi)

原因
- URL.query に "authentication": "ActiveDirectoryIntegrated", を突っ込んでいた :sweat_smile: 

対処
- 上記を削除

### uvicorn 起動時にエラー Login timeout expired
> sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/e3q8)

なんか色々あった・・Connection String は見直し必須。その上で以下かな

原因
- SQL Server Express の Port を固定していなかった？

対処
- SQL Server Configuration Manager で設定して、Server 再起動<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/bff4082e-ffa4-090d-c490-f4b220e90cea.png)

:::note info
SQL Server Configuration Manager は以下から Install
:::
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16

# 最後に実際のデータ取得確認
DB に合わせて、以下を修正した上で・・
schemas.py
models.py 

無事、LinqPadと同じデータが取れることを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/9968a5a3-9fd1-9a64-0eeb-f6ed3763d4c1.png)


# 参考
確認用にLocalにSQL Server Express を入れる場合は以下

[SQL Server 2019 express](https://www.microsoft.com/ja-jp/download/confirmation.aspx?id=ecda22c2-e899-4110-b529-6a647c2f1861)

# あとがき
ようやく、FastAPI で SQL Server のデータを取得出来るとこまできた・・
次は Database First で、Model の自動生成方法を・・かな

# 前
https://qiita.com/baku2san/items/4fc6bf962bb8efdd9dec