I want to graph the number of photo AC downloads [MySQL ring cooperation] ~ Coding 10 lines a day ~

Day 4

Continuing from yesterday, we will create a part of the graph app that actually stores the data.

[Technical theme]

Environment

language

MySQL

Target deliverable

Today, I would like to complete the construction of the MySQL environment and store the previous information in the DB.

Code and documentation

1. Install MySQL

https://qiita.com/hkusu/items/cda3e8461e7a46ecf25d Execute the specified command referring to this article

[Execution command]

ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

result

curl: (22) The requested URL returned error: 404 Not Found

It was useless probably because the referenced data was old .... I thought, but this should be done with the Homebrew installation, so I'll run the next MySQL installation part. [Execution command]

brew update
brew install mysql

Installation is complete with just this! I've set the password etc., but it seems that it can no longer be easily accessed from the recent sequence pro ....

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

I had to make this setting.

2. Add library

In order to put data in MySQL, you need a library for it, so add the following line to the Gemfile.

gem 'activerecord'
gem 'mysql2', '0.5.2'

In that state

bundle install --path .bundle When you execute. I got an error

bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl/lib"

When I ran this command, I was able to install it without any problems.

3. Storage of log data

First, define a table to store the data. What we need this time is how many images are downloaded in what days, so I wondered if the table would be roughly as shown below.

CREATE TABLE `logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `image_id` int DEFAULT NULL,
  `download` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Then modify the code for the crawler. First for connecting to MySQL

`crawler.rb`


require 'active_record'
require 'date'


#DB connection processing
ActiveRecord::Base.establish_connection(
  :adapter  => 'mysql2',
  :database => 'Database name',
  :host     => 'localhost',
  :username => 'username',
  :charset => 'utf8mb4',
  :encoding => 'utf8mb4',
  :collation => 'utf8mb4_general_ci',
  :password => 'password'
)

class Log < ActiveRecord::Base; end

Add the above code. On top of that, the main part of the parsing process

`crawler.rb`


page = agent.get("https://www.photo-ac.com/creator/list/?pl_q=&pl_order=-releasedate&pl_pp=200&pl_disp=all&pl_ntagsec=&pl_tags50over=&pl_chkpsd=")
doc = Nokogiri::HTML.parse(page.body, nil, 'utf-8')
doc.css(".photo-list").each{|div|
  image_id =  div.css(".sectiondata li")[0].text.split("：")[1]
  download = div.css(".sectionimg .preview")[0].text
  Log.create({image_id:image_id,download:download,date:Date.today})
}

Rewrite like this. And if you run it as usual, the data will be stored properly.