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

Python PostgreSQL

■ はじめに

PyGreSQL を使って、PostgreSQL にアクセスする。

別の方法については、以下の関連記事を参照のこと。

Python で PostgreSQL を使う ～ psycopg2編 ～
https://dk521123.hatenablog.com/entry/2020/05/06/141029

目次

【１】環境設定
　０）設定環境例
　１）設定手順
【２】サンプル
　データベース構築
　例１：SELECT文
　例２ : INSERT文
　例３：DB-API 2.0 & Transaction
　例４：外部ファイルを実行する

【１】環境設定

０）設定環境例

* OS : Windows10
* DB : postgresql-12.1-1
* PyGreSQL : PyGreSQL-5.1

１）設定手順

[1] インストール 

pip install PyGreSQL

[2] 環境変数 PATH の設定値追加 

# デフォルトインストールなら
PATH = C:\Program Files\PostgreSQL\12\bin

【２】サンプル

データベース構築

CREATE DATABASE sample_db
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Japanese_Japan.932'
    LC_CTYPE = 'Japanese_Japan.932'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

DROP TABLE IF EXISTS users;
CREATE TABLE users(
   user_id integer PRIMARY KEY,
   user_name VARCHAR (50) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO public.users(
 user_id, user_name)
 VALUES
(1, 'Mike'),
(2, 'Tom'),
(3, 'Sam');

例１：SELECT文

from pg import DB

if __name__ == '__main__':
  db = DB(dbname='sample_db', host='localhost',
    port=5432, user='postgres', passwd='password')

  result = db.query('SELECT * FROM users')
  print(result)

  result1 = db.query('SELECT * FROM users WHERE user_id = $1', 1)
  print(result1)

  db.close()

出力結果 

user_id|user_name|       created_at
-------+---------+-------------------------
      1|Mike     |2019-12-19 21:28:08.43232
      2|Tom      |2019-12-19 21:28:08.43232
      3|Sam      |2019-12-19 21:28:08.43232
(3 rows)
user_id|user_name|       created_at
-------+---------+-------------------------
      1|Mike     |2019-12-19 21:28:08.43232
(1 row)

例２ : INSERT文

https://www.postgresql.org/docs/7.1/pygresql.html

import pg
from datetime import datetime
from time import time

if __name__ == '__main__':
  con = pg.connect(dbname='sample_db', host='localhost', port=5432,
    user='postgres', passwd='password')
  result = con.query("SELECT * FROM users")
  print(result)
  print('**************')

  params = ('Tom', )
  result = con.query("SELECT * FROM users WHERE user_name=$1", params)
  print(result)
  print('**************')

  params = (4, 'Kevin', )
  con.query(
    'INSERT INTO users(user_id, user_name) VALUES($1, $2)',
    params)

# 以下を実行すると、
# 「OSError: ERROR:  列"created_at"のデータがありません」
# ってエラーになる
#
#  params = ([5, 'Smith'], [6, 'Ken'])
#  con.inserttable('users', params)

  current_timestamp = str(datetime.fromtimestamp(time()))
  params = (
    [5, 'Smith', current_timestamp],
    [6, 'Ken', current_timestamp])
  con.inserttable('users', params)

  result = con.query("SELECT * FROM users")
  print(result)
  print('**************')

出力結果 

user_id|user_name|       created_at       
-------+---------+------------------------
      1|Mike     |2019-12-19 21:50:21.3885
      2|Tom      |2019-12-19 21:50:21.3885
      3|Sam      |2019-12-19 21:50:21.3885
(3 rows)
**************
user_id|user_name|       created_at
-------+---------+------------------------
      2|Tom      |2019-12-19 21:50:21.3885
(1 row)
**************
user_id|user_name|        created_at
-------+---------+--------------------------
      1|Mike     |2019-12-19 21:50:21.3885
      2|Tom      |2019-12-19 21:50:21.3885
      3|Sam      |2019-12-19 21:50:21.3885
      4|Kevin    |2019-12-19 21:50:27.483248
      5|Smith    |2019-12-19 21:50:27.484514
      6|Ken      |2019-12-19 21:50:27.484514
(6 rows)

例３：DB-API 2.0 & Transaction

from pgdb import connect

if __name__ == '__main__':
  connection = connect(
    database='sample_db', host='localhost:5432',
    user='postgres', password='password')

  try:
    cursor = connection.cursor()

    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for i, row in enumerate(rows):
      print("Row = {} value = {}".format(i, row))
    print('*************')

    print('Insert1')
    params = (8, 'Paul', )
    cursor.execute(
      'INSERT INTO users(user_id, user_name) VALUES(%s, %s)',
      params)
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for i, row in enumerate(rows):
      print("Row = {} value = {}".format(i, row))
    print('*************')

    print('Insert2')
    # 以下だと、キー重複で例外となりロールバックされる
    params = (8, 'Linda', )
    # params = (9, 'Linda', )
    cursor.execute(
      'INSERT INTO users(user_id, user_name) VALUES(%s, %s)',
      params)

    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for i, row in enumerate(rows):
      print("Row = {} value = {}".format(i, row))
    print('*************')
    connection.commit()
  except Exception as ex:
    connection.rollback()
    print(ex)

  print('==================')
  cursor.execute('SELECT * FROM users')
  rows = cursor.fetchall()
  for i, row in enumerate(rows):
    print("Row = {} value = {}".format(i, row))

  connection.close()

出力結果 

Row = 0 value = Row(user_id=1, user_name='Mike', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 1 value = Row(user_id=2, user_name='Tom', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 2 value = Row(user_id=3, user_name='Sam', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 3 value = Row(user_id=4, user_name='Kevin', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 483248))
Row = 4 value = Row(user_id=5, user_name='Smith', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))
Row = 5 value = Row(user_id=6, user_name='Ken', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))
*************
Insert1
Row = 0 value = Row(user_id=1, user_name='Mike', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 1 value = Row(user_id=2, user_name='Tom', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 2 value = Row(user_id=3, user_name='Sam', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 3 value = Row(user_id=4, user_name='Kevin', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 483248))
Row = 4 value = Row(user_id=5, user_name='Smith', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))
Row = 5 value = Row(user_id=6, user_name='Ken', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))
Row = 6 value = Row(user_id=8, user_name='Paul', created_at=datetime.datetime(2019, 12, 19, 22, 6, 34, 779184))
*************
Insert2
ERROR:  重複したキー値は一意性制約"users_pkey"違反となります
DETAIL:  キー (user_id)=(8) はすでに存在します。

==================
Row = 0 value = Row(user_id=1, user_name='Mike', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 1 value = Row(user_id=2, user_name='Tom', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 2 value = Row(user_id=3, user_name='Sam', created_at=datetime.datetime(2019, 12, 19, 21, 50, 21, 388500))
Row = 3 value = Row(user_id=4, user_name='Kevin', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 483248))
Row = 4 value = Row(user_id=5, user_name='Smith', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))
Row = 5 value = Row(user_id=6, user_name='Ken', created_at=datetime.datetime(2019, 12, 19, 21, 50, 27, 484514))

例４：外部ファイルを実行する

import pg

db_connection =
  "dbname='sample_db' host='localhost' port=5432 user='postgres' password='password'"
connection = pg.connect(db_connection)

with open('sample.sql') as file:
  sql_file = file.read()
  sql_queries = sql_file.split(';')
  for sql in sql_queries:
    sql = sql.strip()
    if not sql:
      continue
    print("sql = {}".format(sql))
    result = connection.query(sql)
    print(result)

connection.close()

sample.sql 

INSERT INTO users(
user_id, user_name) VALUES
(10, 'Sam'),
(11, 'Kevin'),
(12, 'Naomi');

SELECT * FROM users;

DELETE FROM users WHERE user_id=11;

SELECT * FROM users;

出力結果 

sql = INSERT INTO users(  
user_id, user_name) VALUES
(10, 'Sam'),
(11, 'Kevin'),
(12, 'Naomi')
3
sql = SELECT * FROM users
user_id|user_name|        created_at        
-------+---------+--------------------------
     10|Sam      |2020-08-26 22:39:55.175736
     11|Kevin    |2020-08-26 22:39:55.175736
     12|Naomi    |2020-08-26 22:39:55.175736
(3 rows)
sql = DELETE FROM users WHERE user_id=11
1
sql = SELECT * FROM users
user_id|user_name|        created_at
-------+---------+--------------------------
     10|Sam      |2020-08-26 22:39:55.175736
     12|Naomi    |2020-08-26 22:39:55.175736
(2 rows)

参考文献

https://qiita.com/baikichiz/items/e69430c6441e7fe7439c

関連記事

Python で PostgreSQL を使う ～ psycopg2編 ～
https://dk521123.hatenablog.com/entry/2020/05/06/141029
Python で MySQLを使う
https://dk521123.hatenablog.com/entry/37744753
Python ～ 基本編 / 日付・日時 ～
https://dk521123.hatenablog.com/entry/2019/10/14/121909
Webフレームワーク 「Flask」 ～ MySQLを使う ～
https://dk521123.hatenablog.com/entry/37745178
ファイルからSQL文を一括で実行するには ～pdqlコマンドの利用～
https://dk521123.hatenablog.com/entry/2014/09/11/002003
Glue から Redshift/PostgreSQL に接続する ～ Python shell編 ～
https://dk521123.hatenablog.com/entry/2020/08/26/193237
Glue から Redshift/PostgreSQL に接続する ～ PySpark編 ～
https://dk521123.hatenablog.com/entry/2020/09/23/111741