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