操作PostgreSQL(连接/创建表/插入/选择/删除/事务)

Contents [hide]

输出结果

预期结果是一个(正则表达式)

1
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
2
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文件来连接到PostgreSQL服务器

1
2
3
DBHOST=0.0.0.0
DBUSER=exampleuser
DBPASSWORD=example20141024

然后使用以下命令准备数据库和用户。

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

编程代码示例


JavaScript

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
<?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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
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