Skip to main content
Unix & Linux

Return to Answer

improved comments.
Source Link
cas
cas
  • 84.1k
  • 9
  • 136
  • 205
#! /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.
###

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# 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;

It looks longer than the average quick shell script, but most of the code is just variable and database handle setup (and that setup code can be re-used in other, similar scripts). The actual code that does the work is (excluding comments) only about half a dozen or so lines.

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

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# NOTE: this 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 ...) ];
#};

my $c_sth = $c_h->prepare("select phonenum from ?");
# and execute it.
$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;

It looks longer than the average quick shell script, but most of the code is just variable and database handle setup (and that setup code can be re-used in other, similar scripts). The actual code that does the work is only about half a dozen or so lines.

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

It looks longer than the average quick shell script, but most of the code is just variable and database handle setup (and that setup code can be re-used in other, similar scripts). The actual code that does the work is (excluding comments) only about half a dozen or so lines.

added transaction start and end, and clean finish/disconnect.
Source Link
cas
cas
  • 84.1k
  • 9
  • 136
  • 205
#! /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.
###

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# NOTE: this 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 ...) ];
#};

my $c_sth = $c_h->prepare("select phonenum from ?");
# and execute it.
$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;

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

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# NOTE: this 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 ...) ];
#};

my $c_sth = $c_h->prepare("select phonenum from ?");
# and execute it.
$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 = ?');

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

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

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# NOTE: this 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 ...) ];
#};

my $c_sth = $c_h->prepare("select phonenum from ?");
# and execute it.
$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;
Source Link
cas
cas
  • 84.1k
  • 9
  • 136
  • 205

While you can do mysql queries/updates/etc in a shell script, it's far easier to use a language (like perl or python) with good support for both databases and CSV files.

Here's one way of doing it in perl, using the perl DBI module, and the DBD::CSV and DBD::mysql modules.

It reads each row from your CSV file (i've called it 'updates.csv' and assumed the column name is phonenum) and issues the SQL UPDATE commands for users table of the dbname database in mysql. Change dbname to suit your database.

NOTE: the following code is untested but it should work. As it's untested it's possible I made a few typos or other mistakes.

I strongly recommend testing it first on a COPY of your database rather than immediately running it on the real data. In fact, it's ALWAYS a good idea to test your code on a copy of the real data, no matter what you're writing or what language you're writing it in.

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

# prepare statement handle for csv db query using a placeholder ? for the
# column name.
#
# NOTE: this 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 ...) ];
#};

my $c_sth = $c_h->prepare("select phonenum from ?");
# and execute it.
$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 = ?');

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

It looks longer than the average quick shell script, but most of the code is just variable and database handle setup (and that setup code can be re-used in other, similar scripts). The actual code that does the work is only about half a dozen or so lines.