This is a short and easy little post regarding Ruby and using it to query a MySQL database…
This came up for one of my students. I wanted him to get used to finding solutions to problems. I gave him a problem of validating some activity by automating a SQL call to a database. Any language could be used, but since we were working with Web Automation I picked Ruby.
there’s a variety of ways to get this done in Ruby, but one way that I found useful was to simply use the MySQL2 gem. Oddly enough the MySQL gem didn’t work for me, but the mysql2 gem did the trick.
This gem makes it easy to construct a SQL insert, delete or select, check it out below:
require 'mysql2' client = Mysql2::Client.new(:host=>"localhost", :username=>"test", :password=>"test") client.query("INSERT INTO sakila.actor (first_name, last_name) VALUES ('MATTHEW', 'WIL');") results = client.query("select * from sakila.actor where last_name='WIL'") results.each do | row | puts row end
The symbols host, username and password are all set up in the client creation object. I also have a local database called sakila with a table called actor. the actor table takes a first_name and last_name value.
The client object is then queried with regular SQL statements. As an example in the above code, I performed an INSERT and later a SELECT.
The select example creates an object called “results” based on a query and we iterate over the object (since more than one may exist) and output the results to console.
Once you have established data flowing in such an example, you can validate results coming back with assertions, in order to pass your test.