SQLTerminal.app
now available

XPath.app
now available
Want fresh tech tips in your inbox?

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'

ActiveRecord::Base.establish_connection(
    :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

Apps I've Built

XPath Expression Editor
Practice and improve your XPath skills with XPath Editor
Click to buy on the App Store
Photo Location Changer
Easily change the location on your photos and videos
Click here for more info about the app
Photo Date Changer
Easily change the dates and times on your photos and videos
Click here for more info about the app
VocabReminder
English dictionary with notifications so you won't forget what you're studying!
Click here for more info about the app
VocabQuiz
The app that quizzes and scores you on your vocabulary!
Click here for more info about the app