the official blog of hyperco.de web services and its cio
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.
you are watching the official blog of hyperco.de web services