Python データベース

参照：SELECT 列名1,列名2,列名3・・ FROM テーブル名 WHERE 抽出条件　ORDER BY 整列順

※抽出条件：sid=1 など。LIKE で文字列の部分検索可能（例：snameが「り」で始まる rsname LIKE "り%"）

※整列順：列名 昇順(ASC) or 降順(DESC)

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

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

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

Pythonが標準で対応するデータベース。さまざまな言語はスマートフォンでも標準対応している。

SQLiteダウンロード

Precompiled Binaries for Windows のsqlite-toolsをダウンロード

https://www.sqlite.org/download.html

sqlite3.exeをコピー。

DBの新規作成

sqlite3 ファイル名

例：sqlite3 hanbai.db

テーブル作成

CREATE TABLE shouhin(sid INTEGER PRIMARY KEY,sname TEXT,tanka INTEGER);

確認：
.tables
.schema テーブル名

データの追加

INSERT INTO shouhin (sname, tanka) VALUES('りんご', 100);

データの表示

※事前に .exaplain ON をしておくと列名なども表示される。

SELECT * FROM shouhin;

終了

.exit

データベースに接続し、connectionクラスのオブジェクトを取得する。
そして、Cursorオブジェクトを取得する。

import sqlite3

con = sqlite3.connect('hanbai.db')
con.row_factory = sqlite3.Row # 検索結果を辞書型で受け取る 

cur = con.cursor()

hanbaiデータベースに接続する例。何度も使うので関数 get_con内に記述。

# コネクション取得
def get_con():
	con = sqlite3.connect('hanbai.db')
	con.row_factory = sqlite3.Row # 検索結果を辞書型で受け取る 
	return con

SQLの実行

まず、connectorクラスのcursorメソッドでCursorオブジェクトを取得する。 sql文を文字列で作成実行する。Cursorオブジェクトのexecuteで実行する。

con = get_con()
cur = con.cursor()

sql = "INSERT INTO shouhin (sname,tanka) VALUES ('ぶどう',100)"

cur.execute(sql) # SQL実行

データベースの切断

処理が終わったら各オブジェクトのclose処理を行い、データベースへの接続を切断する。

con.close()

サンプル

shouhin.py

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

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

例：dbmain.py

import shouhin

shouhin.insert()

１件検索

SELECT時にも基本は同じだが、executeを行った後に fetchoneメソッドで行を取得できる。
一行はタプルとなっている。

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

def find():
    con = get_con()
    cur = con.cursor()

    cur.execute('SELECT * FROM shouhin WHERE sid=1')

    row = cur.fetchone()
    con.close()
    
    return row

実行例：dbmain.py

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

複数件検索

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

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

def all():
    con = get_con()
    cur = con.cursor(dictionary=True)

    cur.execute('SELECT * FROM shouhin')
    rows = cur.fetchall()

    con.close()
    return rows

例：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の引数に指定

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

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

shouhin.py insert関数の例

def insert(sname, tanka):
    con = get_con()
    cur = con.cursor()

    sql = "INSERT INTO shouhin (sname,tanka) VALUES (?,?)"
    cur.execute(sql,[sname, tanka])
    con.close()

dbmain.py

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

insert("もも",300)

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

DBモジュール

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

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

import sqlite3

def get_con():

    con = sqlite3.connect(r'e:\python\db\sqlite\hanbai3.db',  isolation_level=None)
    con.row_factory = sqlite3.Row # 辞書型で受け取る 
    return con

# 挿入
def insert(sname, tanka):
    con = get_con()
    cur = con.cursor()

    sql = "INSERT INTO shouhin (sname,tanka) VALUES (?,?)"
    cur.execute(sql,[sname, tanka])
    con.close()

# 更新
def update(sid, sname, tanka):
    con = get_con()
    cur = con.cursor()

    sql = "UPDATE shouhin SET sname=?,tanka=? WHERE sid=?"
    cur.execute(sql,[sname, tanka, sid])
    con.close()

# 削除
def delete(sid):
    con = get_con()
    cur = con.cursor()

    sql = "DELETE FROM shouhin WHERE sid=?"
    cur.execute(sql,[sid])
    con.close()

# 全件検索
def all():
    con = get_con()
    cur = con.cursor()

    cur.execute('SELECT * FROM shouhin')
    rows = cur.fetchall()

    con.close()
    return rows

# 1件検索
def find(sid):
    con = get_con()
    cur = con.cursor()

    sql = "SELECT * FROM shouhin WHERE sid=?"
    cur.execute(sql,[sid])
    row = cur.fetchone()

    con.close()
    return row

モジュールの利用

dbmain.py

import shouhin

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

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

# 削除
shouhin.delete(10)

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

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