直接 SQL を叩く ; Rails

RailsSQL 直書き処理をさせる方法

1-0. 方法

1-1. 正常系返り値のオブジェクト

1-2. 異常系の挙動


方法

ActiveRecord::Base.connection.execute("SQL文")

でいける。

ex.

ActiveRecord::Base.connection.execute("SELECT * FROM rankings;")

正常系

処理するメソッドによって返ってくるオブジェクトが違う。

前提テーブル

id name score rank created_at updated_at
1 momotaro 8000 1 2014-01-05 13:50:33 2014-01-05 13:50:33
2 inu 7000 2 2014-01-05 13:50:33 2014-01-05 13:50:33
3 kiji 5000 3 2014-01-05 13:50:34 2014-01-05 13:50:34
4 saru 3000 4 2014-01-05 13:50:34 2014-01-05 13:50:34
5 akaoni 2000 5 2014-01-05 13:50:34 2014-01-05 13:50:34
6 aooni 1000 6 2014-01-05 13:50:34 2014-01-05 13:50:34
  • execute (DB の config か)
ActiveRecord::Base.connection.execute("SELECT * FROM rankings;")
#:array, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, 
:database_timezone=>:utc, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>-2147442171, :cast=>true, :default_file=>nil, 
:default_group=>nil, :adapter=>"mysql2", :encoding=>"utf8", :database=>"prac_development", :pool=>5, :username=>"root", :password=>nil, 
:socket=>"/var/lib/mysql/mysql.sock", :flags=>2}>
  • select (DB レコードのハッシュ)
ActiveRecord::Base.connection.select("SELECT * FROM rankings;")
#<ActiveRecord::Result:0xa4f8544 @columns=["id", "name", "score", "rank", "created_at", "updated_at"], 
@rows=[[1, "momotaro", 8000, 1, 2014-01-05 13:50:33 UTC, 2014-01-05 13:50:33 UTC], [2, "inu", 7000, 2, 2014-01-05 13:50:33 UTC, 2014-01-05 13:50:33 UTC],
[3, "kiji", 5000, 3, 2014-01-05 13:50:34 UTC, 2014-01-05 13:50:34 UTC], [4, "saru", 3000, 4, 2014-01-05 13:50:34 UTC, 2014-01-05 13:50:34 UTC], 
[5, "akaoni", 2000, 5, 2014-01-05 13:50:34 UTC, 2014-01-05 13:50:34 UTC], [6, "aooni", 1000, 6, 2014-01-05 13:50:34 UTC, 2014-01-05 13:50:34 UTC]], @hash_rows=nil, @column_types={}> 

異常系

SQL文のSyntaxError や、存在しないテーブルの指定などをするとどうなるか。

例外が返ってくる。確認するところ、Mysql2::Error

  • SQL文を SELECT でなくて SELE にする

  • 存在しないテーブルを指定

SELE * FROM rankings;

2.0.0-p353 :004 > ActiveRecord::Base.connection.execute('SELE * FROM rankings;')
   (0.6ms)  SELE * FROM rankings;
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELE * FROM rankings' at line 1: SELE * FROM rankings;
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELE * FROM rankings' at line 1: SELE * FROM rankings;
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `query'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `block in execute'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:435:in `block in log'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activesupport-4.0.2/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `execute'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:222:in `execute'
    from (irb):4
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands/console.rb:90:in `start'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands/console.rb:9:in `start'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands.rb:62:in `'
    from bin/rails:4:in `require'
    from bin/rails:4:in `
'

SELECT * FROM mat5ukawa;

2.0.0-p353 :005 > ActiveRecord::Base.connection.execute('SELECT * FROM mat5ukawa;')
   (0.7ms)  SELECT * FROM mat5ukawa;
Mysql2::Error: Table 'samp_development.mat5ukawa' doesn't exist: SELECT * FROM mat5ukawa;
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'samp_development.mat5ukawa' doesn't exist: SELECT * FROM mat5ukawa;
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `query'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `block in execute'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:435:in `block in log'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activesupport-4.0.2/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:430:in `log'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:287:in `execute'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/activerecord-4.0.2/lib/active_record/connection_adapters/mysql2_adapter.rb:222:in `execute'
    from (irb):5
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands/console.rb:90:in `start'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands/console.rb:9:in `start'
    from /home/matsu/.rvm/gems/ruby-2.0.0-p353@two/gems/railties-4.0.2/lib/rails/commands.rb:62:in `'
    from bin/rails:4:in `require'
    from bin/rails:4:in `
'

引用元

記すに足らず。 # 勉強になりました