PythonでPosgreSQLを操作する

この記事のまとめ：

PythonでPostgreSQLのデータベースにSQL文を流す。
サンプルコード紹介。

背景：

Webスクレイピングで取得した結果のデータなどなどさまざまのデータ管理を行う上で、はじめはCSVファイルなんかで管理しようとしていたのですが、CSVだと限界を感じ始めたことをきっかけにPostgreSQLをデータベースとして使い始めたのですが、それを行うと必然的にプログラム上でPostgreSQLを操作できる必要が出てきますので調べた次第です。

PythonでPostgreSQLのデータベースにSQL文を流す

PostgreSQL用ライブラリをインストールする

PostgreSQL用のライブラリはいくつかあるようです（PostgreSQLの公式WiKiにまとめられているます）が開発が止まっているものもあるようです。今回は最もポピュラーで開発も継続されている"Psycopg2"パッケージを使いたいと思います。

pipでインストールする場合には下記でインストールできます。

$ pip install Psycopg2

Condaでインストールする場合には下記でインストールできます。

$ conda install -c anaconda Psycopg2

Anaconda上での"Psycopg2"パッケージ情報は下記から参照できます。

https://anaconda.org/anaconda/psycopg2

PythonからPostgreSQLデータベースに接続する

psycopg2を使えば、ホスト名、データベース名、ユーザー名、パスワード、ポート番号の情報を与えてやれば、簡単にPostgreSQLデータベースに接続してくれます。接続情報の与え方は、2種類あり、接続情報を一つ一つ引数として与える、他、URIとして文字列で与えることもできます。

import psycopg2
 
conn = psycopg2.connect(host='{ホスト名(IPアドレス)}', dbname='{データベース名}', user='{ユーザー名}', password='{パスワード}', port='{ポート番号}')
# もしくは
conn = psycopg2.connect(dsn='postgresql://{ユーザー名}:{パスワード}@{ホスト名(IPアドレス)}:{ポート番号}/{データベース名}')

SQL文を実行する

上述にて、データベースに接続できれば、SQL文のクエリや、その結果を一時的に蓄えておくためのcursorオブジェクトを作ります。cursorオブジェクトは、connect()関数によって生成したconnectionオブジェクト(上記でいうとconnオブジェクト)を使って生成します。

cur = conn.cursor()

あとはcursorオブジェクトにSQL文を渡し、実行します。例として、SELECT文のクエリを渡してみます。

cur.execute('SELECT * FROM {テーブル名}')
cur.commit()
results = cur.fetchall()
for r in results:
  print(r[0])

上記2行目は、connectionオブジェクトのデフォルト設定では、SQLクエリのトランザクションは手動で呼び出す必要があり、その呼び出しとしてcommit()関数を呼び出しています。後ほど、自動でトランザクションを実行する方法を紹介します。 3行目で、SQL文のクエリの実行結果をすべて取り出すことができます。ただし、cursorオブジェクトの戻り値は2次元配列となっており、ほしい列情報が何列目にあるのかわかりにくいと思われることがあります。これを解決するために、各列情報を列名で指定するために辞書型配列で戻り値が返ってくるcursorオブジェクトのつくり方も後ほど紹介します。

トランザクションの自動コミット

先ほどexecute()関数でSQLクエリのトランザクションをcommit()関数を呼び出すことで実行しましたが、execute()関数を呼び出すだけでSQLクエリのトランザクションを実行できるようにします。

connectionオブジェクトのautocommitをTrueにします。それだけです。これ以後は、execute()関数でSQL文を流せばすぐに実行されます。

conn.autocommit = True

辞書型配列のSQL実行結果の取得

上記でSQLを実行した結果が2次元配列になっており、扱いにくいと伝えましたが、辞書型配列で結果を返すようにします。

cursorオブジェクトを生成するときにcursor_factory=psycopg2.extras.DictCursorを引数として与えます。なお、psycopg2.extrasをインポートしている必要があります。

import psycopg2.extras
dictcur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

これによって得られたSQLの実行結果（下記で言うとresults変数）は、辞書型配列結果になっており、辞書の値としてデータベースの列名で指定することができます。

dictcur.execute('SELECT * FROM {テーブル名}')
results = dictcur.fetchall()
for r in results:
  print(r['column'])

サンプルコード

めんどくさい処理をひとまとめにしておきました。

import psycopg2
import psycopg2.extras
 
class postgres:
 
    def connection(self, host, user, password, dbname, port=5432):
        self.conn = psycopg2.connect(host=host, dbname=dbname, user=user, password=password, port=port)
        self.conn.autocommit = True # Trun on auto commit
        self.dictcur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Use dictionary type variable
 
    def execute(self, sql):
        self.dictcur.execute(sql)
 
    def fetch(self, sql):
        self.dictcur.execute(sql)
        return self.dictcur.fetchall()

使い方

使い方はpostgresオブジェクトを作って、connectionを張るだけですぐ使えるようにしました。

pg = postgres()
pg.connection(host=host, port=port, user=user, password=password, dbname=dbname)
results = pg.fetch('SELECT * FROM {テーブル名}')

参考

詳細は下記の記事が非常にわかりやすいですのでご参照ください。

PythonからPostgreSQLに接続する方法

今回は以上です。最後まで読んでいただき、ありがとうございます。

　← 気に入っていただければ応援ポチをお願いします！

Search This Blog