Zenn
Open10

PyScriptから直接MySQLデータベースにアクセスするトライアル

MySQL
#
pyscript
Teru roomTeru room

MySQLのインストール

(base) macpro：PyScript sharland$ 
brew install mysql

起動

(base) macpro：PyScript sharland$ 
brew services start mysql
実行結果
==> Tapping homebrew/services
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Enumerating objects: 2414, done.
remote: Counting objects: 100% (2414/2414), done.
remote: Compressing objects: 100% (1126/1126), done.
remote: Total 2414 (delta 1121), reused 2337 (delta 1100), pack-reused 0
Receiving objects: 100% (2414/2414), 657.26 KiB | 7.92 MiB/s, done.
Resolving deltas: 100% (1121/1121), done.
Tapped 1 command (45 files, 824.9KB).
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

停止

(base) macpro：PyScript sharland$ 
brew services stop mysql
実行結果
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)
Teru roomTeru room

対話型シェルでMySQLにアクセス

(base) macpro：PyScript sharland$ 
mysql -u root
実行結果
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, 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 > 
exit;
実行結果
Bye
Teru roomTeru room

Python仮想環境を作る

(base) macpro：PyScript sharland$ 
python -m venv /Users/sharland/dev/PyScript/venv

cd /Users/sharland/dev/PyScript/
(base) macpro：PyScript sharland$ 
source venv/bin/activate
(venv) (base) macpro：PyScript sharland$ 
pip install --upgrade pip
実行結果
Collecting pip
  Downloading https://files.pythonhosted.org/packages/07/51/2c0959c5adf988c44d9e1e0d940f5b074516ecc87e96b1af25f59de9ba38/pip-23.0.1-py3-none-any.whl (2.1MB)
    100% |████████████████████████████████| 2.1MB 7.4MB/s 
Installing collected packages: pip
  Found existing installation: pip 19.0.3
    Uninstalling pip-19.0.3:
      Successfully uninstalled pip-19.0.3
Successfully installed pip-23.0.1

MySQLドライバをインストールする

(venv) (base) macpro：PyScript sharland$ 
pip install mysql-connector-python
実行結果
Collecting mysql-connector-python
  Downloading https://files.pythonhosted.org/packages/d4/45/aa4c12dd5c552936f79cfb9ddaa2d49b5311bb7cc2c191c54db2357521a0/mysql_connector_python-8.0.32-py2.py3-none-any.whl (381kB)
    100% |████████████████████████████████| 389kB 14.6MB/s 
Collecting protobuf<=3.20.3,>=3.11.0 (from mysql-connector-python)
  Downloading https://files.pythonhosted.org/packages/fe/8f/d9db035740002d61b4140aaef53a8bac7e316b18ec8744eb6c1fcf83c310/protobuf-3.20.3-cp37-cp37m-macosx_10_9_x86_64.whl (981kB)
    100% |████████████████████████████████| 983kB 12.1MB/s 
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.32 protobuf-3.20.3
Teru roomTeru room

データベース作成用コードの実装

(venv) (base) macpro：PyScript sharland$ 
touch db/create_db.py
db/create_db.py
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1', user='root', password='')
cursor = conn.cursor()

cursor.execute(
    'CREATE DATABASE example_db'
)

cursor.close()
conn.close()

コードを実行

(venv) (base) macpro：PyScript sharland$ 
python db/create_db.py

MySQLに接続し、データベースが作成されていることを確認

(base) macpro：PyScript sharland$ 
mysql -u root
mysql> 
show databases;
実行結果
+--------------------+
| Database           |
+--------------------+
| example_db         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)
Teru roomTeru room

テーブルの作成

(base) macpro：PyScript sharland$
touch db/create_table.py
db/create_table.py
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1', user='root', password='', database='example_db')
cursor = conn.cursor()

cursor.execute(
    'CREATE TABLE users('
    'id int NOT NULL AUTO_INCREMENT,'
    'name varchar(20) NOT NULL,'
    'PRIMARY KEY(id))'
)

cursor.close()
conn.close()
(base) macpro：PyScript sharland$
python db/create_table.py
(base) macpro：PyScript sharland$
python db/create_table.py

作成したDBに接続

mysql> 
use example_db;
実行結果
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

テーブルの確認

mysql> 
show tables;
実行結果
+----------------------+
| Tables_in_example_db |
+----------------------+
| users                |
+----------------------+
1 row in set (0.01 sec)

テーブル定義の確認

mysql> 
desc users;
実行結果
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
Teru roomTeru room

データの登録

(venv) (base) macpro：PyScript sharland$ 
touch db/regist_data.py
db/regist_date.py
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1', user='root', password='', database='example_db')
cursor = conn.cursor()

cursor.execute('INSERT INTO users(name) values("山田 太朗")')
cursor.execute('INSERT INTO users(name) values("鈴木 一郎")')
cursor.execute('INSERT INTO users(name) values("森田 花子")')
conn.commit()

cursor.close()
conn.close()
(venv) (base) macpro：PyScript sharland$ 
python db/regist_data.py
Teru roomTeru room

登録したデータを読み込む

(venv) (base) macpro：PyScript sharland$ 
touch db/read_data.py
db/read_data.py
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1', user='root', password='', database='example_db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM users')

for row in cursor:
    print(row)

cursor.close()
conn.close()
(venv) (base) macpro：PyScript sharland$ 
python db/read_data.py
実行結果
(1, '山田 太朗')
(2, '鈴木 一郎')
(3, '森田 花子')
Teru roomTeru room

DBのテーブルを読み込んで結果を表示するWebページを作る

(venv) (base) macpro：PyScript sharland$ 
touch test/read_db_data.html
read_db_data.html
<html>
  <head>
    <meta charset="utf-8"/>
    <link rel="stylesheet" href="https://pyscript.net/latest/pyscript.css" />
    <script defer src="https://pyscript.net/latest/pyscript.js"></script>
  </head>
  <body>
    <py-config>
      packages = ["pymysql", "mysql-connector-python"]
    </py-config>
    <py-config>
      [[fetch]]
      files = ["./../db/read_data.py"]
    </py-config>
    <py-script src="./../db/read_data.py"></py-script>
  </body>
</html>
Teru roomTeru room

MySQLサービスがHTTPサービスから参照できるようにする

(base) macpro：PyScript sharland$
vim /usr/local/etc/my.cnf
/usr/local/etc/my.cnf 
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1

HTTPサービスを起動する

(base) macpro：PyScript sharland$
python3 -m http.server
実行結果
Serving HTTP on 0.0.0.0 port 8000 (http://0.0.0.0:8000/) ...

Webブラウザでアクセスする

  • URL : http://localhost:8000/test/read_db_data.html
実行結果
Traceback (most recent call last):
  File "/lib/python3.10/site-packages/mysql/connector/network.py", line 600, in open_connection
    self.sock.connect(sockaddr)
BlockingIOError: [Errno 26] Operation in progress

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/lib/python3.10/_pyodide/_base.py", line 460, in eval_code
    .run(globals, locals)
  File "/lib/python3.10/_pyodide/_base.py", line 306, in run
    coroutine = eval(self.code, globals, locals)
  File "<exec>", line 2, in <module>
  File "/lib/python3.10/site-packages/mysql/connector/pooling.py", line 294, in connect
    return MySQLConnection(*args, **kwargs)
  File "/lib/python3.10/site-packages/mysql/connector/connection.py", line 167, in __init__
    self.connect(**kwargs)
  File "/lib/python3.10/site-packages/mysql/connector/abstracts.py", line 1178, in connect
    self._open_connection()
  File "/lib/python3.10/site-packages/mysql/connector/connection.py", line 571, in _open_connection
    self._socket.open_connection()
  File "/lib/python3.10/site-packages/mysql/connector/network.py", line 602, in open_connection
    raise InterfaceError(
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (26 Operation in progress)