Rails 3 execute custom sql query without a model

Question

I need to write a standalone ruby script that is supposed to deal with database. I used code given below in rails 3

@connection = ActiveRecord::Base.establish_connection(
:adapter => "mysql2",
:host => "localhost",
:database => "siteconfig_development",
:username => "root",
:password => "root123"
)

results = @connection.execute("select * from users")
results.each do |row|
puts row[0]
end

but getting error:-

`<main>': undefined method `execute' for #<ActiveRecord::ConnectionAdapters::ConnectionPool:0x00000002867548> (NoMethodError)

what i am missing here?

SOLUTION

After getting solution from denis-bu i used it following way and that worked too.

@connection = ActiveRecord::Base.establish_connection(
            :adapter => "mysql2",
            :host => "localhost",
            :database => "siteconfig_development",
            :username => "root",
            :password => "root123"
)

sql = "SELECT * from users"
@result = @connection.connection.execute(sql);
@result.each(:as => :hash) do |row| 
   puts row["email"] 
end

denis-bu · Accepted Answer · 2013-12-09 08:51:31Z

168

Maybe try this:

ActiveRecord::Base.establish_connection(...)
ActiveRecord::Base.connection.execute(...)

edited Dec 9, 2013 at 8:51

answered Mar 14, 2013 at 11:48

denis-bu

3,4461 gold badge20 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

radixhound Over a year ago

FWIW, Using empty params is not idiomatic ruby. So do connection.execute rather than connection().execute

Leigh McCulloch Over a year ago

Works on Rails 4 also.

Igor Kasyanchuk Over a year ago

github.com/igorkasyanchuk/execute_sql less code is needed plus you can do it in directly rails console

Sachin R · Accepted Answer · 2014-07-18 05:37:37Z

99

connection = ActiveRecord::Base.connection
connection.execute("SQL query")

edited Jul 18, 2014 at 5:37

answered Mar 14, 2013 at 11:52

Sachin R

11.9k11 gold badges37 silver badges42 bronze badges

3 Comments

Watusimoto Over a year ago

In this case, connection = ActiveRecord::Base.connection; connection.execute("SQL query") would work. Less typing!

Kasper Grubbe Over a year ago

Or just ActiveRecord::Base.connection.execute("SQL query")

lee Over a year ago

This connection may have to be returned to the connection pool afterwards to avoid threading issues: ActiveRecord::Base.connection_pool.checkin(connection) apidock.com/rails/ActiveRecord/ConnectionAdapters/…

Community · Accepted Answer · 2017-05-23 12:10:29Z

40

I'd recommend using ActiveRecord::Base.connection.exec_query instead of ActiveRecord::Base.connection.execute which returns a ActiveRecord::Result (available in rails 3.1+) which is a bit easier to work with.

Then you can access it in various the result in various ways like .rows, .each, or .to_hash

From the docs:

result = ActiveRecord::Base.connection.exec_query('SELECT id, title, body FROM posts')
result # => #<ActiveRecord::Result:0xdeadbeef>


# Get the column names of the result:
result.columns
# => ["id", "title", "body"]

# Get the record values of the result:
result.rows
# => [[1, "title_1", "body_1"],
      [2, "title_2", "body_2"],
      ...
     ]

# Get an array of hashes representing the result (column => value):
result.to_hash
# => [{"id" => 1, "title" => "title_1", "body" => "body_1"},
      {"id" => 2, "title" => "title_2", "body" => "body_2"},
      ...
     ]

# ActiveRecord::Result also includes Enumerable.
result.each do |row|
  puts row['title'] + " " + row['body']
end

note: copied my answer from here

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered May 24, 2016 at 2:09

hajpoj

13.4k2 gold badges48 silver badges64 bronze badges

2 Comments

Paul Chernoch Over a year ago

As stated, exec_query returns the actual results - what I want, while execute just returns status information.

Masa Sakano Over a year ago

As of Rails-7.0 and Ruby-3.1, .to_hash is undefined (Ver.7.0.4 doc). Instead, .to_a returns an Array of Hash. Make sure the column names are unique in using .to_a, e.g., specify "SELECT 3 a,4 b,5 c;" as opposed to "SELECT 3,4,5;".

montrealmike · Accepted Answer · 2014-09-23 16:42:45Z

25

You could also use find_by_sql

# A simple SQL query spanning multiple tables
Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id"
> [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "first_name"=>"Quentin"}>, ...]

edited Sep 23, 2014 at 16:42

answered Oct 24, 2013 at 12:43

montrealmike

11.7k10 gold badges68 silver badges87 bronze badges

3 Comments

Neeraj Over a year ago

my script is outside rails application so i don't think find_by_sql would work.

montrealmike Over a year ago

you're right, i read your question too quickly. This answer might help others who come to this page based on your question title.

Малъ Скрылевъ Over a year ago

how to get not an array but relation?

ant · Accepted Answer · 2013-03-14 11:51:55Z

4

How about this :

@client = TinyTds::Client.new(
      :adapter => 'mysql2',
      :host => 'host',
      :database => 'siteconfig_development',
      :username => 'username',
      :password => 'password'

sql = "SELECT * FROM users"

result = @client.execute(sql)

results.each do |row|
puts row[0]
end

You need to have TinyTds gem installed, since you didn't specify it in your question I didn't use Active Record

edited Mar 14, 2013 at 11:51

answered Mar 14, 2013 at 11:45

ant

23k36 gold badges139 silver badges185 bronze badges

4 Comments

denis-bu Over a year ago

Sure, TinyTds is whole other gem.. It is not ActiveRecord. To use it you should install it separately.

Neeraj Over a year ago

I don't want to use any additional GEM as long as mysql2 and active-record is available

ant Over a year ago

Good, you should mention that in your question (tag/title)

cliffordheath Over a year ago

TinyTDS works with MSSQL and Sybase, it doesn't work with MySQL. ant: You should mention that in your answer, and not assume that the OP is using the same niche DBMS as you are.

Collectives™ on Stack Overflow

Rails 3 execute custom sql query without a model

5 Answers 5

3 Comments

3 Comments

2 Comments

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

2 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related