PythonでPostgreSQLに接続してみた
概要
前回の記事ではPostgreSQLのインストールを行いました。今回はPythonからPostgreSQLへの接続を行う方法をまとめます。
(前回のPostgreSQLのインストール記事:PostgreSQLをyumでインストール(yumとrpmの復習込み) - 薮蛇なエンジニアの開発備忘録)
PythonからDB接続するためにはドライバというモジュールをインストールする必要があるようです。
psycopg2というドライバが2016/1時点で一番DLされているようなので、そちらを利用しました。
(参考サイト:PythonからPostgreSQLに接続する方法 | アシスト)
psycopg2のインストール
シンプルにpipでインストールしたところエラーが出てしまいました。
pip install psycopg2
エラー内容から一部抜粋
Error: pg_config executable not found. Please add the directory containing pg_config to the PATH or specify the full executable path with the option: python setup.py build_ext --pg-config /path/to/pg_config build ... or with the pg_config option in 'setup.cfg'.
エラーをgoogleで検索したところ既に解決している方がいたので、その方法を使わせて頂きました。
(参考サイト:Python / PostgreSQL on Heroku でpsycopg2がない & pg_configがないと怒られる時 - 木木木)
pg_configへのPATHを追加してください的な内容のエラーなので、pg_configの場所を調べてPATHに追加してやれば良いようです。
以下は解決方法の引用です。
locate pg_config
これで私の場合は/usr/pgsql-9.4/bin/pg_configが出てきたので次のようにしました。
2016.05.20追記:別の環境で構築したところ、locateコマンドを叩いても何も表示されませんでした。updatedbというコマンドを叩くとlocate用ファイルが更新されてパスが表示されるようになりました。
export PATH="/usr/pgsql-9.4/bin:$PATH"
再度インストール
pip install psycopg2 python >>> import psycopg2 >>> >>> exit()
無事インストールできて、実際にimportして確認してもエラーは出ませんでした。
2016.05.20追記:何故かbottle環境からpsycopg2を利用しようとするとインポートエラーになりました。yum -y install psycopg2を実行したところ直りました。yumとpipの関係が分かっていないので今後調べます。
今回の私の原因とは違うので余談ですが、postgresql-develをインストールしていない場合でも Error: pg_config executable not found. のエラーが起きるようです。
(参考サイト:(Djangoメモ)データベースにPostgreSQLを設定 - Qiita)
pythonからPostgreSQLに接続
DBユーザの作成、DBの作成は済ませているものとします。
以下のサイトを参考にしてまずはDB接続を行いました。
(参考サイト:PythonからPostgreSQLに接続する方法 | アシスト)
【】で括った部分は既に作成済みのDB情報に合わせるものとします。
python >>> import psycopg2 >>> connection = psycopg2.connect("host=【IPアドレス】 port=5432 dbname=【DB名】 user=【ユーザ名】 password=【パスワード】")
これで繋がると思ったらエラーが出てしまいました。
エラー内容から一部抜粋
psycopg2.OperationalError: could not connect to server: Connection refused
接続が拒否されているようなので、外部から接続できるように設定します。以下のサイトを参考に試みました。
(参考サイト1:http://rina.jpn.ph/~rance/linux/postgresql/connect.html)
(参考サイト2:PostgreSQLのポート番号を変更する - (=゜ω゜)ノぃょぅ にっき)
まずは受け入れポート等の設定変更
locate postgresql.conf vim /var/lib/pgsql/9.4/data/postgresql.conf
postgresql.confの中身からlisten_addressとportのコメントを外して書き換え
listen_addresses = 'localhost, 【IPアドレス】' port = 5432
接続できるクライアントの指定
locate pg_hba.conf vim /var/lib/pgsql/9.4/data/pg_hba.conf
pg_hba.confに以下内容を追加
host all all 【IPアドレス】/32 trust
service postgresql-9.4 restart
設定を変更してpostgresqlを再起動した後、再びpsycopg2.connect(引数は省略)を実行したところ、正常に接続することができました。
注意:pg_hba.confで接続できるクライアントのIPアドレスに、自身のIPアドレスを指定して追加しただけなのでセキュリティ的に大丈夫と思いますが、この設定内容を利用する方がいましたら各自で再度問題ないかご確認ください。
INSERTとSELECTの実行
データベースに接続できたので、INSERTとSELECTを実行してみます。
(参考サイト:psycopg2によるPython2.7からのAmazon Redshiftアクセスサンプル | Developers.IO)
python >>> import psycopg2 >>> conn = psycopg2.connect( ... host="【IPアドレス】", ... database="【DB名】", ... port="5432", ... user="【ユーザ名】", ... password="【パスワード】" ... ) >>> cursor = conn.cursor() >>> cursor.execute("INSERT INTO table_name (column1) VALUES ('hoge')") >>> cursor.execute("INSERT INTO table_name (column1) VALUES ('fuga')") >>> cursor.execute("SELECT * FROM table_name") >>> results = cursor.fetchall() >>> for row in results: ... print(row[0]) ... hoge fuga >>> conn.commit() >>> cursor.close() >>> conn.close()
【】で括った項目とtable名とカラム名は事前に作成しておいた名前を入力するものとします。
insertしたhogeとfugaをselectの結果として表示することができました。
ここでは全件取得でcursor.fetchall()を用いましたが、1レコードだけ取り出すときはcursor.fetchone()を利用するようです。
プレースホルダの利用
以下のサイトを見ながらプレースホルダを使用してみました。
(参考サイト:PostgreSQLをPythonからpsycopg2を使っていじる — そこはかとなく書くよん。)
一つ前に書いたコード中のINSERT文を次のように置き換えできました。
cursor.execute("INSERT INTO table_name (column1) VALUES (%s)", ["hoge"])
値の渡し方に癖があるらしく、("hoge")にすると受け付けないらしいです。
あとINT型でも%sを使うようです。
明示的なCOMMIT/ROLLBACKの必要性
以下のサイトによりますと、参照の処理だけでもトランザクション開始されるようなので、明示的にCOMMIT/ROLLBACKする必要性があるとのことです。
(参考サイト:PythonからPostgreSQLに接続する方法 | アシスト)
自動COMMITさせるためには次のように属性を変更すると可能なようです。
>>> connection.autocommit = True
まとめ
PythonでPostgreSQLに接続する方法について調べて、基本的なDB操作ができるようになりました。
PostgreSQLのインストールさえできていればPythonから簡単に呼び出せるかと思っていましたが、意外と設定に苦戦してしまいました。
もっとスマートな環境構築方法やPostgreSQLの設定等についてはおいおい詳細を勉強していきたいです。