Database Support Blog

  • 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のインストーラによる方法をご紹介していました。しかし現在(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へ接続を検討される方の一助になれば幸いです。

 



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

■商標に関して
 OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
 文中の社名、商品名等は各社の商標または登録商標である場合があります。


Oracle Databaseライセンスのご相談ならアシスト

関連している記事

  • PostgreSQL
  • EDB
2022.05.25

アシストがPostgreSQLグローバル開発グループのSponsor一覧に掲載されたので、中の人に話を聞いてみた!(前編)

アシストは2009年からPostgreSQLのプロダクトサポートを開始、2010年に日本PostgreSQLユーザー会への協賛会員として参画、2012年にPostgreSQL エンタープライズ・コンソーシアムに参画しています。現在JPUGで活躍する田中・喜田の両名がどのような活動をしているのか2回にわたってお届けします。

  • Oracle Database
  • Oracle Cloud
  • PostgreSQL
  • AWS
  • Exadata
2022.05.24

入社2年目がレポート!Cloud Database Days 2022技術セッションの見どころ

2022年5月26日~6月3日に開催される「Cloud Database Days 2022」。その中でも10個に分かれた技術セッションについて、ポイントを押さえてそれぞれご紹介いたします!

  • EDB
  • PostgreSQL
  • AWS
2022.04.13

クラウドへのデータベース移行を検討中の方必見!「EDB」とは?

2022年4月22日(金)に開催する「アシスト流 EDB活用術 on AWS」の内容をチラ見せ。「EDBって何?」という方から「EDBは知ってるけどクラウド上でもEDB??」という方まで、ぜひお申し込みください。

ページの先頭へ戻る