Um script para capturar dados do arquivo csv e realizar operações relacionadas em um banco de dados

Question

Eu sou novo no mundo dos scripts de shell e não tenho muita idéia do shell script. Meu trabalho exige que eu escreva um script que leva os números de telefone de uma lista de registros armazenados em um arquivo CSV.

Para cada número de telefone, ele pesquisa na tabela de usuários no banco de dados e, se encontrar uma correspondência, atualiza a coluna do número de telefone adicionando "A2B1" à frente do número de telefone.

Por exemplo, se um registro no banco de dados for encontrado para um número de telefone como "456789" no arquivo csv contendo registros, a coluna do número de telefone será atualizada como "A2B1 456789".

Pensei na seguinte maneira: Primeiro, usarei o comando "cut" para obter a segunda coluna de cada linha do arquivo CSV. (No entanto, eu não sei, como armazenar o valor na segunda coluna em cada linha em uma variável, para que eu possa usá-lo em uma instrução de consulta SQL.) Crie um link db e escreva a instrução / consulta de pesquisa SQL .

Então, se algum registro for retornado, a atualização mencionada acima será feita.

Eu não sei como fazer isso ou escrevê-lo na linguagem shell. Eu tentei pensar em uma maneira de transmitir a saída de uma consulta para um arquivo e verificar se o tamanho do arquivo é zero e se não levar a variável no arquivo (que é o registro / número de telefone) retornado pela consulta, então armazene-o na variável e realize a operação de atualização mas duvido que este seria um bom caminho e além disso, dada a minha infância de dois dias neste campo estou não confiante sobre a sintaxe também. Ansiosamente esperando por sua ajuda

    

Answer 1

Você pode usar este script:

#!/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

Isto, se você executá-lo com o seu arquivo CSV como um parâmetro (por exemplo, ./script.sh /path/to/mydata.csv ), gerará uma série de instruções SQL que atualizarão os dados conforme você descreve. Modifique o script para usar o nome correto da tabela e da coluna.

Depois de verificar se ele fornece as instruções desejadas, você poderá canalizá-lo para o mecanismo SQL de sua escolha ou salvar a saída em um arquivo SQL que poderá ser executado da maneira que preferir com ./script.sh /path/to/mydata.csv > /path/to/updatephonenumbers.sql .

    

Answer 2

Enquanto você pode fazer mysql consultas / atualizações / etc em um script de shell, é muito mais fácil usar um idioma (como perl ou python ) com um bom suporte para os dois bancos de dados e arquivos CSV.

Aqui está uma maneira de fazer isso em perl , usando o módulo perl DBI e os módulos DBD::CSV e DBD::mysql .

Ele lê cada linha do seu arquivo CSV (denominei 'updates.csv' e assumiu que o nome da coluna é phonenum ) e emite os comandos UPDATE do SQL para a tabela users do dbname banco de dados no mysql. Altere dbname para se adequar ao seu banco de dados.

NOTA: o código a seguir não foi testado, mas deve funcionar. Como não foi testado, é possível que eu tenha cometido alguns erros de digitação ou outros erros.

Eu strongmente recomendo testá-lo primeiro em uma COPY do seu banco de dados em vez de executá-lo imediatamente nos dados reais. Na verdade, é SEMPRE uma boa ideia testar seu código em uma cópia dos dados reais, não importa o que você esteja escrevendo ou em que idioma você está escrevendo.

#! /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;

Parece mais longo que o script shell médio, mas a maior parte do código é apenas variável e a configuração do identificador de banco de dados (e esse código de configuração pode ser reutilizado em outros scripts semelhantes). O código real que faz o trabalho é (excluindo comentários) apenas cerca de meia dúzia de linhas.