CSVファイルのインポートエラーを防ぐための前処理方法

現在、Oracle Cloud上でOSがOracle Linux 9.4のインスタンスでDrupalを運用しています。サーバーのOS情報を確認するためには、以下のコマンドを実行します。

cat /etc/os-release

このコマンドを実行すると、サーバーのOSに関する詳細な情報が表示されます。以下は実行結果の一例です。

NAME="Oracle Linux Server"
VERSION="9.4"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="9.4"
PLATFORM_ID="platform:el9"
PRETTY_NAME="Oracle Linux Server 9.4"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:9:4:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 9"
ORACLE_BUGZILLA_PRODUCT_VERSION=9.4
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=9.4

上記の情報は、システムがOracle Linux Serverのバージョン9.4であることや、Oracleが提供するサポートやバグ報告のリンクが含まれていることを示しています。また、ID_LIKE="fedora" という行は、このLinuxディストリビューションがFedoraに類似していることを意味します。このようなOSの詳細は、トラブルシューティングや環境構築時に役立つことがあります。

次に、Drupalにはデータベースが必要であり、そのためにMySQLを使用しています。MySQLがインストールされていない場合、以下の手順でインストールします。

MySQLのインストール

まず、以下のコマンドを実行してMySQL Community Serverをインストールします。

sudo dnf install mysql-server

その後、MySQLサーバーを起動します。

sudo systemctl start mysqld

補足説明
「このコマンドは、MySQLのサービスを手動で開始するものです。サーバーを再起動しても自動的にMySQLが起動するようにするには、次のコマンドを使ってサービスを有効にします。」

sudo systemctl enable mysqld

MySQL Workbenchを使用したデータベース管理

通常はターミナルを使用してMySQLに接続しますが、今回はMySQL WorkbenchというGUIツールを使用してデータベースを簡単に管理します。MySQL Workbenchは、コマンドラインに不慣れな方でも視覚的に操作できるため、特に初心者にとって便利です。


MySQLへの接続とデータベース確認

MySQLに接続するには、以下のコマンドを使用します。

mysql -u root -p

接続後、以下のコマンドでMySQLサーバー上のすべてのデータベースを表示します。

show databases;

補足説明
「次に、MySQLに接続した後、以下のコマンドを使用して現在存在するデータベースの一覧を表示します。サーバーにあるすべてのデータベースが表示されます。」

+--------------------+
| Database |
+--------------------+
| drupaldb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.11 sec)

「外部からの接続ですので、事前に準備が必要です。OS自体のファイアウォールでポート番号3306のトラフィックを許可しておきます。以下のコマンドを実行して、現在のファイアウォール設定を確認します。」

sudo firewall-cmd --list-all

「ポート3306を開放するためには、以下のコマンドを使用します。」

sudo firewall-cmd --add-port=3306/tcp --permanent

「設定を有効にするために、以下のコマンドでファイアウォールを再読み込みします。」

sudo firewall-cmd --reload

再度状態を確認すると、以下のように表示され、ポートが開放できていることが確認できます。特に、ports: 3306/tcp という部分が表示されていることを確認してください。

public (active)
target: default
icmp-block-inversion: no
interfaces: enp0s3
sources:
services: dhcpv6-client ssh
ports: 3306/tcp
protocols:
forward: yes
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:

さらに、Oracle Cloudのセキュリティリストにあるイングレスルールにも、外部接続を許可する設定が必要です。これは、クラウド上で稼働しているサーバーが外部からの接続をブロックしないようにするための設定です。具体的には、MySQLが使用するポート番号3306へのトラフィックを許可するように設定します。

イングレスルールの設定方法

  1. Oracle Cloudのダッシュボードにログインします。
  2. 左側のメニューから「ネットワーク」を選択し、「セキュリティリスト」をクリックします。
  3. あなたのインスタンスに適用されているセキュリティリストを選択し、「イングレスルールの追加」をクリックします。
  4. 次のようにルールを設定します。
    • ソースCIDR0.0.0.0/0(すべてのIPアドレスを許可する場合。ただし、セキュリティ面では制限することが推奨されます。特定のIPアドレスを許可する場合は、そのIPアドレスを入力してください)
    • プロトコルTCP
    • ポート範囲3306
  5. 設定を保存します。

これで、MySQLのデフォルトポート3306への外部からの接続が許可されるはずです。

MySQL Workbenchでの接続

次に、MySQL Workbenchを使用して、MySQLに接続します。MySQL Workbenchは、データベースを視覚的に管理できるツールで、特に初心者の方にはコマンドラインよりも使いやすいです。

  1. MySQL Workbenchを起動します。
  2. 新しい接続を作成し、次の情報を入力します。
    • ホスト名:Oracle CloudのパブリックIPアドレス(例:123.45.67.89
    • ポート番号3306
    • ユーザー名:MySQLで作成したユーザー名(例:root
    • パスワード:MySQLユーザーのパスワード

その後、「接続」ボタンを押して接続を試みます。

エラーが発生した場合

「接続できません」というエラーメッセージが表示される場合、次の手順で原因を確認します。まず、MySQLに接続できるユーザーの一覧を確認しましょう。

  1. ターミナルを開き、MySQLに接続します。
    mysql -u root -p
  2. 接続後、次のコマンドを実行してユーザー一覧を確認します。
    select user, host from mysql.user;

このコマンドを実行すると、以下のように表示されます。

+------------------+-----------+
| user | host |
+------------------+-----------+
| mamushi | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+

問題の原因

この結果からわかるように、すべてのユーザーが localhost からしか接続できない設定になっています。これは、MySQLがローカル接続しか許可していないため、外部からの接続ができないことが原因です。localhost とは、サーバー自身を指すため、サーバー内からしか接続できない設定です。

MySQLのデフォルト設定では、外部から接続できるユーザーは作成されていないため、新しく外部から接続できるユーザーを追加する必要があります。これにより、MySQL Workbenchなどのツールを使ってリモートからデータベースに接続できるようになります。

外部から接続できるユーザーの追加方法

  1. MySQLに接続
    まず、ターミナルを開いてMySQLに接続します。rootユーザーで接続するため、以下のコマンドを実行します。
    mysql -u root -p
    このコマンドを実行すると、パスワードを求められます。rootユーザーのパスワードを入力し、接続します。
  2. 新しいユーザーを作成
    次に、外部から接続できる新しいユーザーを作成します。このユーザーには、任意のパスワードを設定できます。以下のコマンドで、workbenchという名前のユーザーを作成し、どこからでも接続できるようにします。
    CREATE USER 'workbench'@'%' IDENTIFIED BY 'Password123#';
    解説
    • 'workbench'@'%'%は、任意のIPアドレスからの接続を許可することを意味します。つまり、どこからでも接続できるユーザーが作成されます。
    • 'Password123#' はユーザーのパスワードです。このパスワードは、強力なものに変更することを推奨します。
  3. ユーザーに権限を付与
    作成したユーザーに、データベースを操作できる権限を付与します。以下のコマンドで、すべてのデータベースとテーブルに対するフルアクセスを許可します。
    GRANT ALL ON *.* TO 'workbench'@'%';
    解説
    • GRANT ALL は、すべての権限を付与することを意味します。これにより、ユーザーは既存のデータベースを操作したり、新しいデータベースを作成したりすることが可能になります。
    • *.* は、すべてのデータベースとテーブルに対して権限を付与するという意味です。
  4. セキュリティの注意
    セキュリティが心配な場合、% の部分を特定のIPアドレスに置き換えることで、特定の場所からのみ接続できるように制限することができます。たとえば、接続元のIPアドレスが 192.168.1.100 の場合、以下のように変更します。
    CREATE USER 'workbench'@'192.168.1.100' IDENTIFIED BY 'Password123#';
    GRANT ALL ON *.* TO 'workbench'@'192.168.1.100';
    解説
    • この設定により、192.168.1.100 というIPアドレスからのみ workbench ユーザーが接続できるようになります。セキュリティを強化したい場合には、このようにIPアドレスを指定することを推奨します。
  5. 権限の反映
    権限を付与した後、以下のコマンドを実行して、権限が正しく反映されるようにします。
    FLUSH PRIVILEGES;
    解説
    • FLUSH PRIVILEGES コマンドは、MySQLに変更を反映させるために実行します。

MySQL Workbenchでの接続再試行

これで、workbenchユーザーを使って外部からMySQLに接続できるはずです。再度MySQL Workbenchで接続を試みます。

  1. MySQL Workbenchを起動し、新しい接続を作成します。
  2. 必要な情報を入力します。
    • ホスト名:Oracle CloudのパブリックIPアドレス(例:123.45.67.89
    • ポート番号3306
    • ユーザー名workbench
    • パスワードPassword123#
  3. 「接続」ボタンをクリックし、接続を試みます。

接続が成功したら、外部からMySQLデータベースに接続できていることになります。


この手順で新しいユーザーを作成し、外部からMySQLにアクセスできるように設定しました。セキュリティ面で気を付けるべき点として、% を使ってどこからでも接続できるようにする場合は、必ず強力なパスワードを設定し、必要に応じてOracle CloudやOS自体のファイアウォールでIPアドレスを制限することをお勧めします。

DockerでのMySQL外部接続について

通常、サーバーにMySQLをインストールした場合は、外部からの接続がデフォルトでは許可されておらず、手動で設定を行う必要があります。しかし、Docker環境では、MySQLコンテナを作成するときに適切なポートマッピングを設定すれば、デフォルトで外部接続が可能になります。つまり、Dockerを使うことで、外部からの接続が簡単に許可されます。

DockerでMySQLを外部接続可能にする方法

  1. MySQLコンテナの作成
    DockerでMySQLコンテナを作成する際に、外部からの接続を許可するには、以下のようにポートをマッピングします。
    docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=Password123# -p 3306:3306 -d mysql:latest
    解説
    • -p 3306:3306 というオプションは、ホストマシンのポート3306をコンテナ内のMySQLのポート3306にマッピングすることを意味します。これで、ホストマシンの3306ポートを通して外部からMySQLにアクセスできるようになります。
    • MYSQL_ROOT_PASSWORD=Password123# で、rootユーザーのパスワードを設定します。このパスワードは、後で外部からMySQLに接続する際に使用します。
  2. MySQL Workbenchでの接続
    MySQL WorkbenchなどのGUIツールを使用して、外部からコンテナ内のMySQLに接続できます。接続情報は次の通りです。
    • ホスト名:ホストマシンのIPアドレス(例:123.45.67.89
    • ポート番号3306
    • ユーザー名root
    • パスワード:コンテナ作成時に設定したパスワード(例:Password123#
    これで、外部からMySQLコンテナに接続できるはずです。

Dockerと通常のMySQLインストールの違い

通常のインストール
通常、MySQLをサーバーにインストールすると、外部からの接続はデフォルトでは許可されていません。ファイアウォールの設定や、MySQLのユーザー設定を手動で行う必要があります。

Dockerの場合
Dockerでは、コンテナを作成する際にポートマッピングを設定するだけで、外部接続が可能になります。これは、初心者にとっては非常に便利で、手間を省くことができます。

セキュリティについての注意

Docker環境でも、外部から接続が容易であるため、必ず強力なパスワードを設定し、必要に応じてファイアウォールやOracle Cloudのセキュリティリストでアクセスを制限することが重要です。外部接続を許可する場合は、常にセキュリティを意識して設定を行うことが推奨されます。


Docker環境でのMySQLは、ポートマッピングを設定するだけで外部接続がデフォルトで許可されるため、通常のインストールと比べて簡単に外部からアクセスできるようになります。初心者にとっても、Dockerを利用することでMySQLの設定が容易になる利点がありますが、セキュリティ面には十分配慮して設定を行いましょう。

次は、データベースを作成してCSVファイルをインポートします。

新規で作成したデータベースの上で右クリックして、「Table Data Import Wizard」を選択します。
インポート完了後にテーブルを展開して「Select Rows」をクリックすると中のデータを確認することができます。

その際、Workbenchに表示されている構文はターミナルに貼り付けて表示することができます。

SELECT * FROM test.`9984`;

CSVファイルのインポート前にテーブルを定義する重要性

CSVファイルをMySQLにインポートする際には、まずインポート先となるテーブルの構造を定義しておくことが重要です。テーブル構造が定義されていない場合、インポート時にエラーが発生することがあります。特に、CSVファイルの列がデータベースのカラムに正しく対応しているかどうかがポイントです。

1. テーブルの作成

MySQL Workbenchを使用して、まずテーブルを作成します。次のSQL文をWorkBenchで実行して、新しいテーブルを作成します。このテーブルはCSVファイルの列に対応するように定義されています。

CREATE TABLE s20240913 (
ID INT AUTO_INCREMENT PRIMARY KEY,
コード VARCHAR(255),
現在日付 DATE,
現在値時刻 TIME,
銘柄名称 VARCHAR(255),
現在値 DECIMAL(10,2),
出来高 BIGINT,
売買代金 BIGINT,
時価総額 BIGINT,
逆日歩 DECIMAL(10,2),
信用倍率 DECIMAL(10,2),
貸借倍率 DECIMAL(10,2),
権利落日 VARCHAR(255),
決算発表日 VARCHAR(255),
PER DECIMAL(10,2),
PBR DECIMAL(10,2),
市場部名称 VARCHAR(255)
);

補足

  • 各カラムがCSVファイル内の列に対応しており、データ型が適切に設定されています。
  • ID カラムには、自動的に番号が割り振られるように AUTO_INCREMENT を設定しています。
  • 日付や数値のデータ型(DATE, DECIMAL, BIGINT など)も適切に設定しています。

2. CSVファイルのインポート手順

次に、MySQL Workbenchを使ってCSVファイルをインポートします。

  1. インポートするテーブルの選択
    作成したテーブルの上で右クリックして、「Table Data Import Wizard」を選択します。
  2. CSVファイルの選択
    インポートするCSVファイルを選択します。
  3. カラムマッピングの確認
    CSVファイルの各列と、テーブルのカラムが正しく対応していることを確認します。特に、日付や数値などのデータ型が一致しているかに注意します。ここで適切に設定されていないと、インポート時にエラーが発生することがあります。

3. インポート後のデータ確認

インポートが完了したら、テーブルを展開して「Select Rows」をクリックし、データが正しくインポートされているか確認します。

ワンポイントアドバイス
MySQL Workbenchに表示される構文は、ターミナルで実行することもできます。インポート時にエラーが発生した場合は、エラーメッセージに従ってどの部分に問題があるか確認し、必要に応じてテーブルの定義やCSVファイルの形式を修正しましょう。

CSVインポートが失敗する理由とその対処法

CSVファイルをMySQLなどのデータベースにインポートする際、以下のような理由で失敗することがあります。

1. 空白セルや特定の記号が原因

CSVファイル内に空白のセルや、単独の「-」、長い連続する「#」などの記号があると、MySQLはそれを適切に処理できず、エラーが発生することがあります。これらの値は、データベース内で適切な形式に変換する必要があります。

2. 文字コードの問題(エンコーディング)

日本語のデータを含むCSVファイルは、文字コードがUTF-8Shift-JIScp932)でエンコードされていることが多いです。しかし、データベースがこれらの文字コードに対応していない場合、文字化けやインポートエラーが発生します。特に、MySQLはデフォルトでutf8mb4を使用することが多いので、CSVファイルを適切なエンコーディングに変換する必要があります。

スクリプトによる解決策

このスクリプトは、CSVファイルの内容を前処理して、インポートエラーを防ぐための非常に有効な手段です。このスクリプトが何をしているかを簡単に説明します。

スクリプトの説明

import csv
import re

# CSVファイルのパスを生文字列として指定
input_file = r'c:\temp\20240913.csv'
output_file = r'c:\temp\import.csv'

# 入力ファイルを開き、出力ファイルに書き込む
with open(input_file, mode='r', newline='', encoding='cp932') as infile, \
open(output_file, mode='w', newline='', encoding='cp932') as outfile:
reader = csv.reader(infile)
writer = csv.writer(outfile)

for row in reader:
# 空白セル、単独の「-」、または長い連続するハッシュ記号を 'NULL' で置換
new_row = ['NULL' if x.strip() == '' or x.strip() == '-' or re.match(r'^#+$', x.strip()) else x for x in row]
writer.writerow(new_row)

このスクリプトは、CSVファイルのインポート時にエラーが発生する原因となる特定の値(空白セルや「-」、連続した「#」)を'NULL'に置き換えます。これで、データベースにインポートする際にエラーを防ぎます。

  1. 空白セルや「-」の処理
    • CSVファイル内の空白セルや単独の「-」をデータベースにそのままインポートすると、エラーが発生する可能性があります。このスクリプトは、それらを'NULL'という値に置き換えて、データベースが適切に処理できるようにします。
  2. 連続する「#」の処理
    • もしCSVファイル内に連続した「#」がある場合、それも'NULL'に置き換えます。これは、これらの記号が意味のないデータであることが多いためです。
  3. 文字コードの設定
    • このスクリプトはcp932という文字コード(Shift-JISに基づく)を使用しています。もしデータベースがUTF-8を要求している場合、以下のようにutf-8に変更することができます。
    with open(input_file, mode='r', newline='', encoding='utf-8') as infile, \ open(output_file, mode='w', newline='', encoding='utf-8') as outfile:

CSVファイルをインポートする際のワンポイントアドバイス

  • エンコーディングに注意
    CSVファイルの文字コードがデータベースと一致しない場合、文字化けやインポートエラーが発生します。MySQLでは通常utf8mb4が推奨されるため、CSVファイルもUTF-8で保存することが推奨されます。
  • 空白や特殊文字に対する処理
    空白セルや特定の記号を'NULL'に置き換えることで、インポート時のエラーを防ぐことができます。特に大量のデータをインポートする場合、このような前処理は非常に重要です。

CSVファイルのインポートが失敗する主な原因は、空白セルや特定の記号、文字コードの不一致です。このスクリプトはこれらの問題を解決するための有効な手段であり、初心者でも簡単に実行できる方法です。インポートエラーを防ぐためには、まずCSVファイルの前処理を行い、必要に応じて文字コードを適切に設定することが重要です。

タイトルとURLをコピーしました