今回はPythonからMySQLデータベースに接続し、データを更新したり取得する方法について解説します。
MySQL は無料で使える本格的なデータベースであり、商用利用も盛んにされていますので、仕事で必要に迫られるケースも多いと思います。
少し前までは mysqlclient が有名でしたが、Windowsではサポートされなくなっており、2024年9月においては
mysql-connector-python がよく使われています。
そこで、本記事では Windows上で mysql-connector-python を使って PythonからMySQLに接続する方法を、サンプルソース付きで解説致します。
「【 コピペでOK】９割の機能を網羅！PytonからMySQLを扱うクラスを作ってみました。」では、本記事の内容をもとに、MySQLで便利な自作クラスを紹介していますので、興味のある方は併せてご覧ください。
mysql-connector-python インストール
コマンドプロンプトから、pipコマンドで mysqlclientをインストールします。
pip install mysql-connector-python
mysql-connector-pythonの概要
SQLやDDLを実行するための手順は、SQLite、Oracle、PostgreSQL など多くのデータベースと同様です。
尚、インストールは mysqlclient ですが、 import は MySQLdb なのでご注意ください。
|
1 |
import MySQLdb |
mysql-connector-pythonの使い方
MySQL への接続
MySQLへの接続には、mysql.connector.connect メソッドに接続に必要な接続情報を引数として渡します。
また、処理が終れば close() メソッドを使って接続を切断しておきます。
|
1 2 3 4 5 6 7 8 9 10 |
# モジュールのインポート import mysql.connector # MySQLへの接続 conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining') # ～ここに処理を書く～ # MySQLへの接続を切断 conn.close() |
| 引数名 | 内容 | 補足説明 |
|---|---|---|
| host | サーバー名 | IPアドレス（例：192.168.0,110） 又はホスト名（例：'hoge'） |
| database | データベース名 | |
| user | ユーザー名 | |
| password | パスワード | |
| port | ポート番号 | 省略時は3306が設定される |
他のDatabaseでは、接続文字列（接続情報を繋げて１つの文字列にしたもの）が使えるものも多いですが、mysql-connector-pythonは接続文字列をサポートしていません。
テーブルの作成と削除
テーブルの作成や削除、インデックスの追加、カラム名の変更など、いわゆる定義系のSQL（DDL＝Data Definition Language）は、execute メソッドを使います。
execute メソッドを使うには、DBに接続後にカーソルを取得する必要があるため、結果的に下記の様になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# MySQLへの接続 conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining') # カーソルを取得 cur = conn.cursor() #任意のDDL(Createなど）を実行 cur.execute('～任意のDDL文～') # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
例えば、新しいテーブルを作成する場合は、次のように記述します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
# モジュールのインポート import mysql.connector # MySQLへの接続 conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining') # create 文の作成 sql_statement = """ CREATE TABLE hoge ( id VARCHAR(5) NOT NULL, name VARCHAR(20), age INT, create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ); """ # カーソルを取得し、Select文を実行後、結果を取得する cur = conn.cursor() cur.execute(create_statement ) # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
テーブルを削除する場合は、sql_statement を次の内容に変更します。
|
1 2 3 |
sql_statement = """ DROP TABLE IF EXISTS hoge; """ |
データの挿入と更新
データを挿入する場合も基本的にはDDLと同じく、execute メソッドを使います。
異なるのは、データの更新を確定させるために、commitメソッドを呼ぶ必要があることです。これをしないと、いくらテーブルを更新しても結果が反映されません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# モジュールのインポート import mysql.connector # MySQLへの接続 conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining') cur = conn.cursor() # インサート文の実行 cur.execute("insert into hoge(id,name,age) values('A1001','山田',30)") cur.execute("insert into hoge(id,name,age) values('A1002','鈴木',45)") cur.execute("insert into hoge(id,name,age) values('A1003','斉藤',29)") # 更新内容の確定（コミット） conn.commit() # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
executeの引数を update文 に換えると、指定した条件のデータを更新（アップデート）できます。
|
1 |
cur.execute("update hoge set age=50 where id='A1001'") |
検索(Select）と結果の取得
データを検索する場合も同様、execute メソッドを使います。
また、fetchall メソッドを使って検索結果を受け取ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# モジュールのインポート mport mysql.connector # MySQLへの接続 conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining') # カーソルを取得し、Select文を実行後、結果を取得する cur = conn.cursor() cur.execute('select * from hoge') res = cur.fetchall() # 取得した結果をプリント print(res) # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
fetchall は検索結果を全てメモリに読み込むため、大量のデータを受け取る場合はメモリ不足のエラーが発生します。
そのため、検索結果を１件づつ読み出す fetchoneメソッドや、指定した件数毎に結果を受け取れる fetchmanyメソッドが用意されています。
| 機能 | メソッド | 備考 |
|---|---|---|
| 全件取得 | fetchall() | リスト形式で全件を返す。 |
| 1件づつ取得 | fetchone() | データがあれば1件をタプル形式で返す。 データが無くなれば None を返す。 |
| 指定件数づつ取得 | fetchmany(件数) | データがあれば指定件数づつタプル形式で返す。 データが無くなれば None を返す。 |
以下は、fetchmany を使ったサンプルです。２件づつデータを読み取りながらループしていますが、書き方が２通りあるため、参考に載せておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# モジュールのインポート import mysql.connector # MySQLへの接続 conn = mysql.connector.connect( host='dbserver', user='mining', database='mining', password='mining') # カーソルを取得し、Select文を実行後、結果を取得する cur = conn.cursor() cur.execute('select * from hoge') res = cur.fetchmany(2) while res: print(res) res = cur.fetchmany(2) # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
# モジュールのインポート import mysql.connector # MySQLへの接続 conn = mysql.connector.connect( host='dbserver', user='mining', database='mining', password='mining') # カーソルを取得し、Select文を実行後、結果を取得する cur = conn.cursor() cur.execute('select * from hoge') while True: res = cur.fetchmany(2) if res == []: break print(res) # カーソルとコネクションをクローズ（解放） cur.close() conn.close() |
まとめ
今回は Python を使って MySQL に接続するために必要な mysql-connector-python のインストール方法 と、 MySQL に接続した後にデータの抽出や更新を行うための簡単なサンプルを紹介しまた。
Pythonのデータベース接続は、データベースの種類によってコネクションの部分が多少異なるものの、接続後の手順は同じです。
この記事がPythonで MySQL に接続するための参考になれば幸いです。
コメント