【Python】SQLAlchemyでMySQLに接続する

今回は、Pythonで簡単にSQLを実行できる`SQLAlchemy`という`O/Rマッパー`を使用し、実際にMySQLとマッピングしてCSVファイルのデータを追加したり、CRUD操作をする方法について実装を行ったので、備忘録として残しておこうと思います。

# 前提
- SQLAlchemyがインストールされていること
- mysql-connector-pythonがインストールされていること

上記モジュールがインストールされていない場合は、pipにてインストールを実行してください。

```terminal
% pip install SQLAlchemy
% pip install mysql-connector-python
```

# DBと接続しCSVファイルのデータを追加する
まずは、DBとの接続情報を環境変数として定義します。

## 環境変数の定義
私の場合、`.env`に必要な情報を環境変数として定義し、config.pyで読み込みと定義を行なっています。

ここに`PORT`が必要となる方は、PORTも定義してください。

```python:config.py
from dotenv import load_dotenv
load_dotenv()

import os

DB_USER = os.getenv('DB_USER')
PASSWORD = os.getenv('PASSWORD')
HOST = os.getenv('HOST')
DATABASE = os.getenv('DATABASE')
```

## DBと接続し、データを追加する
環境変数の定義が完了したら、DBと接続しCSVファイルのデータを追加します。
以下がその全容を表したコードです。

```python:sql_sample.py
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Float

import pandas as pd

import config

user = config.DB_USER
password = config.PASSWORD
host = config.HOST
db_name = config.DATABASE

# engineの設定
engine = create_engine(f'mysql+mysqlconnector://{user}:{password}@{host}/{db_name}')

# セッションの作成
db_session = scoped_session(
  sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
  )
)

# テーブルを作成する
Base = declarative_base()
Base.query  = db_session.query_property()

# テーブルを定義する
# Baseを継承
class Wine(Base):
  """ワインの情報をもつCSVファイルのクラス

  Args:
      Base (_type_): DeclarativeBase
  """
  # テーブル名
  __tablename__ = 'wines'
  # カラムの定義
  id = Column(Integer, primary_key=True, autoincrement=True)
  wine_class = Column(Integer, unique=False)
  alcohol = Column(Float, unique=False)
  ash = Column(Float, unique=False)
  hue = Column(Float, unique=False)
  proline = Column(Integer, unique=False)
  
  def __init__(self, wine_class=None, alcohol=None, ash=None, hue=None, proline=None):
    self.wine_class = wine_class
    self.alcohol = alcohol
    self.ash = ash
    self.hue = hue
    self.proline = proline

Base.metadata.create_all(bind=engine)

def read_data():
  """CSVファイルを読み込み、DBにデータを追加する関数
  """
  wine_df = pd.read_csv('./data/wine_class.csv')

  for index, _df in wine_df.iterrows():
    row = Wine(wine_class=_df['Class'], alcohol=_df['Alcohol'], ash=_df['Ash'], hue=_df['Hue'], proline=_df['Proline'])
    # データを追加する
    db_session.add(row)

  db_session.commit()

read_data()
```

### 接続設定
```python:sql_sample.py
user = config.DB_USER
password = config.PASSWORD
host = config.HOST
db_name = config.DATABASE

# engineの設定
engine = create_engine(f'mysql+mysqlconnector://{user}:{password}@{host}/{db_name}')

# セッションの作成
db_session = scoped_session(
  sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
  )
)

# テーブルを作成する
Base = declarative_base()
Base.query  = db_session.query_property()
```
正直、接続して初期データを追加するまでは定型的な記述となります。
まず重要な点としては、`create_engine()`にて、「どのDBにどのように接続するか」といった設定を保存する部分です。

ここに先ほど、環境変数で定義した値を渡すことで接続設定が行われます。

次にセッション(`sessionmaker`)の作成ですが、`SQLAlchemy`では、このセッションを用いてDBとのやり取りを行うため必須の記述となります。

その次の、`Base`はテーブルを作成するための基盤のようなもので、こちらも必須の記述です。

ここで、ようやく設定が完了しました。

### テーブル定義

```python:sql_sample.py
# テーブルを定義する
# Baseを継承
class Wine(Base):
  """ワインの情報をもつCSVファイルのクラス

  Args:
      Base (_type_): DeclarativeBase
  """
  # テーブル名
  __tablename__ = 'wines'
  # カラムの定義
  id = Column(Integer, primary_key=True, autoincrement=True)
  wine_class = Column(Integer, unique=False)
  alcohol = Column(Float, unique=False)
  ash = Column(Float, unique=False)
  hue = Column(Float, unique=False)
  proline = Column(Integer, unique=False)
  
  def __init__(self, wine_class=None, alcohol=None, ash=None, hue=None, proline=None):
    self.wine_class = wine_class
    self.alcohol = alcohol
    self.ash = ash
    self.hue = hue
    self.proline = proline

Base.metadata.create_all(bind=engine)
```

設定が完了したら、テーブルのデータをオブジェクト(クラス)として扱うために、テーブルの内容を定義します。
必要となる情報は、テーブル名とテーブルのカラム名(CSVの列名)です。
そしてインスタンスが作成されたときに、データを追加(`INSERT`)するため、コンストラクタを定義します。

最後に実際にDBに存在するテーブルとマッピングを行うため、`Base.metadata.create_all()`でバインドを行います。

※ 実際に使用するDBにテーブルが存在しないと`doesn't exist`とマッピング先のテーブルがないというエラーが起こります。

### CSVファイルのデータを追加する

```python:sql_sample.py
def read_data():
  """CSVファイルを読み込み、DBにデータを追加する関数
  """
  wine_df = pd.read_csv('./data/wine_class.csv')

  for index, _df in wine_df.iterrows():
    row = Wine(wine_class=_df['Class'], alcohol=_df['Alcohol'], ash=_df['Ash'], hue=_df['Hue'], proline=_df['Proline'])
    # データを追加する
    db_session.add(row)

  db_session.commit()

read_data()
```
いよいよデータを追加していきます。
あらかじめ用意しているCSVファイルを `pandas`で読み込み、1行ずつ追加(`add`)していきます。
この部分がCRUDでいうところの`CREATE`になります。

最後に`commit()`をしてDBに処理を反映させます。
これはSQLを使用している人は理解しやすいと思いますが、コミットしないとテーブルに対して行なった処理が反映されないので、コミットが必要となります。

# CRUD操作
つづいて、上記でマッピングしたテーブルやデータに対してCRUD操作の実装を行います。

## READ
まずは、全件取得してみましょう。
SQLでいうところの`SELECT * FROM テーブル名`です。
```python:sql_sample.py
# DBにあるWineのデータを全件取得
db = db_session.query(Wine).all()
for row in db:
  # カラムを指定してデータを取得する
  print(row.alcohol)
```

`SQLAlchemy`では基本的に`session.query(クラス名).行いたい処理`という形式でクエリを発行します。
そして、取得するデータはインスタンスで取得されるので、カラムを指定するなどしないと、下記の出力がリストでなされることになります。
```
<__main__.Wine object at 0xxxxxxxxxxx>
```

基本的なREADの解説は完了したので、以下に続く処理については最低限の解説をするにとどめます。

### カラムを指定して取得
```python:sql_sample.py
db = db_session.query(Wine.hue, Wine.proline).all()
```

### 条件抽出(WHERE句)
```python:sql_sample.py
db = db_session.query(Wine).filter(Wine.hue > 1.0).all()
```
`WHERE`にあたる部分が`filter()`となり、引数に条件を指定することで条件抽出が可能となります。

### 取得レコード数に件数制限をかける(LIMIT句)
```python:sql_sample.py
db = db_session.query(Wine).limit(20).all()
```
こちらは、そのまんまですが`limit()`に取得件数を渡して実装します。

### レコードの並び替え(ORDER BY句)
```python:sql_sample.py
from sqlalchemy import desc
db = db_session.query(Wine).order_by(desc(Wine.proline)).all()
```
ORDER BYを使用する場合も、そのまんまですが、`SQLAlchemy`の`desc関数`をインポートする必要があります。

## CREATE
続いてCRUDのCですが、こちらも簡単に行えます。
```python:sql_sample.py
# CREATE
wine = Wine(wine_class=1, alcohol=1, ash=1, hue=1, proline=1)
# Insertされる
db_session.add(wine)
db_session.commit()
```
最初に`SQLAlchemy`は`O/Rマッパー`であると説明しましたが、テーブルとマッピングしているため、マッピングしているオブジェクトのインスタンスを生成し、セッションで`add()`するだけでCREATEできます。

最後にコミットを忘れなければデータの作成ができます。

## UPDATE
SQLでもそうですが、UPDATEは条件を指定する必要があります。
なぜなら、条件を指定しないと、全データがUPDATEされてしまうからです。
その点を考慮すると、先ほどの`READ`の部分での実装と組み合わせればできそうですね。

```python:sql_sample.py
# UPDATE
# 条件に合致するものを1件(first())取得
db = db_session.query(Wine).filter(Wine.proline == 1).first()
db.wine_class = 10
db_session.commit()
```
まずは、UPDATEしたいデータを条件抽出し、「どの値を更新するか」を指定し、更新する値を代入します。
この部分はSQLのUPDATE文内の`SET`にあたります。

そして、値の代入が完了したら、こちらもコミットします。

## DELETE
こちらも注意点としてはUPDATEと同じで、条件を指定する必要があります。

```python:sql_sample.py
# DELETE
db_session.query(Wine).filter(Wine.proline == 1).delete()
db_session.commit()
```
DELETEの場合は、条件を`filter()`で抽出し、`delete()`するだけで実装できます。
こちらも例のごとくコミットします。

# まとめ
Pythonの`SQLAlchemy`によるSQLの実装(DB操作)について解説しました。
ここまで簡単にSQLが扱えるのは、SQL経験者としては非常にラクだなと感じました。また、私の場合はJavaで仕事しているので、Pythonのとっつきやすさを感じました。ぜひご自身の環境で実装してみてください。

# 参考文献
- [vol.032 データベース操作がめっちゃ楽になる！PythonのORM SQLAlchemyとは？ | 中学生でもわかるPython入門シリーズ](https://www.youtube.com/watch?v=inEqP234Dpc)
- [SQLAlchemyを使ってMySQLに接続する](https://www.gesource.jp/weblog/?p=8545)