Tuesday’s command-line kludge

Today’s kludge is a response to the following need: a web form is used to populate a MySQL database table on a Linux server with information. Periodically, we would like to send out the contents of this table in a form that Excel users can easily adapt for their purposes.

Here’s one possible way to satisfy the need. It’s the result of combining various tips from around the web. It’s not elegant, but it works. We’re going to string together some sql, a bit of bash and the mutt mail user agent to accomplish our goal.

First, we want to use MySQL to write out the data to a CSV file. Here’s how to do that:

USE your_database;
SELECT "firstname","lastname","email","school","schoolyear","affiliation","atty",
"barnumber","vegetarian","crdate","tstamp","uid","source","ipaddress"
UNION
SELECT * from your_table INTO OUTFILE '/tmp/formdata.csv' FIELDS TERMINATED
BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'rn'

Save this in a file that has the name formdata.sql or something equally memorable.

One of the gotchas here is that the “SELECT .. INTO OUTFILE” part of this requires the MySQL FILE privilege, which is generally granted system-wide and not per-database. That is, it’s really only meant to be run by database administrators, and not mere mortals. Ordinary database accounts do not usually have this privilege. You may need to be the SQL “root” or similarly high-privilege user in order to have the ability write the data file. In our case, we are going to create a .my.cnf file in our home directory that gives us the ability to use mysql without supplying a user name and password on the command line. It looks like this:

[client]
host=localhost
user=root
password=s0m3r3@11yh@rDPW

Note that “root” here refers to the MySQL user named “root”, not to the system user. If you go this route, make sure that the user that owns this file is the only one that can read the .my.cnf (chmod 600 .my.cnf).

You may be wondering about the first line of the SQL script: SELECT “firstname” … UNION … . This is a clever hack to output the field names as the first line of the CSV file. You will need to enter the headers manually and enclose them with quotes and commas. The UNION just means that this output will be combined with the results of the normal select statement.

Now you need a small shell script to glue some things together:

#!/bin/bash
rm -f /tmp/formdata.csv
mysql < /home/sqluser/formdata.sql
mutt -s "Updates" -a /tmp/formdata.csv bjchapm@emory.edu < /home/sqluser/message.txt

As you can see from the script, we’re telling the script to force remove /tmp/formdata.csv. Then, we invoke mysql with the formdata.sql sql command file. Finally, we send out an email, using the mutt command-line mail tool to attach the csv file. Mutt is very useful for these sorts of things: we set the subject with “-s”, the attachment with “-a” and pipe in a message body from /home/sqluser/message.txt. This last is done to avoid tripping spam filters that check for the absence of a message body, although it could also provide useful information for your recipient, of course.

Enhanced by Zemanta
Advertisements

2 thoughts on “Tuesday’s command-line kludge

  1. Good observation. It’s a sign of the limitation of doing things in this fashion. The sql snippet (“into outfile …”) will recreate the file, so it’s not really a problem; however, it’s hard to read and maintain. It would be better to use a “here document” within the shell script to create the sql. That way, at least everything would be in one file and it would be easier to review.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s