ツールについて

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

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

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"に操作できる

インストール

 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()

要素	説明
dialect	DBの種類(sqlite, mysql, postgresql, oracle, mssql)
driver	DBに接続するためのドライバー("default" DBAPIになる）
username	DBに接続することができるユーザ名
password	DBに接続するためのパスワード
host	ホスト名。(localhost, IPアドレス)
port	ポート番号。(指定しなければ、defaultのポート番号)
database	接続するデータベース名
charset_type	文字コード(utf8とか)

テーブル作成

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とすることで、カラムはレコードが追加される毎に連番がわりふられていきます。

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公式ドキュメント

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

Pyodbcライブラリをインストールする

pip install pyodbc

2.Pyodbcモジュールをインポート

import pyodbc

3.接続を確立する
サーバー名、データベース名、ユーザー名、パスワードが必要になります。

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user_name;PWD=password')

4.データベースとのやり取りをする
cursor.execute()メソッドを使用します。

cursor = conn.cursor()
cursor.execute('SELECT * FROM employees')

for row in cursor:
   print(row)

5.接続を閉じる

conn.close()

SQL Server、Python、Pyodbc、およびSQLAlchemy