PythonによるMySQLの操作

PythonでMySQLを介してDBを操作してみました。
その際に引っかかった箇所や初歩的な箇所を簡単にですがまとめてみました。

対象者

PythonでMySQLを操作してみたい方
大量データを素早くDBに書き込みたい方

MySQLとは

オープンソースで公開されている関係データベース管理システム (RDBMS) の一つである。
GNU GPL(無料で使えるライセンス)と商用ライセンスのデュアルライセンスとなっている。
(ウィキペディアから一部抜粋)
ウィキペディア/MySQL

メリット

• 運用経験者が多い
• 高いパフォーマンス

デメリット

• 脆弱性対応が遅い

↓その他のDBMSとの比較するときのご参考に
SQLの観点から「Oracle Database」「PostgreSQL」「MySQL」の特徴を整理しよう！

MySQLを導入する際は下記を参考にすると良いと思います。
MySQLのダウンロード＆インストールと初期設定方法

Pythonの必要なパッケージ

pip

pip install MySQL-Python

MySQLと環境設定の説明はこの辺にして実際に書いたコードの内容に移ります。

実装概要

• MySQLに接続&文字のエンコード(utf-8)
• 指定DBのアクティブ化
• データテーブルの作成
• データの挿入(大量データに対応)
• データテーブルから指定データの取り出し

実装内容

今回はクラスが実装時に都合が良かったので下記で説明していく機能は全て１つのクラスのメソッドになります。

• MySQLに接続&文字のエンコード(utf-8)

DB_Manipulate/__init__

def __init__(self):
    # Connect
    self.conn = MySQLdb.connect(
        user='root',
        passwd='password',
        host='localhost',
        db='mysql'
    )
    
    # Encoding
    self.conn.set_character_set('utf8')
    self.cur = self.conn.cursor()
    self.cur.execute('SET NAMES utf8;')
    self.cur.execute('SET CHARACTER SET utf8;')
    self.cur.execute('SET character_set_connection=utf8;')

自身のMySQL上で登録したユーザー名とパスワードをいれてください。
接続後に下の行でデータを入れる際に文字コードをエンコードするようにMySQLにSQL文で指示を送っています。
MySQLに何か操作をする際には基本的にconn.cur.execute('SQL文')で送ります。

• 今回使用するSQL文

SQL

sql_DBactive = "use "
sql_cretable = "CREATE TABLE IF NOT EXISTS "
sql_insert = "INSERT INTO "

"use"は指定したDBをアクティブにする
"CREATE TABLE IF NOT EXISTS "は既に指定したテーブル名が
存在するか確認して無い場合はテーブルを作成する
"INSERT INTO "データの挿入を行います

• 指定DBのアクティブ化

DB_Manipulate/DB_activate

def DB_activate(self, DBname):
    sql = self.sql_DBactive + DBname
    self.cur.execute(sql)

実際にexcuteに入っているSQL文は'use DataBaseName'
これでどのデータベースを使用するか指定できます。

• データテーブルの作成

DB_Manipulate/CREATETABLE_City_ID_Name

def CREATETABLE_City_ID_Name(self, table_name):
    self.table_name = table_name
    sql = self.sql_cretable +\
            self.table_name +\
            " (ID INT," +\
            "City CHAR(100)," +\
            "Country CHAR(100))"
    self.cur.execute(sql)

CREATE TABLEとそのテーブルの中に入れる列名とデータ型の指定をしています。
MySQLのデータ型の詳細は【MySQL】データ型一覧
扱えるデータ型がまとまっておりわかりやすいです。
また、一度テーブル作成後のカラムの変更は可能です詳細はカラムの名前と定義の変更

• データの挿入(大量データに対応)

DB_Manipulate/INSERT_City_ID_Name

def INSERT_City_ID_Name(self, city_data):
    sql = self.sql_insert +\
            self.table_name+\
            " (ID, City, Country) VALUES (%s, %s, %s)"
    self.cur.executemany(sql, city_data)
    self.conn.commit()

データを挿入する時はINSERT文で1つずつデータ挿入しますが大量データを挿入する際は一括で挿入する必要があります。
この目的を達成するにはSQL文とexcuteに工夫が必要で
"INSART INTO TABLENAME (カラム名＊カラムの数だけ記入する) VALUES (%s＊カラムの数だけ記入する)"
することで大量データを入力するSQL文にします
次にexecuteでSQL文の投げていたところをexecutemanyに変更することで処理時間を短く簡単なコードでその目的を達成することが出来ます。

• データテーブルから指定データの取り出し

DB_Manipulate/SELECT_Column

def SELECT_Column(self, table_name, *input_column_name):
    for i in range(len(input_column_name)):
        if i == 0:
            column_name = input_column_name[0]
        else:
            column_name = column_name + ',' + input_column_name[i]

    sql = "SELECT " + column_name + " from " + table_name
    self.cur.execute(sql)

    return self.cur.fetchall()

データの取り出しについてですが任意のカラムを引っ張れるようなコードにしてみました。
input_column_nameで複数の引数を入力できるようにしてfor文で適当なSQL文の形に修正しています。
ちなみにinput_column_nameに"*"を入力すると通常のSQL文同様全カラムのデータを取得するようになっています。
executeでSQL文を投げた後return のfetchallで取得したデータを返しています。

コード全文

DB_Manipulate

import MySQLdb

class cl_DB_Manipulate:
    #Properties
    sql_DBactive = "use "
    sql_cretable = "CREATE TABLE IF NOT EXISTS "
    sql_insert = "INSERT INTO "
    
    #constructor
    def __init__(self):
        # Connect
        self.conn = MySQLdb.connect(
            user='root',
            passwd='password',
            host='localhost',
            db='mysql'
        )
        
        # Encoding
        self.conn.set_character_set('utf8')
        self.cur = self.conn.cursor()
        self.cur.execute('SET NAMES utf8;')
        self.cur.execute('SET CHARACTER SET utf8;')
        self.cur.execute('SET character_set_connection=utf8;')

    #method
    def DB_activate(self, DBname):
        sql = self.sql_DBactive + DBname
        self.cur.execute(sql)
        
    def CREATETABLE_City_ID_Name(self, table_name):
        self.table_name = table_name
        sql = self.sql_cretable +\
                self.table_name +\
                " (ID INT," +\
                "City CHAR(100)," +\
                "Country CHAR(100))"
        self.cur.execute(sql)

    def INSERT_City_ID_Name(self, city_data):
        sql = self.sql_insert +\
                self.table_name+\
                " (ID, City, Country) VALUES (%s, %s, %s)"
        self.cur.executemany(sql, city_data)
        self.conn.commit()
    
    def SELECT_Column(self, table_name, *input_column_name):
        for i in range(len(input_column_name)):
            if i == 0:
                column_name = input_column_name[0]
            else:
                column_name = column_name + ',' + input_column_name[i]
        sql = "SELECT " + column_name + " from " + table_name
        self.cur.execute(sql)

        return self.cur.fetchall()

    def __del__(self):
        self.conn.close()

このコード私個人が使いやすい形にしているだけなので使用する場合は各々使いやすい形にしてもらえればと思います。

また、クラスを使った理由はコンストラクタとデストラクタでクラスを定義した時にMySQLと接続、開放した時に接続を切る処理を行ってくれるためです。
Pythonの詳しいクラスの使い方はPython基礎講座(13 クラス)

まとめ

データを効率的に扱いたいと言うのが独学でDBを扱うモチベーションかなと思いまずは大量データ書き込みをメインに話を進めていきました。
PythonでDBを扱ってみたい方の助けになればと思います。

現在はwebアプリにMySQLを絡めた簡単ものを作成中です。
完成したらそちらも投稿しようと思います。