Fourty One SQL Queries in 60 Seconds

I needed to capture some data from a database into CSV files. MySQL supports this easily, so this in itself isn’t a problem. Part of the requirement was that the data had to be divided into separate files, one for each month since 2008. Being May 2011, that gives us 41 months.

The only way I could do this was to run 41 separate queries, one for each month. Each query will differ in 3 places from the other. These are

1. The start date
2. The end date
3. The CSV filename

If I had to create 41 queries by hand it will take me quite a while and there is a big possibility of making a mistake and messing up the result.

But my friend Linux and Bash is around, which allows me to do it very quickly and with virtually 0 probability of errors or mistakes. I planned to generate a SQL script with the queries.

The command I ended up with was as follows (I split it into multiple lines for clarity):
year=2008; for from in {1..12} {1..12} {1..12} {1..5}; do 
[ $from -eq 12 ] \
&& { tyear=$((year + 1)); to=1; } \
|| { tyear=$year; to=$((from + 1)); }; 
echo "SELECT columns FROM table INFO OUTFILE '$year-$from.csv' " \
"WHERE time>='$year-$from-01' AND time<'$tyear-$to-01';"; 
year=$tyear;
done >> dbscript.sql

After executing this I had a file dbscript.sql which I can run on the database to give me the files I needed. It took me less than a minute to type up the command and use it to generate the full script.

The command itself might seem complex, but it’s really very simple.

1. In the first line I’m looping through the numbers 1 to 12 three times, and then through the numbers 1 to 5
2. The next 3 lines inside the loop calculates the “next” month’s year and month number. For example, if the current iteration’s month and year is 9 and 2008, then the next month would be 10 and 2008, or for the month 12 and 2008, it will be 1 and 2009.
3. The 5th and 6th lines are just echoing the generated SQL query.
4. The last line sets the base year to use for the next iteration’s month, as calculated in nr.2 above.
5. The resulting output of the whole loop is appended to the file called dbscript.sql.

If you had to the command regularly, you could always make a bash script from it and execute it whenever you need it. This will save you having to type it out every time. Bash scripts are nothing more than what you can type on the command line, and are very powerful.

So Why Love Linux? Because the command line allows you to write “smart” commands.