PostgreSQLの操作(DB接続/Table作成/Insert/Select/Drop/Transaction)

Contents [hide]

出力結果

プログラムの流れは

PostgreSQLに接続
トランザクション処理を開始
exampleテーブルがあったら削除
example_sex Typeがあったら削除
example_sex Typeを作成
exampleテーブルを作成
データをInsert
InsertしたデータをSelect
exampleテーブルを削除
example_sex Typeを削除
トランザクション処理を終了(commit)
PostgreSQLへの接続を閉じる

となっており、プロンプトには正規表現で表すと以下のパターンに合致する結果が出力されます。

r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$"

出力結果例

1   name1   male    2019/10/29 18:04:45 2019/10/29 18:04:45
2   name2   female  2019/10/29 18:04:45 2019/10/29 18:04:45

前準備

以下のような.envファイルにDB接続情報が保存されていると想定します

DBHOST=0.0.0.0
DBUSER=exampleuser
DBPASSWORD=example20141024

データベース上で以下のSQLを発行し、exampleデータベースとそれに権限を持つexampleuserが用意されていると想定します

CREATE USER exampleuser PASSWORD 'example20141024';
CREATE DATABASE example;
GRANT ALL ON DATABASE example TO exampleuser;
CREATE TYPE sex_example AS ENUM ('male', 'female');

コード例

JavaScript

class PsqlManipulator {
    constructor(opt={}) {
    const { Pool, Client } = require('pg');
    require('dotenv').config();
    this.rdbh = new Client({
      host: process.env.DBHOST,
      user: process.env.DBUSER,
      password: process.env.DBPASSWORD,
      database: 'example'
    })
  }
 
  async beginTransaction() {
    const sql = "BEGIN"
    await this.executeSQL(sql)
  }
 
  async createExampleData() {
    const datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    for (const data of datas) {
      const query = {
        text: 'INSERT INTO example (name, sex) VALUES ($1, $2)',
        values: [data[0], data[1]]
      }
      await this.executeSQL(query);
    }
  }
 
  async createExampleTable() {
    const sql = `
    CREATE TABLE example (
      id SERIAL NOT NULL,
      name VARCHAR(255) NOT NULL,
      sex example_sex DEFAULT NULL,
      created_at timestamp with time zone DEFAULT NOW(),
      updated_at timestamp with time zone DEFAULT NOW(),
      PRIMARY KEY (id)
    );
    CREATE INDEX created_at_idx ON example(created_at);
    CREATE INDEX name_idx ON example(name);
    CREATE INDEX updated_at_idx ON example(updated_at);
    `;
    await this.executeSQL(sql);
  }
 
  async createExampleType() {
    const sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
    await this.executeSQL(sql);
  }
 
    async dbclose() {
    if (typeof(this.rdbh) !== "undefined") {
      await this.rdbh.end();
    }
  }
 
    async dbconnect() {
    await this.rdbh.connect();
    const timezone = "Asia/Tokyo";
    const sql = "SET TIME ZONE '"+ timezone +"'"
    await this.executeSQL(sql)
  }
 
  async dropExampleTable() {
    const sql = "DROP TABLE IF EXISTS example";
    await this.executeSQL(sql);
  }
 
  async dropExampleType() {
    const sql = "DROP TYPE IF EXISTS example_sex";
    await this.executeSQL(sql);
  }
 
  async endTransaction() {
    const sql = "END"
    await this.executeSQL(sql)
  }
 
  async executeSQL(sql) {
    try {
      const res = await this.rdbh.query(sql);
    }
    catch(err){
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message);
    }
  }
 
  async selectFromExampleTable() {
    const sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
    try {
      const res = await this.rdbh.query(sql)
      for(const row of res.rows){
        console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join("\t"))
      }
    }
    catch(err) {
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message)
      process.exit(-1)
    }
  }
};
module.exports = PsqlManipulator;
 
if(!module.parent) {
  (async () => {
    const rdb = new PsqlManipulator()
    await rdb.dbconnect()
    await rdb.beginTransaction()
    await rdb.dropExampleTable()
    await rdb.dropExampleType()
    await rdb.createExampleType()
    await rdb.createExampleTable()
    await rdb.createExampleData()
    await rdb.selectFromExampleTable()
    await rdb.dropExampleTable()
    await rdb.dropExampleType()
    await rdb.endTransaction()
    await rdb.dbclose()
  })()
}

Perl

package PsqlManipulator;
use strict;
use DBI;
use Dotenv;
 
sub new() {
  my $self = {};
  Dotenv->load();
  return bless $self;
}
 
sub beginTransaction(){
  my $self = shift;
  my $sql = "BEGIN";
  $self->executeSQL($sql);
}
 
sub createExampleData(){
  my $self = shift;
  my @datas = (
    ['name1', 'male'],
    ['name2', 'female']
  );
  my $sth = $self->{'rdbh'}->prepare("INSERT INTO example (name, sex) VALUES (?, ?)");
  foreach my $data_ref (@datas) {
    $sth->execute($data_ref->[0], $data_ref->[1]);
  }
  $sth->finish();
}
 
sub createExampleTable() {
  my $self = shift;
  my $sql = <<EOF;
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
EOF
  $self->executeSQL($sql);
}
 
sub createExampleType() {
  my $self = shift;
  my $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
  $self->executeSQL($sql);
}
 
sub dbclose(){
  my $self = shift;
  $self->{'rdbh'}->disconnect();
}
 
sub dbconnect(){
  my $self = shift;
  my $database = "example";
  my $timezone = "Asia/Tokyo";
  $self->{'rdbh'} = DBI->connect("DBI:Pg:database=".$database.";host=".$ENV{'DBHOST'}, $ENV{'DBUSER'}, $ENV{'DBPASSWORD'}, {
   AutoCommit => 0,
   PrintError => 1,
   PrintWarn => 0
   }) or die "Failed to connect to db.";
  my $sql = "SET TIME ZONE '".$timezone."'";
  $self->{'rdbh'}->do($sql);
}
 
sub dropExampleTable(){
  my $self = shift;
  my $sql = "DROP TABLE IF EXISTS example";
  $self->executeSQL($sql);
}
 
sub dropExampleType(){
  my $self = shift;
  my $sql = "DROP TYPE IF EXISTS example_sex";
  $self->executeSQL($sql);
}
 
sub endTransaction(){
  my $self = shift;
  $self->{'rdbh'}->commit();
}
 
sub executeSQL(){
  my $self = shift;
  my $sql = shift;
  $self->{'rdbh'}->do($sql) or die("Failed to execute ".$sql);
}
 
sub selectFromExampleTable(){
  my $self = shift;
  my $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
  my $sth = $self->{'rdbh'}->prepare($sql) or die("Failed to prepare ".$sql);
  $sth->execute() or die("Failed to execute ".$sql);
  while(my $row_ref = $sth->fetchrow_hashref) {
    print(join("\t", $row_ref->{'id'}, $row_ref->{'name'}, $row_ref->{'sex'}, $row_ref->{'created_at'}, $row_ref->{'updated_at'})."\n");
  }
  $sth->finish();
}
 
if ($0 eq __FILE__) {
  my $rdb = new PsqlManipulator();
  $rdb->dbconnect();
  $rdb->beginTransaction();
  $rdb->dropExampleTable();
  $rdb->dropExampleType();
  $rdb->createExampleType();
  $rdb->createExampleTable();
  $rdb->createExampleData();
  $rdb->selectFromExampleTable();
  $rdb->dropExampleTable();
  $rdb->dropExampleType();
  $rdb->endTransaction();
  $rdb->dbclose();
}
else{
  1;
}

PHP

<?php
require_once __DIR__ . '/vendor/autoload.php';
 
class PsqlManipulator {
  private $rdbh;
  public function __construct($opt=[]) {
    $dotenv = Dotenv\Dotenv::create(__DIR__);
    $dotenv->load();
  }
 
  function beginTransaction() {
    $this->rdbh->beginTransaction();
  }
 
  function createExampleData(){
    $datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    $sql = "INSERT INTO example (name, sex) VALUES (?, ?)";
    $sth = $this->rdbh->prepare($sql);
    foreach($datas as $data) {
      $sth->execute([$data[0], $data[1]]);
    }
  }
 
  function createExampleTable(){
    $sql =  <<< EOF
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
EOF;
    $this->executeSQL($sql);
  }
 
  function createExampleType(){
    $sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')";
    $this->executeSQL($sql);
  }
 
  function dbclose() {
    $this->rdbh = null;
  }
   
  function dbconnect(){
    $dbname = "example";
    $timezone = "Asia/Tokyo";
    try {
      $this->rdbh = new PDO('pgsql:dbname=example;host='.getenv('DBHOST'), getenv('DBUSER'), getenv('DBPASSWORD'));
      $this->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
      die("Failed to connect: ".$e->getMessage());
    }
    $sql = "SET TIME ZONE '".$timezone."'";
    $this->executeSQL($sql);
  }
 
  function dropExampleTable() {
    $sql = "DROP TABLE IF EXISTS example";
    $this->executeSQL($sql);
  }
 
  function dropExampleType(){
    $sql = "DROP TYPE IF EXISTS example_sex";
    $this->executeSQL($sql);
  }
 
  function endTransaction(){
    $this->rdbh->commit();
  }
 
  function executeSQL($sql) {
    $sqls = explode(';', $sql);
    foreach ($sqls as $sql) {
      if($sql) {
        $sth = $this->rdbh->prepare($sql);
        $sth->execute();
      }
    }
  }
 
  function rollback(){
    $this->rdbh->rollback();
  }
   
  function selectFromExampleTable(){
    $sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example";
    $sth = $this->rdbh->prepare($sql);
    $sth->execute();
    while($row = $sth->fetch(PDO::FETCH_ASSOC)){
      print(implode("\t", [$row['id'], $row['name'], $row['sex'], $row['created_at'], $row['updated_at']])."\n");
    }
  }
}
 
if ( !isset(debug_backtrace()[0]) ) {
  $pro = new PsqlManipulator();
  try{
    $pro->dbconnect();
  } catch(PDOException $e){
    echo $e->getMessage();
    exit(1);
  }
 
  try{
    $pro->beginTransaction();
  } catch(PDOException $e){
    echo $e->getMessage();
    $pro->dbclose();
    exit(1);
  }
 
  try {
    $pro->dropExampleTable();
    $pro->dropExampleType();
    $pro->createExampleType();
    $pro->createExampleTable();
    $pro->createExampleData();
    $pro->selectFromExampleTable();
    $pro->dropExampleTable();
    $pro->dropExampleType();
    $pro->endTransaction();
    $pro->dbclose();
  } catch(PDOException $e){
    $pro->rollback();
    $pro->dbclose();
    throw $e;
  }
}

Python

from firstclass_dotenv import Dotenv
import psycopg2
import os
 
class PsqlManipulator():
  def __init__(self, opt=[]):
    dotenv = Dotenv()
    dotenv.load()
 
  def beginTransaction(self):
    sql = "BEGIN"
    self.executeSQL(sql)
 
  def createExampleType(self):
    sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')"
    self.executeSQL(sql)
 
  def createExampleTable(self):
    sql = '''\
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
'''
    self.executeSQL(sql)
 
  def createExampleData(self):
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    with self.rdbh.cursor() as cur:
      for data in datas:
        cur.execute("INSERT INTO example (name, sex) VALUES (%s, %s)", [data[0], data[1]])
 
  def dbclose(self):
    self.rdbh.close()
 
  def dbconnect(self):
    dbname = "example"
    timezone = "Asia/Tokyo"
    self.rdbh = psycopg2.connect("host=" + os.environ['DBHOST'] +" dbname=" + dbname + " user="+ os.environ['DBUSER'] + " password=" + os.environ['DBPASSWORD'])
    self.executeSQL("SET TIME ZONE '"+ timezone + "'")
 
  def dropExampleTable(self):
    sql = "DROP TABLE IF EXISTS example"
    self.executeSQL(sql)
 
  def dropExampleType(self):
    sql = "DROP TYPE IF EXISTS example_sex"
    self.executeSQL(sql)
 
  def endTransaction(self):
    self.rdbh.commit()
 
  def executeSQL(self, sql):
    with self.rdbh.cursor() as cur:
      cur.execute(sql)
 
  def rollback(self):
    self.rdbh.rollback()
 
  def selectFromExampleTable(self):
    sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example"
    with self.rdbh.cursor() as cur:
      cur.execute(sql)
      for [id, name, sex, created_at, updated_at] in cur:
        print("\t".join([str(id), name, sex, str(created_at), str(updated_at)]))
 
if __name__ == "__main__":
  psql = PsqlManipulator()
  psql.dbconnect()
  try:
    psql.beginTransaction()
    try:
      psql.dropExampleTable()
      psql.dropExampleType()
      psql.createExampleType()
      psql.createExampleTable()
      psql.createExampleData()
      psql.selectFromExampleTable()
      psql.dropExampleTable()
      psql.dropExampleType()
      psql.endTransaction()
    except Exception as e:
      psql.rollback()
      print(e)
    finally:
      psql.dbclose()
  except Exception as e:
    print(e)
  finally:
    psql.dbclose()

Ruby

require 'dotenv'
require 'pg'
 
class PsqlManipulator
  def initialize(opt=[])
    Dotenv.load(
      File.join(File.dirname(File.expand_path(__FILE__)), '.env')
    )
  end
 
  def dbconnect
    dbname = "example"
    timezone = "Asia/Tokyo"
    @rdbh = PG::connect(:host => ENV["DBHOST"], :user => ENV["DBUSER"], :password => ENV["DBPASSWORD"], :dbname => dbname)
    sql = "SET client_min_messages = 'error'"
    @rdbh.query(sql)
    sql = "SET TIME ZONE '"+ timezone + "'"
    @rdbh.query(sql)
  end
 
  def beginTransaction
    sql = "BEGIN"
    executeSQL(sql)
  end
 
  def createExampleType
    sql = "CREATE TYPE example_sex AS ENUM ('male', 'female')"
    executeSQL(sql)
  end
 
  def createExampleTable
    sql = '''
CREATE TABLE example (
  id SERIAL NOT NULL,
  name VARCHAR(255) NOT NULL,
  sex example_sex DEFAULT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);
CREATE INDEX created_at_idx ON example(created_at);
CREATE INDEX name_idx ON example(name);
CREATE INDEX updated_at_idx ON example(updated_at);
'''
    executeSQL(sql)
  end
 
  def createExampleData
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    datas.each do |data|
      @rdbh.exec_params("INSERT INTO example (name, sex) VALUES ($1, $2)", [data[0], data[1]])
    end
  end
 
  def dbclose
    @rdbh.close()
  end
 
  def dropExampleTable
    sql = "DROP TABLE IF EXISTS example"
    executeSQL(sql)
  end
 
  def dropExampleType
    sql = "DROP TYPE IF EXISTS example_sex"
    executeSQL(sql)
  end
 
  def endTransaction
    sql = "END"
    @rdbh.query(sql)
  end
 
  def executeSQL(sql)
    @rdbh.query(sql)
  end
 
  def selectFromExampleTable
    sql = "SELECT id, name, sex, to_char(created_at, 'YYYY/MM/DD HH24:MI:SS') AS created_at, to_char(updated_at, 'YYYY/MM/DD HH24:MI:SS') AS updated_at FROM example"
    results = @rdbh.query(sql)
    results.each do |row|
      print([row["id"], row['name'], row['sex'], row['created_at'], row['updated_at']].join("\t") + "\n")
    end
  end
end
 
if $0 == __FILE__
  rdb = PsqlManipulator.new()
  begin
    rdb.dbconnect()
  rescue => err
    print(err)
    exit(1)
  end
 
  begin
    rdb.beginTransaction()
    begin
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.createExampleType()
      rdb.createExampleTable()
      rdb.createExampleData()
      rdb.selectFromExampleTable()
      rdb.dropExampleTable()
      rdb.dropExampleType()
      rdb.endTransaction()
    rescue => err
      puts("Automatically roll backed.")
      print(err)
      rdb.dbclose()
      exit(1)
    end
  rescue => err
    rdb.dbclose()
    print(err)
    exit(1)
  end
end

プログラミング言語比較サイトProgrammingLang.comでは、同じ問題を複数のプログラミング言語がそれぞれどのような記述で解決できるのかの例を提供。
複数の言語を比較し、貴方の問題を解決するのに最適な言語の選択と、その言語での解法を得る事を手助けします。
全問題カバー: JavaScript Perl PHP Python Ruby | 一部: C C# C++ Go Java Rust Shell

問題解法大分類(50音順)
Class | 時間 | 数値 | System | Database | Test | Network | 配列 | ファイルシステム | 変数 | 文字列

その他役立ちコンテンツ

監修: 栗田創/(株)1st Class 代表取締役 (他にも共同創業で取締役の企業有)
毎日何万人もの方に利用頂くサイト群(作品DB/ブログ検索皆声/最速一括検索/職業訓練.com/その他多数)の管理人
資格: AWS Solution Architect / Professional Scrum Master I / 第1種情報処理(国家資格) / TOEIC900+ / 東京大学経済経営卒
Xで更新情報やIT/マーケの役立ち情報を提供してるので、宜しければフォローして下さい
IT系: VPS比較 | AWS等Publicクラウド比較 | 最安ドメイン比較

※当サイトではアフィリエイトプログラムを利用して商品を紹介しています。

プログラミング言語比較