Open10
PyScriptから直接MySQLデータベースにアクセスするトライアル
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)
対話型シェルで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
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
データベース作成用コードの実装
(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)
テーブルの作成
(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)
データの登録
(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
登録したデータを読み込む
(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, '森田 花子')
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>
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)