PyMySQL による OB Cloud データベース接続のサンプルプログラム

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

      クリックして python-pymysql サンプルプロジェクトをダウンロード
  </a>
  <a role='video' href='https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/video-center/video/video/03%20python-pymysql.mp4'>
      
      PyMySQL による OceanBase データベース接続のサンプルプログラム（MySQLモード）
  </a>

• Python 3.x および pip がインストールされていること。
• OB Cloud データベースのアカウントを登録し、クラスタインスタンスと MySQL互換モード のテナントを作成していること。詳細は、クラスタインスタンスの作成 および テナントの作成 をご参照ください。

手順

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

手順 1：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 は、純粋な Python で実装された MySQL クライアントライブラリであり、インストールが簡単で、コンパイルが不要で、クロスプラットフォームの互換性に優れています。MySQL データベースと対話するためのインターフェースを提供し、Python 3.x バージョンに対応しています。

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

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

   例：

   cd python-pymysql
   

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

   例：

   pip install -r requirements.txt
   

手順 3：OB Cloud データベースの接続情報の取得

1. OB Cloud コンソール にログインします。インスタンス一覧ページで、対象インスタンスの情報を展開し、対象テナントの下で 接続 > 接続文字列の取得 を選択します。

   詳細な操作については、接続文字列の取得 をご参照ください。

2. 作成済みの OB Cloud データベースに基づいて、以下の URL の対応する情報を入力します。

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

   パラメータの説明：

   • $host：OB Cloud データベースの接続アドレスを指定します。例：t********.********.oceanbase.cloud。

   • $port：OB Cloud データベースの接続ポートを指定します。デフォルトは 3306 です。

   • $database_name：アクセスするデータベース名を指定します。

     注意

     テナントに接続するユーザーは、該当データベースに対する CREATE、INSERT、DROP、および SELECT 特権を持っている必要があります。アカウント特権の詳細については、アカウントの作成と管理 をご参照ください。

   • $user_name：データベースにアクセスするアカウントを指定します。

   • $password：アカウントのパスワードを指定します。

   例：

   obclient -h t********.********.oceanbase.cloud -P3306 -u mysql001 -p****** -Dtest
   

手順 4：config.py ファイル内のデータベース接続情報の変更

手順 3：OB Cloud データベースの接続情報の取得 の情報に基づいて、プロジェクトファイル 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': 't5******.********.oceanbase.cloud',
       'port': 3306,
       'user': '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 パッケージとそのバージョン情報を格納するために使用されます。

  説明

  本記事で取得したコードには 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：OB Cloud データベースの接続アドレス。
• $port：OB Cloud データベースの接続ポート。
• $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 ループを通じて結果を 1 行ずつ出力します。

   コードは以下のとおりです。

   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 を使用して OB Cloud データベースに接続する際、さまざまなエラーが発生する可能性があります。以下に、一般的なエラーとその処理方法を示します。

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

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

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

4. データ型エラー：挿入するデータ型がテーブル定義と一致しない場合は、挿入するデータ型が正しいか確認してください。

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

関連情報

• OB Cloud データベースへの接続に関する詳細は、接続方法の概要 をご参照ください。