Python データベース(SQLite)

全検索：SELECT * FROM テーブル名

SELECT * FROM shouhin

一件検索：SELECT * FROM テーブル名 WHERE 抽出条件

SELECT * FROM shouhin WHERE sid=1

追加：INSERT INTO テーブル名(列名1,列名2,列名3・・) VALUES(値1,値2,値3・・)

INSERT INTO shouhin(sname, tanka) VALUES('もも', 300)

更新：UPDATE テーブル名 SET 列名1=値 , 列名2=値 , 列名3=値・・ WHERE 抽出条件

UPDATE shouhin SET sname='かき', tanka=120 WHERE sid=4

削除：DELETE FROM テーブル名 WHERE 抽出条件

DELETE FROM shouhin WHERE sid=4

まずはデータベースに接続（.dbファイルを開く）ための関数 get_conを作成する。sqlite3.connect(ファイル名)により、データベースに接続し、connectionクラスのオブジェクトを取得する。ファイルが存在しない場合には作成される。

• ファイルの場所は同じフォルダ内とするため、os.path.dirname を使用
• isolation_level=Noneは、自動的に結果を確定（commit）させるためのもの。
• row_factory属性にsqlite3.Rowを設定し、検索結果を辞書型で受け取る（指定しないとタプルになる）。

shouhin.py

import os
import sqlite3

# コネクション取得
def get_con():
    path = os.path.dirname(__file__) + '/hanbai.db' # ファイルの場所
    con = sqlite3.connect(path ,  isolation_level=None)
    con.row_factory = sqlite3.Row
    return con

追加

insert関数を作成し、行を追加できるようにする。
get_con()メソッドを呼び出し、戻り値をconに入れる。withを使うことで自動的にconはclose()される。 そして、conのcursorメソッドでCursorオブジェクト cur を取得する。 sql文を文字列で作成し、Cursorオブジェクトのexecuteで実行する。

shouhin.py

def insert():
    with get_con() as con:
        cur = con.cursor()
        sql = "INSERT INTO shouhin (sname,tanka) VALUES ('ぶどう',100)"
        cur.execute(sql)

別ファイルから実行

関数としてまとめられているので別ファイルからモジュールとしてインポートし、実行できる。

dbmain.py

import shouhin

shouhin.insert()

１件検索

SELECT時にも基本は同じだが、executeを行った後に fetchoneメソッドで行を取得できる。

例：shouhin.pyにfind関数を作成し、sid=1の行を検索し返す。

def find():
    with get_con() as con:
        cur = con.cursor()
        cur.execute("SELECT * FROM shouhin WHERE sid=1")
        return cur.fetchone()

fetchoneで返された行は辞書と同様に使用可能。

実行例：dbmain.py

row = shouhin.find()

print(row['sid'], row['sname'], row['tanka'])

複数件検索

検索結果が複数ある場合、fetchallでリストとして取り出せる。

例：shouhin.py にall関数を作成。

def all():
    with get_con() as con:
        cur = con.cursor()
        cur.execute('SELECT * FROM shouhin')
        return cur.fetchall()

例：dbmain.py 取得した行をfor文で一行ずつ表示

rows = shouhin.all()

for row in rows:
    print(row['sid'], row['sname'], row['tanka'])

単純に文字列を連結し、SQLを発行する形のプログラムはセキュリティ上「SQLインジェクション」の危険がある。 これを避けるにはパラメータ埋め込みを使用する。

１．SQL中の埋め込みたい箇所に ? と書く

sql = "INSERT INTO shouhin (sname,tanka) VALUES (?, ?)"

２．埋め込み場所に入れる値をリストで指定し、executeの引数に指定

?に埋め込みたい値を順番にリストで指定。execute時に２番目の引数に指定する。

cur.execute(sql, [sname, tanka])

shouhin.py insert関数の例

def insert(sname, tanka):
    with get_con() as con:
        cur = con.cursor()
        sql = "INSERT INTO shouhin (sname,tanka) VALUES (?,?)"
        cur.execute(sql,[sname, tanka])

dbmain.py

引数で指定して追加の実行

insert("もも",300)

関数化したデータベースのファイルを外部からモジュールとして利用できる。

DBモジュール

shouhin.py。データベースの操作を実現するモジュール（関数）。

• insert 追加
• update 更新
• delete 削除
• all 全行を返す
• find 主キーで１件検索し返す

import os
import sqlite3

def get_con():
    path = os.path.dirname(__file__) + '/hanbai.db'
    con = sqlite3.connect(path ,  isolation_level=None)
    con.row_factory = sqlite3.Row
    return con

# 挿入
def insert(sname, tanka):
    with get_con() as con:
        cur = con.cursor()
        sql = "INSERT INTO shouhin (sname,tanka) VALUES (?,?)"
        cur.execute(sql,[sname, tanka])

# 更新
def update(sid, sname, tanka):
    with get_con() as con:
        cur = con.cursor()
        sql = "UPDATE shouhin SET sname=?,tanka=? WHERE sid=?"
        cur.execute(sql,[sname, tanka, sid])

# 削除
def delete(sid):
    with get_con() as con:
        cur = con.cursor()
        sql = "DELETE FROM shouhin WHERE sid=?"
        cur.execute(sql,[sid])

# 全件検索
def all():
    with get_con() as con:
        cur = con.cursor()
        cur.execute('SELECT * FROM shouhin')
        return cur.fetchall()

# 1件検索
def find(sid):
    with get_con() as con:
        cur = con.cursor()
        cur.execute("SELECT * FROM shouhin WHERE sid=?",[sid])
        return cur.fetchone()

モジュールの利用

dbmain.py

import shouhin

# 追加
shouhin.insert("もも",100)

# 変更
shouhin.update(10,"もも",200)

# 削除
shouhin.delete(10)

# 一件検索
row = shouhin.find(2)
print(row['sid'], row['sname'], row['tanka'])

# 全検索
rows = shouhin.all()
for row in rows:
    print(row['sid'], row['sname'], row['tanka'])