Python を使用して Azure Database for MySQL に接続してデータ操作してみました

Posted at 2022-03-31

概要

MySQLサーバの操作コマンドの確認と、Python を使用して Azure Database for MySQL に接続してデータ操作するプログラムの簡単な紹介です。

ローカル環境

macOS Monterey 12.3
python 3.8.12
Azure CLI 2.34.1
mysql Ver 8.0.28 for macos12.2 on x86_64 (Homebrew)

前提条件

Azure環境がすでに用意されていること（テナント/サブスクリプション）
ローカル環境に「azure cli」がインストールされていること
ローカル環境に「mysql」がインストールされていること

事前準備

Python 用 MySQL コネクタのインストール

pip install mysql-connector-python

２. この記事を実行し、Azure Database for MySQL を Single構成で作成できていること

項目	値
MySQLサーバ名	iturumysql01.mysql.database.azure.com
データベース名	iotdummydb
管理者名	adminadmin@iturumysql01
管理者パスワード	HogeHogeHoge!

MySQLサーバ操作

MySQLサーバへの接続

$ mysql --host=iturumysql01.mysql.database.azure.com --user=adminadmin@iturumysql01 -p
Enter password: [HogeHogeHoge!]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62991
Server version: 5.6.47.0 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

利用するデータベースの定義

mysql> use iotdummydb;
Database changed

テーブルの作成

## 構文
CREATE TABLE [テーブル名] (
    [カラム名] [データ型] [オプション]
);

### 例）
CREATE TABLE `users` (
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
    `name` VARCHAR(50) NOT NULL COMMENT "名前",
    `mail` VARCHAR(100) NOT NULL COMMENT "メールアドレス",
    `created_at` datetime DEFAULT NULL COMMENT "登録日"
);

## 実行
mysql> CREATE TABLE `users` (
    ->     `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
    ->     `name` VARCHAR(50) NOT NULL COMMENT "名前",
    ->     `mail` VARCHAR(100) NOT NULL COMMENT "アドレス",
    ->     `created_at` datetime DEFAULT NULL COMMENT "作成日"
    -> );
Query OK, 0 rows affected (0.67 sec)

テーブルの一覧表示

## 構文
show tables;

## 実行
mysql> show tables;
+----------------------+
| Tables_in_iotdummydb |
+----------------------+
| users                |
+----------------------+
1 row in set (0.01 sec)

テーブル定義の確認

## 構文
show columns from [テーブル名];

## 実行
mysql> show columns from users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)  | NO   |     | NULL    |                |
| mail       | varchar(100) | NO   |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

レコードの追加

## 構文
INSERT INTO [テーブル名] [カラム名] VALUES [値];

### 例）
INSERT INTO users (name, mail, created_at)
    VALUES ("Yamada Ichiro", "iyamada@hogehoge.local", now());

## 実行
mysql> INSERT INTO users (name, mail, created_at)
    ->     VALUES ("Yamada Ichiro", "iyamada@hogehoge.local", now());
Query OK, 1 row affected (0.21 sec)

全レコードの表示

## 構文
SELECT * FROM [テーブル名];

## 実行
mysql> SELECT * FROM users;
+----+---------------+------------------------+---------------------+
| id | name          | mail                   | created_at          |
+----+---------------+------------------------+---------------------+
|  1 | Yamada Ichiro | iyamada@hogehoge.local | 2022-03-31 04:34:15 |
+----+---------------+------------------------+---------------------+
1 row in set (0.01 sec)

全レコードの削除

## 構文
DELETE FROM [テーブル名];

## 実行
mysql> DELETE FROM users;
Query OK, 1 row affected (0.20 sec)

## 確認
mysql> SELECT * FROM users;
Empty set (0.01 sec)

## おまけ
### 一部レコード削除
DELETE FROM [テーブル名] WHERE [条件式];

テーブル削除

## 構文
DROP TABLE [テーブル名];

## 実行
mysql> DROP TABLE users;
Query OK, 0 rows affected (0.39 sec)

PythonプログラムからのMySQLサーバへのデータ操作

データの事前登録

## usersテーブルに以下のデータを事前に登録しておく
mysql> SELECT * FROM users;
+----+---------------+------------------------+---------------------+
| id | name          | mail                   | created_at          |
+----+---------------+------------------------+---------------------+
|  2 | Yamada Ichiro | iyamada@hogehoge.local | 2022-03-31 05:11:57 |
|  3 | Yamada Ichiro | iyamada@hogehoge.local | 2022-03-31 05:12:06 |
|  4 | Suzuki Jiro   | jsuzuki@hogehoge.local | 2022-03-31 05:12:35 |
+----+---------------+------------------------+---------------------+
3 rows in set (0.02 sec)

Pythonプログラム

mysql_sample.py

import mysql.connector
from mysql.connector import errorcode
import time
from datetime import datetime

config = {
    'host': 'iturumysql01.mysql.database.azure.com',
    'user': 'adminadmin@iturumysql01',
    'password': 'HogeHogeHoge!',
    'database': 'iotdummydb'
}


# MySQLサーバ操作
def GetMySQL():
  try:
    conn = mysql.connector.connect(**config)
    print("Connection established")
  except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
      print("Something is wrong with the user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
      print("Database does not exist")
    else:
      print(err)
  else:
    cursor = conn.cursor()

    # Insert some data into table (3 rows)
    cursor.execute("INSERT INTO users (name, mail, created_at) VALUES (%s, %s, %s);", ("banana", "banana@fruit.com", datetime.now()))
    print("Inserted",cursor.rowcount,"row(s) of data.")
    cursor.execute("INSERT INTO users (name, mail, created_at) VALUES (%s, %s, %s);", ("orange", "orange@fruit.com", datetime.now()))
    print("Inserted",cursor.rowcount,"row(s) of data.")
    cursor.execute("INSERT INTO users (name, mail, created_at) VALUES (%s, %s, %s);", ("apple", "apple@fruit.com", datetime.now()))
    print("Inserted",cursor.rowcount,"row(s) of data.")
    conn.commit()

    # View table data
    cursor.execute("SELECT * FROM users;")
    rows = cursor.fetchall()
    print(f'Read: {cursor.rowcount}, row(s) of data.')

    # print all rows
    for row in rows:
      print(f'Data row = {row}')

    # Cleanup
    cursor.close()
    conn.close()
    print("Done.")


if __name__ == '__main__':
    start = time.time()
    GetMySQL()
    making_time = time.time() - start

    print("")
    print("処理時間:{0}".format(making_time) + " [sec]")
    print("")

プログラムの実行

3つのレコードを新規追加し、事前登録と合わせて６つのレコードを取得

$ python mysql_sample.py

Connection established
Inserted 1 row(s) of data.
Inserted 1 row(s) of data.
Inserted 1 row(s) of data.
Read: 6, row(s) of data.
Data row = (2, 'Yamada Ichiro', 'iyamada@hogehoge.local', datetime.datetime(2022, 3, 31, 5, 11, 57))
Data row = (3, 'Yamada Ichiro', 'iyamada@hogehoge.local', datetime.datetime(2022, 3, 31, 5, 12, 6))
Data row = (4, 'Suzuki Jiro', 'jsuzuki@hogehoge.local', datetime.datetime(2022, 3, 31, 5, 12, 35))
Data row = (5, 'banana', 'banana@fruit.com', datetime.datetime(2022, 3, 31, 15, 51, 59))
Data row = (6, 'orange', 'orange@fruit.com', datetime.datetime(2022, 3, 31, 15, 51, 59))
Data row = (7, 'apple', 'apple@fruit.com', datetime.datetime(2022, 3, 31, 15, 51, 59))
Done.

処理時間:0.6202619075775146 [sec]

MySQLサーバ上での確認

mysql> SELECT * FROM users;
+----+---------------+------------------------+---------------------+
| id | name          | mail                   | created_at          |
+----+---------------+------------------------+---------------------+
|  2 | Yamada Ichiro | iyamada@hogehoge.local | 2022-03-31 05:11:57 |
|  3 | Yamada Ichiro | iyamada@hogehoge.local | 2022-03-31 05:12:06 |
|  4 | Suzuki Jiro   | jsuzuki@hogehoge.local | 2022-03-31 05:12:35 |
|  5 | banana        | banana@fruit.com       | 2022-03-31 15:51:59 |
|  6 | orange        | orange@fruit.com       | 2022-03-31 15:51:59 |
|  7 | apple         | apple@fruit.com        | 2022-03-31 15:51:59 |
+----+---------------+------------------------+---------------------+
6 rows in set (0.02 sec)

まとめ

MySQLサーバの操作コマンドの確認ができ、合わせて Pythonプログラムを使用して Azure Database for MySQL に接続してデータ操作することを確認できました。

参考文献

以下の記事を大いに参考にさせていただきました（そのまま利用させていただいている部分あり）。感謝申し上げます。
MySQLコマンド（テーブル作成関連）
SQLでテーブル作成
 Python を使用して Azure Database for MySQL に接続してデータを照会する

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up