Python

エクセルの内容をMySQLのテーブルに保存する方法

 

今回はエクセルの内容をテーブルに保存する方法のメモです。

テーブルは既に存在していてエクセルの内容を使ってテーブルの内容を更新します。

動作環境

venvです。

テーブルに保存するエクセルの内容

下記とします。

idnamepricestock
1りんご10050
2バナナ15030
3オレンジ12020

ライブラリのインストール

MySQLにアクセスする為のライブラリとフォルダ操作をする為のライブラリをインストールする為に下記のコマンドを叩きます。

pip install mysql-connector-python openpyxl

ライブラリを使う為に下記の記述をします。

import mysql.connector
import openpyxl

動作した時とエラーが起きた時の処理

下記の記述をします。

import mysql.connector
import openpyxl


//ここから追加
try:

except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")
//ここまで追加

MySQLに接続

下記の記述をします。

import mysql.connector
import openpyxl

try:


    //ここから追加
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()
    //ここまで追加
    

except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")

9行目ですが「localhost」にするとエラーになる場合があります。

その場合は「127.0.0.1」に変更するとうまくいくかもしれません。

user・password・databaseの値は自分のMySQLの環境に合わせて下さい。

14行目でMySQLにアセクスができるようにします。

アクセスができなかったら18行目動作します。

19行目はMySQL以外のコードが間違っている場合のエラーを表示する為の記述です。

エクセルファイルにアクセス

下記の記述をします。

import mysql.connector
import openpyxl

try:
    conn = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()


    //ここから追加
    excel_file = "excel_files/products.xlsx"
    wb = openpyxl.load_workbook(excel_file)
    ws = wb.active
    //ここまで追加


except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")

15行目でアクセスするフォルダ名とエクセルファイルを指定しています。

フォルダは記述しなくてもいいです。

カラムを取得

下記の記述をします。

import mysql.connector
import openpyxl

try:
    conn = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()

    excel_file = "excel_files/products.xlsx"
    wb = openpyxl.load_workbook(excel_file)
    ws = wb.active


    //ここから追加
    headers = []
    for cell in ws[1]:
        headers.append(cell.value)
    //ここまで追加


except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")

21行目でエクセルファイルのカラム（id・name・price・stock）の部分を取得します。

カラムに対応する値を取得

下記の記述をします。

import mysql.connector
import openpyxl

try:
    conn = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()

    excel_file = "excel_files/products.xlsx"
    wb = openpyxl.load_workbook(excel_file)
    ws = wb.active

    headers = []
    for cell in ws[1]:
        headers.append(cell.value)
        
    //ここから追加
    for row in ws.iter_rows(min_row=2, values_only=True):
        data = dict(zip(headers, row))
    //ここまで追加


except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")

23行目の「min_row=2」でエクセルファイルの2行目以降を取得して「values_only=True」でセルの値のみを取得します。

「ws.iter_rows(min_row=2, values_only=True)」でエクセルの行をタプルの形式で取得します。

(1, "りんご", 100, 50)
(2, "バナナ", 150, 30)
(3, "オレンジ", 120, 20)

headersには19行目で下記の値になっています。

headers = ["id", "name", "price", "stock"]

24行目の「zip(headers, row)」の記述でカラムごとにタプルになってリストの値に変換します。

例えば「row = (1, “りんご”, 100, 50)」の場合は下記になります。

[("id", 1), ("name", "Apple"), ("price", 100), ("stock", 50)]

そして「dict(zip(headers, row))」の記述でリストを辞書に変換します。

{"id": 1, "name": "Apple", "price": 100, "stock": 50}

テーブルの値を更新

下記の記述をします。

import mysql.connector
import openpyxl

try:
    conn = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()

    excel_file = "excel_files/products.xlsx"
    wb = openpyxl.load_workbook(excel_file)
    ws = wb.active

    headers = []
    for cell in ws[1]:
        headers.append(cell.value)

    for row in ws.iter_rows(min_row=2, values_only=True):
        data = dict(zip(headers, row))
        
        
        //ここから追加
        sql = """
        UPDATE products
        SET name = %s, price = %s WHERE id = %s
        """
        cursor.execute(sql, (data["name"], data["price"], data["id"]))
    conn.commit()
    //ここまで追加
    

except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")

28行目の「%s」はプレースフォルダーです。

SQL文で直接値を代入するとSQLインジェクションされる可能性があるので30行目の「data[“name”]」・「data[“price”]」・「data[“id”]」で値を間接的に代入しています。

31行目でテーブルの値の更新が確定します。

最後の処理

処理が終わった後のサーバーへの負荷を抑える為に下記の記述をします。

import mysql.connector
import openpyxl

try:
    conn = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="root",
        database="shop"
    )
    cursor = conn.cursor()

    excel_file = "excel_files/products.xlsx"
    wb = openpyxl.load_workbook(excel_file)
    ws = wb.active

    headers = []
    for cell in ws[1]:
        headers.append(cell.value)

    for row in ws.iter_rows(min_row=2, values_only=True):
        data = dict(zip(headers, row))
        sql = """
        UPDATE products
        SET name = %s, price = %s WHERE id = %s
        """
        cursor.execute(sql, (data["name"], data["price"], data["id"]))
    conn.commit()

except mysql.connector.Error as err:
    print(f"{err}")
except Exception as e:
    print(f"{e}")


//ここから追加    
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()
//ここまで追加

これで完成です。

RELATED POST