頑張らないために頑張る

ゆるく頑張ります

pyodbcでDockerコンテナのPostgreSQLに接続する

Posted at — Apr 11, 2020

はじめに

Pythonを使ってDB操作をする場合、pyodbcを利用すると思います。そこで、Dockerコンテナで立ち上がっているPostgreSQLに対して、pyodbcで接続する手順をメモします。なお、確認用としてPostgreSQLにはテスト用のデータを少しだけ格納しておきます。

ちなみに、今回のソースはこちらにあります。

環境

ざっくりした手順

  1. psqlとpyodbcをインストールする。
  2. DockerでPostgreSQLのコンテナを起動する。
  3. Pythonから接続してみる。

詳しい手順

各種インストール

psqlをインストールする

まずは、何はなくともpsqlが必要です。インストールします。

psqlとはPostgreSQLのターミナル型フロントエンドです。 対話的に問い合わせを入力し、それをPostgreSQLに対して発行して、結果を確認することができます。

macOSなのでHomebrewを使うのが1番早いです。

brew update
brew install postgresql

次にODBCの設定ファイルを変更します。もともと（多分）何も記述されていないファイル「odbcinst.ini」に、PostgreSQL用の部分を追記します。

$ cat  /usr/local/etc/odbcinst.ini

[PostgreSQL]  
Driver=/usr/local/lib/psqlodbcw.so

追記の仕方は、下記のようにヒアドキュメントを使うのが多分早いです。

cat <<EOF >> /usr/local/etc/odbcinst.ini  
heredoc else> [PostgreSQL]  
heredoc else> Driver=/usr/local/lib/psqlodbcw.so  
heredoc else> EOF

とりあえずバージョンでも見ておきます。

$ psql --version
psql (PostgreSQL) 12.2

これでpsqlの準備が整いました。

pyodbcのインストール

pyodbcは毎度おなじみpipでインストールします。

pip install pyodbc

基本的には、放っておけばいい感じにインストールしてくれるはずです。

PostgreSQLのDockerコンテナを準備する

DockerでPostgreSQLのコンテナを立ち上げます。まず準備するのは下記のファイルたちです。

それぞれのファイルは、下記のようなフォルダ体系で保存しています。公式DockerHubのPostgreSQLを使う場合、コンテナを立ち上げる際/docker-entrypoint-initdb.dの下にSQLファイルなどのスクリプトが存在しているなら、それらを実行します。なので、下記のフォルダをvolumesで指定しています。なお、実行の順番は、ファイル名でソートした順番です。そのため、ファイルの頭に数字をつけて明示的に実行順序を指定しています。

├── docker-compose.yml
└── postgres
    └── initdb
        ├── 1_create_tables.sql
        └── 2_insert_seed.sql

まずはdocker-compose.ymlから。

version: '3'

services:
    postgres:
        image: postgres:alpine
        restart: always
        environment:
            TZ: "Asia/Tokyo"
            POSTGRES_USER: test
            POSTGRES_PASSWORD: test
            POSTGRES_DB: test_db
        ports:
            - 5432:5432
        volumes:
            - postgres:/var/lib/postgresql/data
            - ./postgres/initdb:/docker-entrypoint-initdb.d

    pgadmin:
        image: dpage/pgadmin4:latest
        restart: always
        ports:
            - 8080:80
        environment:
            PGADMIN_DEFAULT_EMAIL: admin@example.com
            PGADMIN_DEFAULT_PASSWORD: admin
        volumes:
            - pgadmin:/var/lib/pgadmin
        depends_on:
            - postgres

volumes:
    postgres:
    pgadmin:

次にSQLのソース。まずはテーブルをcreateするSQL文。

create table users
(
    id serial primary key,
    username varchar(50) unique not null,
    password varchar(50) not null,
    email varchar(255) unique not null
);

次にデータをinsertするSQL文

insert into users (username, password, email) values ('keid', 'keidpass', 'keid@developer.com');
insert into users (username, password, email) values ('jobs', 'jobspass', 'jobs@developer.com');
insert into users (username, password, email) values ('mask', 'maskpass', 'mask@developer.com');

ぶっちゃけ、この2つのSQLはテスト用のデータでしかないので、別にデータを準備できるのであれば必要ありません。

ちなみに/docker-entrypoint-initdb.d配下のスクリプトは、既存のDBに対して実行することはできません。

これらのファイルが準備できたら、あとはdocker-composeするだけです。

docker-compose build
docker-compose up -d

docker-compose psで確認してみます。

docker-compose ps
        Name                   Command           State            Ports
--------------------------------------------------------------------------------
sample_db_pgadmin_1    /entrypoint.sh            Up      443/tcp,
                                                         0.0.0.0:8080->80/tcp
sample_db_postgres_1   docker-entrypoint.sh      Up      0.0.0.0:5432->5432/tcp
                       postgres

確認できたら、ブラウザでlocalhost:8080にアクセスしてみます。

pic

さっき、docker-compose.ymlに指定したメールアドレスとパスワードを利用してログインします。

pic

pgAdminのページにアクセスできたらOKです。

念の為、psqlでもアクセスしてみます。

$ psql -h 0.0.0.0 -p 5432 -d test_db -U test
Password for user test:
psql (12.2)
Type "help" for help.

test_db=# select * from users;
 id | username | password |       email
----+----------+----------+--------------------
  1 | keid     | keidpass | keid@developer.com
  2 | jobs     | jobspass | jobs@developer.com
  3 | mask     | maskpass | mask@developer.com
(3 rows)

test_db=# \q

接続自体も問題なさげです。

Pythonから接続してみる

やっとこさ、pyodbcを使ってPythonから接続してみます。ソースはざっくり下記のように記述しました。

import pyodbc


def login(server, db, uid, pw):
    s = 'DRIVER={PostgreSQL};SERVER=' + server\
        + ';DATABASE=' + db\
        + ';UID=' + uid\
        + ';PWD=' + pw

    return pyodbc.connect(s)


def sql_execute(cnn, sql):
    cursor = cnn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()

    return rows


cnn = login('localhost', 'test_db', 'test', 'test')

sql = '''select * from users'''

res = sql_execute(cnn, sql)

if res != []:
    for l in res:
        print(l)

pyodbcを利用して、select文を実行するだけの簡単なソースです。これの実行するとこんな結果が返ってくるはず。

(1, 'keid', 'keidpass', 'keid@developer.com')
(2, 'jobs', 'jobspass', 'jobs@developer.com')
(3, 'mask', 'maskpass', 'mask@developer.com')

これでPythonからのアクセスも確認できました。

まとめ

pyodbcを使った、DockerコンテナのPostgreSQLへ向けた接続をやってみました。ちなみに、MySQLなどPostgreSQL以外のDBに対しても、基本的に手順は一緒です。その際のPython側は、接続時のDRIVER設定を変えればいいだけなので、接続部分のソースの使い回しがそこそこできるかな？

簡単にDBのテスト環境を用意したいというケースに限りませんが、Dockerのようなコンテナは本当に便利ですね。

参考

開発用データベースのためのDocker Composeの設定方法

Docker で作る postgres 環境

postgresをDockerで動かすベストプラクティス

comments powered by Disqus