Contents
Output
Result can be matched with following 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 of the result
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
Prepare .env file storing db connection information like this.
DBHOST=0.0.0.0 DBUSER=exampleuser DBPASSWORD=example20141024
Execute following sqls and prepare database and a user for scripts.
CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'10.%' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'172.%' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'192.%' IDENTIFIED BY 'example20141024'; CREATE DATABASE example CHARACTER SET utf8mb4; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'localhost'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'10.%'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'172.%'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'192.%';
Code examples
JavaScript
class MysqlManipulator {
constructor(opt={}) {
this.mysql = require('mysql');
require('dotenv').config();
this.rdbh = this.mysql.createConnection({
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']
]
const sql = 'INSERT INTO example (name, sex) VALUES (?, ?)'
for (const data of datas) {
const res = await this.rdbh.query(sql, data)
}
}
async createExampleTable() {
const sql = `CREATE TABLE example (
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex ENUM('male', 'female') DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT NOW(),
updated_at timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
KEY created_at_idx (created_at),
KEY name_idx (name),
KEY updated_at_idx (updated_at)
);`;
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 endTransaction() {
await this.rdbh.commit()
}
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, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example";
try {
await this.rdbh.query(sql, function (err, rows, fields) {
if (err) {
console.log('err: ' + err);
}
for(const row of 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 = MysqlManipulator;
if(!module.parent) {
(async () => {
const rdb = new MysqlManipulator()
await rdb.dbconnect()
await rdb.beginTransaction()
await rdb.dropExampleTable()
await rdb.createExampleTable()
await rdb.createExampleData()
await rdb.selectFromExampleTable()
await rdb.dropExampleTable()
await rdb.endTransaction()
await rdb.dbclose()
})()
}
Perl
package MysqlManipulator;
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 int NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex ENUM('male', 'female') DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT NOW(),
updated_at timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
KEY `created_at_idx` (`created_at`),
KEY `name_idx` (`name`),
KEY `updated_at_idx` (`updated_at`)
);
EOF
$self->executeSQL($sql);
}
sub dbclose(){
my $self = shift;
$self->{'rdbh'}->disconnect();
}
sub dbconnect(){
my $self = shift;
my $database = "example";
my $timezone = "Asia/Tokyo";
my $dbi = 'mysql';
$self->{'rdbh'} = DBI->connect("DBI:".$dbi.":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 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, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') 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 MysqlManipulator();
$rdb->dbconnect();
$rdb->beginTransaction();
$rdb->dropExampleTable();
$rdb->createExampleTable();
$rdb->createExampleData();
$rdb->selectFromExampleTable();
$rdb->dropExampleTable();
$rdb->endTransaction();
$rdb->dbclose();
}
else{
1;
}
PHP
<?php
require_once __DIR__ . '/vendor/autoload.php';
class MysqlManipulator {
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 int NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex ENUM('male', 'female') DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT NOW(),
updated_at timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
KEY `created_at_idx` (`created_at`),
KEY `name_idx` (`name`),
KEY `updated_at_idx` (`updated_at`)
);
EOF;
$this->executeSQL($sql);
}
function dbclose() {
$this->rdbh = null;
}
function dbconnect(){
$dbname = "example";
$timezone = "Asia/Tokyo";
try {
$this->rdbh = new PDO('mysql:dbname='.$dbname.';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 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, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') 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]) ) {
$rdb = new MysqlManipulator();
try{
$rdb->dbconnect();
} catch(PDOException $e){
echo $e->getMessage();
exit(1);
}
try{
$rdb->beginTransaction();
} catch(PDOException $e){
echo $e->getMessage();
$rdb->dbclose();
exit(1);
}
try {
$rdb->dropExampleTable();
$rdb->createExampleTable();
$rdb->createExampleData();
$rdb->selectFromExampleTable();
$rdb->dropExampleTable();
$rdb->endTransaction();
$rdb->dbclose();
} catch(PDOException $e){
$rdb->rollback();
$rdb->dbclose();
throw $e;
}
}
Python
from firstclass_dotenv import Dotenv
import mysql.connector
import os
class MysqlManipulator():
def __init__(self, opt=[]):
dotenv = Dotenv()
dotenv.load()
def beginTransaction(self):
sql = "BEGIN"
self.executeSQL(sql)
def createExampleTable(self):
sql = '''\
CREATE TABLE example (
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex ENUM('male', 'female') DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT NOW(),
updated_at timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
KEY `created_at_idx` (`created_at`),
KEY `name_idx` (`name`),
KEY `updated_at_idx` (`updated_at`)
);
'''
self.executeSQL(sql)
def createExampleData(self):
datas = [
['name1', 'male'],
['name2', 'female']
]
for data in datas:
self.cursor.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 = mysql.connector.connect(host=os.environ['DBHOST'], user=os.environ['DBUSER'], passwd=os.environ['DBPASSWORD'], database=dbname)
self.cursor = self.rdbh.cursor()
self.executeSQL("SET TIME_ZONE = '"+ timezone + "'")
def dropExampleTable(self):
sql = "DROP TABLE IF EXISTS example"
self.executeSQL(sql)
def endTransaction(self):
self.rdbh.commit()
def executeSQL(self, sql):
self.cursor.execute(sql)
def rollback(self):
self.rdbh.rollback()
def selectFromExampleTable(self):
sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example"
self.cursor.execute(sql)
for [id, name, sex, created_at, updated_at] in self.cursor:
print("\t".join([str(id), name, sex, str(created_at), str(updated_at)]))
if __name__ == "__main__":
rdb = MysqlManipulator()
rdb.dbconnect()
try:
rdb.beginTransaction()
try:
rdb.dropExampleTable()
rdb.createExampleTable()
rdb.createExampleData()
rdb.selectFromExampleTable()
rdb.dropExampleTable()
rdb.endTransaction()
except Exception as e:
rdb.rollback()
print(e)
finally:
rdb.dbclose()
except Exception as e:
print(e)
finally:
rdb.dbclose()
Ruby
require 'dotenv'
require 'mysql2'
class MysqlManipulator
def initialize(opt=[])
Dotenv.load(
File.join(File.dirname(File.expand_path(__FILE__)), '.env')
)
end
def dbconnect
dbname = "example"
timezone = "Asia/Tokyo"
@rdbh = Mysql2::Client.new(:host => ENV["DBHOST"], :username => ENV["DBUSER"], :password => ENV["DBPASSWORD"], :database => dbname)
sql = "SET TIME_ZONE = '"+ timezone + "'"
@rdbh.query(sql)
end
def beginTransaction
sql = "BEGIN"
executeSQL(sql)
end
def createExampleTable
sql = '''
CREATE TABLE example (
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex ENUM("male", "female") DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT NOW(),
updated_at timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
KEY created_at_idx (created_at),
KEY name_idx (name),
KEY updated_at_idx (updated_at)
);
'''
executeSQL(sql)
end
def createExampleData
datas = [
['name1', 'male'],
['name2', 'female']
]
sth = @rdbh.prepare("INSERT INTO example (name, sex) VALUES (?, ?)")
datas.each do |data|
sth.execute(data[0], data[1])
end
end
def dbclose
@rdbh.close()
end
def dropExampleTable
sql = "DROP TABLE IF EXISTS example"
executeSQL(sql)
end
def endTransaction
sql = "COMMIT"
@rdbh.query(sql)
end
def executeSQL(sql)
@rdbh.query(sql)
end
def selectFromExampleTable
sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') 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 = MysqlManipulator.new()
begin
rdb.dbconnect()
rescue => err
print(err)
exit(1)
end
begin
rdb.beginTransaction()
begin
rdb.dropExampleTable()
rdb.createExampleTable()
rdb.createExampleData()
rdb.selectFromExampleTable()
rdb.dropExampleTable()
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
