【Python】SQLAlchemyでSQLServerのCRUD操作

スポンサーリンク

以前、SQLAlchemyでSQLServerにテーブルを作成する記事を書きましたがその続きです。
今回は、SQLAlchemyでCRUD操作をやってみたいと思います。やってみた感想は、特にC(create)のINSERT処理は、簡単に書けますし、SQLを直接記述することもできるのでDBのCRUD処理でSQLAlchemyを使わない理由はないなと思いました。当記事は、以前の記事で作成したDBとテーブルを使用しています。同じ環境構成を作りたい方は以下の記事を参考にして下さい。

【Python】SQLAlchemyでSQLServerのテーブルを作成
当記事はSQLAlchemyとpyodbcを使用してSQLServerに接続してデータベースとテーブルを実際に作成したときの対応方法について書こうと思います。

DBの作成とテーブルの作成

以前上記の記事で、作成方法については書いているのでソースのみ掲載します。私の環境ではdb.pyというファイル名で作成しています。

import urllib
from sqlalchemy import Column,String, create_engine
from sqlalchemy.ext.declarative import declarative_base

#pyodbcの接続文字列
#ドライバー名
driver = r'ODBC Driver 17 for SQL Server'
#インスタンス名(サーバ名\インスタンス名)
server = r'localhost\xxxx'
#SQL Serverに接続するユーザー名
username = 'ユーザー名'
#パスワード
password = 'パスワード'
#データベース名
db = 'test'


#ODBCの文字列を作成
odbc_connect = urllib.parse.quote_plus(
    'DRIVER={%s};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s' % (driver, server, username, password,db))


#データベースに接続するエンジンを作成
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % odbc_connect,echo=True)


#テーブル定義の基底クラス
Base = declarative_base()


#基底クラスBaseを継承してクラスを作成
class User(Base):
    __tablename__ = 'users'
    userid = Column('userid',String(32),primary_key = True)
    username = Column('firstname',String(32))


if __name__ == '__main__':
    Base.metadata.create_all(bind=engine)
if __name__ == '__main__':は、python db.pyで直接実行しない限り実行されないようにしています。

CRUD処理

CRUD処理は、新規作成(Create)・読み取り(Read)・更新(Update)・Delete(削除)に対応しています。SQL文でいうと以下のようになります。

CRUD説明SQL
Create新規作成INSERT
Read読み取りSELECT
Update更新UPDATE
Delete削除DELETE

INSERT(新規作成)

まずは、INSERTです。ここではuseridtestid1、firstnametestuser1を追加しています。
SQL文でいうとINSERT INTOを実行しているのと同じです。


以下のソースはdb.pyと同じフォルダにcrud.pyという名前を作成して実行しています。データベースに接続するためのセッションはfrom sqlalchemy.orm import sessionmakerで作成されます。
セッションを確立時に使用するエンジンはdb.pyで作成したengineを使います。なのでimport dbするのを忘れないようにしましょう。また,session.add(user)で追加してもsession.commit()を実行しなければデータベースには反映されません。かならず,反映する場合はcommitを忘れずにしましょう。
あとcloseも忘れずにしましょう。

from sqlalchemy.orm import sessionmaker
import db

#db.pyで作成したエンジンをバインドしてセッションを作成

SessionLoacal = sessionmaker(bind=db.engine)

session = SessionLoacal()

#insertしてみる

user = db.User()

user.userid = 'testid1'

user.username = 'testuser1'

session.add(user)

session.commit()
session.close()
このPGを以下のコマンドで実行します。
python crud.py

以下のようにユーザーが追加されていればOKです。

Read(読み取り)

ここでは,INSERTで追加したtestid1が追加されているかデータを読み取って確認しています。
SQL文でいうとSELECT * FROM users WHERE userid = ‘testid1’を実行したことになります。
SQLAlchemyでデータの抽出条件を書くときはquery(テーブルクラス).filter(条件式)で取得できます。

user = session.query(db.User).filter(db.User.userid == 'testid1').first()
print(user.userid)
(実行結果)
testid1

Update(更新)

ここでは、INSERTで追加したtestid1のfirstnameをtestuser1→testuser2に更新しています。
最初にreadで読み取りした結果を変数にセットして、別の値に書き換えています。

SQLで言うとUPDATE USERS SET firstname = ‘testuser2’ WHERE userid = ‘testid1’を実行した状態です。

user = session.query(db.User).filter(db.User.userid == 'testid1').first()
user.username = 'testuser2'
session.commit()
session.close()

Delete(削除)

ここでは、INSERTで追加したtestid1を抽出して削除しています。
SQLだとDELETE FROM users WHERE userid = ‘testid1’を実行した状態です。

SQLAlchemyでデータを削除するときは、delete()を使います。

user = session.query(db.User).filter(db.User.userid == 'testid1').delete()
session.commit()
session.close()

以下のように削除されていればOKです。

まとめ

  • SQLAlchemyのCreate(作成)は、add()を使う。
  • SQLAlchemyのRead(読み取り)は、
    query(テーブルクラス).filter(条件式)を使う。
  • SQLAlchemyのUpdate(更新)は、読み取りしたデータを
    変数にセットして置き換える。
  • SQLAlchemyのDelete(削除)は、delete()を使う。
  • commit()を実行しなければデータは反映されない。
  • セッションのcloseを忘れずに実行する。

コメント

タイトルとURLをコピーしました