☰

csvファイルからデータをインポートし、データベースで関連タスクを実行するスクリプト

tag-icon • tag-icon shell-script mysql csv
csvファイルからデータをインポートし、データベースで関連タスクを実行するスクリプト

私はシェルスクリプティングの世界が初めてで、シェルスクリプティングの知識はあまりありません。私の仕事では、CSVファイルに保存されている履歴リストから電話番号を取得するスクリプトを作成する必要があります。

各電話番号に対してデータベース内のユーザーテーブルを検索し、一致するものがある場合は、電話番号の前に「A2B1」を追加して電話番号列を更新します。

たとえば、「456789」などの電話番号履歴がその履歴を含むcsvファイルのデータベースに見つかった場合、電話番号列は「A2B1 456789」に更新されます。

私は次のアプローチを考えました。まず、「cut」コマンドを使用してCSVファイルの各行から2番目の列を削除します。 （ただし、各行の2番目の列の値を変数に格納してSQLクエリステートメントで使用できるようにする方法がわかりません。）データベースリンクを作成し、SQLクエリステートメント/クエリを作成します。

その後、いくつかのレコードが返されると、上記の更新が行われます。

これを行う方法やシェル言語で書く方法がわかりません。私はクエリ出力をファイルにパイプし、ファイルサイズが0であるかどうかを確認する方法を考えています。それ以外の場合は、ファイルから変数を取得します（クエリから返されたレコード/電話番号）。その後、変数に保存して更新を実行しますが、これが良いアプローチであるかどうか疑問に思いました。皆様のご支援を心よりお待ちしております

答え1

時できるデータベースとCSVファイル（orなど）の両方をうまくサポートする言語では、シェルスクリプトでクエリ/更新などを実行する方がはるかにmysql簡単です。perlpython

perlPerlDBIモジュールとDBD::CSVandモジュールを使用してDBD::mysqlこれを行う1つの方法は次のとおりです。

これはCSVファイルから各行を読み取り（「updates.csv」と呼び、列名は次のように見なされます）、mysqlのデータベーステーブルに対してphonenumSQLコマンドを実行します。UPDATEデータベースに合わせて変更してください。usersdbnamedbname

注：次のコードはテストされていませんが、機能します。まだテストしていないので、太田やその他の間違いがあるかもしれません。

私強く最初に行くことをお勧めしますコピー実際のデータからすぐに実行する代わりにデータベースを使用します。実はそうですいつも作成している内容やコード化する言語に関係なく、実際のデータコピーでコードをテストすることをお勧めします。

#! /usr/bin/perl

use strict;
use DBI;

### 
### variables setup
### 

# DBD::CSV treats all .csv files in this dir as tables.
# i.e. this directory is the "database" and the .csv files
# are the tables in that database.
my $csv_dir = '/path/to/csv/dir'; 

my $csv_db  = 'updates';    # corresponds to "$csv_dir/updates.csv"

my $m_db    = 'dbname';     # replace with your mysql database name 
my $m_user  = 'username';
my $m_pass  = 'password';
my $m_host  = 'localhost';
my $m_port  = '3306';
my $m_dsn   = "DBI:mysql:database=${m_db};host=${m_host};port=${m_port}";

###
### database handle setup
###

# database handle for CSV connection
my $c_h = DBI->connect ("DBI:CSV:", undef, undef, {
               f_ext      => ".csv/r",
               f_dir => $csv_dir,
               RaiseError => 1,
               }) or die "Cannot connect: $DBI::errstr";


# database handle for mysql connection
my $m_h = DBI->connect($m_dsn, $m_user, $m_pass, { PrintError => 0 });

###
### all set up, time to do some work.
###

# NOTE: this script assumes that the .csv file contains a header line with
# the field names as the first line of the file.
#
# If not, the easiest thing to do is edit it with your preferred text
# editor and add one.  Otherwise, see `man DBD::CSV` to find out how to
# specify field names.
#
# or EDIT and uncomment the following three lines of code:

#$c_h->{csv_tables}{$csv_db} = { 
#  col_names => [ qw(column1 phonenum column3 column4 ...) ];
#};

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
my $c_sth = $c_h->prepare("select phonenum from ?");

# and execute it.  later, we'll use a forech loop to read the data returned
$c_sth->execute($csv_db);

# prepare the SQL statement for the mysql db using placeholders ? for
# the values. this assumes that the column/field name is also called
# 'phonenum' in mysql.  These placeholders are invaluable, they automaticaly
# quote any data that needs to be quoted (e.g. strings) while not quoting
# things that shouldn't be quoted (e.g. integers).  They prevent a huge
# range of common mistakes.
#
# prepare it once, execute it multiple times with different values.

my $m_sth = $m_h->prepare('UPDATE users SET phonenum = ? WHERE phonenum = ?');

$m_h->begin_work;  # begin transaction

foreach ($c_sth->fetchrow_array) {
   chomp;
   my $newphone = "A2B1 $_";
   $m_sth = $m_sth->execute($newphone, $_);
};

$m_h->commit;  # commit transaction

### 
### we're done.  finish the statement handles and disconnect from
### the databases.
###
$c_sth->finish;
$m_sth->finish;
$c_h->disconnect;
$m_h->disconnect;

一般的なクイックシェルスクリプトより長く見えますが、ほとんどのコードは変数とデータベースハンドルの設定にすぎません（その設定コードは他の同様のスクリプトで再利用できます）。アクションを実行する実際のコード（コメントを除く）は約6行です。

答え2

次のスクリプトを使用できます。

#!/bin/bash
PREFIX="A2B1 "
TABLE="sqltablename"
COLUMN="sqlcolumnname"
if [[ ! -r "$1" ]]; then
   echo "unable to read file '$1'"
   exit 1
fi

cut -d, -f2 "$1" | while read phonenum; do
   newnum="$PREFIX $phonenum"
   echo "UPDATE $TABLE SET $COLUMN = '$newnum' WHERE $COLUMN = '$phonenum';"
done

たとえば、CSVファイルを引数として実行すると、説明に従って./script.sh /path/to/mydata.csvデータを更新する一連のSQL文が出力されます。正しい表と列名を使用するようにスクリプトを変更します。

必要なステートメントが提供されていることを確認したら、それを目的のSQLエンジンにパイプしたり、任意の方法で実行できるSQLファイルに出力を保存したりできます./script.sh /path/to/mydata.csv > /path/to/updatephonenumbers.sql。

関連情報