Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
14
Help us understand the problem. What is going on with this article?
@nekonoprotocol

MYSQLをrubyで操作するスクリプト

More than 5 years have passed since last update.

genのインストール

gem install mysql
gem list

gem listruby-mysqlが入っていればインストール成功

rubyのスクリプト作成

test.ruby
require 'rubygems'
require 'mysql'

my = Mysql::new("サーバー名(ipでも可)", "データベース名", "パスワード", "スキーマー名", ポート番号)
selectSql = "select id, name from table;"
res = my.query(selectSql)
res.each_hash do |row|
  id = row["id"]
  tel = row["name"]
  print "id:" + id + "name:" + name
end

ファイル名はtest.rubyなどrubyの拡張子を指定して作成

スクリプト実行

ruby test.ruby

これで結果が返ってくる

おまけ

電話番号を半角数字に正規化するプログラム

require 'rubygems'
require 'mysql'
require 'nkf'

deleteWordHan = Array["-",")","(","+"]

my = Mysql::new("192.XXX.XXX.XXX", "XXXXX", "XXXXXXXX", "XXXXX", 00000)
selectSql = "select id, tel from m_corporation;"

res = my.query(selectSql)
res.each_hash do |row|
  id = row["id"]
  tel = row["tel"]
  tel = NKF.nkf('-m0Z1 -w', tel)

  for var in deleteWordHan do
    tel = tel.delete(var)
  end

  updateSql = "update m_corporation set tel_normalized = '" + tel + "' where id = " + id + "" + "\n"
  print updateSql
  my.query(updateSql)

end

update文もselect文のときと同じようにかける。
全角文字を半角にするためにnkfを使っているがこれはgemのインストールは不要で使える。

14
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
14
Help us understand the problem. What is going on with this article?