Easily build a great vocabulary without studying! Now on the App Store!
Click here for more info about the app

How To Export A MySQL Database to JSON, CSV and XML with Ruby and the ActiveRecord Gem


It's trivial to export data from a mysql database with Ruby and ActiveRecord. All you have to do is establish a connection and define a class that represents the table.

require 'rubygems'
require 'active_record'

    :adapter => "mysql",
    :database => "WSD",
    :username => "root",
    :password => "",
    :host => "localhost"

class Event < ActiveRecord::Base; end
class Person < ActiveRecord::Base; end

File.open("export-events.json", "w") { |f| f.write Event.all.to_json }
File.open("export-people.json", "w") { |f| f.write Person.all.to_json }

You can also use Bundler and a Gemfile to install dependencies. It might look something like this

source 'https://rubygems.org'

gem "activerecord"
gem "mysql

You can install the dependencies by cding into the directory with the Gemfile and execute

bundle install

Bundler is a RubyGem so you will have to have it installed first.

gem install bundler

There is also a cool Gem callsed mysql2xxxx available at https://github.com/seamusabshere/mysql2xxxx

The Gem will let you run arbitrary sql statements and export them in either CSV, JSON or XML.

Here are some ways to use mysql2xxxx

mysql2csv --user=dbuser --password=dbpassword --database=dbname --execute="select * from automobile_makes"
mysql2json --user=dbuser --password=dbpassword --database=dbname --execute="select * from automobile_makes"
mysql2xml --user=dbuser --password=dbpassword --database=dbname --execute="select * from automobile_makes"

And to install

gem install mysql2xxxx
Tagged w/ #bundler #export #databasesruby on rails #mysql