概要

軽い気持ちでWordPressアドレスを変えてしまったところ(上画像の/wordpressを消してしまった)、
変更後のURLにアクセスしてもダッシュボードにアクセスできなくなってしまった。。

対処

その1　wp-config.phpで設定を上書きする

設定ファイルwordpress/wp-config.phpに下記を追加。

wp-config.php

define('WP_SITEURL', 'http://dev.hoge.com/wordpress');

※注意

/* 編集が必要なのはここまでです ! WordPress でのパブリッシングをお楽しみください。 */

より上に追加すること。

ただし...

これをすると、ダッシュボードからはWordPressアドレスを変更できなくなっちゃいます。
あまりよろしくない。

その2　wp_optionsテーブルの中身を修正する

どうやらWordPressアドレスはWordPress用のデータベースのwp_optionsテーブルで管理されているらしい。
編集ツールを使うのも面倒なので、直接コマンドで編集しちゃいます。

1. WordPressのサーバーから、MySQLにアクセス

terminal

$ mysql -u root -p
Enter password: 

2. WordPress用のデータベースに接続

terminal

mysql> use データベース名

補足: WordPress用のデータベースはwp-config.phpで設定したもの。

wp-config.php

/** WordPress のためのデータベース名 */
define( 'DB_NAME', 'データベース名' );

3. テーブルを確認

terminal

mysql> show tables;

+-----------------------+
| Tables_in_hoge        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

ちゃんとwp_optionsがありますね。


4. WordPressアドレスを確認
WordPressアドレスはsiteurlとして管理されているらしい。

terminal

mysql> select option_name,option_value from wp_options where option_name = "siteurl";

+-------------+------------------------+
| option_name | option_value           |
+-------------+------------------------+
| siteurl     | http://dev.hoge.com    |
+-------------+------------------------+
1 row in set (0.00 sec)

ありましたね。URLも変更してしまった後のものです。


5. テーブルを更新
set option_value = "変更したいURL"とし、テーブルの内容を更新します。

terminal

mysql> update wp_options set option_value = "http://dev.hoge.com/wordpress" where option_name = "siteurl";

６. 変更を確認

terminal

mysql> select option_name,option_value from wp_options where option_name = "siteurl";

+-------------+----------------------------------+
| option_name | option_value                     |
+-------------+----------------------------------+
| siteurl     | http://dev.hoge.com/wordpress    |
+-------------+----------------------------------+
1 row in set (0.00 sec)

ちゃんと変更されています。

ダッシュボードにもアクセスでき、変更も反映されていることが確認できます。

まとめ

wp_optionsテーブルの中身を更新することで、無事にWordPressの設定を変更前に戻すことができました。
テーブルの中身をいじるので更新する際はくれぐれも慎重に。

Rails 自作アプリを作った時の経過をまとめていきます。

環境

OS	Ruby	Rails
Mac Mojave 10.14.16	2.6.3p62	5.0.7.2

参考
https://qiita.com/cigalecigales/items/f4274088f20832252374

前提
環境構築済

【conformableの設定】

1.送信メールアドレスの登録

①設定ファイルの追記/編集

config/environments/depelopment.rb

  config.action_mailer.raise_delivery_errors = true
  config.action_mailer.delivery_method = :smtp
  config.action_mailer.smtp_settings = {
    :address => "smtp.gmail.com",
    :port => 587,
    :user_name => "Gメールアドレス", # メアドを書く
    :password => "Gメールパスワード",
    :authentication => :plain,
    :enable_starttls_auto => true
  }

config/initialiezers/devise.rb

  config.mailer_sender = 'Gメールアドレス' # メアドを書く

②gmailアプリパスワードの取得、追記

config/environments/development.rb

　　　（省略）
    :password => "Gメールパスワード", # Gmailのアプリパスワードを書く
　　　（省略）

③サインアップ、ログインの検証

サーバーを起動し、サインアップ。
確認メールをMacで開き、『Confirm my accout』をクリック
その後、ログイン可能か確認する

【lockableの設定】

1.設定値の登録

①設定ファイルの編集 ※ 必要に応じて、設定値やコメントの有無を変更

config/initializers/devise.rb

　　　（省略）

  config.lock_strategy = :failed_attempts

  config.unlock_keys = [:email]

  config.unlock_strategy = :both

  config.maximum_attempts = 20

  config.unlock_in = 1.hour

  config.last_attempt_warning = true

　　　（省略）

【timetableの設定】

1.設定値の登録

①設定ファイルの編集 ※ 必要に応じて、設定値やコメントの有無を変更

config/initializers/devise.rb

　　　（省略）

  # config.timeout_in = 30.minutes


　　　（省略）

【その他の設定】

1.サインアップ画面で入力させたい項目の追加

①設定ファイルの編集 ※ 必要に応じて、入力させたい項目を追加

app/views/devise/registrations/new.html.erb

<h2>サインアップ</h2>

<%= form_for(resource, as: resource_name, url: registration_path(resource_name)) do |f| %>
  <%= render "devise/shared/error_messages", resource: resource %>
  <!-- 省略 -->

  <div class="field">
    <%= f.label :sellername, '取扱者' %><br />
    <%= f.text_field :sellername, autofocus: true, autocomplete: "sellername" %>
  </div>
  <!-- 省略 -->
<% end %>

<%= render "devise/shared/links" %>

2.変更できるようにしたい項目の追加

①設定ファイルの編集 ※ 必要に応じて、入力させたい項目を追加

app/views/devise/registrations/edit.html.erb

<h2>取扱者情報編集</h2>

<%= form_for(resource, as: resource_name, url: registration_path(resource_name), html: { method: :put }) do |f| %>
  <%= render "devise/shared/error_messages", resource: resource %>
  <!-- 省略 -->

  <div class="field">
   <div class="field">
    <%= f.label :sellername, '取扱者' %><br />
    <%= f.text_field :sellername, autofocus: true, autocomplete: "sellername" %>
  </div>
  <!-- 省略 -->
<% end %>

<%= render "devise/shared/links" %>

3.ストロングパラメータの設定

①データベースに登録したい項目をストロングパラメータで指定する

app/controllers/application_controller.rb

class ApplicationController < ActionController::Base
  protect_from_forgery with: :exception
  before_action :configure_permitted_parameters, if: :devise_controller?

  protected
    def configure_permitted_parameters
      devise_parameter_sanitizer.permit(:sign_up, keys: [:sellername])
      devise_parameter_sanitizer.permit(:account_update, keys: [:sellername])
    end
end

はじめに

ローカル環境/本番環境(EC2)の其々でコンテナを起動させた際のデータベース閲覧方法と
環境による閲覧方法の違いがあるのか気になったので、調べてみました。

環境

• Rails:5.0.7
• MySQL:5.6
• Docker:19.03.8
• EC2(AMI):Amazon Linux AMI

ソースコード

Dockerfile

FROM ruby:2.5.1

RUN curl -sL https://deb.nodesource.com/setup_10.x | bash - && apt-get update && apt-get install -y nodejs --no-install-recommends && rm -rf /var/lib/apt/lists/*

RUN mkdir /app

WORKDIR /app

COPY Gemfile /app/Gemfile
COPY Gemfile.lock /app/Gemfile.lock

RUN gem install bundler
RUN bundle install

COPY . /app

RUN mkdir -p tmp/sockets

docker-compose.yml

version: '2'
services:
  db:
    image: mysql:5.6
    environment:
      MYSQL_ROOT_USER: root
      MYSQL_ROOT_PASSWORD: password
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
    volumes:
      - mysql-data:/var/lib/mysql
      - ./mysql/init:/docker-entrypoint-initdb.d
    ports:
      - "3306:3306"

  app:
    build:
      context: .  
    command: bundle exec puma -C config/puma.rb
    volumes:
      - .:/app
      - public-data:/app/public
      - tmp-data:/app/tmp
      - log-data:/app/log
    depends_on:
      - db
    depends_on:
      - web

  web:
    build:
      context: containers/nginx
    volumes:
      - public-data:/app/public
      - tmp-data:/app/tmp
    ports:
      - 80:80

データベース接続

ローカル、EC2共通

#起動中のコンテナ名確認
docker-compose ps
#DBコンテナに入る
docker exec -it DBコンテナNAME  bash
#mysqlへ接続(パスワードはdocker-compose.ymlに記載したもの)
mysql -u root -p
Enter password: 

EC2

#DBコンテナのPORT確認
docker ps
#確認結果(例)
0.0.0.0:3306->3306/tcp
#mysqlへ接続(パスワードはdocker-compose.ymlに記載したもの)
mysql -h 0.0.0.0 -P 3306 -u root -p
Enter password: 

データベースの中身閲覧

ターミナル

#データベース接続
mysql -u root -p
#どんなデータベースがあるか
show databases;
#使用したいデータベースに切り替え
use　データベ-ス名;
#テーブル一覧
show tables;
#テーブルの構造確認
describe テーブル名複数系;
#テーブルの中身確認 
select * from テーブル名複数系;

おわりに

今回ローカルとEC2でそれぞれ起動したコンテナにどのような違いがあるのか、データベースの観点から調べてみました。間違ってる点があれば指摘していただけると幸いです。
同じポート3306で起動しているはずが、ローカルではmysql -h 0.0.0.0 -P 3306 -u root -pのコマンドが効かなかったので引き続き調査します。。

参考URL

https://qiita.com/hayabusa3703/items/9893a53c21ddc3c2403a
https://qiita.com/hot_study_man/items/4e129dacb7c3cab4b568

「テストデータベース」

を

「テスト2」

にテーブル構造だけをコピーしたい。

環境

• OS: Ubuntu 18.04 LTS
• MariaDB (mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2)

データ取得

root でシェルから作業する。

# mysqldump -u root -p --no-data テストデータベース > db.skeleton

通常の mysqldump の使い方に --no-data をつけるだけ

新しいデータベース作る

mysql に root でログインして

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 521
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database テスト2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> exit
Bye

読み込み

ルートでシェルから

# mysql -u root -p テスト2 < db.skeleton

ユーザ権限設定

「MariaDB on Ubuntu18.04 LTS」
https://qiita.com/nanbuwks/items/c98c51744bd0f72a7087
のように、 webdb ユーザに権限を設定していたので同様に設定する必要がある。

mysql に root でログインして、

# mysql -u root -p

MariaDB [(none)]> GRANT ALL ON テスト2.* TO 'webdb'@'localhost';
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> GRANT ALL ON テスト2.* TO webdb@'%';
Query OK, 0 rows affected (0.00 sec)

これでOK

執筆背景

どうも、直人と申します。

現在、自分はlaravelの学習を兼ねて簡単なアプリを作成しようと試みています。

その第一段階として、
昨日VirtualBoxとVagrantを使用してlaravelの開発環境を構築し終えた。

なので、
今日はMVCモデルの学習とMySQL上のデータベースにテーブルを作成し、そこから値を拾ってきて表示するところまでを終わらせよう
と考えていた。

MVCモデルについて一通り学習を終え早速テーブルを作成しようと以下のコマンドを打つ。

$ php artisan make:migration create_books_table

create_books_taqble.phpが作成されるので、ファイル内の以下の部分をデフォルトから変更した。

create_books_taqble.php

 Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('image');
            $table->string('author');
            $table->string('local');
            $table->timestamps();
        });

その後、テーブルを作成しようと

$ php artisan migrate

コマンドを実行すると、以下のエラーが発生した。

   Illuminate\Database\QueryException 

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) (SQL: select * from information_schema.tables where table_schema = Homestead and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:670
    666|         // If an exception occurs when attempting to run a query, we'll format the error
    667|         // message to include the bindings with SQL, which will make this exception a
    668|         // lot more helpful to the developer instead of just the database's errors.
    669|         catch (Exception $e) {
  > 670|             throw new QueryException(
    671|                 $query, $this->prepareBindings($bindings), $e
    672|             );
    673|         }
    674| 

      +34 vendor frames 
  35  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

何故かアクセスが拒否された。
これについての対処方法を以下で述べる。

結論

laravel5.8以降の.envファイル(データベースにアクセスするためのパスワードとか設定されてる)では、
#を文字として認識しない仕様であり、私が設定していたパスワードには#が使われていたため、パスワードをダブルクォーテーションで括ることでmigrateすることができた。

作業内容

以下では、実際の作業内容を綴っていきます。

PHPのバージョンの違いを疑う

ホスト側のPHPとゲスト側のPHPのバージョンが異なるために不具合が生じているのではないかと考え両者のバージョンを確認してみると異なることを確認した。

#ホスト側
$ php -v
PHP 7.1.23 (cli) (built: Feb 22 2019 22:19:32) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

#ゲスト側
$ php -v
PHP 7.4.4 (cli) (built: Mar 19 2020 20:12:35) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.4, Copyright (c), by Zend Technologies

そのため、ホスト側のPHPのバージョンをアップグレードすることにした。
具体的には、brewでインストールしたのち、パスを通して再起動するということを行なっている。

$ brew install php@7.4
$ echo 'export PATH="/usr/local/opt/php@7.4/bin:$PATH"' >> ~/.bash_profile
$ echo 'export PATH="/usr/local/opt/php@7.4/sbin:$PATH"' >> ~/.bash_profile
$ brew services start php

結果、直らなかった…。

デフォルトで作成されるrootユーザを疑う

$ mysql -u root -p

上記の方法であれば、mysqlに接続することができた(パスワードは.envファイルに記載されているものと同じ)。

mysql -u root -pで入力したユーザ名とパスワードは.envファイルに記載されていた同じなのに、何故php artisan migrateコマンドからではアクセスが拒否されたのか
ということに着目し調べてみることにした。

mysqlからuserの一覧を表示すると、デフォルトで作成されたと思われるroot@0.0.0.0ユーザと自分で作ったroot@localhostの二種類が存在することが判明。

これが原因ではないかと思い、デフォルトで作成されているroot@0.0.0.0ユーザを削除してみることにした。

#変更前
mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| 0.0.0.0   | root             |
| localhost | root             |
+-----------+------------------+

#変更後
mysql> drop user root@0.0.0.0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | root             |
+-----------+------------------+

しかしながら直らなかった…。

laravel本体を疑う

さらに色々深く調べてみると以下の記事を発見。
→ https://qiita.com/kotatsu0715/items/a8d3a93c4a46ca925fc4

要約すると、
laravel5.8以降の.envファイルでは#を文字として認識しないため、パスワードに#を使っている人はダブルクォーテーションを使いましょう
というものだった。

私のデータベースのパスワードには見事に#が使われていたため、試しにダブルクォーテーションで括ってテーブルの作成を再実行してみた。

.env

DB_PASSWORD="hoge#"

php artisan migrate
Migration table created successfully.

うまくいった！

執筆背景

どうも、直人と申します。

現在、自分はlaravelの学習を兼ねて簡単なアプリを作成しようと試みています。

その第一段階として、
昨日VirtualBoxとVagrantを使用してlaravelの開発環境を構築し終えた。

なので、
今日はMVCモデルの学習とMySQL上のデータベースにテーブルを作成し、そこから値を拾ってきて表示するところまでを終わらせよう
と考えていた。

MVCモデルについて一通り学習を終え早速テーブルを作成しようと以下のコマンドを打つ。

$ php artisan make:migration create_books_table

create_books_taqble.phpが作成されるので、ファイル内の以下の部分をデフォルトから変更した。

create_books_taqble.php

 Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('image');
            $table->string('author');
            $table->string('local');
            $table->timestamps();
        });

その後、テーブルを作成しようと

$ php artisan migrate

コマンドを実行すると、以下のエラーが発生した。

   Illuminate\Database\QueryException 

  SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) (SQL: select * from information_schema.tables where table_schema = Homestead and table_name = migrations and table_type = 'BASE TABLE')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:670
    666|         // If an exception occurs when attempting to run a query, we'll format the error
    667|         // message to include the bindings with SQL, which will make this exception a
    668|         // lot more helpful to the developer instead of just the database's errors.
    669|         catch (Exception $e) {
  > 670|             throw new QueryException(
    671|                 $query, $this->prepareBindings($bindings), $e
    672|             );
    673|         }
    674| 

      +34 vendor frames 
  35  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

何故かアクセスが拒否された。
これについての対処方法を以下で述べる。

結論

laravel5.8以降の.envファイル(データベースにアクセスするためのパスワードとか設定されてる)では、
#を文字として認識しない仕様であり、私が設定していたパスワードには#が使われていたため、パスワードをダブルクォーテーションで括ることでmigrateすることができた。

作業内容

以下では、実際の作業内容を綴っていきます。

PHPのバージョンの違いを疑う

ホスト側のPHPとゲスト側のPHPのバージョンが異なるために不具合が生じているのではないかと考え両者のバージョンを確認してみると異なることを確認した。

#ホスト側
$ php -v
PHP 7.1.23 (cli) (built: Feb 22 2019 22:19:32) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

#ゲスト側
$ php -v
PHP 7.4.4 (cli) (built: Mar 19 2020 20:12:35) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.4, Copyright (c), by Zend Technologies

そのため、ホスト側のPHPのバージョンをアップグレードすることにした。
具体的には、brewでインストールしたのち、パスを通して再起動するということを行なっている。

$ brew install php@7.4
$ echo 'export PATH="/usr/local/opt/php@7.4/bin:$PATH"' >> ~/.bash_profile
$ echo 'export PATH="/usr/local/opt/php@7.4/sbin:$PATH"' >> ~/.bash_profile
$ brew services start php

結果、直らなかった…。

デフォルトで作成されるrootユーザを疑う

$ mysql -u root -p

上記の方法であれば、mysqlに接続することができた(パスワードは.envファイルに記載されているものと同じ)。

mysql -u root -pで入力したユーザ名とパスワードは.envファイルに記載されていた同じなのに、何故php artisan migrateコマンドからではアクセスが拒否されたのか
ということに着目し調べてみることにした。

mysqlからuserの一覧を表示すると、デフォルトで作成されたと思われるroot@0.0.0.0ユーザと自分で作ったroot@localhostの二種類が存在することが判明。

これが原因ではないかと思い、デフォルトで作成されているroot@0.0.0.0ユーザを削除してみることにした。

#変更前
mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| 0.0.0.0   | root             |
| localhost | root             |
+-----------+------------------+

#変更後
mysql> drop user root@0.0.0.0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | root             |
+-----------+------------------+

しかしながら直らなかった…。

laravel本体を疑う

さらに色々深く調べてみると以下の記事を発見。
→ https://qiita.com/kotatsu0715/items/a8d3a93c4a46ca925fc4

要約すると、
laravel5.8以降の.envファイルでは#を文字として認識しないため、パスワードに#を使っている人はダブルクォーテーションを使いましょう
というものだった。

私のデータベースのパスワードには見事に#が使われていたため、試しにダブルクォーテーションで括ってテーブルの作成を再実行してみた。

.env

DB_PASSWORD="hoge#"

php artisan migrate
Migration table created successfully.

うまくいった！

mysql2のインストール中にエラーが起きた際の対処法で見かけるopensslのインストール先を指定する以下の方法

brew --prefix openssl

brew --prefixとは何ぞや？と思ったので調べてみました。ターミナルで。

そもそも「--prefix」とは？

言わずとしれたインストール先の指定方法

--prefix='インストール先のアドレス'
ex.
--prefix=/usr/local

brew --prefix

じゃあ「brew --prefix」は何だと思って試しにターミナルさんに聞いてみた。

% brew --prefix
/usr/local

brewのインストール先のアドレスだった。単純な話だった。

rails sしてサーバーを切り忘れてしまった後にmysqlのエラーが出る。
よくやってしまうので、自分用にまとめました。実際にやっていて手順が増えたら随時追加する。

エラー

$ rails s
=> Booting Puma
=> Rails 5.2.4.2 application starting in development 
=> Run `rails server -h` for more startup options
Puma starting in single mode...
* Version 3.12.4 (ruby 2.5.3-p105), codename: Llamas in Pajamas
* Min threads: 5, max threads: 5
* Environment: development
* Listening on tcp://localhost:3000
Use Ctrl-C to stop
Started GET "/" for 127.0.0.1 at 2020-04-02 11:39:55 +0900

Mysql2::Error::ConnectionError - Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2):

pumaでrailsサーバーを起動することはできるが、実際にアクセスするとmysqlのエラーが出る。

解決法

$ sudo mysql.server start  # mysqlサーバーの起動
Password:  # アカウントのパスワードを入力
Starting MySQL
.. SUCCESS!

$ mysql_config --socket  # socketの有無を確認
/tmp/mysql.sock

$ chmod 777 /tmp/mysql.sock  # 権限を付与

今回はこれで解決しました。

リンク

• mysqlのsocketエラーでrailsアプリが起動できない

概要

3GBを越えるcsvファイルの前処理のトレーニング備忘録

3GBを越えるcsvファイルは、Excelで開くことができない。
これをデータサイエンティストが分析に使えるようデータを整理し、300行のcsvファイルとして出力する。

前提知識

・SELECTやテーブルといったSQLの基礎知識が僅かにある
・MAMPなどでSQLにログインができる

使用する技術

1. データベースの作成
2. テーブルの作成
3. csvファイルを読み込み
4. 欠損値のあるレコード（行）の削除
5. 文字列に存在する邪魔な文字を指定して削除
6. varchar型からfloat型へ変換
7. varchar型からDate型へ変換 （'04/01/1998'という順序で記録されている文字列を、'1998-04-01' の順に変えて変換）
8. データを並べ替えてcsvファイルに出力

使用するcsvファイル

kaggleのこちらからiowa-liquor-sales.csvをダウンロード

実践内容

ファイルのカラム（列）名の取得

SQLでは、テーブルのカラムを先に指定する必要がある。
今回はRを用いてカラム名と型を取得。

## R
# check colmn name
# ファイルのカラム名を調べる
library(tidyverse)
Iowa <- read_csv("Iowa_Liquor_Sales.csv")
str(Iowa)

出力結果末尾
- attr(*, "spec")=
.. cols(
.. Invoice/Item Number = col_character(),
.. Date = col_character(),
.. Store Number = col_double(),
.. Store Name = col_character(),
.. Address = col_character(),
.. City = col_character(),
.. Zip Code = col_character(),
.. Store Location = col_character(),
.. County Number = col_character(),
.. County = col_character(),
.. Category = col_double(),
.. Category Name = col_character(),
.. Vendor Number = col_double(),
.. Vendor Name = col_character(),
.. Item Number = col_double(),
.. Item Description = col_character(),
.. Pack = col_double(),
.. Bottle Volume (ml) = col_double(),
.. State Bottle Cost = col_character(),
.. State Bottle Retail = col_character(),
.. Bottles Sold = col_double(),
.. Sale (Dollars) = col_character(),
.. Volume Sold (Liters) = col_double(),
.. Volume Sold (Gallons) = col_double()
.. )

ほとんどのカラムが文字列だと分かる。
（DateやState Bottle Costなども）

SQLへ移動

コマンドプロンプトを起動してSQLへ。

## cmd
# Mysqlのあるディレクトリに移動
cd C:\MAMP\bin\mysql\bin

# Mysqlにログイン
mysql -u root -proot

1. データベースの作成

trainという名のデータベースの作成

# trainという名のデータベースを作成
CREATE DATABASE train;

# 使用するデータベース'train'の指定
USE train;

# 使用するテーブル'iowa_liquor_2'が既にある場合は削除（無ければ以下の命令は無視）
DROP TABLE iowa_liquor_2

2. テーブルの作成

先程Rで判明したカラム名と型を用いて、テーブル'iowa_liquor_2'を作成する
欠損値を正確に処理するために一旦すべてのデータはvarchar型で定義する。

# 使用するテーブル'iowa_liquor_2'を作成
CREATE TABLE iowa_liquor_2 (
InvoiceItemNumber varchar(255),
Date varchar(20),
StoreNumber varchar(255),
StoreName varchar(255),
Address varchar(255),
City varchar(255),
ZipCode varchar(255),
StoreLocation varchar(255),
CountyNumber varchar(255),
County varchar(255),
Category varchar(20),
CategoryName varchar(255),
VendorNumber varchar(255),
VendorName varchar(255),
ItemNumber varchar(255),
ItemDescription varchar(255),
Pack varchar(255),
BottleVolume varchar(255),
StateBottleCost varchar(255),
StateBottleRetail varchar(255),
BottlesSold varchar(255),
Sale varchar(255),
VolumeSoldLiters varchar(255),
VolumeSoldGallons varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. csvファイルをインポート

テーブル'iowa_liquor_2'にインポートする
INFILEの後ろに先程Kaggleでダウンロードした'Iowa_Liquor_Sales.csv'が保存されている場所を指定

# 作成したテーブル'iowa_liquor_2'に'Iowa_Liquor_Sales.csv'をインポート
LOAD DATA LOCAL INFILE 'C:/~~~~~~~~~~~~~~/Iowa_Liquor_Sales.csv'
into table iowa_liquor_2 
fields terminated BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;

出力結果
Query OK, 12591077 rows affected, 65535 warnings (4 min 8.84 sec)
Records: 12591077 Deleted: 0 Skipped: 0 Warnings: 89541
読み込みに４分以上掛かかった。
データ数は12591077と判明

上手くインポートできたか確認

# テーブルの列を確認
DESC iowa_liquor_2;

# テーブルの1行目を確認
SELECT *
FROM iowa_liquor_2
LIMIT 1;

インポートはできたが、欠損値を発見。

4. 欠損値があるレコード（列）を削除してデータを整える。

# 欠損値があるレコード（行）を削除
DELETE
FROM iowa_liquor_2
WHERE
    InvoiceItemNumber         = ''
         OR Date              = ''
         OR StoreNumber       = ''
         OR StoreName         = ''
         OR Address           = ''
         OR City              = ''
         OR ZipCode           = ''
         OR StoreLocation     = ''
         OR CountyNumber      = ''
         OR County            = ''
         OR Category          = ''
         OR CategoryName      = ''
         OR VendorNumber      = ''
         OR VendorName        = ''
         OR ItemNumber        = ''
         OR ItemDescription   = ''
         OR Pack              = ''
         OR BottleVolume      = ''
         OR StateBottleCost   = ''
         OR StateBottleRetail = ''
         OR BottlesSold       = ''
         OR Sale              = ''
         OR VolumeSoldLiters  = ''
         OR VolumeSoldGallons = ''
;

5~7. varchara型のカラムを別の型に変換

数字で表されているものは文字でなく数字として分析で使用したいので、数字型に変換する。
下記の８つのカラムのデータの型を変更する

# データの型を変更できるものは変更する
# カラムStoreNumber, CountyNumber, VendorNumber, ItemNumber, Pack, BottlesSoldをINT型に変更
ALTER TABLE
    iowa_liquor_2
    MODIFY StoreNumber INT
    , MODIFY CountyNumber INT
    , MODIFY VendorNumber INT
    , MODIFY ItemNumber INT
    , MODIFY Pack INT
    , MODIFY BottlesSold INT
;

# カラムVolumeSoldLiters float, VolumeSoldGallonsをfloat型に変更
ALTER TABLE
    iowa_liquor_2
    MODIFY VolumeSoldLiters FLOAT
    , MODIFY VolumeSoldGallons FLOAT
;

# テーブルの列の型を確認
DESC iowa_liquor_2;

上記７つのカラムのデータの型を変更できた。

単純な変換ができない変換

カラム（列）'StateBottleCost','StateBottleRetail','Sale'は文字'$'が数字の前に存在しているため、変換ができない。

また、日付を表すであろうカラムDateも文字列として扱われているので変換したい。
以下の４カラムは一工夫施してから型変換が行うことができる。

5. 文字列に存在する邪魔な文字を指定して削除

値段のカラムは数字として扱いたいので、文字'$'を除去し、float型へ変換できるようにする。

# カラム（列）'StateBottleCost','StateBottleRetail','Sale'の文字'$'を除去
UPDATE `iowa_liquor_2` SET StateBottleCost=REPLACE(StateBottleCost, "$", "");
# remove $ StateBottleRetail & Sale
UPDATE `iowa_liquor_2` SET StateBottleRetail=REPLACE(StateBottleRetail, "$", "");
UPDATE `iowa_liquor_2` SET Sale=REPLACE(Sale, "$", "");


# テーブルの1行目を確認
SELECT *
FROM iowa_liquor_2
LIMIT 1;

無事'$'を全て消せたことを確認。

6. varchar型からFLOAT型へ変換

'$'を除去した3列の型をvrchar型からFLOAT型に変更し、数値として扱えるようにする。

# 先程、文字'$'を除去した3列の型をvrchar型からFLOAT型に変更
/* 列の型を変える */
ALTER TABLE
    iowa_liquor_2
    MODIFY StateBottleCost FLOAT
    , MODIFY StateBottleRetail FLOAT
    , MODIFY Sale FLOAT
;


# テーブルの列の型を確認
DESC iowa_liquor_2;

3列の型をFLOAT型に変更できた。

7. DATA型に変換

文字として扱われているカラム'Date'を、日付として扱えるようにDATE型に変換。
'MM/DD/YYYY'という順序で記録されているため、'YYYY-MM-DD' に変える。

# DATA型に変換できるように'MM/DD/YYYY'から'YYYY-MM-DD' に変える
UPDATE `iowa_liquor_2` SET Date=STR_TO_DATE(Date,'%m/%d/%Y');

# テーブルの1行目を確認
SELECT *
FROM iowa_liquor_2
LIMIT 1;

Dateが'YYYY-MM-DD'の順番に変更された。

# 列'Date'をvrchar型からDATA型に変更
ALTER TABLE iowa_liquor_2 MODIFY DATE DATE;

# テーブルの列の型を確認
DESC iowa_liquor_2;

カラムDATEの型がdate型に変更された。

8. csvファイルにエクスポート

StoreName, InvoiceItemNumber, Dateの順に優先して列を昇順に並び替えた最初の300行をcsvファイルへ書き出し

# sort by StoreName, InvoiceItemNumber, Date
/*
StoreName, InvoiceItemNumber, Dateの順に優先して列を昇順に並び替えて、
300行まで取ってきたものをiowa_liquor_2.csvとして書き出し
*/
# 最初のSELECT文で、出力するCSVファイルの列名を支持
(SELECT 
'InvoiceItemNumber',
'Date',
'StoreNumber',
'StoreName',
'Address',
'City',
'ZipCode',
'StoreLocation',
'CountyNumber',
'County',
'Category',
'CategoryName',
'VendorNumber',
'VendorName',
'ItemNumber',
'ItemDescription',
'Pack',
'BottleVolume',
'StateBottleCost',
'StateBottleRetail',
'BottlesSold',
'Sale',
'VolumeSoldLiters',
'VolumeSoldGallons'
) 
UNION(SELECT *
INTO OUTFILE 'iowa_liquor_2.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
FROM iowa_liquor_2
ORDER BY StoreName, InvoiceItemNumber, Date
LIMIT 300
);
# C:\MAMP\db\mysql\trainにできる

目的

• MySQLで基礎的な一連のデータの取り扱い方法をまとめる
• 必要だと思うことを随時追加してゆく

実施環境

• ハードウェア環境

項目	情報	備考
OS	macOS Catalina(10.15.3)
ハードウェア	MacBook Air (11-inch ,2012)
プロセッサ	1.7 GHz デュアルコアIntel Core i5
メモリ	8 GB 1600 MHz DDR3
グラフィックス	Intel HD Graphics 4000 1536 MB

• ソフトウェア環境

項目	情報	備考
MySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いて導入

実施環境

• 下記の方法を用いてMySQLの環境をMac上に構築した。
  • Mac Homebrewで最新のMySQLをインストールする

簡単な前提知識

• 今回紹介するSQL文の予約語(selectなど)は小文字で記載する。
• SQL文の予約語は大文字小文字を考慮しない。

MySQLのターミナルをrootユーザで開く

• 下記コマンドを実行する。

  $ mysql -u root -t
  

データベースを作成する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> create database データベース名;
  

現在存在するデータベースを確認する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> show database;
  

データベース内のテーブルを確認する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> show tables from データベース名;
  

データベース内のテーブル内のカラムを確認する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> show columns from テーブル名 from データベース名;
  

使用するデータベースを指定する。

• 指定する事によりデータベース名を指定するSQL文をのデータベース名を省くことができるので多様したい。

• MySQLのターミナルで下記コマンドを実行する。

  mysql> use データベース名;
  

• 上記を設定すると先に紹介したデータベース内のテーブルを確認するSQL文やカラムを確認するコマンドのデータベース名を省略することができる。

• データベース名をuse文で指定した場合のテーブルを確認するコマンドを記載する。

  mysql> show tables;
  

• データベース名をuse文で指定した場合のテーブル内のカラムを確認する方法をまとめる。

  mysql> show columns from テーブル名;
  

使用しているデータベース名を出力する

• MySQLのターミナルにて、すでにuse文を用いてデータベースを指定している時に下記を実行する。

  mysql> select database();
  

テーブルを作成する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> use テーブルを作成するデータベース名;
  mysql> create table テーブル名 
      -> ( カラム名1 データ型1, カラム名2 データ型2, カラム名3 データ型3(データ型の文字数制限などのオプション)
      -> );
  

作成したテーブルにデータを格納する

• MySQLのターミナルで下記コマンドを実行する。

  mysql> use テーブルを作成するデータベース名;
  mysql> insert into テーブル名 
      -> (カラム名1, カラム名2, カラム名3)
      -> values ('カラム名1に格納したい値', 'カラム名2に格納したい値', 'カラム名3に格納したい値')
      -> ;