☰

csv 파일에서 데이터를 가져오고 데이터베이스에서 관련 작업을 수행하는 스크립트

tag-icon • tag-icon shell-script mysql csv
csv 파일에서 데이터를 가져오고 데이터베이스에서 관련 작업을 수행하는 스크립트

저는 쉘 스크립팅의 세계가 처음이고 쉘 스크립팅에 대한 지식이 많지 않습니다. 내 직업에서는 CSV 파일에 저장된 기록 목록에서 전화번호를 가져오는 스크립트를 작성해야 합니다.

각 전화번호에 대해 데이터베이스의 사용자 테이블을 검색하고 일치하는 항목이 있으면 전화번호 앞에 "A2B1"을 추가하여 전화번호 열을 업데이트합니다.

예를 들어, "456789"와 같은 전화번호 기록이 해당 기록이 포함된 csv 파일의 데이터베이스에서 발견되면 전화번호 열은 "A2B1 456789"로 업데이트됩니다.

나는 다음과 같은 접근 방식을 생각해 냈습니다. 먼저 "cut" 명령을 사용하여 CSV 파일의 각 행에서 두 번째 열을 제거하겠습니다. (단, 각 행의 두 번째 컬럼의 값을 변수에 저장하여 SQL 쿼리문에서 사용할 수 있게 하는 방법을 모르겠습니다.) 데이터베이스 링크를 생성하고, SQL 검색문/쿼리를 작성합니다.

그런 다음 일부 레코드가 반환되면 위에서 언급한 업데이트가 수행됩니다.

이 작업을 수행하는 방법이나 쉘 언어로 작성하는 방법을 모르겠습니다. 쿼리 출력을 파일로 파이프하고 파일 크기가 0인지 확인하는 방법을 생각하고 있습니다. 그렇지 않은 경우 파일에서 변수를 가져옵니다(쿼리에서 반환된 레코드/전화 번호). 그런 다음 변수에 저장하고 업데이트 작업을 수행하지만 이것이 좋은 접근 방식인지 의심스럽고 이 분야에서 이틀 동안 초보였기 때문에 구문에 대해서도 확신이 없습니다. 여러분의 도움을 간절히 기다리고 있습니다

답변1

때를할 수 있는데이터베이스와 CSV 파일(예: or)을 모두 잘 지원하는 언어를 사용하면 쉘 스크립트에서 쿼리/업데이트 등을 수행하는 것이 mysql훨씬 쉽습니다 .perlpython

perlPerl DBI모듈과 DBD::CSVand 모듈을 사용하여 DBD::mysql이를 수행하는 한 가지 방법 은 다음과 같습니다 .

이는 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.

관련 정보