PythonからPostgreSQLにpsycopg2を使って接続する方法

PythonからPostgreSQLに接続する方法

ここ数年、Pythonの人気が高まっています。2016年2月時点では、世界中のインターネット上のドキュメントに多く登場するプログラミング言語を集計したデータを公開しているTIOBE Programming Community のプログラミング言語人気ランキングでもかなり上位に位置しています。

そこで今回はPythonからPostgreSQLへ接続する方法をご紹介します。

PythonのPostgreSQL用ドライバ

PythonからPostgreSQLに接続する場合、"ドライバ"と呼ばれるモジュールを使用します。PostgreSQL用として現在公開されているメジャーなものでは、以下の3つが挙げられます。なお、備考内の"libpq"とはPostgreSQL自体が提供している、クライアントアプリケーション用のC言語のライブラリです。

ドライバ	備考	2016年1月ダウンロード数
psycopg2	Python2,3系対応、libpq依存	863273
pg8000	Python2,3系対応、libpq非依存	52123
py-postgresql	Python 3系のみ動作、libpq非依存	3482

ダウンロード数を確認するとpsycopg2が最も使われているようですので、今回はpsycopg2を使ってPostgreSQLに接続してみます。

検証環境

今回利用した環境は以下です。
* Python実行環境
　- OS: Oracle Linux 6.7 64bit
　- Python 3.4
　- psycopg2 2.6.1
* 接続先PostgreSQL環境
　- OS: Ubuntu 12.0.4 32bit
　- PostgreSQL 9.4.3 32bit

なお、インストールについてはWindowsも含めてご紹介します。

1. psycopg2のインストール

1-1. Windows編

これまでWindows用に移植されたpsycopg2のインストーラによる方法をご紹介していました。しかし現在（2021年11月時点）はインストーラ提供サイトにアクセスできなくなっているため、手動でのインストール方法をご紹介します。

まず、psycopg2の公式サイトより実行環境のpythonのバージョンに合わせたインストーラをダウンロードします。
2021年11月時点では2.9.1が最新です。32bit／64bit環境ではそれぞれ以下のインストーラが該当します。

* psycopg2-2.9.1-cp310-cp310-win32.whl
* psycopg2-2.9.1-cp310-cp310-win_amd64.whl

python　ダウンロード

※最新版がリリースされた場合には、ご利用環境の要件に合わせて適宜インストーラを選択してください。

実行環境のコマンドプロンプトより、python -V を実行し、インストール済みの pythonのバージョンおよびBIT数に合わせたものをダウンロードしてください。

弊社環境では Windows 10 64bi環境で、導入済み Pythonは 64bit版の3.9.7でしたので、psycopg2-2.9.1-cp39-cp39-win_amd64.whl を使用しました。

次に、PIPコマンドを使用して手動でインストールします。
この際、そのまま psycopg2-2.9.1-cp39-cp39-win_amd64.whl を指定すると、以下のエラーが返りました。

　
　c:\work>pip install "C:\work\tools\psycopg2-2.9.1-cp39-cp39-win_amd64.whl"
　ERROR: psycopg2-2.9.1-cp39-cp39-win_amd64.whl is not a supported wheel on this platform.

このエラーを回避するために、ダウンロードしてきたファイル名を「psycopg2-2.9.1-cp39-none-win_amd64.whl」にリネームします。
この対応により 64bitの Pythonに対応した psycopg2をインストールすることができるようになります。

　
　c:\work>pip install "C:\work\tools\psycopg2-2.9.1-cp39-none-win_amd64.whl"
　Processing C:\work\tools\psycopg2-2.9.1-cp39-none-win_amd64.whl
　Installing collected packages: psycopg2
　Successfully installed psycopg2-2.9.1

Psycopg 2.9.1 documentation(https://www.psycopg.org/docs/install.html )にも記載はありませんでしたが、問題なく psycopg2を使用して接続できることを確認しました。

1-2. Linux編(yum)

Linuxの場合、各ディストリビューション毎にビルド済のパッケージが提供されています。OSにデフォルトでインストールされているPythonにpsycopg2をインストールしたい場合はビルド済パッケージを利用することをお薦めします。

Oracle Linuxであればyumが利用できますので、以下の手順で導入できます。

　
　[root@yamax64 ~]# yum install python-psycopg2
　Loaded plugins: refresh-packagekit, security, ulninfo
　Setting up Install Process
　Resolving Dependencies
　--> Running transaction check
　---> Package python-psycopg2.x86_64 0:2.0.14-2.el6 will be installed
　--> Processing Dependency: libpq.so.5()(64bit) for package: python-psycopg2-2.0.14-2.el6.x86_64
　--> Running transaction check
　---> Package postgresql-libs.x86_64 0:8.4.20-4.el6_7 will be installed
　--> Finished Dependency Resolution
　:
　:
　Running rpm_check_debug
　Running Transaction Test
　Transaction Test Succeeded
　Running Transaction
　  Installing : postgresql-libs-8.4.20-4.el6_7.x86_64                                                                                                                                                           1/2 
　/sbin/ldconfig: File /usr/lib/libpq.so.5.7 is empty, not checked.
　  Installing : python-psycopg2-2.0.14-2.el6.x86_64                                                                                                                                                             2/2 
　  Verifying  : postgresql-libs-8.4.20-4.el6_7.x86_64                                                                                                                                                           1/2 
　  Verifying  : python-psycopg2-2.0.14-2.el6.x86_64                                                                                                                                                             2/2 
　Installed:
　  python-psycopg2.x86_64 0:2.0.14-2.el6                                                                                                                                                                            
　Dependency Installed:
　  postgresql-libs.x86_64 0:8.4.20-4.el6_7                                                                                                                                                                          
　Complete!

インストール完了後に以下のコマンドがエラーなく実行できれば正常にインストールができています。

　
　[root@yamax64 ~]# python -V
　Python 2.6.6
　[root@yamax64 ~]# python -c "import psycopg2"
　[root@yamax64 ~]#

1-3. Linux編(pip)

Oracle Linux 6でデフォルトで用意されているPythonは2.6ですが、2.6は8年前にリリースされた古いバージョンです。より新しいPythonを使用するため、異なるバージョンを手動で追加している環境はyumでは上手く導入できません。そのためPythonのパッケージマネージャであるpipを使用したインストール方法をご紹介します。

特にOS側のパッケージを準備しない状態でpipを実行すると、以下の様にpg_configが必要というエラーで失敗します。

　
　[postgres@yamax64 ~]$ pip install psycopg2
　Collecting psycopg2
　  Using cached psycopg2-2.6.1.tar.gz
　    Complete output from command python setup.py egg_info:
　    running egg_info
　    creating pip-egg-info/psycopg2.egg-info
　    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
　    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
　    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
　    writing manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'
　    warning: manifest_maker: standard file '-c' not found
　    
　    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'.
　    
　    ----------------------------------------
　Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-k_ktmmvx/psycopg2

pg_configはpostgresql-develパッケージに含まれていますので、yumで導入します。

　
　[root@yamax64 ~]# yum install postgresql-devel-8.4.20-4.el6_7.x86_64
　Loaded plugins: refresh-packagekit, security, ulninfo
　Setting up Install Process
　Resolving Dependencies
　--> Running transaction check
　---> Package postgresql-devel.x86_64 0:8.4.20-4.el6_7 will be installed
　--> Processing Dependency: postgresql(x86-64) = 8.4.20-4.el6_7 for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Processing Dependency: postgresql-libs(x86-64) = 8.4.20-4.el6_7 for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Processing Dependency: libpgtypes.so.3()(64bit) for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Processing Dependency: libecpg_compat.so.3()(64bit) for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Processing Dependency: libecpg.so.6()(64bit) for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-devel-8.4.20-4.el6_7.x86_64
　--> Running transaction check
　---> Package postgresql.x86_64 0:8.4.20-4.el6_7 will be installed
　---> Package postgresql-libs.x86_64 0:8.4.20-4.el6_7 will be installed
　--> Finished Dependency Resolution
　
　:
　:
　
　Running rpm_check_debug
　Running Transaction Test
　Transaction Test Succeeded
　Running Transaction
　  Installing : postgresql-libs-8.4.20-4.el6_7.x86_64                                                                                                                                                           1/3 
　/sbin/ldconfig: File /usr/lib/libpq.so.5.7 is empty, not checked.
　  Installing : postgresql-8.4.20-4.el6_7.x86_64                                                                                                                                                                2/3 
　  Installing : postgresql-devel-8.4.20-4.el6_7.x86_64                                                                                                                                                          3/3 
　  Verifying  : postgresql-libs-8.4.20-4.el6_7.x86_64                                                                                                                                                           1/3 
　  Verifying  : postgresql-devel-8.4.20-4.el6_7.x86_64                                                                                                                                                          2/3 
　  Verifying  : postgresql-8.4.20-4.el6_7.x86_64                                                                                                                                                                3/3 
　
　Installed:
　  postgresql-devel.x86_64 0:8.4.20-4.el6_7                                                                                                                                                                         
　
　Dependency Installed:
　  postgresql.x86_64 0:8.4.20-4.el6_7                                                                    
　  postgresql-libs.x86_64 0:8.4.20-4.el6_7                                                                   
　
　Complete!

無事にpostgresql-develが導入できました。なお、postgresql-develの依存関係解決のためpostgresql関連のパッケージがいくつか導入されます。

それでは改めてpsycopg2をpipでインストールしましょう。

　
　[postgres@yamax64 ~]$ pip install psycopg2
　Collecting psycopg2
　  Using cached psycopg2-2.6.1.tar.gz
　Building wheels for collected packages: psycopg2
　  Running setup.py bdist_wheel for psycopg2 ... done
　  Stored in directory: /home/postgres/.cache/pip/wheels/e2/9a/5e/7b620848bbc7cfb9084aafea077be11618c2b5067bd532f329
　Successfully built psycopg2
　Installing collected packages: psycopg2
　Successfully installed psycopg2-2.6.1

無事にインストールできました。

インストール完了後に以下のコマンドがエラーなく実行できれば正常にインストールができています。

　
　[postgres@yamax64 ~]$ python -V
　Python 3.4.4
　[postgres@yamax64 ~]$ python -c "import psycopg2"
　[postgres@yamax64 ~]$

2. psycopg2からPostgreSQLへ接続

psycopg2でのPostgreSQLへの接続は以下のようにpyscopg2.connect()で開始します。

　
　>>> import psycopg2
　>>> connection = psycopg2.connect("host=192.168.24.97 port=9403 dbname=sampledb user=sayamada password=pssword")
　>>> connection.get_backend_pid()
　23769

PostgreSQLサーバ側のプロセス23769として接続しています。DBサーバ側でもPostgreSQLのプロセスが起動していることを確認してみます。

　
　$ ps aux |grep 23769 |grep -v grep
　USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
　sayamada 23769  0.0  0.0 158648  5228 ?        Ss   21:14   0:00 postgres: sayamada sampledb 192.168.26.197(15476) idle

対応するPostgreSQLのプロセスが起動しています。

connect()には以下の接続情報を含めることができます。

パラメータ	意味
dbname	接続先のDB名
user	接続に使用するDBユーザ名、省略時はOSユーザ名が使用される
password	接続に使用するDBユーザのパスワード
host	接続先のアドレス、省略時はUnixドメインソケットでの通信を行う
port	接続先のポート番号、省略時は5432が使用される

これらのパラメータは、PostgreSQLが使用する "パラメータキーワード" と同じになっています。

3. SQLの実行方法

接続ができましたので、実際にSQLを発行してみましょう。SQLの発行にはまず"cursor"オブジェクトを取得します。

cursorオブジェクトはconnection.cursor()で取得できます。

　
　>>> cur = connection.cursor()
　>>> print(cur)

SQLはcursorオブジェクトのexecuteメソッドで実行します。

　
　>>> cur.execute("select version()")
　>>> for row in cur:
　...     print(row)
　... 
　('PostgreSQL 9.4.3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 32-bit',)

この例のように、SQL発行後にcursorオブジェクトをループにかければ行を取り出すことができます。あるいは以下のように明示的にfetchone()メソッドを実行することでも行を取り出せます。

　
　>>> cur.execute("select version()")
　>>> cur.fetchone()
　('PostgreSQL 9.4.3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 32-bit',)

また、DML／DDLも同様にexecuteメソッド経由で発行します。

　
　>>> cur.execute("create table demo(id int,data text)")
　>>> cur.execute("insert into demo values(1,'HELLO WORLD')")
　>>> cur.execute("insert into demo values(2,'HELLO KKA')")

SELECTとの違いは、SQL実行後にcursorオブジェクトから取得できる結果がないという点です。

なお、SELECTの結果は列を要素としたタプルで戻るのでSELECTで指定した順で取り出します。

　
　>>> cur.execute("select id, data from demo")
　>>> for row in cur:
　...     print(row[0], row[1]) # 各行の0,1番目の列の結果を取り出す
　... 
　1 HELLO WORLD
　2 HELLO KKA

無事に結果を取り出すことができました。しかし、この方法では必要な列が何番目かを都度確認しなければならず、可読性が下がる場合もあります。そのため列名でアクセスする方法も用意されています。

列名でアクセスするためには以下のようにDictCursorオブジェクトを取得します。

　
　>>> import psycopg2.extras # DictCursorを使用するために拡張機能をimport
　>>> dict_cur = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) # psycopg2.extras.DictCursorを指定
　>>> dict_cur.execute("select id, data from demo")
　>>> for row in dict_cur:
　...     print(row["id"], row["data"]) # 各行のid,data列の結果を取り出す
　... 
　1 HELLO WORLD
　2 HELLO KKA

列名でアクセスできますので、列番号より取り出しが楽になりました。

4. トランザクションの扱い

ここまでいくつかSQLを発行してきました。一度、現時点のPostgreSQL側のプロセスを確認してみましょう。

　
　$ ps aux |grep 23769 |grep -v grep
　USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
　sayamada 23769  0.0  0.0 158648  5228 ?        Ss   21:14   0:00 postgres: sayamada sampledb 192.168.26.197(15476) idle in transaction

ステータスがidle in transactionになっています。これはPostgreSQLのバックエンドプロセスがトランザクションを継続したまま待機していることを示しています。

psycopg2のデフォルトの動作は、明示的にCOMMIT／ROLLBACKを発行するまでトランザクションを継続します。

それでは、先程のトランザクションをCOMMITで確定しましょう。COMMIT／ROLLBACKはそれぞれconnection.commit()／connection.rollback()で実行します。

　
　>>> connection.commit()

特に何も表示されませんが、COMMITが行われました。再度PostgreSQL側のプロセスを確認します。

　
　$ ps aux |grep 23769 |grep -v grep
　USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
　sayamada 23769  0.0  0.0 158628  5420 ?        Ss   21:14   0:00 postgres: sayamada sampledb 192.168.26.197(15476) idle

idle in transactionからidleに変化しています。つまり先程までのトランザクションが終了していることが確認できました。

pyscopg2はトランザクションがオープンされていない状態で、最初にSQLを実行したタイミングからトランザクションをオープンします。この時発行したSQLがSELECTであっても同様です。

　
　>>> cur = connection.cursor()
　>>> cur.execute("select id,data from demo")
　>>> cur.fetchone()
　(1, 'HELLO WORLD')
　>>> connection.commit()

PostgreSQL側で log_min_duration_statement=0 を指定して発行されているSQLを出力させてみると、以下のようにBEGIN／COMMITでトランザクション処理されていることがわかります。

　
　LOG:  duration: 0.230 ms  statement: BEGIN
　LOG:  duration: 51.343 ms  statement: select id,data from demo
　LOG:  duration: 0.084 ms  statement: COMMIT

つまり、参照だけの処理であっても明示的なCOMMIT／ROLLBACKは必須です。

PostgreSQLは古いトランザクションが残っていると、読み取り一貫性を保つため、古いデータの領域を回収できません。これは、データファイルの肥大化につながる恐れがあります。肥大化を避けるためには、SELECTだけであっても一連の処理を終えたタイミングでトランザクションを終了させるようにしましょう。

5. 自動COMMIT

PostgreSQLにバンドルされているpsqlやPostgreSQL JDBCドライバのデフォルト動作は1つのSQL毎にトランザクションが終了するAutoCommit（自動COMMIT）です。

pyscopg2でも、connection.autocommit属性をTrueにすることで同じようにAutoCommitを使用することができます。

　
　>>> print(connection.autocommit) # 初期値はFalseです
　False
　>>> connection.autocommit = True
　>>> print(connection.autocommit) # Trueにかわりました
　True

これでAutoCommitが有効になりました。実際にSQLを発行してみましょう。

　
　>>> cur.execute("select id,data from demo")
　>>> cur.fetchone()
　(1, 'HELLO WORLD')

先程と同じようにPostgreSQL側でSQLを確認します。

　
　LOG:  duration: 0.821 ms  statement: select id,data from demo

先程と違い、"BEGIN"が発行されていません。PostgreSQLはBEGINを発行しない場合はトランザクションはオープンせず、SQL単位で暗黙的にCOMMITされます。上述の動作より、トランザクションはAutoCommitの動きになっていることが確認できました。

おわりに

基本的な部分ではありますが、PythonからPostgreSQLに接続する方法をご紹介しました。セットアップこそ多少複雑ですが使用するのは非常に簡単であることが伝わったかと思います。

本記事が、PythonからPostgreSQLへ接続を検討される方の一助になれば幸いです。

　　EDB、PostgreSQL に関するお問い合わせはこちら　

■本記事の内容について
　本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

新ツール Postgres Workload Report によるパフォーマンス診断～データベース管理の未来を共に創る！～

EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。