Database Support Blog

Database Support Blog>PythonからPostgreSQLに接続する方法

  • PostgreSQL
2016.03.08

PythonからPostgreSQLに接続する方法

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の公式サイト でも紹介されていますが、以下のURLでWindows用に移植されたpsycopg2のインストーラが公開されています。

http://www.stickpeople.com/projects/python/win-psycopg/

このサイトより環境に合わせたインストーラをダウンロードします。2016年2月時点では2.6.1が最新です。32bit/64bit環境ではそれぞれ以下のインストーラが該当します。

* psycopg2-2.6.1.win32-py3.4-pg9.4.4-release.exe
* psycopg2-2.6.1.win-amd64-py3.4-pg9.4.4-release.exe

ダウンロード後にクリックし、インストーラの説明に従ってインストールを行ってください。インストール完了後に以下のコマンドがエラーなく実行できれば正常にインストールができています。

C:\work>python -V
Python 3.4.4
C:\work>python -c "import psycopg2"
C:\work>

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へ接続を検討される方の一助になれば幸いです。

筆者情報

山田 聡 画像

2011年にアシストに入社してからサポートセンターでOracle DatabaseやPostgreSQL、EDB Postgres Advanced Server(EPAS)といったデータベース製品を中心に担当しています。個人的にJavaやNode.js、Python等を触っていますので、PostgreSQLと絡めてアプリケーション周りの情報をお送りしていきます。

関連している記事

  • PostgreSQL
2016.06.03

【PostgreSQL 9.5】max_wal_sizeとmin_wal_sizeの概要

PostgreSQL 9.5で登場したmax_wal_sizeとmin_wal_sizeの概要と、checkpoint_segmentsがこれら2つのパラメータに置き替えられたことによるメリットご紹介します。

  • PostgreSQL
2015.12.21

PostgreSQLの監視で押さえておきたい3つの監視項目

PostgreSQLの監視において、最低限押さえておきたい3つの監視項目を紹介します。これらを監視することでデータベースの障害や障害に繋がりうる問題により早く対処できます。

  • PostgreSQL
2015.11.17

障害発生に備えて設定すべき3つのログ関連パラメーター

PostgreSQLにおいて、障害発生に備えて設定すべき3つのログ関連パラメーターを紹介します。これらのパラメーターを設定しておくことで、障害発生時にその状況を詳細に把握することができます。

カテゴリ一覧

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る