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

Contents

出力結果

プログラムの流れは

MySQLに接続
トランザクション処理を開始
exampleテーブルがあったら削除
exampleテーブルを作成
データをInsert
InsertしたデータをSelect
exampleテーブルを削除
トランザクション処理を終了(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'@'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.%';

コード例

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