SQL Server、Python、Pyodbc、およびSQLAlchemy

# ツールについて

SQL Serverは、
Microsoftによって開発されたリレーショナルデータベース管理システムです。他のアプリケーションが必要とするデータの格納と取得に使用されます。

PyMySQLは
PythonからMySQLに接続するためのライブラリの一つ
公式ドキュメント

https://pymysql.readthedocs.io/en/latest/

Pythonは
高水準で解釈されるプログラミング言語であり、そのシンプルさと可読性で知られています。データ分析、Web開発、機械学習などに広く使用されています。

Pyodbcは、
SQL Serverに接続するためのオープンソースのPythonライブラリです。データベースとのやり取りにOpen Database Connectivity（ODBC）の標準を使用し、
Pythonとさまざまなデータベースシステムを接続することができる万能なツールです。

SQLAlchemyは、
Pythonアプリケーション向けのSQLツールキットとオブジェクトリレーショナルマッピング（ORM）の機能を提供する別のPythonライブラリです。
SQLコマンドとPythonコードを使用して、PythonアプリケーションがSQLデータベースと接続してやり取りすることができます。


# SQLAlchemyを使用してSQL Serverに接続する方法は？

# SQLAlchemyとは
SQLAlchemyは、PythonのORMの1つで、Pythonの中でよく利用されているORMの1つ

# ORMとは
Object-Relational Mappingの略
テーブルとオブジェクト志向言語のクラスを1対1に対応させて，そのクラスのメソッド経由でデータを取得したり，変更したりできる

# ORMの利点とは
①異なるDBの違いを吸収する
データベースの種類によらず、同じソースコードで操作できるので，複数のDBを併用する場合や，DBを変更する場合にも，コードの書き換えの必要がない．
②SQLを書かなくてもよい
SQLAlchemyを使うと，SQLを直接記述することなしに，DBを"Pythonic"に操作できる

# インストール
```py
 pip install sqlalchemy
 # MySQL用
 pip install PyMySQL
 # SQL Server用
 # pip install pyodbc
```

# 設定ファイルの作成/DB接続
```setting.py
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

dialect = "mysql"
driver = "pymysql"
username = "root"
password = "root"
host = "127.0.0.1"
port = "3306"
database = "sample_db"
charset_type = "utf8"
db_url = f"{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type}"

# DB接続するためのEngineインスタンス
ENGINE = create_engine(db_url,
                       # echo=Trueだと実行のたびにSQLが出力される 
                       echo=True)

# DBに対してORM操作するときに利用
# Sessionを通じて操作を行う
session = scoped_session(
    sessionmaker(autocommit=False, 
                 autoflush=False, 
                 bind=ENGINE)
)

# 各modelで利用
# classとDBをMapping
Base = declarative_base()
Base.query = session.query_property()
```
<table>
    <thead>
        <tr>
            <th style="text-align:center">要素</th>
            <th style="text-align:center">説明</th>
        </tr>
    </thead>
    <tbody>
    <tr>
        <td style="text-align:center">dialect</td>
        <td style="text-align:center">DBの種類(sqlite, mysql, postgresql, oracle, mssql)</td>
    </tr>
    <tr>
    <td style="text-align:center">driver</td>
    <td style="text-align:center">DBに接続するためのドライバー("default" DBAPIになる）</td>
    </tr>
    <tr>
        <td style="text-align:center">username</td>
        <td style="text-align:center">DBに接続することができるユーザ名</td>
    </tr>
    <tr>
        <td style="text-align:center">password</td>
        <td style="text-align:center">DBに接続するためのパスワード</td>
    </tr>
    <tr>
        <td style="text-align:center">host</td>
        <td style="text-align:center">ホスト名。(localhost, IPアドレス)</td>
    </tr>
    <tr>
        <td style="text-align:center">port</td>
        <td style="text-align:center">ポート番号。(指定しなければ、defaultのポート番号)</td>
    </tr>
    <tr>
        <td style="text-align:center">database</td>
        <td style="text-align:center">接続するデータベース名</td>
    </tr>
    <tr>
        <td style="text-align:center">charset_type</td>
        <td style="text-align:center">文字コード(utf8とか)</td>
    </tr>
    </tbody>
</table>

# テーブル作成
```user.py
from sqlalchemy import Column, Integer, String, DateTime, Sequence
from datetime import datetime
from os.path import dirname, abspath
import sys
import pprint

# pprint.pprint(sys.path)
# user.pyから3つ上のディレクトリの絶対パスを取得し、sys.pathに登録する
parent_dir = dirname(dirname(dirname(abspath(__file__)))) # 追加
if parent_dir not in sys.path: # 追加
    sys.path.append(parent_dir) # 追加

from sql.setting.setting import ENGINE, Base
print(ENGINE)
print(Base)

class User(Base):
    """
    ユーザモデル
    """
    __tablename__ = 'users'
    __table_args__ = {
        'comment': 'ユーザー情報のマスターテーブル'
    }
    
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True, autoincrement=True)
    name = Column('name', String(200))
    age = Column('age', Integer)
    email = Column(String(255))
    created_at = Column('created', DateTime, default=datetime.now, nullable=False)
    updated_at = Column('modified', DateTime, default=datetime.now, nullable=False)


def main(args):
    Base.metadata.create_all(bind=ENGINE)


if __name__ == "__main__":
    main(sys.argv)
```
```primary_key```
primary_key=Trueとすることで、カラムを主キーとして扱います。

```auto_increment```
auto_increment=Trueとすることで、カラムはレコードが追加される毎に連番がわりふられていきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/592268/bdb7ba4d-a1c9-dec4-c2db-6a01f4407622.png)

# ORM操作
```main.py
from models.user import User
from setting.setting import session
from sqlalchemy.sql import text

# INSERT
user = User()
user.name = '山田太郎'
user.email = 'yamata@xxx.com'
user.age = 39
session.add(user)
session.commit()
session.close()

# Select
users = session.query(User).filter(User.name == "山田太郎").all()
for user in users:
    print(user.name, user.age)
session.close()

# Update
user = session.query(User).filter(User.id =="2").first()
user.name = '次郎'
session.commit()
session.close()

# Delete
session.query(User).filter(User.name == "山田太郎").delete()
session.commit()
session.close()


# 直接SQLを実行
#(1)生のSELECTを定義
t = text("select * from users")
#(2)SQL実行
result = session.execute(t)
#(3)取得結果のレコードから値が取得
for row in result:
    print(row)
```

SQLAlchemy公式ドキュメント

https://docs.sqlalchemy.org/en/20/dialects/index.html

# Pyodbcを使用してPythonをSQL Serverに接続する

1. Pyodbcライブラリをインストールする
```py
pip install pyodbc
```


2.Pyodbcモジュールをインポート
```py
import pyodbc
```

3.接続を確立する
 サーバー名、データベース名、ユーザー名、パスワードが必要になります。
 ```py
 conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user_name;PWD=password')
 ```
4.データベースとのやり取りをする
 cursor.execute()メソッドを使用します。
 ```py
 cursor = conn.cursor()
 cursor.execute('SELECT * FROM employees')
 
 for row in cursor:
    print(row)
 ```
5.接続を閉じる
```py
conn.close()
```
****