こんにちは。キノコードです。
前回はPostgreSQLの環境構築の解説動画をアップしました。
今回の動画は番外編として、PythonからPostgreSQLを操作するという動画をお送りします。
この動画で解説するように、PythonからPostgreSQLへ接続もできますし、MySQLやGCPのBigQueryにも接続できます。他にも、色々なデータベースを操作できます。
1つの例としてご覧いただければと思います。
このレッスンではPythonのPandasというライブラリを使います。
まだ学習がお済みではない方は、Pythonを使えればこんなことができるんだーってゆるりとみていただければと思います。
またここのレッスンで作成したテーブルは次回のレッスンでも使います。
キノコードでは、SQL超入門講座の他に、人気言語のPython超入門講座、SQLのようなデータ抽出やデータ前処理が得意なPandas入門講座、Pythonを使った仕事の自動化の講座なども配信しています。
チャンネル登録がまだの方は、チャンネルがどこにいったかわからなくならないように、チャンネル登録をお願いします。
また、キノコードではメンバーシップをやっています。キノコードの動画制作活動を応援してもいいよーって方は、キノコードのチャンネルメンバーになって応援をお願いします。290円から応援できます。詳しくはチャンネル登録ボタンの隣にある「メンバーになる」をクリックしてください。解説動画があります。
それではレッスンスタートです。
ライブラリの準備
まず、ライブラリをインストールします。
PythonからPostgreSQLに接続するには「psycopg2」と「sqlalchemy」というライブラリが必要です。
pipでインストールしましょう。
Jupyter Labからpipを使う場合は、最初にエクスクラメーションをつけます。
実行します。
次に先ほどインストールしたライブラリをインポートします。
psycopg2とsqlalchemyの中のcreate_engineをインポートします。
また、データフレームをそのままデータベースのテーブルにしたいと思いますので、
Pandasをインポートします。
これで準備が完了です。
データフレームの作成
それではまず、データフレームを作ります。
実行します。
このようなデータフレームです。
氏名と出席番号と国語のカラムがあるデータフレームです。
これをそのままデータベースのテーブルにします。
データフレームからテーブルにする方法
connection_configという変数に接続情報を辞書型で代入します。
user,password,host,port,databaseにカンマで区切ってデータを渡します。
そして、create_engineを使って、接続情報をengineをいう変数に渡します。
これで準備が完了です。
実際にテーブルを作りましょう。
先ほど作成したデータフレームにドット、to_sqlと書きます。
丸括弧の第一引数にはテーブル名を記述します。
今回はtest_table01というテーブルにしましょう。
conという引数には先ほど作成したengine、if_existsという変数にはreplaceを渡します。
このif_existsは、すでにテーブルが存在している場合に、どのような処理をするか決めるものです。
すでに同じ名前のテーブルがあったらreplaceは書き換える、failはエラーにする、appendは追加をするということになります。
それでは実行します。
テーブルができました。
復習のためにももう1つ作ってみましょう。
もう一度データフレームを作成します。
今度は国語の点数ではなく、数学の点数としましょう。
氏名も一部変更しています。
次は、test_table02としましょう。
実行します。
テーブルができました。
次に、csvを読み込んでそれをテーブルにしましょう。
csvファイルはこのようなファイルです。
csvファイルを読み込むにはread_csvメソッドを使います。
実行します。
head関数でデータフレームの上位5件をみてみましょう。
このようなデータフレームです。
IDのカラムをデータフレームのインデックスにしましょう。
set_indexメソッドを使います。
実行します。
IDがindexに設定されています。
それではto_sqlメソッドを使ってテーブルにしましょう。
テーブル名はtest_tableとしましょう。
みなさんはもっと変わりやすいテーブル名にしてくださいね。
実行します。
テーブルができました。
Pythonでテーブルのデータを取得する方法
次に、Pythonでテーブルのデータを取得。
データフレームに変換する方法です。
テーブルのデータをデータフレームに変換するには、read_sqlメソッドを使います。
最初のsqlという引数には記述するsql文を渡します。
conの引数には先ほどと同じ接続情報が代入されたengineを渡します。
実行します。
これでデータフレームに変換されたはずです。
データフレームが代入されたdfを表示させてみましょう。
格納されています。
データフレームに変換されているので、SQLでできる並び替えなどをやったり、Pythonならではの可視化、関数を作ってフラグ立てなどのデータ加工をすることもできます。
またSQL文も細かく記述をすることができます。
SQL文の中に文字列がある場合はシングルクオテーションでくくり、全体をダブルクオテーションでくくります。
商品分類をボトムスだけで抽出してみましょう。
実行します。
ボトムスだけ抽出できました。
また一部をPythonの変数に置き換えることもできます。
商品分類の3商品をリストにして、for分でまわして順番にwhere句で指定したSQLで抽出をしてみましょう。
Pythonがわからない人のために簡単に補足すると、product_nameにハーフパンツ、ジーンズ、ロングパンツの3つが代入されています。
for i in product_nameと記述すると、ハーフパンツ、ジーンズ、ロングパンツが順番にiに代入されます。
そして、そのiをwhereの商品名に順番に渡します。
そうすると、
select from test_table where 商品名 = 'ハーフパンツ';
select from test_table where 商品名 = 'ジーンズ';
select * from test_table where 商品名 = 'ロングパンツ';
というSQL文が順番に実行されます。
実行します。
ハーフパンツ、
ジーンズ、
ロングパンツのデータが抽出できました。
