Python

ターミナルのMySQLの中にあるテーブル情報をエクセルに出力する方法

  /

今回はターミナルの中に作成したテーブルの情報をエクセルに出力する為のメモです。

動作環境

venvです。

テーブルの情報

下記になっていたとします。

+----+----------------+----------+---------+
| id  | name         |  price  | stock |
+----+----------------+----------+---------+
|  1 | りんご1       | 100.00|    50  |
|  2 | バナナ         | 150.00|    30  |
|  3 | オレンジ      | 120.00|    20  |
+----+----------------+----------+---------+

テーブル名は「products」です。

フォルダ構成

下記で考えます。

exel -- main.py

MySQLを立ち上げる

ターミナルのMySQLを立ち上げないとPythonのコードを書いた時のエクセル操作ができないので下記のコマンドで立ち上げます。

mysql.server start

Pythonのコードを書く前にインストールしないといけないライブラリがあるのでインストールします。

MySQL操作をするライブラリのインストール

下記のコマンドを叩きます。

pip install mysql-connector-python

エクセル操作をするライブラリのインストール

下記のコマンドを叩きます。

pip install openpyxl

ライブラリのインポート

main.pyに下記の記述をします。

import mysql.connector
import openpyxl
import os

1行目はMySQLを操作できるようにする為の記述で2行目はエクセル操作をできるようにする為の記述で3行目はファイルやフォルダの操作をできるようにする為の記述です。

正常に動作した時とエラーが出た時の場合分け

下記の記述をします。

import mysql.connector
import openpyxl
import os


//ここから追加
try:
   
except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")
//ここまで追加

7行目は正常に動作した時で9行目はMySQLのエラーが出た時で12行目はMySQL以外のエラーが出た時です。

8行目の所に実装する為のコードを書いていきます。

MySQLにアクセス

下記の記述をします。

import mysql.connector
import openpyxl
import os

try:


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


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

10行目ですが値をlocalhostにするとMySQL接続エラーになるかもしれません。

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

11行目はユーザー名を記述して12行目はパスワードを記述して13行目はDB名を記述します。

自分のターミナルのMySQLの環境の内容を記述します。

15行目でSQL文が使えるようになります。

エクセルファイルを出力するフォルダを作成

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

  
    //ここから追加
    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    //ここまで追加


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

17行目でフォルダを作成します。

「exist_ok=True」の記述があるとフォルダが既にある場合はフォルダが作成されません。

エクセルファイルを作成してシートを作成

下記の赤枠を作成します。

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    
    
    //ここから追加
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "商品リスト"
    //ここまで追加


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

19行目でエクセルを作成して20行目でシートに情報を埋め込めるようにして21行目でシート名を設定しています。

シートに値を書き込む

下記の赤枠を作成します。

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "商品リスト"
    
     //ここから追加   
    cursor.execute("SHOW COLUMNS FROM products")
    columns = []
    for col in cursor:
        columns.append(col[0])
    ws.append(columns)
    //ここまで追加


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

22行目でカラムに関する情報を取得します。

例えばproductsテーブルが下記の構成になっていたとします。

+--------+-------------------+----------+---------+-----------+-----------------------+
|  Field |       Type        |  Null   |   Key  | Default |         Extra         |
+--------+-------------------+----------+---------+-----------+------------------------
|    id	 |      int(11)	    |   NO	  |   PRI	 |   NULL	  | auto_increment |
| name | varchar(255)	|   NO		|          |   NULL	|                         |
| price  |     int(11)	    |   NO		|          |   NULL	|                         | 	
| stock |     int(11)	      |   NO		|          |   NULL	|                         | 	
+--------+-------------------+----------+---------+-----------+-----------------------+

22行目の記述でカラムについてタプルの形で値を取得して1つ1つのタプルが配列の要素になります。

[
    ("id", "int(11)", "NO", "PRI", None, "auto_increment"),
    ("name", "varchar(255)", "NO", "", None, ""),
    ("price", "int(11)", "NO", "", None, ""),
    ("stock", "int(11)", "NO", "", None, "")
]

25行目の「col[0]」はカラム名だけを取得する為の記述です。

次は下記の赤枠を作成します。

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "商品リスト"
      
    cursor.execute("SHOW COLUMNS FROM products")
    columns = []
    for col in cursor:
        columns.append(col[0])
    ws.append(columns)
 
    //ここから追加    
    cursor.execute("SELECT * FROM products")
    products = cursor.fetchall()
    for row in products:
        ws.append(row)
    //ここまで追加


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

現在のproductsテーブルの中身は下記になっています。

+----+----------------+----------+---------+
| id  | name         |  price  | stock |
+----+----------------+----------+---------+
|  1 | りんご1       | 100.00|    50  |
|  2 | バナナ         | 150.00|    30  |
|  3 | オレンジ      | 120.00|    20  |
+----+----------------+----------+---------+

29行目の記述でレコードについてタプルの形で値を取得して1つ1つのタプルが配列の要素になります。

products = [
    (1, "Apple", 100, 50),
    (2, "Banana", 150, 30),
    (3, "Orange", 200, 20)
]

エクセルファイルの出力

プロジェクトの中にexcel_filesフォルダを作成してその下にproducts.xlsxを配置します。

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "商品リスト"
      
    cursor.execute("SHOW COLUMNS FROM products")
    columns = []
    for col in cursor:
        columns.append(col[0])
    ws.append(columns)
   
    cursor.execute("SELECT * FROM products")
    products = cursor.fetchall()
    for row in products:
        ws.append(row)
      
    //ここから追加    
    file_path = os.path.join(folder_name, "products.xlsx")
    wb.save(file_path)
    //ここまで追加


except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")

MySQLのアクセスを閉じる

今回はないですが処理が終わったらMySQLへのアクセスを閉じずに次の処理が入るとDBへの負荷がかかるのでそれが起きないようにします。

下記の記述をします。

import mysql.connector
import openpyxl
import os

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

    folder_name = "excel_files"
    os.makedirs(folder_name, exist_ok=True)
    
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "商品リスト"
      
    cursor.execute("SHOW COLUMNS FROM products")
    columns = []
    for col in cursor:
        columns.append(col[0])
    ws.append(columns)
   
    cursor.execute("SELECT * FROM products")
    products = cursor.fetchall()
    for row in products:
        ws.append(row)
       
    file_path = os.path.join(folder_name, "products.xlsx")
    wb.save(file_path)

except mysql.connector.Error as err:
    print(f"MySQL エラー: {err}")

except Exception as e:
    print(f"その他のエラー: {e}")
    
    
//ここから追加       
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()
//ここまで追加

これで完成です。

RELATED POST