DB操作

最終投稿日：2022年6月18日

○ DB操作（SQLAlchemy）

[サンプルDB][宣言型モデルクラス][トランザクション][クエリの発行]

○ SQLite

[SQLiteによるDB操作]

○ mysqlclient

[mysqlclientによるMySQL操作

DB操作（SQLAlchemy）

サンプルDB

[SQLAlchemy]ライブラリを使ったDB操作となります。

● DB作成

create database `python15` character set='utf8mb4';
create user 'python';
grant all on python15.* to 'python'@'localhost' identified by 'python';

mysql -u python python15 -ppython

● サンプルDDL

create table python15.department(id int not null auto_increment

,name varchar(50) not null
,primary key(id)
) engine=innodb default character set utf8mb4;

create table python15.section(id int not null auto_increment

,department_id int not null
,name varchar(50) not null
,tel varchar(13) not null
,primary key(id)
,foreign key (department_id) references python15.department(id)
) engine=innodb default character set utf8mb4;

● サンプルデータ

insert into department (name) values ('営業本部');
insert into department (name) values ('総務部');
insert into department (name) values ('経理部');
insert into department (name) values ('企画部');
insert into department (name) values ('広報部');

insert into section (department_id,name,tel) values (1,'一課','020-1234-5678');
insert into section (department_id,name,tel) values (1,'二課','020-1234-1234');
insert into section (department_id,name,tel) values (1,'三課','020-9999-1234');
insert into section (department_id,name,tel) values (2,'総務課','020-9999-9999');
insert into section (department_id,name,tel) values (2,'庶務課','020-9999-8888');
insert into section (department_id,name,tel) values (2,'お茶課','020-9999-7777');
insert into section (department_id,name,tel) values (3,'経理課','030-9999-1111');
insert into section (department_id,name,tel) values (3,'財務課','030-9999-2222');
insert into section (department_id,name,tel) values (3,'お金課','030-9999-3333');
insert into section (department_id,name,tel) values (4,'企画課','050-9999-4444');
insert into section (department_id,name,tel) values (4,'マーケティング課','050-9999-5555');
insert into section (department_id,name,tel) values (4,'遊び課','050-9999-6666');
insert into section (department_id,name,tel) values (5,'広報課','060-7777-6666');
insert into section (department_id,name,tel) values (5,'CM課','060-6666-6666');
insert into section (department_id,name,tel) values (5,'ラジオ課','060-7777-6666');

宣言型モデルクラス

上記サンプルDBの内容でエンティティクラスを定義する
------------------------------------------------------------------------------------------------------

【entity.py】テーブル定義

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref

Base = declarative_base() ※型宣言モデル定義の基底クラス生成

class Department( Base ): ※各テーブル名でクラスを定義 ※基底クラスを継承

__tablename__ = 'department' ※テーブル名を指定

id = Column(Integer, primary_key = True) ※各カラムの定義
name = Column(String(50), nullable = False) ※各カラムの定義

sections = relationship('Section', backref=backref('department'))
※リレーション定義（変数名は任意）。第1引数：相手テーブルのクラス名、
名前引数 backref：backref メソッドに自身のテーブル名を指定

def __repr__(self):
                     ※オブジェクト自身を参照(print等)された際に返却されるメソッド。
                          結果セットが複数ある場合は内容がリスト型となる
    return 'Department:{0}:{1}'.format(self.id, self.name)

class Section(Base):

__tablename__ = 'section'

id = Column(Integer, primary_key = True)
department_id = Column(Integer, ForeignKey('department.id'), nullable = False)
※ForeignKey 属性にはリレーションを結んでいる相手の[テーブル名.カラム名]を指定
name = Column(String(50), nullable = False)
tel = Column(String(13), nullable = False)

def __repr__(self):
return 'Section:{0}:{1}:{2}:{3}'.format(self.id,self.department_id, self.name, self.tel)

------------------------------------------------------------------------------------------------------

【dbcon.py】DB接続設定

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def init():
    engine = create_engine('mysql+pymysql://python:python@localhost/python15?charset=utf8mb4',echo=False)
                     ※mysql+pymysql://ユーザー名:パスワード@接続先/DB名?charset=文字セット
    SessionMaker = sessionmaker(bind = engine)
                     ※1.恐らく返却されたものはクラスで、接続情報が内包されている状態（関数を返却する関数みたいなもの？）
                     ※2.実際にDB接続されたインスタンスを返却している。（接続情報が内包されているクラスのインスタンスを初期化する事で接続する？）
    return SessionMaker()

※上記2行は恐らく以下の様な仕組みにしていると思われる

# /////////////////////////////////////////////////////////////////////////////////////////////////
#    def sessionmaker(bind):                                          ※クラス定義を返却するメソッド
#        class hoge:                                                                 ※無名クラスなので名前は何でも良い
#            def __init__(self):
#                self.connect = bind                                          ※接続情報を内包する
#            def query(self):                                                     ※色々なメソッドを定義する
#                return self.connect + 'を使ってSQLを発行するメソッドとなる。。。'
#
#        return hoge                                                                ※無名クラスを返却する
#
#
#    SessionMaker = sessionmaker(bind = '接続する情報')            ※1.
#    con = SessionMaker()                                                                            ※2.
#    print(con.query())
#
#【出力内容】接続する情報を使ってSQLを発行するメソッドとなる。。。
# /////////////////////////////////////////////////////////////////////////////////////////////////

【メモ】

その他のDBへの接続設定 ※未確認。。

[ ORACLE ]

ORACLE_CONNECTION_STRING = 'oracle+cx_oracle://%(user)s:%(password)s@%(server)s:%(port)s/%(sid)s' % {
                     'user': ORACLE_USER,
                     'password': urllib.quote_plus(ORACLE_PASSWORD),
                     'server': ORACLE_HOST,
                     'port': ORACLE_PORT,
                     'sid': ORACLE_SID}

engine = create_engine(ORACLE_CONNECTION_STRING, coerce_to_unicode=True, echo=True)
SessionMaker = sessionmaker(bind=engine)
con = SessionMaker()

または、
engine = create_engine('oracle://ユーザー名:パスワード@127.0.0.1:1521/sid名')

[ POSTGRESQL ]

engine = create_engine('postgresql://ユーザー名:パスワード@localhost:5432/DB名')
SessionMaker = sessionmaker(bind=engine)
con = SessionMaker()

[ Microsoft SQL Server ]

engine = create_engine('mssql+pyodbc://ユーザー名:パスワード@mydsn')
SessionMaker = sessionmaker(bind=engine)
con = SessionMaker()

[ SQLite ]

engine = create_engine('sqlite:////absolute/path/to/foo.db')
SessionMaker = sessionmaker(bind=engine)
con = SessionMaker()

------------------------------------------------------------------------------------------------------

【hoge.py】

import dbcon
from entity import *

con = dbcon.init() ※DB接続

department = con.query(Department).first() ※色々SQLを発行する。。
print(department.name)

department = con.query(Department).all()
for row in department:
print((row.id,row.name))
print(department)

トランザクション

※con オブジェクトは、DB接続のインスタンスとする。
[SQLAlchemy]ではデフォルトでは、autocommit = False の設定になっているので、
データ操作した場合は常に commit が必要です。
デフォルトを autocommit = True にしたい場合は、接続設定をする際の以下のメソッドにて設定します。

SessionMaker = sessionmaker(bind = engine, autocommit = True)

【注意】

autocommit を設定すると rollback はできなくなります。

● テーブルロック

レコードをロックする場合とテーブルをロックする場合
例）

query = con.query(Section).filter(Section.id == 14).with_lockmode('update') ※レコードをロックする
result = query.one() ※query インスタンスは抽出条件を内包しているので、そのまま one() メソッドで抽出する。
result.name = 'ほげ' ※値を更新する
con.commit()

query = con.query(Section).with_lockmode('update')            ※テーブル全体をロックする
result = query.filter(Section.id == 14).one()                      ※抽出条件を設定して one() メソッドで抽出する。
result.name = 'ふ～'                                                                    ※値を更新する
con.commit()

● その他の制御

con.rollback()
con.commit()
con.close()

クエリの発行

テーブルの種類や関係は前述の例の通り以下の内容とする。

[テーブル]

department 部TBL
section 課TBL

[親子関係]

department.id = section.department_id 課TBLは部TBLの子となる

con オブジェクトは、DB接続のインスタンスとする。

※部TBLから最初の1レコードを取得する。query の引数はクラス名
result = con.query(Department).first()
print( result.name ) ※1行のみ取得なので、これで各カラムの値を取得できる

result = con.query(Department).all()       ※TBLから全てのレコードを取得する
    for row in result:
        print( row.name )                                      ※複数レコードが取得できるので、for で回す必要がある

print( result.sections ) ※リレーションは張った子の情報を取得

for row in result.sections:
print( row.name ) ※子の各カラムの情報を取得

● 検索条件

※filter メソッドを使う
※all() を指定すると、結果が1件でも回して値を取得する必要がある。
result = con.query(Section).filter(Section.name == 'お茶課').all()
for row in result:
print(row.name)

※結果が1件と分かる場合は one() を使うと値の取得が簡潔になる。(複数あるとエラー)
result = con.query(Section).filter(Section.name == 'お茶課').one()
print(result.name)

※レコード数が取得できる
result = con.query(Section).filter(Section.name == 'お茶課').count()
print(result.name)

※取得カラムを指定
result = con.query(Section.id,Section.name).filter(Section.name == 'お茶課').first()

※Like句の場合はこの様に記述する
result = con.query(Section).filter(Section.tel.like('050%')).all()

※in句は何故かアンダーバー付きの「in_」にし、内容はリスト型で記述する
result = con.query(Section).filter(Section.id.in_([2,5])).all()

※notin句も同上
※チルダ「~」と「in_」の組み合わせて「not in句」にもなる
result = con.query(Section).filter(Section.id.notin_([2,5])).all()
result = con.query(Section).filter(~Section.id.in_([2,5])).all()

※イコール、ノットイコール
result = con.query(Section).filter(Section.name == '遊び課').all()
result = con.query(Section).filter(Section.name != '遊び課').all()

※is null、is not null
result = con.query(Section).filter(Section.name == None).all()
result = con.query(Section).filter(Section.name != None).all()

※以上
result = con.query(Section).filter(Section.id >= 5).all()

from sqlalchemy import and_ ※AND 検索には、「and_」のインポートが必要
result = con.query(Section).filter(and_(Section.id == 5,Section.name == '庶務課')).all()

※OR 検索には、「or_」のインポートが必要
from sqlalchemy import or_
result = con.query(Section).filter(or_(Section.name == 'お茶課',Section.name == '庶務課')).all()

※ORDER BY で DESC を利用する場合は「desc」のインポートが必要
from sqlalchemy import desc
result = con.query(Section).filter(Section.id > 1).order_by(desc(Section.department_id),Section.id)

● 平文を使う

execute メソッドを使い、fetchall メソッドで抽出する
例）

result = con.execute("select * from section where id > 10").fetchall()
for row in result: ※fetchall() は複数件取得用なので for で回して
print(row['name']) ※連想配列で取得できる

● プレースホルダを使う

result = con.execute("select * from section where id > :id and name = :name",{'id':10,'name':'CM課'}).fetchall()
for row in result:
print(row) ※第2引数に辞書型でプレースホルダを設定できる

mySql = "select d.name as b_name, s.name as s_name from department d,section s where d.id = s.department_id and s.department_id = :id"
result = con.execute(mySql,{'id':3}).fetchall()
for row in result:
print(row['b_name']) ※平文なので結合も何でもできる

● 更新処理

特に更新処理に必要なメソッドはありません。
データをセレクトし、カラムの内容を変更し、commit すると勝手に更新されます。
※query メソッドから返却されたオブジェクトのみ有効
例）

result = con.query(Section).filter(Section.name == 'お茶課').one()
result.name = 'お茶係' ※one() メソッドでは1件しか抽出されないので直接更新できる
con.commit()

result = con.query(Section).filter(Section.id > 13).all()
for row in result:
row.name = 'ほのぼの' ※複数抽出される場合は、回して更新する
con.commit()

※execute メソッドの更新はそのまま平文で良い。
例）

result = con.execute("update section set name='8課' where id = 1")

● 登録処理

データの新規登録は add メソッド、add_all メソッドを使います。
例）

※テーブルのクラス名に対して設定する
con.add(Section(department_id=5, name='ばぁ', tel='030-999'))
con.commit()

con.add_all([                                          ※複数登録する場合はこちら
    Section(department_id=5, name='ほげ', tel='030-999')
    ,Section(department_id=5, name='ふ～', tel='030-999')
])
con.commit()

result = con.execute("insert into section (department_id, name, tel) values (5, 'ホゲ課', '090-9999')")
con.commit() ※平文も普通にOK

● 削除処理

データの削除は、delete メソッドを使う
例）

con.query(Section).filter(Section.id == 19).delete()
con.commit()

result = con.execute("delete from section where id > 16")
con.commit()