Fetch data from mysql and use it in Selenium tests


650
650 points
fetch data from mysql and use it in selenium tests
fetch data from mysql and use it in selenium tests

Fetch data from mysql and use it in Selenium tests

Sometimes our tests cannot become reliable if we don’t use some data from the database , you might have to fetch data from mysql and use it in Selenium tests. On every project that I’ve worked I have to create a database helper and fetch different data and use it in my selenium tests.

Prerequisites :

You need to have installed a mysql client on your machine , here is how to install mysql on linux  and here is how to install mysql on mac,   on windows should be pretty similar and much simpler I guess.

Install mysql gem file
gem install mysql
Create a database helper
require 'mysql2'

module MainModule
  class DB
    DB = MainModule.config.db
    DB_PORT = 3306
    TIMEOUT = 5
    LOG = '[MYSQL]'

    class << self
      def connect
        if !defined?(@@mysql) || !@@mysql.ping
          @@mysql = Mysql2::Client.new(host: DB['host'],
                                       username: DB['username'],
                                       password: DB['password'],
                                       database: DB['name'],
                                       port: DB_PORT,
                                       connect_timeout: TIMEOUT,
                                       auto_reconnect: true)
        end
      rescue Mysql2::Error => err
        warn_message LOG, err
      end

      def close_connection
        debug_message LOG, 'Close connection'
        @@mysql.close if defined?(@@mysql)
      end

      def clear_logins_counter(user = nil)
        user ||= MainModule.config.account['username']
        debug_message LOG, "Clear logins counter for user #{user}"

        execute_query do
          %{DELETE FROM user_login_counters
            WHERE username='#{user}';}
        end
      end


      # Execute mysql query (given: block with query as return value)
      def execute_query
        query = yield
        connect
        debug_message '[MYSQL]', @@mysql
        debug_message '[MYSQL PING]', @@mysql.ping
        debug_message LOG, query
        result = @@mysql.query(query) if defined?(@@mysql)
        # Print query execution result
        debug_message LOG, "Result:\n", result.to_a.join("\n") if result.respond_to? :to_a
      rescue Exception => err
        warn_message err
      ensure
        return result
      end

      private :execute_query
    end
  end
end

Your database credentials are stored inside /features/support/config.yml

db:
            name: database_name
            host: database.host
            password: TopSecretpassword
            username: username

And here is your cucumber step :

Advertisements
Given /^\[DB\] Clear logins counter$/ do
  MainModule::DB.clear_logins_counter
end

From now on , the world it’s yours!

If you are wandering where to chunk your module into the structure , you can have a look at one of the previous article about ruby watir page object structure.

Happy Testing!

 


Like it? Share with your friends!

650
650 points
Test engineer

0 Comments

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.