【python】MySQLを操作する

pythonからMySQLを操作する

MySQLサーバーの構築ができたので、Pythonから操作していきます。

ライブラリのインストール

pythonからMySQLを操作するにあたって、ライブラリのインストールを行います。
ChatGPTに聞いたところ、以下の２つのライブラリが提案されました。
・mysql-connector-python
・PyMySQL
私はpyにどっぷり染まっているので、迷わず後者のpymysqlを選択しました。
本当はメリット・デメリットを検証した方がいいんでしょうけどね。

インストール方法は言わずもがな。pipコマンドのみです。

pip install PyMySQL

データベースへ接続する

データベースへ接続を行います。
PyMySQLではConnectオブジェクトを使います。
hostからcharsetまでは、MySQLサーバー構築時に設定したものです。

# MySQLに接続（local_infile=True がポイント） self.connection: pymysql.Connection[Cursor] = pymysql.connect( host=hostname, user=user_id, password=password, database=database, charset=self.CHAR_SET, local_infile=self.LOCAL_INFILE )

　local_infileは定数化していますがTrueを指定しています。

LOCAL_INFILE:bool = True

これは、クライアント側のローカルファイルを使ってMySQLサーバーへデータ投入することができる指定です。（ちょっと理解が間違えているかも）
この定義はサーバー側、クライアント側ともにONになっている必要があります。
クライアント側はプログラム側でTrueにすればOK

サーバー側は「/etc/my.cnf」に対して設定を追加します。
これはあくまでDocker on MySQLの場合で、環境によって異なる場合がありますのでご注意ください。

[mysqld] local_infile=1

この定義を追加します。
なお、私のDocker環境にはviエディター、nanoエディターもろもろが入っていないため、echoコマンドを使って追記しようとしたところ誤って上書きしてしまったので、mac側でmy.cnfファイルを作成して、scpコマンドでアップロードし直しました。（事前にcatしておいてよかった...）

定義追加後、DockerのMySQLサーバーを再起動します。

docker restart コンテナID または コンテナ名

その後、MySQLサーバーへ入り直し、以下コマンドで結果を確認します。

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | ON | +---------------+-------+ 1 row in set (0.00 sec)

local_infile の Value が ON になっていれば OK です。
最初、定義を追加したのに OFF から ON にならず、設定ファイルが実は違うところにあるのかと疑ったのですが、macで再作成するときに拡張子を間違えて「my.conf」でファイルを作っていたためでした。
mvコマンドで 「my.cnf」にリネームしたら無事反映されました。

テーブルへデータをインポートする

まず、今回やりたかったのはデータのインポートでしたので、インポート用のSQLを作ります。
PyMySQLではConnectオブジェクトを使います。
hostからcharsetまでは、MySQLサーバー構築時に設定したものです。

sql = f""" LOAD DATA LOCAL INFILE '{csv_path}' INTO TABLE {tablename} FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '{rtnchar}' """

　LOCAL INFILE はプログラムで作成するCSVファイルの絶対パスです。
先ほどのlocal_infileが OFF だと以下のようなエラーが出ます。

"/Users/ユーザー名/.pyenv/versions/3.13.2/lib/python3.13/site-packages/pymysql/err.py", line 150, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.OperationalError: (3948, 'Loading local data is disabled; this must be enabled on both the client and server sides')

INTO TABLE はテーブル名です。
FIELDS TERMINATED BY は区切り文字です。今回はCSVファイルなので,(カンマ)です。
ENCLOSED BY ;は項目を囲っている記号です。今回はダブルクォーテーションです。（インポートが終わったら中間ファイルを速攻で消す動きになっているので、ここが実際に合っているかをみれていないw）
LINES TERMINATED BY は改行コードです。環境に合わせて設定してください。

続いてSQLの実行です。
コネクションからカーソルを作成します。
カーソル（cursor） とは、SQLを実行したり、結果を取り出すための「操作用オブジェクト」 のことです。（by ChatGPT）

# カーソルを生成する myCorsor = self.connection.cursor()

カーソルを作ったらSQLを実行させます。

# SQLを実行する myCorsor.execute(sql)

実行が終わったらコミットします。（結果にコミ。。。）

# コミットする self.connection.commit()

コミットまで終了したらカーソルを閉じておきます。
基本的には使い終わったら閉じておくのがよいかと思いますが、プログラム次第ですのでご自由に、ですね。

# カーソルを閉じる myCorsor.close()
mysql> select * from NVD_DB.NVD_CVE_INFO; +-------+------------------+---------------------+---------------------+-------------------------+ | cveid | sourceidentifier | published | lastModified | vulnStatus | +-------+------------------+---------------------+---------------------+-------------------------+ | | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 3 | | 0 | CVE-1999-0095 | 0000-00-00 00:00:00 | 1988-10-01 04:00:00 | 2025-04-03T01:03:51.193 | +-------+------------------+---------------------+---------------------+-------------------------+

妙なゴミデータが入っていますが、インポートできるはできています。
あれ、、、なんかインポート結果がおかしいw
こんな感じでインポートは終了です。

コネクションを切断する

後始末のdisconnectです。

self.connection.close()

コネクションを貼ったら、オブジェクトのプロパティに一時的に持たせているので selfがついていますが、close()メソッドを発行するだけです。
コネクションも基本的には使い割ったら閉じる方がよいと思います。
用もないのにプログラム実行中コネクションを貼り続けるのもどうかと思うので、必要なときに必要な間ただけコネクトおくのがよいと思います。

今回はここまでです。また、MySQL関連で気づきがあれば追記していきます。

コメント

コメントを投稿

このブログの人気の投稿

【python】PySide6 におけるウィンドウとかダイアログボックス内のアイテムの自動調整

PySide6 におけるウィンドウとかダイアログボックス内のアイテムの自動調整 PySide6などに同封されるDesignerでWindowやDialogを作成する際、verticalLayoutWidgetをまず画面いっぱいに配置して、その中にhrizonalLayoutWidgetを配置して、、、とGUIを作成していきます。 ただ、Designer上でverticalLayoutWidgetの設定を「SetDefaultConstraint」にする方法がわからず、毎回 GitHub Copilotに修正してもらっていたのですが、現在 GitHub Copilotの１ヶ月間で使用できる無料の範囲を使い切ってしまったので、ChatGPTに質問してみました。 質問内容はただ単に、ダイアログボックスとかの大きさにあわせてverticalLayoutWidgetを動的にサイズ変更したい、という至ってシンプルな内容です。 #before <property name="sizeConstraint"> <enum>QLayout::SizeConstraint::SetFixedSize</enum> </property> #after <property name="sizeConstraint"> <enum>QLayout::SizeConstraint::SetDefaultConstraint</enum> </property> 本当はDesignerから変更する方法がわかれば手っ取り早いのですが、今はこれで妥協しておきます。
続きを読む

脆弱性評価システムを作る〜CVSSとは〜

週間・脆弱性評価システムを作ろう〜CVSSとは〜 前回に引き続いて脆弱性情報についてです。今回はCVSSについてまとめました。 ソースは引き続きIPAとChatGPTとなります。 CVSS （ Common Vulnerability Scoring System) とは 共通脆弱性評価システム CVSS （ Common Vulnerability Scoring System) は、情報システムの脆弱性に対するオープンで包括的、汎用的な評価手法の確立と普及を目指し、米国家インフラストラクチャ諮問委員会（ NIAC: National Infrastructure Advisory Council ）のプロジェクトで 2004年10月に原案が作成されました。 その後、CVSSの管理母体として FIRST(Forum of Incident Response and Security Teams)が選ばれ、FIRSTのCVSS-SIG(Special Interest Group)で適用推進や仕様改善が行われており、2005年6月にCVSS v1が、2007年6月にCVSS v2が公開されました。 CVSS は、情報システムの脆弱性に対するオープンで汎用的な評価手法であり、ベンダーに依存しない共通の評価方法を提供しています。CVSSを用いると、脆弱性の深刻度を同一の基準の下で定量的に比較できるようになります。また、ベンダー、セキュリティ専門家、管理者、ユーザ等の間で、脆弱性に関して共通の言葉で議論できるようになります。 CVSSでは次の3つの基準で脆弱性を評価します。 CVSSの目的 標準化された評価   ベンダーごとに異なっていた脆弱性の深刻度表現を標準化し、共通の尺度で評価します。 定量的な評価   脆弱性の深刻度を0.0から10.0の数値で定量的に表現し、比較可能にします。 共通の言語   セキュリティ担当者、ベンダー、ユーザーな...
続きを読む

dict型オブジェクトを内包するlistオブジェクトのコピー

dict型オブジェクトを内包するlistオブジェクトのコピー（代入）をしたくて、当初は以下の通り行っていたが、これだと値ではなく、参照渡しがされてしまってい るようで、 work_driverPaths を変更すると driverPaths の方にもその結果が反映されてしまった。 self.work_driverPaths = self.driverPaths そのため、以下の通り copy ライブラリの deepcopy を使用して解決。 self.work_driverPaths = copy.deepcopy(self.driverPaths) コパイロットぴ◯ょん君の回答 list型とdict型を併用した場合に参照渡しが発生する要因 = で代入するときの動作だね。代入した側のオブジェクトとオリジナルのオブジェクトがリンクすることについて気づいたかな？ 実際、 = は 浅いコピー（shallow copy） を作るだけなので、両方のオブジェクトが同じ参照を共有してしまうんだ。 deepcopyの機能について 深いコピー : オリジナルのオブジェクトとそのコピーが完全に独立して動作する。 再帰的処理 : 内部のネストされたオブジェクトもすべてコピーする。 安全性 : オリジナルを変更してもコピーには影響がない。
続きを読む