How can I integrate Ruby scraping scripts with databases?

Integrating Ruby scraping scripts with databases involves several steps. You'll need to scrape the data, process it, and then store it in the database of your choice. Here's a general process for integrating Ruby scraping scripts with databases:

Step 1: Set Up the Database

Before you can store any scraped data, you need to set up a database. This could be MySQL, PostgreSQL, SQLite, or any other DBMS that Ruby can interact with. Here's an example of setting up a SQLite database using Ruby's sqlite3 gem.

First, install the sqlite3 gem if you haven't already:

gem install sqlite3

Then, create a new SQLite database and a table to store your scraped data:

require 'sqlite3'

# Create a SQLite database in memory
db = SQLite3::Database.new ':memory:'

# Create a table to store the data
db.execute <<-SQL
  CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR(100),
    content TEXT
  );
SQL

Step 2: Scrape the Data

For scraping, you can use libraries like Nokogiri to parse HTML/XML content. Install the Nokogiri gem if it's not already installed:

gem install nokogiri

Now, use Nokogiri to scrape the data:

require 'nokogiri'
require 'open-uri'

# Fetch and parse the HTML document
doc = Nokogiri::HTML(URI.open('http://example.com/'))

# Let's assume you are scraping articles and they have a title and content
articles = doc.css('.article').map do |article|
  {
    title: article.at_css('.title').text.strip,
    content: article.at_css('.content').text.strip
  }
end

Step 3: Store the Data in the Database

Now that you have the data, you can insert it into the database you set up earlier:

articles.each do |article|
  db.execute "INSERT INTO articles (title, content) VALUES (?, ?)", [article[:title], article[:content]]
end

Step 4: Query the Database

After storing the data, you can query the database when needed:

db.execute "SELECT title, content FROM articles" do |row|
  puts row.join(" - ")
end

Error Handling and Data Validation

When integrating scraping scripts with databases, it's crucial to handle errors and validate data to prevent SQL injection or corrupt data entering your system. Use parameterized queries, as shown above, to avoid SQL injection.

Ruby ORM Option (ActiveRecord)

Alternatively, you can use an Object-Relational Mapping (ORM) library like ActiveRecord to make database interactions more manageable. ActiveRecord is a part of Ruby on Rails but can be used standalone as well.

To use ActiveRecord without Rails, you need to install the activerecord and sqlite3 gems:

gem install activerecord sqlite3

Then, set up the database connection and create a model:

require 'active_record'

# Establish connection
ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3',
  database: 'db/articles.db'
)

# Define a model
class Article < ActiveRecord::Base
end

# Create the table
ActiveRecord::Schema.define do
  create_table :articles do |t|
    t.string :title
    t.text :content
  end
end

# Use the model to interact with the database
Article.create(title: 'Sample Article', content: 'This is the content of the article.')

Conclusion

By following these steps, you can successfully integrate Ruby scraping scripts with databases. Just remember to keep your data clean and your database interactions secure.