ここではPythonでMySQLを操作する方法を解説します。
MySQLにデータを保存することで大規模なデータを簡単に扱うことができるようになります。
MySQLはデータベース(DB)の一種で、リレーショナルデータベース(RDB)に分類されます。
DBの分類は他にNoSQL(ノーエスキューエル)があり、MongoDBやなどがNoSQLに分類されます。
RDBはトランザクションを重要視しておりデータの整合性を保つことができます。
一方でNoSQLはデータの整合性を弱める代わりに、スケーラビリティや読み書き性能を高めたデータベースです。
使用ライブラリ
PythonからMySQLに接続するためのライブラリはいくつかありますが、ここでは PyMySQL
を使用します。
インストール :必要
MySQL用ライブラリを比較すると以下のようになります。どのライブラリを使用してもコードの内容はほぼ同じです。
ライブラリ | Python3対応 | 作者 | ライセンス |
---|---|---|---|
PyMySQL | ○ | Pete Hunt | MIT |
mysql-connector-python | ○ | Oracle(MySQL公式) | GNU GPLv2 |
mysqlclient | ○ | Inada Naoki | GNU GPL |
MySQLdb | × | Andy Dustman | GPL |
MySQL-Python | × | Andy Dustman | GPL |
PythonではPEP249 (DB-API 2.0)で、DB接続やSQLの実行、結果の取り出し方法などのAPIの仕様を定めています。多くのモジュールがこれに沿って実装されているため、どのライブラリを使用しても基本は同じメソッドで実行する事ができるようになっています。
・関連記事:PythonでMySQLを操作する(mysql-connector-python)
ライブラリのインストール
pip
コマンドで PyMySQL
ライブラリをインストールします。
・関連記事:Pythonライブラリのインストール(pipの使い方)
1 | python -m pip install PyMySQL |
Successfully installed PyMySQL-*.*.* と表示されればインストールは成功です。
MySQLを準備する
MySQLの事前準備について解説します。
すでにMySQLを導入していて、作成済のデータベースを利用する場合は飛ばしてください。
MySQLのインストール(Windows)
MySQLがインストールされていない場合はPCにインストールします。
インストール方法はこちらの記事を参照してください。
・参考記事:【Windows11対応】MySQLのインストール手順
データベースを作成する
インストールが完了したら、コマンドプロンプトを開きMySQLに接続して、データベースを作成します。接続するには以下のように入力します。
1 | C:\>mysql -u root -p |
Enter password : と表示されるので、インストール時に設定したパスワードを入力します。
下図のようにメッセージが表示され、MySQLを操作できる状態になります。
まず、データベースを作成します。CREATE DATABASE データベース名;
と入力し正常に処理されると、Query OK, 1 row affected
と表示されます。
1 2 | mysql> CREATE DATABASE myDB; Query OK, 1 row affected (0.05 sec) |
メッセージが表示されず以下のように、-> が表示される場合は、文末の ;(セミコロン)が抜けていないか確認してください。抜けていた場合は、;(セミコロン) を入力すれば正常に処理されます。
1 2 | mysql> CREATE DATABASE myDB -> |
入力をやり直したい場合はCTRL+Cでクエリをキャンセルしてやり直す事ができます。
テーブルを作成する
次にテーブルを作成します。
先程作成したデータベースに接続するため、USE データベース名;
と入力します。Database changed
と表示されれば正常に接続されています。
1 2 | mysql> USE myDB; Database changed |
ERROR 1049 (42000): Unknown database 'データベース名'
と表示された場合は、指定したデータベース名が存在していないため、作成済のデータベース名を指定してください。
以下のクエリを入力してテーブルを作成します。
1 2 3 4 5 6 7 | 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 ; |
Query OK
とメッセージが表示されれば正常にテーブルが作成されています。
1 | Query OK, 0 rows affected, 3 warnings (0.18 sec) |
作成済のテーブルを確認する場合は、show TABLES;
で一覧を表示する事ができます。
1 2 3 4 5 6 7 | mysql> show TABLES; +----------------+ | Tables_in_mydb | +----------------+ | users | +----------------+ 1 row in set (0.05 sec) |
レコードを挿入する(INSERT)
以下のプログラムは公式サイトを参考にしています。(公式サンプル)
レコードを挿入するにはcursorオブジェクトの execute
メソッドでINSERTクエリを実行します。
クエリの文末にデリミタ(;)は不要です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | import pymysql.cursors # データベースに接続 connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # レコードを挿入 sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.execute(sql, ('webmaster@python.org', 'very-secret')) # コミットしてトランザクション実行 connection.commit() |
結果を確認するために、MySQLで以下のようにselectクエリを実行してみます。
1 2 3 4 5 6 7 | mysql> select * from users; +----+----------------------+-------------+ | id | email | password | +----+----------------------+-------------+ | 1 | webmaster@python.org | very-secret | +----+----------------------+-------------+ 1 row in set (0.00 sec) |
上記のようにテーブルが表示されれば、正常に登録されています。
with文を使わずに以下のように記述することも可能です。
ただしWith文を使用しない場合は、自動的に接続を閉じてくれないため close()
メソッドで明示的に閉じる必要があります。
1 2 3 4 5 6 7 8 9 10 11 | # レコードを挿入 cursor = connection.cursor() sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.execute(sql, ('webmaster@python.org', 'very-secret')) # コミットしてトランザクション実行 connection.commit() # 終了処理 cursor.close() connection.close() |
MySQLでテーブルの全レコードを削除するには、以下のようにTRUNCATEクエリを実行します。
1 2 | mysql> TRUNCATE users; Query OK, 0 rows affected (0.07 sec) |
複数レコードを一括で挿入する(INSERT)
レコードを一括で挿入するには、cursorオブジェクトの executemany()
メソッドでINSERTクエリを実行します。
第二引数に挿入するデータのリストもしくはタプルを指定します。
複数レコードを一括で挿入するサンプルプログラムです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | import pymysql.cursors # データベースに接続 connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) values = [['webmaster1@python.org', 'very-secret1'], ['webmaster2@python.org', 'very-secret2'], ['webmaster3@python.org', 'very-secret3'], ['webmaster4@python.org', 'very-secret4'], ['webmaster5@python.org', 'very-secret5']] with connection: with connection.cursor() as cursor: # レコードを挿入 sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.executemany(sql, values) # コミットしてトランザクション実行 connection.commit() |
結果を確認するために、MySQLで以下のようにselectクエリを実行してみます。
1 2 3 4 5 6 7 8 9 10 11 | mysql> select * from users; +----+-----------------------+--------------+ | id | email | password | +----+-----------------------+--------------+ | 1 | webmaster1@python.org | very-secret1 | | 2 | webmaster2@python.org | very-secret2 | | 3 | webmaster3@python.org | very-secret3 | | 4 | webmaster4@python.org | very-secret4 | | 5 | webmaster5@python.org | very-secret5 | +----+-----------------------+--------------+ 5 rows in set (0.00 sec) |
上記のようにテーブルが表示されれば、正常に登録されています。
データを取得する(SELECT)
SQL実行結果からデータを取得するには fetch
メソッドを使用します。fetch
メソッドには、fetchone()
:1件ずつ取得, fetchall()
:全件取得, fetchmany()
:指定行数取得があります。
データを1件ずつ取得する
データを1件ずつ取得するには、cursorオブジェクトのexecute
メソッドでSQLのSELECTクエリを実行します。
cursorオブジェクト.fetchone()
以下の例は、WHERE句で条件を指定して1件文のレコードを抽出して、fetchone()
でデータを取得しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | import pymysql.cursors # データベースに接続 connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # データ読み込み sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s" cursor.execute(sql, ('webmaster1@python.org')) result = cursor.fetchone() print(result) |
with文を使わずに以下のように記述することもできます。
1 2 3 4 5 6 7 8 9 10 | # データ読み込み cursor = connection.cursor() sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s" cursor.execute(sql, ('webmaster1@python.org')) result = cursor.fetchone() print(result) # 終了処理 cursor.close() connection.close() |
全データを取得する
複数レコードを取得するSELECTクエリを実行し、fetchall()
で全データを取得します。
cursorオブジェクト.fetchall()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | import pymysql.cursors # データベースに接続 connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # データ読み込み sql = "SELECT `id`, `password` FROM `users`" cursor.execute(sql) result = cursor.fetchall() print(result) |
出力結果は以下のようになります。
with文を使用しない方法は以下になります。
1 2 3 4 5 6 7 8 9 10 | cursor = connection.cursor() sql = "SELECT `id`, `password` FROM `users`" cursor.execute(sql) result = cursor.fetchall() print(result) # 接続を切断 cursor.close() connection.close() |
データの更新(UPDATE)
登録済みのデータを更新するには、cursorオブジェクトの execute
メソッドでUPDATEクエリを実行します。
次の例では、usersテーブル id=1 の email「webmaster1@python.org」を「master@python-work.com」に更新します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | import pymysql.cursors connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # データ更新 sql = "UPDATE users SET email = %s WHERE email = %s" cursor.execute(sql, ('master@python-work.com', 'webmaster1@python.org')) connection.commit() |
MySQLコンソールで email が変更されていることが確認できます。
1 2 3 4 5 6 7 8 9 10 11 | mysql> select * from users; +----+------------------------+--------------+ | id | email | password | +----+------------------------+--------------+ | 1 | master@python-work.com | very-secret1 | | 2 | webmaster2@python.org | very-secret2 | | 3 | webmaster3@python.org | very-secret3 | | 4 | webmaster4@python.org | very-secret4 | | 5 | webmaster5@python.org | very-secret5 | +----+------------------------+--------------+ 5 rows in set (0.00 sec) |
データの削除(DELETE)
データを削除するには、cursorオブジェクトの execute
メソッドでDELETEクエリを実行します。cursor.rowcount
で削除した行数を取得できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | import pymysql.cursors connection = pymysql.connect(host='localhost', user='root', password='MySQLのパスワード', database='myDB', cursorclass=pymysql.cursors.DictCursor) with connection: with connection.cursor() as cursor: # データ削除 sql = "DELETE FROM users WHERE email = %s" cursor.execute(sql, ('master@python-work.com')) connection.commit() print(cursor.rowcount, 'rows deleted') |
MySQLのコンソールでデータが削除されていることを確認できます。
1 2 3 4 5 6 7 8 9 | mysql> select * from users; +----+-----------------------+--------------+ | id | email | password | +----+-----------------------+--------------+ | 2 | webmaster2@python.org | very-secret2 | | 3 | webmaster3@python.org | very-secret3 | | 4 | webmaster4@python.org | very-secret4 | | 5 | webmaster5@python.org | very-secret5 | +----+-----------------------+--------------+ |
PythonでMySQLを操作する方法を解説しました。
・関連記事:PythonでMySQLを操作する(mysql-connector-python)
・関連記事:PythonでMongoDBを操作する(PyMongo)