PyMySQL

本記事では、PyMySQLライブラリとOceanBaseデータベースを使用してアプリケーションを構築し、テーブルの作成、データの挿入、データのクエリ、テーブルの削除などの基本的なデータベース操作を実現する方法について説明します。

クリックしてpython-pymysqlサンプルプロジェクトをダウンロード

前提条件

• Python 3.xとpipがインストール済みであること。
• OceanBaseデータベースがインストール済みで、MySQLモードのテナントが作成されていること。

操作手順

1. Python と pip のバージョンを確認します。
2. PyMySQLライブラリをインストールします。
3. OceanBaseデータベースの接続情報を取得します。
4. config.py ファイル内のデータベース接続情報を修正します。
5. main.py ファイルを実行します。

ステップ１：Pythonとpipのバージョンを確認する

コマンドプロンプトまたはPowerShellターミナルを開き、python --version と pip --version コマンドを実行して、Pythonとpipが正常にインストールされていることを確認します。

例：

PS C:\Windows\system32> python --version
Python 3.7.0
PS C:\Windows\system32> pip --version
pip 22.3.1 from d:\python\python37\lib\site-packages\pip (python 3.7)

ステップ2：PyMySQLライブラリをインストールする

PyMySQLは、Pure Pythonで実装されたMySQLクライアントライブラリです。インストールが簡単でコンパイルも不要なため、クロスプラットフォームでの互換性に優れています。このライブラリは、MySQLデータベースとやり取りするためのインターフェースを提供しており、Python 3.x系に対応しています。

コマンドプロンプトまたはPowerShellターミナルを開き、以下のコマンドを実行してPyMySQLライブラリをインストールします。

1. 以下のコマンドを実行して、コードの python-pymysql ディレクトリに移動します。

   例：

   cd python-pymysql
   

2. 以下のコマンドを実行して、プロジェクトに必要なPythonライブラリをインストールします。

   例：

   pip install -r requirements.txt
   

ステップ3：OceanBaseデータベース接続情報を取得する

OceanBaseデータベースのデプロイ担当者または管理者から、該当するデータベース接続文字列を取得します。

obclient -h$host -P$port -u$user_name -p$password -D$database_name

パラメータの説明：

• $host：OceanBaseデータベースへの接続IPアドレス。OceanBaseデータベースプロキシ(OceanBase Database Proxy、ODP)接続方式ではODPアドレスを使用し、直接接続方式ではOBServerノードのIPアドレスを使用します。

• $port：OceanBaseデータベースへの接続ポート。ODP接続方式のデフォルトポートは2883で、ODPデプロイ時にカスタマイズ可能です。直接接続方式のデフォルトポートは2881で、OceanBaseデータベースのデプロイ時にカスタマイズ可能です。

• $database_name：アクセス対象のデータベース名。

  注意

  テナントに接続するユーザーは、データベースに対する CREATE、INSERT、DROP、および SELECT 権限が付与されていなければなりません。ユーザー権限の詳細については、MySQLモードでの権限分類 を参照してください。

• $user_name：テナントの接続アカウント。ODP接続の一般的な形式：ユーザー名@テナント名#クラスタ名またはクラスタ名:テナント名:ユーザー名。直接接続方式の形式：ユーザー名@テナント名。

• $password：アカウントのパスワード。

接続文字列の詳細については、OBClientによるOceanBaseテナントへの接続を参照してください。

例：

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

ステップ4：config.pyファイル内のデータベース接続情報を修正する

ステップ3：OceanBaseデータベース接続情報を取得する に記載されている情報に基づいて、プロジェクトファイル python-pymysql/config.py 内のデータベース接続情報を修正します。

1. python-pymysql プロジェクトフォルダに移動します。

2. config.py ファイル内のデータベース接続情報を修正します。

   • Windows環境の場合は、テキストエディタを使用して config.py ファイルを開き、ファイル内のデータベース接続情報を実際の状況に合わせて修正します。
   • Linux環境の場合は、vi config.py または vim config.py コマンドを使用して config.py ファイルを編集し、ファイル内のデータベース接続情報を実際の状況に合わせて修正します。

   config.py ファイル内のデータベース接続情報例は次のとおりです：

   DB_CONFIG = {
       'host': '10.10.10.1',
       'port': 2881,
       'user': 'test_user001@mysql001',
       'password': '******',
       'database': 'test',
       'charset': 'utf8mb4'
   }
   

ステップ5：main.pyファイルを実行する

コマンドプロンプトまたはPowerShellターミナルを開き、main.py ファイルを実行して、データをクエリし、結果を出力します。

1. python-pymysql プロジェクトディレクトリに移動します。

   例：

   cd D:\demo\demo\python-pymysql
   

2. main.py ファイルを実行します。

   例：

   python main.py
   

   実行結果は次のとおりです：

   2023-11-10 16:56:48,021 - INFO - Start executing the script
   2023-11-10 16:56:48,021 - INFO - Start creating the table
   2023-11-10 16:56:48,281 - INFO - Table creation successful
   2023-11-10 16:56:48,281 - INFO - Start inserting data
   2023-11-10 16:56:48,540 - INFO - Data insertion successful
   (1, 'John', 20)
   (2, 'Lucy', 25)
   (3, 'Tom', 30)
   2023-11-10 16:56:48,737 - INFO - Start dropping the table
   2023-11-10 16:56:48,999 - INFO - Table dropped successfully
   2023-11-10 16:56:48,999 - INFO - Script execution completed
   

プロジェクトコードについて

python-pymysql をクリックして、プロジェクトコードをダウンロードします。python-pymysql.zip という名前の圧縮ファイルです。

解凍すると、python-pymysql という名前のフォルダが作成されます。ディレクトリ構造は以下のとおりです：

python-pymysql
├── config.py
├── test_sql.py
├── main.py
└── requirements.txt

ファイルの説明：

• config.py：データベース接続情報を格納するために使用されます。

• test_sql.py：SQLステートメントを格納するために使用されます。

• main.py：メインプログラムのエントリポイントで、テーブルの作成、データの挿入、データのクエリ、テーブルの削除などの基本的なデータベース操作を実行します。

• requirements.txt：プロジェクトで必要なPythonパッケージとそのバージョン情報を格納するために使用されます。

  説明

  本記事で提供されているコードの `requirements.txt` には、PyMySQLライブラリのバージョン要件のみが記載されています。sudo pip install -r requirements.txt コマンドを実行することで、必要なライブラリが自動的にインストールされます。

config.pyコードの紹介

本記事で取得した config.py ファイル内のコードは、データベース接続情報を定義しています。データベース接続情報には主に以下の部分が含まれます：

データベースに接続するためのIPアドレス、ポート番号、ユーザー名、パスワード、データベース名、文字セットを指定します。

コード：

DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

パラメータの説明：

• $host：OceanBaseデータベースへの接続IPアドレス。ODP接続方式ではODPアドレスを使用し、直接接続方式ではOBServerノードのIPアドレスを使用します。

• $port：OceanBaseデータベースへの接続ポートを提供します。デフォルトは 2881 です。ODP使用時のデフォルトポートは 2883 です。

• $user_name：データベースに接続するためのユーザー名。

• $password：データベースに接続するためのパスワード。

• $database_name：接続するデータベース名。

• charset：データベースに接続する際に使用される文字セット。

  注意

  ここでのパラメータ値は、具体的な環境とデータベース設定によって決まるため、実際の状況に応じて変更する必要があります。

test_sql.pyコードの紹介

本記事で取得した test_sql.py ファイル内のコードは、テーブルの作成、データの挿入、データのクエリ、テーブルの削除を含むデータベース操作のSQLステートメントを定義しています。これらのSQLステートメントは、PyMySQLでデータベースに接続した後に実行して、対応する機能を実現できます。

このファイル内のコードには主に以下の部分が含まれます：

1. テーブル作成のSQLステートメント。

   テーブル test_pymysql を作成するSQLステートメントを定義し、テーブル内に id、name、age の3つのフィールドを定義します。id は自動インクリメントの主キーです。

   コード：

   CREATE_TABLE_SQL = '''
   CREATE TABLE test_pymysql (
   id INT(11) NOT NULL AUTO_INCREMENT,
   name VARCHAR(128) NOT NULL,
   age INT(11) NOT NULL,
   PRIMARY KEY (id)
   )
   '''
   

2. データ挿入のSQLステートメント。

   test_pymysql テーブルにデータを挿入するSQLステートメントを定義します。挿入されるデータは3行です。各データには name と age の2つのフィールドが含まれます。各フィールドの値は、SQLステートメント実行時にプレースホルダー %s の形式で渡されます。

   コード：

   INSERT_DATA_SQL = '''
   INSERT INTO test_pymysql (name, age) VALUES 
   (%s, %s),
   (%s, %s),
   (%s, %s)
   '''
   

3. データクエリのSQLステートメント。

   データのクエリを実行するSQLステートメントを定義し、test_pymysql テーブルのすべてのデータのクエリを実行します。

   コード：

   SELECT_DATA_SQL = '''
   SELECT * FROM test_pymysql
   '''
   

4. テーブル削除のSQLステートメント。

   テーブルを削除するSQLステートメントを定義し、test_pymysql テーブルを削除します。

   コード：

   DROP_TABLE_SQL = '''
   DROP TABLE test_pymysql
   '''
   

main.pyコードの紹介

本記事で取得した main.py ファイル内のコードは、pymysql モジュールを呼び出してMySQLデータベースに接続します。logging モジュールを呼び出してログ情報を出力し、テーブルの作成、データの挿入、データのクエリ、テーブルの削除の操作を実現します。

main.py ファイル内のコードには主に以下の部分が含まれます：

1. 必要なモジュールをインポートします。

   1. logging モジュールをインポートします。
   2. pymysql モジュールをインポートします。
   3. データベース接続情報が定義された config.py モジュールをインポートします。
   4. データベース操作のSQLステートメントが定義された test_sql.py モジュールをインポートします。

   コード：

   import logging
   import pymysql
   from config import DB_CONFIG
   from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL
   

2. ログ記録のレベルと形式を設定し、スクリプト実行開始を示すINFOレベルのログ情報を出力します。

   コード：

   logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
   logging.info('Start executing the script')
   

3. テーブル作成の関数を定義します。

   create_table() という名前の関数を定義し、テーブル作成開始を示すINFOレベルのログ情報を出力します。with ステートメントを使用してデータベース接続とカーソルオブジェクトのライフサイクルを管理し、データベース接続とカーソルオブジェクトの安全なクローズを保証し、メモリリークなどの問題を回避します。テーブル作成のSQLステートメントを実行し、トランザクションをコミットしてログ情報を出力するか、トランザクションをロールバックしてエラーログ情報を出力します。

   コード：

   def create_table():
       logging.info('Start creating the table')
       with pymysql.connect(**DB_CONFIG) as conn:
           with conn.cursor() as cursor:
               try:
                   cursor.execute(CREATE_TABLE_SQL)
                   conn.commit()
                   logging.info('Table creation successful')
               except Exception as e:
                   conn.rollback()
                   logging.error('Table creation failed, Reason：%s' % e)
   

4. データ挿入の関数を定義します。

   insert_data() という名前の関数を定義し、データ挿入開始を示すINFOレベルのログ情報を出力します。with ステートメントを使用してデータベース接続とカーソルオブジェクトのライフサイクルを管理し、データベース接続とカーソルオブジェクトの安全なクローズを保証し、メモリリークなどの問題を回避します。データ挿入のSQLステートメントを実行し、トランザクションをコミットしてログ情報を出力するか、トランザクションをロールバックしてエラーログ情報を出力します。

   コード：

   def insert_data():
       logging.info('Start inserting data')
       with pymysql.connect(**DB_CONFIG) as conn:
           with conn.cursor() as cursor:
               try:
                   data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                   flattened_data = [d for item in data for d in item]
                   cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                   conn.commit()
                   logging.info('Data insertion successful')
               except Exception as e:
                   conn.rollback()
                   logging.error('Data insertion failed, Reason：%s' % e)
   

5. データクエリの関数を定義します。

   データベースのデータのクエリを実行する select_data() という名前の関数を定義します。with ステートメントを使用してデータベース接続とカーソルオブジェクトのライフサイクルを管理し、データベース接続とカーソルオブジェクトの安全なクローズを保証し、メモリリークなどの問題を回避します。execute() メソッドを使用して SELECT_DATA_SQL で定義されたSQLステートメントを実行し、データのクエリを実行します。fetchall() メソッドを使用してクエリ結果を取得し、for ループで行ごとに結果を出力します。

   コード：

   def select_data():
       with pymysql.connect(**DB_CONFIG) as conn:
           with conn.cursor() as cursor:
               cursor.execute(SELECT_DATA_SQL)
               result = cursor.fetchall()
               for row in result:
                   print(row)
   

6. テーブル削除の関数を定義します。

   drop_table() という名前の関数を定義します。この関数は事前に定義されたデータベース接続情報（DB_CONFIG）とテーブル削除のSQLステートメント（DROP_TABLE_SQL）を使用します。関数はテーブルを削除する操作を実行し、操作の成功または失敗を示す対応するログ情報を出力します。テーブルを削除する操作が失敗した場合、エラー情報を出力します。

   コード：

   def drop_table():
       logging.info('Start dropping the table')
       with pymysql.connect(**DB_CONFIG) as conn:
           with conn.cursor() as cursor:
               try:
                   cursor.execute(DROP_TABLE_SQL)
                   conn.commit()
                   logging.info('Table dropped successfully')
               except Exception as e:
                   conn.rollback()
                   logging.error('Table drop failed, Reason：%s' % e)
   

7. プログラムのエントリポイントを定義します。これは、主にデータベースを操作する関数の実行に使用されます。

   まず、現在のモジュールがメインプログラムとして実行されているかどうかを判断し、そうであれば以下の操作を実行します：

   1. create_table() 関数を呼び出して、データベースのテーブルを作成します。
   2. insert_data() 関数を呼び出して、テーブルにデータを挿入します。
   3. select_data() 関数を呼び出して、テーブルのデータのクエリを実行します。
   4. drop_table() 関数を呼び出して、データベースのテーブルを削除します。

   コード：

   if __name__ == '__main__':
       create_table()
       insert_data()
       select_data()
       drop_table()
   

8. スクリプトの実行が完了したことを示すINFOレベルのログ情報を出力します。

   コード：

   logging.info('Script execution completed')
   

全コード表示

# Database Connection
DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

# Create table
CREATE_TABLE_SQL = '''
CREATE TABLE test_pymysql (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
  )
'''

# Insert data
INSERT_DATA_SQL = '''
INSERT INTO test_pymysql (name, age) VALUES 
(%s, %s),
(%s, %s),
(%s, %s)
'''

# Query data
SELECT_DATA_SQL = '''
SELECT * FROM test_pymysql
'''

# Delete table
DROP_TABLE_SQL = '''
DROP TABLE test_pymysql
'''

import logging
import pymysql
from config import DB_CONFIG
from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logging.info('Start executing the script')

# Create table 
def create_table():
    logging.info('Start creating the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(CREATE_TABLE_SQL)
                conn.commit()
                logging.info('Table creation successful')
            except Exception as e:
                conn.rollback()
                logging.error('Table creation failed, Reason：%s' % e)

# Insert data
def insert_data():
    logging.info('Start inserting data')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                flattened_data = [d for item in data for d in item]
                cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                conn.commit()
                logging.info('Data insertion successful')
            except Exception as e:
                conn.rollback()
                logging.error('Data insertion failed, Reason：%s' % e)

# Query data
def select_data():
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(SELECT_DATA_SQL)
                result = cursor.fetchall()
                for row in result:
                    print(row)
            except Exception as e:
                logging.error('Data query failed, Reason：%s' % e)

# Delete table
def drop_table():
    logging.info('Start dropping the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(DROP_TABLE_SQL)
                conn.commit()
                logging.info('Table dropped successfully')
            except Exception as e:
                conn.rollback()
                logging.error('Table drop failed, Reason：%s' % e)

if __name__ == '__main__':
    create_table()
    insert_data()
    select_data()
    drop_table()

logging.info('Script execution completed')

エラー処理

PyMySQLを使用してOceanBaseデータベースに接続する際、様々なエラーが発生する可能性があります。以下は、一般的なエラーとその処理方法です：

1. 接続エラー：データベースに接続できない場合は、ホスト名、ポート、ユーザー名、パスワード、データベース名を含む接続パラメータが正しいかどうか確認してください。

2. 権限エラー：権限に関連するエラーが発生した場合は、ユーザーが必要な操作を実行するために十分な権限を持っていることを確認してください。

3. SQL構文エラー：SQLステートメントに構文エラーがある場合は、SQLステートメントの構文が正しいかどうか確認してください。

4. データ型エラー：挿入したデータ型がテーブルの定義とマッチしない場合は、挿入したデータ型が正しいことを確認してください。

コード内で try-except ステートメントを使用してこれらのエラーをキャッチし、処理することで、エラー発生時にプログラムがクラッシュすることなく適切に対処できるようにします。同時に、logging モジュールを使用してエラー情報を記録することで、デバッグや問題のトラブルシューティングを容易にします。

関連ドキュメント

• OceanBaseデータベースに接続するためのその他の詳細情報については、接続方法の概要を参照してください。

• データベースの作成に関するその他の詳細については、CREATE DATABASEを参照してください。