Cloud9＆Django環境にMySQLを設置③PyMySQL

PythonとMariaDBをつなぐ

まず「Cloud9＆Django環境にMySQLを設置①」から発生した「Cloud9＆Django環境にMySQLを設置②」で試験した環境ではなく①のまま進む。
（感想：Cloud9だとそんなことも苦にならず試験できるのが助かる）
つぎに②で使ったcheck.pyを実行してみる。

ec2-user:~/environment/my_django_project/mysite $ python3 check.py[ENTER]
Traceback (most recent call last):
File "/home/ec2-user/environment/my_django_project/mysite/check.py", line 1, in <module>
import MySQLdb
ModuleNotFoundError: No module named 'MySQLdb'
ec2-user:~/environment/my_django_project/mysite $

terminal

②のcheck.pyを修正して確認

import pymysql.cursors

/my_django_project/mysite/check.py

ec2-user:~/environment/my_django_project/mysite $ python3 check.py[ENTER]
Traceback (most recent call last):
File "/home/ec2-user/environment/my_django_project/mysite/check.py", line 1, in <module>
import pymysql.cursors
ModuleNotFoundError: No module named 'pymysql'
ec2-user:~/environment/my_django_project/mysite $

terminal

モジュールをインストール

pip install PyMySQL

ec2-user:~/environment/my_django_project/mysite $ pip install PyMySQL[ENTER]
Defaulting to user installation because normal site-packages is not writeable
Collecting PyMySQL
Downloading PyMySQL-1.1.1-py3-none-any.whl (44 kB)
|████████████████████████████████| 44 kB 2.3 MB/s
Installing collected packages: PyMySQL
Successfully installed PyMySQL-1.1.1
ec2-user:~/environment/my_django_project/mysite $

terminal

再度、check.pyを実行するとエラーメッセージは出なかった。

ec2-user:~/environment/my_django_project/mysite $ python3 check.py[ENTER]
ec2-user:~/environment/my_django_project/mysite $

terminal

動作確認

データベースの作成

最初にデータベースをリスタートする。
データベースに入室する。

ec2-user:~/environment/my_django_project/mysite $ sudo service mariadb restart[ENTER]
Redirecting to /bin/systemctl restart mariadb.service
ec2-user:~/environment/my_django_project/mysite $ sudo mariadb -u root -p[ENTER]
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.23-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

terminal

ユーザの作成

作成したユーザ情報はあとのプログラム.pyで使用します。
sample(=database)
user(=user)
localhost(=host)
passwd(=password)

MariaDB [sample]> create user 'user'@'localhost' identified by 'passwd';[ENTER]
Query OK, 0 rows affected (0.004 sec)

MariaDB [sample]>

mariadb

権限付与

MariaDB [(none)]> grant all on sample.* to 'user'@'localhost';[ENTER]
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]>

権限変更の有効化

MariaDB [(none)]> FLUSH PRIVILEGES;[ENTER]
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

テストテーブルの作成

データベースを選択してテストテーブル作成をします。

MariaDB [(none)]> use sample[ENTER]
Database changed
MariaDB [sample]> CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE utf8_bin NOT NULL, `password` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;[ENTER]
Query OK, 0 rows affected (0.008 sec)

MariaDB [sample]>

pythonファイル

usersテーブルにデータを追加、追加をSELECTで確認するsample.pyを作成する。
（charset='utf8mb4'は①で指定済みで省略）

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
    user='user',
    password='passwd',
    database='sample',
    cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save 
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

/my_django_project/mysite/sample.py

ec2-user:~/environment/my_django_project/mysite $ python3 sample.py[ENTER]
{'id': 1, 'password': 'very-secret'}
ec2-user:~/environment/my_django_project/mysite $

terminal

MariaDBで確認

ec2-user:~/environment/my_django_project/mysite $ sudo mariadb -u root -p[ENTER]
Enter password:[ENTER]
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> use sample;[ENTER]
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sample]> select * from users;[ENTER]
+----+----------------------+-------------+
| id | email | password |
+----+----------------------+-------------+
| 1 | webmaster@python.org | very-secret |
+----+----------------------+-------------+
1 row in set (0.000 sec)

MariaDB [sample]>

terminal

未確認情報

Django プロジェクトのディレクトリに PyMySQL はインストールします。＊

ありがとうございます。出典元：
@boccham(Kazuki Amino)　PythonでMariaDBに接続