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 抽出条件

pipコマンドで mysql-connector-python をインストールする。

pip install mysql-connector-python

ファイルの先頭でimportを行う。

import mysql.connector

まず、データベースに接続し、connectorクラスのオブジェクトを取得する。

con = mysql.connector.connect(
    host = 'ホスト名',   # ローカルなら127.0.0.1 または省略可能
    user = 'ユーザ名',
    password = 'パスワード',
    database = 'データベース名',
    autocommit = True	# 自動コミットを有効にしておく
)

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

# コネクション取得
def get_con():
    con = mysql.connector.connect(
        user = 'root',
        password = '',
        database = 'hanbai',
        autocommit = True
    )
    return con

SQLの実行

まず、connectorクラスのcursorメソッドでCursorオブジェクトを取得する。 次にcursorオブジェクトのexecuteでSQLを実行する。

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) # 一行表示

辞書として取得

現状は検索結果がタプルなので0番目がsid、1番目がsname、2番目がtanka になっており個別の項目を表示する際に番号で指定しなくてはならない。

例：dbmain.py

# sname のみ表示
print(row[1])

そこで find関数で、cursorを取得する際にdictionary=Trueを指定する。

例：shouhin.py

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

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

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

すると辞書として取得出来る。

例：dbmain.py

# sname のみ表示
print(row['sname'])

複数件検索

検索結果が複数ある場合、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)

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

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

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

２．埋め込み場所に入れる値をリストで指定し、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 (%s,%s)"
    cur.execute(sql,[sname, tanka])
    con.close()

dbmain.py

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

insert("もも",300)

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

DBモジュール

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

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

import mysql.connector

def get_con():
    con = mysql.connector.connect(
        user = 'root',
        password = '',
        database = 'hanbai',
        autocommit = True
    )
    return con

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

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

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

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

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

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

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

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

    con.close()
    return rows

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

    sql = "SELECT * FROM shouhin WHERE sid=%s"
    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 r in rows:
    print(r)

# 一件検索
row = shouhin.find(1)
print(row)