Contents
Output
expected result is following one(regex expression)
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+$"
example
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
Prerequisites
Assuming you prepared .env file like
DBHOST=0.0.0.0 DBUSER=exampleuser DBPASSWORD=example20141024
for connection to PostgreSQL server
Then prepare database and a user using following command.
CREATE USER exampleuser PASSWORD 'example20141024';
CREATE DATABASE example;
GRANT ALL ON DATABASE example TO exampleuser;
CREATE TYPE sex_example AS ENUM ('male', 'female');
Code examples
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
