【Python】Python で PostgreSQL を使う ~ psycopg2編 ~

■ はじめに

https://dk521123.hatenablog.com/entry/2019/11/27/000316

の続き。

以下の関連記事で ローカル環境に Postgre の SSL設定をしたのだが
SSL接続する際に、psycopg2を使ったのでメモ。

https://dk521123.hatenablog.com/entry/2020/05/05/221239

■ インストール

pip install psycopg2

conda install psycopg2

https://pypi.org/project/psycopg2/

Linux環境

CentOS7 etc..

sudo yum update -y
sudo yum install -y python3-pip python3-devel postgresql-devel
sudo pip3 install --user psycopg2

■ サンプル

例1:No SSL + INSERT文

まずは、SSLなしでデータを入れる

SQL (CREATE TABLE)

-- customers
DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
  id INTEGER PRIMARY KEY,
  customer_no VARCHAR (50),
  customer_name VARCHAR (50) NOT NULL,
  sex CHAR(1),
  birth_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Pythonコード

import psycopg2
from psycopg2.extras import DictCursor

if __name__ == '__main__':
  with psycopg2.connect(
    dbname='your_db', user='postgres', password='password',
    host='localhost', port=5432) as connection:
    # 「cursor_factory=DictCursor」は実行結果を辞書として得る
    with connection.cursor(cursor_factory=DictCursor) as cursor:
      # ! Note !
      # 全て文字列として指定できる(例えば、integer型のidも文字列として挿入できる)
      input_values = (
        # id,customer_no,customer_name,sex,birth_date,created_at
        ('1', 'X0001', 'Mike', 'm', '2000-09-18', '2020-05-08 11:22:22.222'),
        ('2', 'X0002', 'Tom', 'm', '1988-11-08', '2020-05-08 11:22:22.222'),
        ('3', 'X0003', 'Naomi', 'f', '1922-07-10', '2020-05-08 11:22:22.222'),
        ('4', 'X0004', 'Mike', 'f', '2010-12-31', '2020-05-08 11:22:22.222'),
      )
      
      print("Start")
      for input_value in input_values:
        id = input_value[0]
        cursor.execute("DELETE FROM customers WHERE id = %s", (id, ))
        cursor.execute(
          "INSERT INTO customers " +
          "(id,customer_no,customer_name,sex,birth_date,created_at) " +
          "VALUES(%s,%s,%s,%s,%s,%s)",
          input_value)
      connection.commit()
      cursor.execute(
        'SELECT * FROM customers WHERE customer_name = %s',
        ('Mike', ))
      rows = cursor.fetchall()
      for row in rows:
        print(row['id'])
        print(row['customer_no'])
        print(row['customer_name'])
        print(row['sex'])
        print(row['birth_date'])
        print(row['created_at'])
        print("*" * 10)

  print("Done")

出力結果

Start
1
X0001
Mike
m
2000-09-18
2020-05-08 11:22:22.222000
**********
4
X0004
Mike
f
2010-12-31
2020-05-08 11:22:22.222000
**********
Done

例2:SSL接続

前提条件

Windowsの場合、
以下の関連記事の「【2】 SSL設定手順」を行っていること

https://dk521123.hatenablog.com/entry/2020/05/05/221239

そして、「server.crt」をコピーして
C:\Users\UserName\AppData\Roaming\postgresql
に「root.crt」としておいておくこと

Pythonコード

import psycopg2
import psycopg2.extras

if __name__ == '__main__':
  with psycopg2.connect(
    dbname='your_db', user='postgres', password='password',
    host='localhost', port=5432,
    # 「sslmode='verify-full'」については以下参照。
    #  https://gist.github.com/pfigue/3440e2bc986550a6b8ec
    sslmode='verify-full') as connection:
    connection.set_client_encoding('utf-8')
    with connection.cursor(cursor_factory=DictCursor) as cursor:
      cursor.execute('SELECT * FROM customers')
      for row in cursor.fetchall():
        print(row['id'])
        print(row['customer_no'])
        print(row['customer_name'])
        print(row['sex'])
        print(row['birth_date'])
        print(row['created_at'])
        print("*" * 10)

出力結果

1
X0001
Mike
m
2000-09-18
2020-05-08 11:22:22.222000
**********
2
X0002
Tom
m
1988-11-08
2020-05-08 11:22:22.222000
**********
3
X0003
Naomi
f
1922-07-10
2020-05-08 11:22:22.222000
**********
4
X0004
Mike
f
2010-12-31
2020-05-08 11:22:22.222000
**********

参考文献

https://stackoverflow.com/questions/28228241/how-to-connect-to-a-remote-postgresql-database-through-ssl-with-python
https://area-b.com/blog/2015/03/10/1938/
https://qiita.com/t20190127/items/0fb944c8679795257e01
http://minamitama-minamitama.blogspot.com/2011/04/pythonpostgresqlinsert.html
困ったら、以下のサイトをみているといいかも...
https://qiita.com/hoto17296/items/0ca1569d6fa54c7c4732

関連記事

CSVデータをPostgreSQLにインポートする ~ copy_from() 編 ~
https://dk521123.hatenablog.com/entry/2020/05/08/175525
CSVデータをPostgreSQLにインポートする ~ execute_values() 編 ~
https://dk521123.hatenablog.com/entry/2020/05/09/113559
PythonPostgreSQL を使う ~ PyGreSQL編 ~
https://dk521123.hatenablog.com/entry/2019/11/27/000316
PostgreSQLSSL通信に関する あれこれ
https://dk521123.hatenablog.com/entry/2020/05/05/221239