rails: fast mysql table export to csv

In: random stuff

14 Dec 2009

Sometimes it is necessary to have a function for easily downloading a SQL table exported to a plain csv file for further external processing or manual data lookup. Normally people in forums on the net recommend to use phpmyadmin for tasks like this, but on a real production server phpmyadmin is of course no option security wise.

Another approach often recommended is to use FasterCSV to build the csv-file. FasterCSV is a nice tool when you want to freely create custom csv files, but performance wise this approach takes some time if you are really exporting large tables (>10000 rows).

Much faster is to just use MySQL's built-in method for exporting a table directly into csv format.

Just put this into the model you want to export as csv.

# model # function returns table as csv string def self.to_csv db_name = self.configurations[Rails.env]['database'] # pay attention to the backticks `` return `echo "use #{db_name}; select * from #{self.table_name};" | mysql -u monitor_user --password="1234"` end

Ensure that you have a seperate mysql user with only select priviliges on your database to get this working safely - in this example the user name is 'monitor_user' with password '1234'.
Of course if you are planning to implement this functionality in several models, you could put this into a module into ./lib and just include the module in each desired model class.

To provide the generated csv string from the model for download, you could use some code like this in your controller.

# controller def download_csv send_data(MyModel.to_csv, { :type => 'text/csv', :filename => "my_model_#{Date.today.to_s}.csv" } ) end

Beneficial over the phpmyadmin solution,
you define the security rules directly in your application,
no need to rely on mysql priviliges and
most important, you don't expose a common interface to the database server which is permanently under fire from automated attack bots1.

  1. for those of you who are using phpmyadmin just a few words ... even we don't have it running on our server - we are getting more than 1000 requests per day just on one domain, trying to gain access to several possible phpmyadmin paths and underlying scripts - and as I already said, it is not there, all those bots fail - so imagine for a second, how those attack bots would try to penetrate your phpmyadmin installation, once they found it

Comment Form

You need to enable javascript in order to use Simple CAPTCHA.
Security Code: