■ はじめに

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

２）Windows環境

pip install psycopg2

【２】サンプル

例１：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

例２：SSL接続

前提条件

Windowsの場合、
以下の関連記事の「【２】 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
**********

例３：SELECTした結果をJSON化

import psycopg2
from psycopg2.extras import DictCursor
import json
from datetime import date, datetime

# エラー「TypeError: Object of type 'datetime' is not JSON serializable」対策
# 参考：https://www.yoheim.net/blog.php?q=20170703
def serialize_json_value(target_value):
  if isinstance(target_value, (datetime, date)):
    return target_value.isoformat()
  raise TypeError ("Type %s not serializable" % type(target_value))

target_table_name = "users"

with psycopg2.connect(
  dbname='sample_db', user='postgres', password='password',
  host='localhost', port=5432) as connection:
    with connection.cursor(cursor_factory=DictCursor) as cursor:
      cursor.execute(f'SELECT * FROM {target_table_name}')
      rows = cursor.fetchall()
      results = []
      for row in rows:
        results.append(dict(row))

      with open(f'output_{target_table_name}.json', 'w') as json_file:
        json.dump(results, json_file, default=serialize_json_value, indent=2)

print(f"Done - {target_table_name}")

出力結果：output_users.json

[
  {
    "user_id": 1,
    "user_name": "Mike",
    "created_at": "2019-12-19T21:50:21.388500"
  },
  {
    "user_id": 2,
    "user_name": "Tom",
    "created_at": "2019-12-19T21:50:21.388500"
  },
  {
    "user_id": 3,
    "user_name": "Sam",
    "created_at": "2019-12-19T21:50:21.388500"
  },
  {
    "user_id": 4,
    "user_name": "Kevin",
    "created_at": "2019-12-19T21:50:27.483248"
  },
  {
    "user_id": 5,
    "user_name": "Smith",
    "created_at": "2019-12-19T21:50:27.484514"
  },
  {
    "user_id": 6,
    "user_name": "Ken",
    "created_at": "2019-12-19T21:50:27.484514"
  },
  {
    "user_id": 10,
    "user_name": "Sam",
    "created_at": "2020-08-26T22:39:55.175736"
  },
  {
    "user_id": 12,
    "user_name": "Naomi",
    "created_at": "2020-08-26T22:39:55.175736"
  },
  {
    "user_id": 20,
    "user_name": "Tim",
    "created_at": "2021-08-02T16:39:55.175736"
  },
  {
    "user_id": 30,
    "user_name": "kevin",
    "created_at": "2021-08-02T16:40:55.175736"
  }
]

参考文献

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
Python で PostgreSQL を使う～ PyGreSQL編～
https://dk521123.hatenablog.com/entry/2019/11/27/000316
PostgreSQL の SSL通信に関するあれこれ
https://dk521123.hatenablog.com/entry/2020/05/05/221239
Python ～基本編 / JSONあれこれ～
https://dk521123.hatenablog.com/entry/2022/02/14/000000

プログラムの超個人的なメモ

Memo for Programming.

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

■ はじめに

目次

【１】インストール

１）Linux環境

２）Windows環境

【２】サンプル

例１：No SSL + INSERT文

例２：SSL接続

例３：SELECTした結果をJSON化

参考文献

関連記事