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

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.

This completes the data collection part. (It's been a little past ....)

Recommended Posts

I want to graph the number of photo AC downloads [MySQL ring cooperation] ~ Coding 10 lines a day ~
I want to graph the number of photo AC downloads [Scraping implementation] ~ 10 lines per day coding ~
I want to output the day of the week
[Ruby] I want to make a program that displays today's day of the week!
I want to call a method and count the number
I want to display the number of orders for today using datetime.
I want to var_dump the contents of the intent
I want to recursively get the superclass and interface of a certain class
[Rails] I want to display the link destination of link_to in a separate tab
I want to reduce the number of unnecessary queries. From considering counter_cache to introducing counter_culture.
I want to call a method of another class
I want to know the answer of the rock-paper-scissors app
I want to display the name of the poster of the comment
I want to be aware of the contents of variables!
I want to return the scroll position of UITableView!
I want to connect to Heroku MySQL from a client
I want to add a delete function to the comment function
I want to get a list of the contents of a zip file and its uncompressed size
[Java] I want to convert a byte array to a hexadecimal number
I want to expand the clickable part of the link_to method
I want to make a specific model of ActiveRecord ReadOnly
I want to change the log output settings of UtilLoggingJdbcLogger
I want to create a form to select the [Rails] category
I want to give a class name to the select attribute
When you want to change the MySQL password of docker-compose
I want to narrow down the display of docker ps
[Ruby] I want to reverse the order of the hash table
I want to temporarily disable the swipe gesture of UIPageViewController
[Swift] When you want to know if the number of characters in a String matches a certain number ...
I tried to express the result of before and after of Date class with a number line
I want to understand the flow of Spring processing request parameters
The story of Collectors.groupingBy that I want to keep for posterity
I made a gem to post the text of org-mode to qiita
I want to limit the input by narrowing the range of numbers
I want to control the default error message of Spring Boot
I made a tool to output the difference of CSV file
I want to change the value of Attribute in Selenium of Ruby
Coding 10 lines a day ~ Scraping implementation ~
A memo when you want to clear the time part of the calendar
[Rails] I want to send data of different models in a form
Create a mechanism to post reservations to Qiita [Reservation posting] ~ Coding 10 lines a day ~ ~
I want to know the JSP of the open portlet when developing Liferay
[Ruby] I want to extract only the value of the hash and only the key
I want to pass the argument of Annotation and the argument of the calling method to aspect
[CircleCI] I was addicted to the automatic test of CircleCI (rails + mysql) [Memo]
I want to get the field name of the [Java] field. (Old tale tone)
I want you to use Enum # name () for the Key of SharedPreference
After all I wanted to preview the contents of mysql with Docker ...
I want to get a list of only unique character strings by excluding fixed character strings from the file name