Sending and Managing Email Accounts in a MySQL Database using Perl

A friend of mine has a small newsletter that he sends via email once a month. Initially, he was just blind-copying everyone on his list, but as the list grew, it was a pain for him to keep manually editing his BCC list. Also, some people weren’t receiving the newsletter as they had email filters that would block emails that didn’t have their name in the “To” field. And, he needed a way to allow people to opt-out of his newsletter without having to contact him directly. His solution was that he wanted to use a MySQL database to send the email and manage his list, so that he could easily bypass the subscribers that had opted out.

We started by creating a MySQL table with just a few fields – ID, email_address and news_and_events. The “news_and_events” field would be set to “yes” for those that wanted to receive the newsletter, and could be set to “no” if they wanted to opt-out. Here is the database:

CREATE TABLE `email_address_test` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`email_address` varchar(50) NOT NULL,
`news_and_events` varchar(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

After he had imported all of the email addresses into the database (including setting the news_and_events column value to “yes”), we were ready to go. We are using a Perl script to connect to the MySQL database and to retrieve all of the email addresses that have a value of “news_and_events = yes”. We can then send each one an email with their email address in the “To” field. We also create a log file to store the results. To manage new subscribers, he modified his web site so that new email addresses would also be stored in the database. An earlier post shows you how to insert information into a MySQL database via Perl.

This script uses a text file to store the database connectivity information. (See this earlier post for instructions on how to use this text file).

This script also includes a “remove me” link (using a Perl script named remove_me.pl) in the email to allow people to opt-out of receiving future emails. I include the remove_me.pl script below the main script.

Here is the main script to send email. There are comments in the script to help you figure out how to modify the script for your use.

#!/usr/bin/perl -w use strict;
use DBI;

my $database;

# create a log file to store the results
open LOG, ">filename.txt" or die "Cannot create output file: $!";

my $id;
my $CurrentLine = 0;

     # connect to database

     $dbh = ConnectToMySql($database);

     $query = "select email_address, id from email_address_test where news_and_events = \'yes\'";

     $sth = $dbh->prepare($query);

     $sth->execute();

while (@data = $sth->fetchrow_array()) {
      $email_address = $data[0];
      $id = $data[1];

     # count the number of emails that we send
     $CurrentLine++;
     
# edit this line to match the location of your sendmail program
$sendmail = "/usr/sbin/sendmail -t";

# you will need to modify this information
$reply_to = "From: Scripting MySQL Blog <info\@return_email.com>\n";
$from = "From: Scripting MySQL Blog <info\@return_email.com>\n";
$subject = "Subject: Test email from MySQL\n";
$to = "To: $email_address\n";

# this is the body of the email - along with the unsubscribe script - remove_me.pl
$content = "<html><body>This is a test email.<p>" .

          "<a href=\"http:\/\/cgi-bin/remove_me.pl?id=$id&email_address=$email_address\">Remove me.<\/a>";
     
     open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
     print SENDMAIL $reply_to;
     print SENDMAIL $from;
     print SENDMAIL $subject;
     print SENDMAIL $to;
# use this line if you want to send a plain text email
#     print SENDMAIL "Content-type: text/plain\n\n";
     print SENDMAIL "Content-type: text/html\n\n";
     print SENDMAIL $content;
     close(SENDMAIL);
     
     # comment these line if you don't want output to your monitor
     print "$CurrentLine Email sent to $email_address - $subject";
     # comment this line if you don't want output to your log file
     print LOG "$CurrentLine Email sent to $email_address - $subject";

     # sleep X seconds after each email is sent
     sleep 1;
     
}

print "\nFinished...$CurrentLine emails were sent.\n";
print LOG "\nFinished...$CurrentLine emails were sent.\n";

close(LOG);

# for more information on this sub-routine and the use of the file accessBLOG, visit
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     $database = <ACCESS_INFO>;
     $host = <ACCESS_INFO>;
     $userid = <ACCESS_INFO>;
     $passwd = <ACCESS_INFO>;

     # the chomp() function will remove any newline character from the end of a string
     chomp ($database, $host, $userid, $passwd);

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

And here is the remove_me.pl script. I don’t provide any error-checking to make sure that the news_and_events value is equal to “yes”, so you will need to edit the script if you want this functionality.

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI qw(:standard);

my $database;

$query = new CGI;

$id = $query->param("id");
$email_address = $query->param("email_address");

print header;

# connect to the database and update news_and_events to "no"

     $dbh = ConnectToMySql($database);

     $query = "update email_address_test set news_and_events = 'no' where id = '$id'";
          
     $sth = $dbh->prepare($query);

     $sth->execute();
     
     $sth->finish;

     $dbh->disconnect;

print "Your email address $email_address has been removed.";

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

     open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

     my $connectionInfo="dbi:mysql:$database;$host";
     close(ACCESS_INFO);

     chomp($userid);
     chomp($passwd);
     # make connection to database
     my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
     return $l_dbh;

}

exit;

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Using MySQL to Import and Retrieve Blobs and Display as Image Files in HTML

I received a phone call from a friend of mine who had some questions about storing image files as blobs in MySQL. He wanted to design a web site that would allow someone to upload an image, and then that image would be stored in the MySQL database for viewing later. He stated that he was going to be storing thousands of images. I mentioned that it might be better to store the images on the file system, and then to just store the location of the image in the database. But, he still wanted to know how to do it and would decide which solution he would incorporate later.

I already had a Perl script that allowed me to upload files to a web site, as I would give out this URL to people that wanted to send me large files. And, I know that you can store images (and other files) in MySQL as a blob, but I wasn’t sure how to display the blob as an image in HTML (once I had retrieved it from the MySQL database). A search on Google provide bits and pieces of the solution, but the solution would only provide the ability to retrieve one image at a time. My friend wanted to be able to display multiple images on a single web page.

I figured out that I needed five things – a database to store the blob, a web page to upload the file into the database, a Perl script to retrieve the results of a search from the database, a Perl script to go back to the database and retrieve the information and blob (image) from a query, and finally a Perl script to display the blobs where an HTML img tag could “read” it. I tried to do it all with just three scripts (ignoring the last script), but I had a problem retrieving and displaying more than one image at a time from a single query. Google just wasn’t providing the answers.

First, I created a “blobs” database to store the images. The database consists of two columns – a serial_number (int, 10) and a mediumblob. In the scripts, I do not check to make sure that the image size is smaller than what is allowed by a mediumblob (16,777,215 bytes), but this could easily be added. Here is the create table statement:

CREATE TABLE `blobs` (
`serial_number` varchar(10) NOT NULL,
`blob_file` mediumblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Next, I needed a simple web page that will allow the user to upload an image file (and the database may also be used for any other type of file). This form will take the file and place it into the blobs MySQL database. For this example, I have hard-coded a serial number (1234567890) into the web form, but you could easily generate a unique serial number or pull an auto-increment value from the database.

<HTML>
<BODY> </BODY>
<title>Uploading A File</title>
<FORM ACTION="http://192.168.1.2/cgi-bin/mysql/blob_upload.pl" METHOD="post" ENCTYPE="multipart/form-data">
<table>
<tr>
<td>
<font face=verdana size=1 color=#003366> File to Upload:  
</td>
<td>
<INPUT size=70 TYPE="file" NAME="file">
</td>
</tr>
<td>
<input type=hidden name="serial_number" value="1234567890">
</td>
<td>
<INPUT TYPE="submit" NAME="Submit" VALUE="Submit Form">
</td>
</tr>
</table>
</FORM>
</BODY>
</HTML>

The web page sends the information to a Perl script named “blob_upload.pl”, which then places the information (serial number and image file) into the database. The script also creates a directory for today’s date, and then stores a copy of the image on the server in that directory. (For more information on using Perl to connect to MySQL, especially my use of the subroutine ConnectToMySql – see this earlier post. You could also insert the location of the file into another column in the database, but I chose to bypass that functionality for this example. Here is the script:

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI;
use POSIX 'strftime'; # Need to explicitly load the functions in POSIX

# Note that if you pass no argument to localtime, it assumes the current time
$Date = strftime '%Y-%m-%d', localtime;

$database = "scripting_mysql";
$upload_dir = "$Date\_uploaded_files";

#make directory unless it already exists
mkdir "$upload_dir", 0777 unless -d "$upload_dir";

$query = new CGI;

$filename = $query->param("file");
$serial_number = $query->param("serial_number");

print $query->header ( );

$filename =~ s/.*[\/\\](.*)/$1/;
$filename =~ s/ /_/g;
$upload_filehandle = $query->upload("file");
$directory_filename = "$upload_dir/$filename";

print "$filename $serial_number";

# upload the file to the server

open UPLOADFILE, ">$directory_filename";
binmode UPLOADFILE;

while ( <$upload_filehandle> )
{
    print UPLOADFILE;
}

close UPLOADFILE;

# Open the file
open MYFILE, $directory_filename || print "Cannot open file";
my $blob_file;

# Read in the contents
while (<MYFILE>) {
    $blob_file .= $_;
    }

close MYFILE;

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
    $dbh = ConnectToMySql($database);

    # set the value of your SQL query
    $query = "insert into blobs (serial_number, blob_file) values (?, ?) ";

    $sth = $dbh->prepare($query);
    $sth->execute($serial_number, $blob_file) || print "Can't access database";

    $sth->finish;
    $dbh->disconnect;

#----------------------------------------------------------------------

print " <TITLE>File Uploaded</TITLE>";

print " <font face=verdana size=2 color=#003366>Thanks for uploading your file!<br>";
print " <font face=verdana size=2 color=#003366>File directory: $Date\_uploaded_files<br>";
print " <font face=verdana size=2 color=#003366>Your file: $filename<br>";
print " <font face=verdana size=2 color=#003366>Your serial number: $serial_number<br>";

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
    my $database = <ACCESS_INFO>;
    my $host = <ACCESS_INFO>;
    my $userid = <ACCESS_INFO>;
    my $passwd = <ACCESS_INFO>;

    # the chomp() function will remove any newline character from the end of a string
    chomp ($database, $host, $userid, $passwd);

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}


Now we have a web page to upload the image files and a Perl script to store the image files. We just need a script to print out the serial number and the blob – but we need to print the blob out as an image in HTML by using the img tag. This is the tricky part. To accomplish this, we create another Perl script that simply “prints” the blob out in a way that an HTML img tag can read it. But first, here is a script to go to the database and retrieve the blob based upon a serial number (which is hard coded in this example). This script is titled “blob_view.pl”. In this script, we are sending a query to the MySQL database to retrieve the image based upon the serial number. But, the script has a “while loop” so that you can bring back more than one image if you desire – and in this loop we call the fourth script – get_blob.pl – to bring each blob back one at a time and to display it inside the HTML img tag. Here is the script to retrieve the image with the same serial number from the web page (hard coded – serial number 1234567890):

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI qw(:standard);

print header;
print "<html><body>Here is your picture:<p>";

$dbh = ConnectToMySql($Database);

$query = "select serial_number from blobs where serial_number = '1234567890'";

$sth = $dbh->prepare($query);

$sth->execute();

while (@data = $sth->fetchrow_array()) {
$serial_number = $data[0];

print "<p>$serial_number <p> <img width=200 src=\"get_blob.pl?sn=$serial_number\"><br>get_picture.pl?sn=$serial_number";

}

$sth->finish;
$dbh->disconnect;

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

chomp($userid);
chomp($passwd);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

The key here is to use another script named get_blob.pl that only returns the image, which allows us to use an HTML img tag to display the image. You pass the serial number to get_blob.pl as a variable named “sn”, and it retrieves the blog so that the img tag can display it. There are other ways in Perl to display a single image without using another script, but I wanted this to be able to handle multiple images from a single query. If I changed the query to something like “$query = “select serial_number from blobs where serial_number like ’123456789%’”;”, it would retrieve more than one row (in my case), and print each image separately. So, you will need this script as well to retrieve the images:

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI;
#use CGI qw(:standard);

$query = new CGI;

$serial_number = $query->param("sn");

     $dbh = ConnectToMySql($Database);

     $query = "select serial_number, blob_file from blobs where serial_number = '$serial_number'";
     
$sth = $dbh->prepare($query);

$sth->execute();

while (@data = $sth->fetchrow_array()) {
$serial_number = $data[0];
$blob_file = $data[1];
print STDOUT "Content-Type: image/jpeg\n";
print STDOUT "Content-length: \n\n";
binmode STDOUT;
print STDOUT $blob_file;

}

     $sth->finish;
     $dbh->disconnect;

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

chomp($userid);
chomp($passwd);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

This may seem like a lot of code, and I am sure that some Perl experts will show me what I am doing wrong (for example, I didn’t use strict or warnings), or a way to do it with less code. But I like to write this code so that a novice Perl user will be able to (hopefully) figure out what I am doing and be able to incorporate it into their project. I know that Google didn’t provide me with an easy answer, so hopefully this will help someone with their next project.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Splitting a MySQL Dump File Into Smaller Files Via Perl

I was trolling the MySQL forums web site and noticed a post regarding someone who was trying to load a 50-gigabyte MySQL dump file. The author of the post had stated that the loading of the file had been running for days, and was wondering if there was a better way to import the file. They did not have access to anything else (i.e. – original database) but this file.

I have had to restore several databases in the past from a single large MySQL dump file – which led me to start backing up each database individually. These databases are for my own personal use and are not updated that often, so I don’t need to have point-in-time recovery – and so a MySQL dump works just fine. If I had a production system, I would invest in the MySQL Enterprise Backup and the MySQL Enterprise Monitor applications.

But, I have had problems when I tried to restore a relatively small MySQL instance of 21 databases with a total file size of less than 900 megabytes. So, I wrote a Perl script to simply split the large MySQL dump file into smaller files, which I could then load one at a time to isolate the problem. I am sure that there are many other ways of troubleshooting this person’s problem, but I thought I would dig out this script and post it here. This script is also handy for splitting any text file that you have into smaller files. All you would have to do is to change the keywords that causes a new file to be created.

The script opens the MySQL dump file, and starts outputting each line to a new file, looking for the keywords “– Current Database: “. When the script sees those keywords, it closes the last file and opens a new one, and then prints that line with the keywords out to the new file until the keyword appears again. The script names each new file with a sequential number – as in database_01.sql, database_02.sql, etc. The first file that this script creates (named database_00.sql) contains the header information from the MySQL dump file, and this information is included in each of the subsequent database files (thanks to Nick Miles for pointing this out – see comments).

I tested this script on an 850-megabyte MySQL dump file, and it worked perfectly (and executed in less than one minute). To make sure that your new files contain the same amount of data as the original file, you can use the Unix command “wc” to show you the size of each file, and then you can compare the sum of these sizes to the size of your MySQL dump file. I first ran the script, and it produced the following 22 files:

sh-3.2# /usr/bin/perl split_file.pl
2011-09-13 21:24:23 - Creating file database_00.sql
2011-09-13 21:24:23 - Creating file database_01.sql
2011-09-13 21:24:23 - Creating file database_02.sql
2011-09-13 21:24:23 - Creating file database_03.sql
2011-09-13 21:24:23 - Creating file database_04.sql
2011-09-13 21:24:23 - Creating file database_05.sql
2011-09-13 21:24:23 - Creating file database_06.sql
2011-09-13 21:24:23 - Creating file database_07.sql
2011-09-13 21:24:23 - Creating file database_08.sql
2011-09-13 21:24:23 - Creating file database_09.sql
2011-09-13 21:24:23 - Creating file database_10.sql
2011-09-13 21:24:23 - Creating file database_11.sql
2011-09-13 21:24:23 - Creating file database_12.sql
2011-09-13 21:24:23 - Creating file database_13.sql
2011-09-13 21:24:23 - Creating file database_14.sql
2011-09-13 21:24:24 - Creating file database_15.sql
2011-09-13 21:24:24 - Creating file database_16.sql
2011-09-13 21:24:24 - Creating file database_17.sql
2011-09-13 21:24:24 - Creating file database_18.sql
2011-09-13 21:24:24 - Creating file database_19.sql
2011-09-13 21:24:24 - Creating file database_20.sql
2011-09-13 21:25:14 - Creating file database_21.sql

Here are the sizes of the individual database*.sql files, as well as the original source file named all_data.sql:

sh-3.2# wc database*sql
sh-3.2# wc database*.sql
         18      66     807 database_00.sql
         51     175    1676 database_01.sql
        413   18379 1135180 database_02.sql
        596  101335 5281418 database_03.sql
        571   14161  367113 database_04.sql
        571   10116  264555 database_05.sql
         26      87     943 database_06.sql
         88    2849   38786 database_07.sql
        565   11121  411554 database_08.sql
        338   10954  427717 database_09.sql
         92     403    7529 database_10.sql
        300   12690  326553 database_11.sql
        273    1929   43345 database_12.sql
         62     218    2143 database_13.sql
        75   82987 13049334 database_14.sql
        755   74021  909296 database_15.sql
         62     324    3775 database_16.sql
        209  535852 8778365 database_17.sql
        147    2391   37442 database_18.sql
         26      87     946 database_19.sql
    1243 28094397 826796485 database_20.sql
        333  134098 5402798 database_21.sql
    6814 29108640 863287760 total

sh-3.2# ls -l all_data.sql
-rw-rw-rw- 1 tonydarnell admin 863270813 Sep 13 20:23 all_data.sql

As you can see, the total size of the database*.sql files is 863287760, and the size of our original MySQL dump file (all_data.sql) is 863270813. But, we have to take into account that we added the header information to each file. The header file size is 807, and we have 21 files, for a total of 16,947. If we subtract 16,947 from the total 863287760, we get 863270813, which is the same size as our original file.

After splitting the main MySQL dump file, you can now restore/import each file individually, and if you have any problems, they will be easier to troubleshoot.

Here is the script:

#!/usr/bin/perl -w

# Need to explicitly load the functions in POSIX
use POSIX 'strftime';
use File::Copy;

# file name of the file you want to split
$filename = "all_data.sql";

# Note that if you pass no argument to localtime, it assumes the current time
$time = strftime '%Y-%m-%d %H:%M:%S', localtime;

$count = 0;
$filecount = 0;

# filename for original file

# syntax for the first file name - the header file - and it will be named with 00
# if you have more than 100 files, you could modify this statement to place a leading
# zero if the file is greater than 10 but less than 100
if ($filecount < 10)

{
     $header_filename = "database_0" . $filecount . ".sql";
}

print "$time - Creating file $header_filename\n";

open(OUTPUT, ">$header_filename") || die "Can't redirect stdout";

open(LINE ,"$filename") || die ("\nERROR - could not open file: $filename\n");
while (<LINE>)

{

if ($_ =~ "^-- Current Database: " && $count > 1)

{

               if ($count eq 1)
               
               {
                    print "Closing header file.\n";
                    close (OUTPUT);
               }

                    # close the old file
                    close(OUTPUT);

                    $filecount++;

                    # syntax for new file names
                    if ($filecount < 10)
                    
                    {
                         $new_filename = "database_0" . $filecount . ".sql";
                    }
                    
                    else
                    
                    {
                     $new_filename = "database_" . $filecount . ".sql";
                    }
                    
                    $time = strftime '%Y-%m-%d %H:%M:%S', localtime;
                    print "$time - Creating file $new_filename\n";

                    # copy the header file to the next new file, so the header info is in the file
                    copy("$header_filename","$new_filename") or die "Copy failed: $!";

                    open(OUTPUT, ">>$new_filename") || die "Can't redirect stdout";
                    print OUTPUT $_;

}

else

{
          # print information into the header file
                    print OUTPUT $_;
}

$count++;

# end while
}

close (LINE);
close (OUTPUT);

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Retrieving Data from MySQL via Python

In an earlier post Connecting to MySQL with Python, we only returned a single result line from a “SELECT VERSION()” query. We will now take a look at returning multiple rows of data from a MySQL database using Python.

You will need to have Python (including the MySQLdb and sys modules) and MySQL installed. We will also use the same database table (address) from a previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:



SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.


insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

Next, we will take the script from the earlier post and we simply have to modify our query, the "fetch" line (so it retrieves multiple values), and the print statement.

Fetch a single line:

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

Fetch multiple rows:

# fetch all of the rows from the query
data = cursor.fetchall ()

Here is the python script:


- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/python
# view_rows.py - Fetch and display the rows from a MySQL database query

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = "192.168.1.2", user = "user", passwd = "password, db = "scripting_mysql")

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute ("select name_first, name_last from address")

# fetch all of the rows from the query
data = cursor.fetchall ()

# print the rows
for row in data :
print row[0], row[1]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()
- - - - STOP SCRIPT - - - - (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

MySQL – Use mysqldump and FTP the Backup Files Offsite via Perl

In an earlier post (Scripting Backups of MySQL with Perl via mysqldump), I showed you how to use Perl to script a mysqldump backup of selected databases. Reminder – using mysqldump to backup your databases is not as safe or effective as using MySQL Enterprise Backup.

A blog reader sent me an email asking me how they could automate sending the backup files via FTP to another server (preferably one that is off-site). This was easily accomplished by adding a series of FTP commands to the original script.

As in the first post, we use a text file that contains a list of the databases that we want to backup. See this post for more information on using the script to backup your files – I don’t want to duplicate the entire post here.

For the FTP part of this script, you will need to edit the script to make sure that you change the following to match your systems:

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "my_hostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site under your root/login directory
$ftp_backup_directory = "backups";

# set the local directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

The script will create a folder on the local server under the $backup_folder directory, with the naming convention of YYYY-MM-DD-HHMM, and place the backup files for each database in this directory. It will then create the same folder on your FTP server under the $ftp_backup_directory, backup each database, and then send all of those files to your FTP server to be placed in the same directory. The script does a backup for each database individually, compresses the file, and then sends each file via FTP before it backs up the next database.

For example, on your local server, it will create a directory such as “2011-09-07-1756″ under the $backup_folder directory (in this example – /Users/tonydarnell/cgi/mysql/mysqlbak). The script will also create the same directory “2011-09-07-1756″ on the FTP server under the $ftp_backup_directory named “backups”. This $ftp_backup_directory should be created under the root directory (or the default login directory) for your FTP user prior to running the script.

Of course, it will be up to you to remove the older backups from the local server and FTP server. I also have quite a few print statements showing you each step of the process. Feel free to comment out these lines if you don’t want the script to print anything.

Here is a sample output from the script, backing up one database named cbgc:

# /usr/bin/perl mysql_backup_ftp.pl
Backing up cbgc...
FTP - 2011-09-07-2008_cbgc.sql.Z... Goodbye.
FTP Server ready.
User u36379513-777 logged in
CWD command successful
Type set to I
Opening BINARY mode data connection for 2011-09-07-2008_cbgc.sql.Z
Transfer complete
Finished backing up cbgc...

Here is the script:


#!/usr/bin/perl

use Net::FTP;
use warnings;
use File::Basename;

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "myhostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site
$ftp_backup_directory = "backups";

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

# we use a config file to keep a list of all of the databases that we want to backup
# You may use a comment to bypass any database that you don't want to backup
# example config file
# # unwanted_database (commented - will not be backed up)
# database_name1
# database_name2

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);
$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

    # login to the FTP server and create the folder to store the backup files
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->mkdir("$folder")
    or die "Cannot create directory $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # end FTP

# backup each database contained in the @databases array
foreach my $database (@databases) {
    next if ($database eq '');
    chomp($database);

    my $table = '';
    # Get just 1 table in the database - if there is a ' '(space) in the db name
    if(index($database,' ')+1) {
     my @parts = split(' ',$database);
     $database = $parts[0];
     $table = $parts[1];
    }

    print "Backing up $database...\n";

    # we add the year, month, day and time of day to the backup filename
    my $file = "$year-$mon-$mday-$hour$min\_$database";
    $file .= '_' . $table if($table ne '');
    $file .= ".sql";

    # perform a mysql dump on each database
    # change the path of mysqldump to match your system's location
    # and add/change any of the backup options - this is just an example
    # make sure that you change the root password above to match the correct password
    `/usr/local/mysql/bin/mysqldump -R --triggers -u $mysql_user_name --password=$mysql_user_password $database $table | compress > $folder/$file.Z`;

    # start FTP
    print "FTP - $file.Z... ";

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory/$folder")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->binary()
    or die "Change to binary mode failed. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->put("$folder/$file.Z")
    or die "Put failed - $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # FTP end

    print "Finished backing up $database...\n\n";
}

exit;

# this subroutine simply creates an array of the list of the databases
sub getFileContents {
    my $file = shift;
    open (FILE,$file) || die("Can't open '$file': $!");
    my @lines=<FILE>;
    close(FILE);

    return @lines;
}

# remove any commented tables from the @lines array
sub removeComments {
    my @lines = @_;

    @cleaned = grep(!/^\s*#/, @lines); #Remove Comments
    @cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines

    return @cleaned;
}

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Perl TCP Listener for Detecting Available Ports for MySQL Enterprise Monitor

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor.

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a centralized Service Manager and lightweight Service Agent this is installed on each monitored MySQL server.

For this demo, we had to install the Enterprise Monitor Application on one server, and then the Enterprise Monitor Agent on another server that was hosting the MySQL database that we wanted to monitor. In this case, the servers that we were using for the install were in separate data centers, and both had firewalls restricting port access. We wanted to install the Monitor in one data center, and the Agent in another. Since both applications use various TCP/IP ports, during the install we were having problems finding openings in the firewalls to allow the programs to “talk to each other”.

So, we needed to know which ports were available on each server – without having to get a network admin involved. One easy way of checking to see if you can reach another server via a particular port is to simply telnet to that server on that particular port, and a successful telnet connection to port 3306 would look like this:

sh-3.2# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.

However, for this to work, you must have telnet enabled on the target machine (which is usually not a problem). For those cases where you don’t have telnet enabled, you can use a Perl script to act as a “TCP listener” – which will open a TCP socket and listen to a particular port. And of course, for this script to work, you need to have Perl installed on that machine as well.

I wrote this TCP listener script for a different application, but for this example, it works really well. The script simply opens up a TCP port, and waits for something to connect on that port. When a connection is made, it prints the current date/time and the information that it received via that port.

Here is the Perl script:

#!/usr/bin/perl

use IO::Socket;
use IO::Socket::INET;
use POSIX 'strftime'; # Need to explicitly load the functions in POSIX

# Note that if you pass no argument to localtime, it assumes the current time
my $DateTime = strftime '%Y-%m-%d-%H:%M:%S', localtime;

# ----------------------------------------------------------------------------------
print "\nStarting.... $DateTime\n\n";
# ----------------------------------------------------------------------------------

$count = 1;
$limit = 100;

while ($count <= $limit)

{
     my $sock = new IO::Socket::INET (
     LocalPort => '3306',
     Proto => 'tcp',
     Listen => SOMAXCONN,
     ReusePort => 1
     );
     
     die "Could not create socket: $!\n" unless $sock;

     my $new_sock = $sock->accept();
     while(<$new_sock>) {
     
      s/ //g;
      chop $_;
      my $DateTime = strftime '%Y-%m-%d-%H:%M:%S', localtime;
      print "$count -- $DateTime -- $_ \n";
     }
     
     close($sock);

$count++;

}

The reason that I have a "while ($count <= $limit)" loop is because for some reason, after the script received a connection, it would time out after a couple of minutes and quit working. With the while loop, if it drops the connection, the connection will be restarted automatically until the $limit variable value is reached.

Remember – you need to have Perl installed on the target machine for this to work. Once you have this script running, you can connect to that machine on the port specified via several methods. This is what it looks like when you connect via telnet: (I manually typed in “hello” and “goodbye” so you could see the output from the TCP listener script)

# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.
hello
goodbye
^]
telnet>

And this is what the output looks like on the server that you connected to:

# /usr/bin/perl tcp_listen.pl

Starting.... 2011-09-06-14:26:20

-- 2011-09-06-14:26:24 -- hello
-- 2011-09-06-14:26:27 -- goodbye

You can also connect via http – as http://192.168.1.2:3306. Note that your browser will not display anything, as it is waiting for the Perl script to respond. But you will see something like this output from the script:

sh-3.2# /usr/bin/perl tcp_listen_test.pl

Starting.... 2011-09-06-14:27:45

-- 2011-09-06-14:27:50 -- GET/HTTP/1.1
-- 2011-09-06-14:27:50 -- Host:192.168.1.2:3306
-- 2011-09-06-14:27:50 -- User-Agent:Mozilla/5.0(Macintosh;IntelMacOSX10.6;rv:6.0.1)Gecko/20100101Firefox/6.0.1
-- 2011-09-06-14:27:50 -- Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
-- 2011-09-06-14:27:50 -- Accept-Language:en-us,en;q=0.5
-- 2011-09-06-14:27:50 -- Accept-Encoding:gzip,deflate
-- 2011-09-06-14:27:50 -- Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.7
-- 2011-09-06-14:27:50 -- Connection:keep-alive
........

This script is handy for testing the connectivity availability of any port from one machine to the next, to make sure that the port is not being blocked by a firewall. Or, if you need a TCP listener, it works great for that as well. But, you won’t be able to use this script on a port that is already being used by another application.

Here are the default ports commonly used by MySQL Enterprise Monitor and the Agent:

Service Port #
Bundled MySQL 13306
Proxy  4040
Tomcat Server 18080
Tomcat SSL 18443
Tomcat Shutdown 18005
Tomcat SSL 18443

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Convert .csv File to MySQL Database via Perl

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this spreadsheet had 45 columns of varying lengths. Ouch.

Please keep in mind that this was a quick hack. I did some testing on some small data, and then crunched the big spreadsheet. Everything seemed to work out okay, but I did get a bunch of Perl errors “Use of uninitialized value…”. So, I just turned off “warnings” and proceeded. Feel free to show me how to correct these errors – I just didn’t want to take the time. I am not an expert at writing Perl code anyway.

The script is fairly simple. The .csv file has to have header information in order to be able to create the column names, the delimiter must be double-quotes and a comma – “,” – and so each line must look something like this:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"

There are times when you will have a .csv file that doesn’t have the full “,” delimiter – such as when a column is a number, the “s are left off and only a comma is used as the delimiter – and I could have incorporated that functionality into the script, but my .csv file had the full delimiter for both numbers and text.

I created a test .csv file, and this is the data that I used to initially test the script:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"
"Tony Davidson","100 Main Street","Suwanee","Georgia","30024","6785552222","440.03","4522.00","82837"
"Jack Smith","5000 Washington Ave.","Chattanooga","California","31069","(770)5552941","400","456.787","2828"
"Paul Davis","P.O. Box AB123","Jackson","Georgia","31069","(770)555-6532","325.1","837.3","3848"
"Jacky Smith-Davidson","400 Metropolitan Ave","Macon","Georgia","30024-2032","770-555-8987","503.920","392.4","292833"
"Davd Jackson","100 Oak Drive","Savannah","Tennessee","40024","770) 555- 8941","5492.0","15674.01","43"

The script only determines three data types – varchar, integer and decimal. If your data doesn’t fit into these three types, or if you want to use different data types, then the program probably won’t work for you. The script calculates the longest length for each column, so if you will be changing and increasing the length of the data in the columns, you will want to edit the length of the columns in the “create table” statement that is created.

Once the script was run against the above data, two files were created – mysql_create_table.sql, which is the SQL that you can use to create the actual table – and mysql_data_values.sql, which is the “insert into” file that you can use to load the data into the table.

Here is the SQL statement that was created by the script (mysql_create_table.sql), which can use used to create the table for the data above:

CREATE TABLE `Addresses` (
`Name` varchar (20),
`Address` varchar (20),
`City` varchar (11),
`State` varchar (10),
`Zip` varchar (10),
`Phone` varchar (14),
`Amount1` decimal (7,3),
`Amount2` decimal (8,3),
`Amount3` int (6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And here is the “insert” data (mysql_data_values.sql) that is created that you can use to load the database:

insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Tony Davidson', '100 Main Street', 'Suwanee', 'Georgia', '30024', '6785552222', '440.03', '4522.00', '82837');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jack Smith', '5000 Washington Ave.', 'Chattanooga', 'California', '31069', '(770)5552941', '400', '456.787', '2828');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Paul Davis', 'P.O. Box AB123', 'Jackson', 'Georgia', '31069', '(770)555-6532', '325.1', '837.3', '3848');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jacky Smith-Davidson', '400 Metropolitan Ave', 'Macon', 'Georgia', '30024-2032', '770-555-8987', '503.920', '392.4', '292833');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Davd Jackson', '100 Oak Drive', 'Savannah', 'Tennessee', '40024', '770) 555- 8941', '5492.0', '15674.01', '43');

Here is the MySQL command that you can use to import your .sql file into the MySQL database:

mysql -u -p database_name < mysql_data_values.sql

You will want to edit the script and edit/enter your relevant information – such as .csv file name, output file names and storage engine.

I tried this on my 27,000 line .csv file, and it appeared to work. If the data in the .csv file had been a bit cleaner, I could state that it positively did work, but there was so much data that it was difficult to check. The theory is that with this script, you can take a unwieldy .csv file, import it into MySQL, do your work, and then export it again as a .csv file. And, you can always write a script to automatically execute the creation of your table and the importing of the data.

So, feel free to give this a shot and let me know if it worked for you or not.


#!/usr/bin/perl

use File::Copy;
#use warnings;

# edit these next lines to match your preferences
$TABLE_NAME = "Addresses";
$DATABASE_ENGINE = "InnoDB";
$DEFAULT_CHARSET = "latin1";

# enter the file name that you want to convert to the SQL statements and values
$filename = "test.csv";

# create your output files
open(TABLE, ">mysql_create_table2.sql") || die "Can't redirect stdout";
open(VALUES, ">mysql_data_values2.sql") || die "Can't redirect stdout";

# count how many lines are processed
$count = 0;

# set to be blank
$Columns_Values = "";

# open the file to be processed
open FILE, "$filename" or die $!;

# assign the first line which contains headers to the variable $columns
my $columns = <FILE>;

# remove carriage return (for Windows, if you have a CR and a LF, you will need to chop twice)
chop $columns;
# uncomment the next line for Windows files
#chop $columns;

# check to see if the field contains a ' - and if so, add a slash \ in front
$columns =~ s/'/\\'/g;

# ...remove the first " and then "," will be our delimiter
$columns =~ s/\"//;
# ...remove the last " from the end of the line so that "," will be our delimiter
chop $columns;

# remove spaces, add an underscore _
$columns =~ s/ /_/g;
#print "$columns\n";

# split first line into individual field names
@Field_Names = split("\",\"",$columns);

# total number of field names
$Field_Names_Count = $#Field_Names;

# add one to the $Field_Names_Count
$Field_Names_Count_Plus_One = $Field_Names_Count + 1;

# start the field count at zero
$field_count = 0;

# create the column names (values) for the "insert into" part of the SQL statement
if ($count == 0)

{

$column_count = 0;

   while ($column_count <= $Field_Names_Count)
   
   {
      if ($column_count < $Field_Names_Count)
   
      {
         $Columns_Values = $Columns_Values . $Field_Names[$column_count] . ", ";
      }
      
      
      if ($column_count == $Field_Names_Count)
   
      {
         $Columns_Values = $Columns_Values . $Field_Names[$column_count];
      }

      $column_count++;
   }
   
# end if ($count == 0)
}

$count = 0;

# continue to parse the rest of the file which contains the data
while (<FILE>)

{

# remove the carriage return
chomp $_;

# remove the first " and then...
$_ =~ s/\"//;

# ...remove the last " from the end of the line so that "," will be our delimiter
chop $_;

# split the first line into what will be used as the column names
@Field_Values = split("\",\"",$_);

while ($field_count <= $Field_Names_Count )

{

   # check to see if the field contains a ' - and if so, add a slash \ in front
   $Field_Values[$field_count] =~ s/'/\\'/g;

         # if a field is blank, set it to zero, and then remove the zero later
         if (length($Field_Values[$field_count]) < 1)
         
         {
            $Field_Values[$field_count] = "0";
         }

         # check to see if the field value contains any alphabet characters
         if ( $Field_Values[$field_count] =~ m/[a-zA-Z]/)
         
         {
               $type[$field_count] = "varchar";
               
               # find the longest length of the data in the column
               if ($length[$field_count] < 'length($Field_Values[$field_count])')
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
         }
   
   
   # once a field has been designated as a varchar, we don't need to test it any further
   # as we aren't going to change a varchar field back to a number or decimal field
   if ($type[$field_count] ne "varchar")
   
   {
         # check to see if the field value does not contain any alphabet characters
         if ( $Field_Values[$field_count] =~ m/[^a-zA-Z]/)
   
         {
            # if the field was already determined to be a decimal, then keep it a decimal
            # if not, then set it to be a number
            if ($type[$field_count] ne "decimal")
            
            {
               $type[$field_count] = "int";
               
               # find the longest length of the data in the column
               if ($length[$field_count] lt 'length($Field_Values[$field_count])')
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
            }
         }
   
         # if the field contains numbers and a period
         if ( $Field_Values[$field_count] =~ m/[0-9.]/)
   
         {
               @count_periods = split("\\.",$Field_Values[$field_count]);
               $number_of_periods = $#count_periods;
            
            
            # if there are two periods in the field, then it is a varchar
            if ($number_of_periods > 1)
            
            {
   
            $type[$field_count] = "varchar";
            
         
               # check for the length of the field to make sure we have the highest field length
               if ($length[$field_count] < 'length($Field_Values[$field_count])')
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
   
                  # set these values to be zero - in case the previous field contained a decimal number
                  $decimal_length1[$field_count] = "";
                  $decimal_length2[$field_count] = "";
               }
   
            # if there is only one period in the field, then it is a decimal with X number of decimal places
            if ($number_of_periods == 1)
            
            {
               $type[$field_count] = "decimal";
               
               # split the number to find out the length of each side of the decimal
               # example 1234.56 = 4,2
               @split_decimal_number = split("\\.",$Field_Values[$field_count]);
               
               # find the length of each side of the decimal and keep the highest value
               # this is for the number to left of the decimal
               if ($decimal_length1[$field_count] lt length($split_decimal_number[0]))
               
               {
                  $decimal_length1[$field_count] = length($split_decimal_number[0]);
               }
               
               # find the length of each side of the decimal and keep the highest value
               # this is for the number to right of the decimal
               if ($decimal_length2[$field_count] lt length($split_decimal_number[1]))
               
               {
                  $decimal_length2[$field_count] = length($split_decimal_number[1]);
               }
                           
            # end if ($number_of_periods == 1)
            }
   
         # end if ( $Field_Values[$field_count] =~ m/[0-9.]/)
         }
                  
         # if the field contains anything else besides a 0-9 or a period (.)
         if ( $Field_Values[$field_count] =~ m/[^0-9.]/)
         
         {
               $type[$field_count] = "varchar";
   
               # find the longest length of the data in the column
               if ($length[$field_count] lt 'length($Field_Values[$field_count])')
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
         # end if ( $Field_Values[$field_count] =~ m/[^0-9.]/)         
         }
   
   # end if ($type[$field_count] ne "varchar")
   }
   
   else
   
   {         
   
               # check for the length of the field to make sure we have the highest field length
               if ($length[$field_count] < length($Field_Values[$field_count]))
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
   
   # end else
   }
   
   
   # uncomment this line if you want to see the data being processed - as well as another line below
   # print "$Field_Values[$field_count] $type[$field_count] $length[$field_count] $decimal_length1[$field_count] $decimal_length2[$field_count] | ";
   
   
         # if a field is blank, we set it to zero earlier, now we remove the zero
         if (length($Field_Values[$field_count]) < 1)
         
         {
            $Field_Values[$field_count] = "";
         }

   
      # create the syntax needed for the "insert into" statement    
      if ($field_count == 0)
      
      {
         print VALUES "insert into $TABLE_NAME ($Columns_Values) \nvalues ('$Field_Values[$field_count]'";
      }
      
         if ($field_count > 0 && $field_count < $Field_Names_Count_Plus_One)
         
         {
            print VALUES ", '$Field_Values[$field_count]'";
         }
         
      $field_count++;
      # end while ($field_count < $Field_Names_Count_Plus_One )
      }
   
         # check for last entry and then start over on next line
         if ($field_count == $Field_Names_Count_Plus_One)
         
         {
            $field_count = 0;
            $count++;
         
            # close the print statement for the column values
            print VALUES ");\n";
         }
   
   # uncomment this line if you want to see the data being processed
   # print "\n";

# end while (<FILE>)
}

# print the create table statement
print TABLE "\n\nCREATE TABLE `$TABLE_NAME` (\n";

$count_columns = 0;

# loop through the columns and print the type and length for each
while ($count_columns < $Field_Names_Count_Plus_One)

{
   # make sure that we don't have a blank field value
   if (length($Field_Names[$count_columns]) > 0)
   
   {
      if ($type[$count_columns] =~ "decimal")
      
      {
         $decimal_field_length = $decimal_length1[$count_columns] + $decimal_length2[$count_columns];
         print TABLE " `$Field_Names[$count_columns]` $type[$count_columns] ($decimal_field_length,$decimal_length2[$count_columns])";
      }
      
      else
      
      {
         print TABLE " `$Field_Names[$count_columns]` $type[$count_columns] ($length[$count_columns])";
      }
   
      if ($count_columns < $Field_Names_Count)
      
      {
         print TABLE ",\n";
      }
      
      if ($count_columns == $Field_Names_Count_Plus_One)
      
      {
         print TABLE "\n\n";
      }
      
   # end if (length($Field_Names[$count_columns]) > 0)
   }

$count_columns++;

# end while ($count_columns < $Field_Names_Count_Plus_One)
}

# print an output to show how many lines were processed
print "Processed $column_count columns and $count lines.\n";

print TABLE "\n) ENGINE=$DATABASE_ENGINE DEFAULT CHARSET=$DEFAULT_CHARSET\n";

print TABLE "\n\n";

close(FILE);

exit;

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Follow

Get every new post delivered to your Inbox.

Join 39 other followers