MySQL과 Ruby를 사용하여 간단한 데이터 정리 기능을 구현하는 방법

CREATE DATABASE data_cleaning;
USE data_cleaning;

CREATE TABLE raw_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(50)
);

CREATE TABLE clean_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(50)
);

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "password", :database => "data_cleaning")

csv_data = CSV.read('raw_data.csv', headers: true)

csv_data.each do |row|
  client.query("INSERT INTO raw_data (name, age, email) VALUES ('#{row['name']}', #{row['age']}, '#{row['email']}')")
end

client.close

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "password", :database => "data_cleaning")

client.query(
  "INSERT INTO clean_data (name, age, email)
  SELECT DISTINCT name, age, email
  FROM raw_data"
)

client.close

require 'mysql2'
require 'csv'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "password", :database => "data_cleaning")

clean_data = client.query("SELECT * FROM clean_data")

CSV.open('clean_data.csv', 'w') do |csv|
  csv << clean_data.fields
  clean_data.each do |row|
    csv << row.values
  end
end

client.close