📝

SQLite3 のデータの追加について

2021/07/19に公開

たとえば、別々に 1 日中スクレイピングプログラムを回してデータを書き込んだ２つの SQLite3 データベースファイルを、重複無しに一つにできると 2 日フル分のデータが１日でできる。 4 日フル分でも１日でできる。スクレイピングプログラムのスレッドを増やすことができれば。

（並列で別のプロセスで SQLite3 に書き込んでいった複数の同じデータ構造のデータベース (files) を足し合わせると時間短縮できる。）

--- あたりまえだけど。---

iRuby というので google colab で Ruby 2.5.1 を扱えるので、その環境だとブラウザのスレッドを増やすと（ 2 つ colab のノート開くと 2 スレッド）スクレイピングのプログラムを増やすことができる。
しかし、 colab ではなく Android で Termux で Nokogiri がインストールできたので 2 つの Amazon Fire タブレットのターミナルで Ruby 3 (ruby 3.0.2p107 (2021-07-07 revision 0db68f0233) [arm-linux-androideabi-android]) で実践する。特に意味はないが、colab で iRuby をセットアップするよりダイレクトなのと、このままでは Ruby 3 で hello world 以上のことをする機会が来ない。

ライブラリの対応を調べていくのは少しハードルが高いかと思ったが、Termux への Nokogiri install については Nokogiri 公式サイトに載っていた。
バージョン指定で 1.8.1 を指定するとインストールできることまでは確認してそこでサスペンドしていたが、Nokogiri 公式の非公式なこの方法だと、より新しいバージョンでインストールされる。

pkg install ruby clang make pkg-config libxslt
gem install nokogiri --platform=ruby -- --use-system-libraries

SQLite3 のデータの足し合わせ

スクレイピングでデータを収集（参照:こんな）した 2 つの SQLite3 ファイル（db1.db , db2.db）、両方に約 2 万件以上のデータがあるとして、足して約 4 万件にして、重複データを消すというのを Ruby バインドでやるとすると、attach table なのかな？と思ってやってみると、結合についてまるで理解していないので、まずどう結合していいのかわからない。
それは、おいおいわかってくるとすれば、いずれはきっとうまいこといくでしょう、ですが、ここではわからないままでなんとかしたい。

おそらく、 SQLite3 じゃないサーバー型の SQL データベースだと別のプロセスでデータベースにアクセスしていくのが当然すぎて問題にならないのだと思う（イメージです。解って言っていることではありません）が、 SQLite3 には当てはまらず複数のアクセスでデータを記録していく方法がわからない。

何を言っているのかわからない、という人はそれは正解で、わかっていないからこのようにしていると思ってください。

手続きとしては単純なことなので SQLite3 で並べかえて、CSV へエクスポートして、そしても一度 SQLite3 に読み込んで 1 つのテーブルにすればよいと当初考えていたけれども、やってみると、とてもデータが減るので、重複がそんなにあるはずない ! とすると CSV にしたときにエクスポートやインポートに使うツールによりリミット上限なのか他の要因でかカットされていたということに気がついた。毎回件数が定数になっているということで、ようやく。

素直に愚直に、一件取り出して、比較して、一件データを書き足していくようにした。
そのうちもっといい方法がわかるようになるのか、もうわからないのか。

rqlite が使えたら、もうちょっと速くデータが集められるようになると思う。たぶん。

25006件入った db1 "mangathank_new1.db" tbl_manga

db1  tbl_manga
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text

28994 件入った db2 "mangathank_new2.db" tbl_manga

db2  tbl_manga
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text

この 2 つのデータベースのファイルのテーブルににスクレイピングで得たデータが入っている。それをデータの重複なく 1 つのテーブルにまとめたいということだ。

db1 "mangathank_new1.db"
tbl_manga

id	title	url	updated_datetime	author	book_title	tags
1	...	○	...	...	...	...
2	...	■	...	...	...	...
3	...	◆	...	...	...	...
.	...	...	...	...	...	...
.	...	...	...	...	...	...
25006	...	◇	...	...	...	...

db2 "mangathank_new2.db"
tbl_manga

id	title	url	updated_datetime	author	book_title	tags
1	...	▽	...	...	...	...
2	...	□	...	...	...	...
3	...	○	...	...	...	...
.	...	...	...	...	...	...
.	...	...	...	...	...	...
28994	...	▲	...	...	...	...

db1 と db2 を、 db1 "mangathank_new1.db" にまとめる。

db1 "mangathank_new1.db"
tbl_manga

id	title	url	updated_datetime	author	book_title	tags
1	...	○	...	...	...	...
2	...	■	...	...	...	...
3	...	◆	...	...	...	...
.	...	...	...	...	...	...
25006	...	◇	...	...	...	...
.	...	...	...	...	...	...
34000	...	▲	...	...	...	...

このようにしたい。url がユニークなので、重複か、そうでないかは url の値を媒介しててテストし、重複がなければ "mangathank_new1.db" の方へ新しいプライマリキーの id 値を割り振ってコラムデータをつけ足していく。

add_sqldbs.rb

require 'sqlite3'

db1 = SQLite3::Database.open "mangathank_new1.db"
db2 = SQLite3::Database.open "mangathank_new2.db"

last1 = db1.execute("select id from tbl_manga order by id desc limit 1")
last2 = db2.execute("select id from tbl_manga order by id desc limit 1")

i = 0
k = last1[0].pop # offset
l = last2[0].pop # 28994

l.times do |index|
    i += 1
    res = db2.execute("select * from tbl_manga where id='#{i}' ;")
    if res.empty?
        next
    end
    x = res[0][2] # 'url'
    f = db1.execute("select id,title from tbl_manga where url='#{x}' ;")
    p i

    if !f.empty? then
        p res[0][1]
        puts 'already exist in db'
        next
    else
        k += 1
	#res = db2.execute("select * from tbl_manga where id='#{i}' ;")
        puts
        puts
        res.each do |data|
            title = data[1]
            title.gsub!(/\'/,"\'\'")
            url = data[2]
            url.gsub!(/\'/,"\'\'")
            updated_datetime = data[3]
	    updated_datetime.gsub!(/\'/,"\'\'")
	    author = data[4]
            author.gsub!(/\'/,"\'\'")
            book_title = data[5]
            book_title.gsub!(/\'/,"\'\'")
            tags = data[6]
	    tags.gsub!(/\'/,"\'\'")
	    
            #puts k, title, url, updated_datetime, tags
	    
            db1.execute("insert into tbl_manga (id, title, url, updated_datetime, tags ) values( '#{k}','#{title}','#{url}','#{updated_datetime}','#{tags}') ;")
        end                                               
    end
end

db1.close
db2.close

puts 'last id:',k

"mangathank_new1.db" から "mangathank_new.db" を新たに作ってソートする。

id_sort_by_.rb

require 'sqlite3'

db = SQLite3::Database.open "mangathank_new1.db"
db2 = SQLite3::Database.open "mangathank_new.db"

SQL =<<EOS
create table tbl_manga (
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text
);
EOS

db2.execute(SQL)
db2.close

counter = 0
db2 = SQLite3::Database.open "mangathank_new.db"

db.execute("select * from tbl_manga order by title ;") do |data|
    if data.empty?
      next
    end

    counter += 1
    id = counter
    title = data[1].to_s.gsub(/\'/,"\'\'")
    url = data[2]
    updated_datetime = data[3]
    author = data[4].to_s.gsub(/\'/,"\'\'")
    book_title = data[5].to_s.gsub(/\'/,"\'\'")
    tags = data[6].to_s.gsub(/\'/,"\'\'")

    db2.execute("insert into tbl_manga (id, title, url, updated_datetime, author, book_title, tags ) values('#{id}', '#{title}','#{url}','#{updated_datetime}','#{author}','#{book_title}','#{tags}')")
    puts title
    puts counter
end

db2.execute("select id from tbl_manga order by id desc limit 1 ;") do |data|
    print "last id : "
    puts data
end

db2.close
db.close

こんな

sql.rb

require 'sqlite3'

db = SQLite3::Database.open "mangathank_new1.db"

$index = 0
#db.execute("select * from tbl_manga where id>42800;") do |data|
db.execute("SELECT id FROM tbl_manga ORDER BY id DESC LIMIT 1;") do |data|
    #pp data
    $index = data.pop
end

db.close
puts $index

scraping_to_sqlite3.rb

#2021.7.20
require 'net/http'
require 'uri'
require 'open-uri'

require 'sqlite3'
require 'nokogiri'
require 'csv'
require 'time'
require 'date'
require './mangathank_url'
require './sql.rb'

def get_url(uri)
    redirect = Net::HTTP.get_response(uri)['location']
    return redirect
end

target_uri = $mangathank_url
uri = URI.parse(target_uri)

SQL =<<EOS                                                                     
create table tbl_manga (
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text
    );
EOS

#db = SQLite3::Database.open "mangathank_new1.db"
#db.execute(SQL)
#db.close

count = $index.to_i # sql.rb $index
puts 'last id :',count

30000.times do |loop|
    db = SQLite3::Database.open "mangathank_new1.db"

    URL = get_url(uri)
    flag = db.execute("select id from tbl_manga where url='#{URL}'")

    puts
    #pp flag
    ##temp = flag[0]
    puts

    #if flag.any? then
    if !flag.empty? then
        p *flag
	puts 'already exist in db'
        ##db.execute("select * from tbl_manga where id='#{temp[0]}'") do |data|
        ##    puts data
        ##    puts
        ##end
        next
    else
        doc = Nokogiri::HTML(URI.open(URL))

        title = doc.css('.entry-title').inner_text
        tags = doc.css('#tags > p').children.inner_text
        updated = doc.css('.updated')
        updated_datetime = updated.attribute('datetime')
        title.to_s.strip!

#       p title
#       p tag
#       p updated_datetime.to_s
#       puts

        count += 1
        url = URL
        url.to_s.gsub!(/\'/,"\'\'")
        title.to_s.gsub!(/\'/,"\'\'")
        author_name = title.slice(/(?<=\[).*?(?=\])/)
        updated_datetime.to_s.gsub!(/\'/,"\'\'")
        tags.to_s.gsub!(/\'/,"\'\'")
        id = count

#       puts id
        puts
#       puts title
        #puts author_name

        db.execute("insert into tbl_manga (id, title, url, updated_datetime, tags ) values('#{id}', '#{title}','#{url}','#{updated_datetime}','#{tags}')")

        if author_name != nil then #
            author_name.gsub!(/\'/,"\'\'")

#            pp author_name

        end

        book_title = title.slice(/((?<=\]).+?$)/)

        if book_title then
            num = book_title.to_s.slice(/((?<=第)\d+(?=巻|卷$))/)
            book_title.gsub!(/((?=第).*(巻|卷))/,'')
            book_title.gsub!(/((?=第).*話)/,'')
            book_title.gsub!(/(.(?<=\()文庫版(?=\)).)/,'')
            book_title.gsub!(/(.(?<=\[)文庫版(?=\]).)/,'')
            book_title.gsub!(/文庫版/,'')
            book_title.gsub!(/(.(?<=\()完(?=\)).)/,'')
            book_title.gsub!(/(.(?<=【).*(?=】).)/,'')
            book_title.gsub!(/(.(?<=\[).+?(?=\]).)/,'')
            book_title.gsub!(/\'/,"\'\'")
            book_title.lstrip!
            book_title.rstrip!
        else
            book_title = title

            num = book_title.to_s.slice(/((?<=第)\d+(?=巻|卷$))/)
            book_title.gsub!(/((?=第).*(巻|卷))/,'')
            book_title.gsub!(/((?=第).*話)/,'')
            book_title.gsub!(/(.(?<=\()文庫版(?=\)).)/,'')
            book_title.gsub!(/(.(?<=\[)文庫版(?=\]).)/,'')
            book_title.gsub!(/文庫版/,'')
            book_title.gsub!(/(.(?<=\()完(?=\)).)/,'')
            book_title.gsub!(/(.(?<=【).*(?=】).)/,'')                                                                                                                                                            book_title.gsub!(/(.(?<=\[).+?(?=\]).)/,'')
            book_title.gsub!(/\'/,"\'\'")
            book_title.strip!
        end

        if num != nil then
            num = num.to_i
            book_title += ' ' + num.to_s
        end
#            pp book_title                                                                                                                                                                            
        db.execute("update tbl_manga set author = '#{author_name.to_s}', book_title = '#{book_title.to_s}' where id = '#{id}' ;")
        db.execute("select * from tbl_manga where id='#{id}'") do |data|
            puts data
            puts
        end
end

puts 'last id :',count

さてここへきて、Ruby 約 2 ヶ月。中学で必要な英語力くらいで、見てわかる自然言語のようなプログラムコードになるのはいいと思う。
https のリクエストの間隔をもうちょっと速くしたいので、速くなるのかどうかわからないが、一連を crystal でつくってみたいところだが、Android だと cystal コンパイラを arm に移植するというところからになるらしいので、nim か go に進みたい。
そんなにスクレイピングにはまりこみたいわけではないけれども、ひとつの目的を他の手段でどうするのかを見てみたい。

他の言語を眺めていたら、Lua が Ruby に似ていると気がついた。
Lua を Termux にセットして、パッケージマネージャ pkg からパッケージマネージャー luarocks をインストールしたら、それだけでもエラーが当たり前にあり、多難。
（インストールと同時に luarocks が参照するシンボリックリンクが間違っている。いったんデフォルトで luarocks からリンクされている Lua へのパスを unlink する。そこに Lua がない場合は、which Lua などコマンドで探して、シンボリックをつくりなおす。）

@localhost ~> ln -s /data/data/com.termux/files/usr/bin/lua5.3 /data/data/com.termux/files/usr/bin/lua

さらに SQLite3 も別途インストールして attach table をいじることしばし。
コーヒをいれ考える。

サブクエリーの書き方と様子をチェック。Lua の情報を読む（英語でも少ない。）、見る（ Youtube ）。

https://youtu.be/kgiEF1frHQ8

Lua 目線で SQLite のハウツーを探してみると Ruby よりはしっかりあるように感じた。Ruby の場合は、Rails が前提でいろいろ書かれたものは多いのだけど、それはカスタムメソッド基準なので除外していくので、そうするといくつも残らない。Lua の場合は元々情報が多くはない。

attach table を使って、データに重複ありの A ("mangathank_new1.db")、 B ("mangathank_new2.db")、2つのデータベースのテーブルからお互い重複を除外した B のデータで新たにデータベース C (new.db) をつくり、C に A のデータを足して、ソートして、新たに D というデータベースを用意して D にソートした C のコピー ("sorted_new_db2.db")を空白を除外してつくるプログラム。
attach についてはこのチュートリアルを参考にした。

ab_cd_sqlite3.rb

require 'sqlite3'

# A
db = SQLite3::Database.open "mangathank_new1.db"
# C
new_db2 = SQLite3::Database.open "new.db"

SQL =<<EOS
create table tbl_manga (
    id INTEGER PRIMARY KEY,
    title text,
    url text,
    updated_datetime datetime,
    author text,
    book_title text,
    tags text
    );
EOS

new_db2.execute(SQL)

last_id = 0
counter = 0

# B 
db2 = SQLite3::Database.open "mangathank_new2.db"
# B ... last 'id' number
db2.execute("select id from tbl_manga order by id desc limit 1 ;") do |data|
    last_id = data[0]
end

db2.close

# B attach to A
db.execute("attach 'mangathank_new2.db' as db2 ;")

(1..last_id).each do |n|
    flag_res = db.execute("select * from tbl_manga where url in (select url from db2.tbl_manga where id='#{n}') ;")

    if !flag_res.empty?

        puts 'already exist',"#{flag_res[0][1]}"
        next
    else
        counter += 1
        puts counter
        puts "#{flag_res[0][1]}"

        new_db2.execute("insert into tbl_manga (id, title, url, updated_datetime, author,book_title,tags ) values( '#{counter}','#{flag.res[0][1]}','#{flag.res[0][2]}','#{flag.res[0][3]}','#{flag.res[0][4]}','#{flag.res[0][5]}','#{flag.res[0][6]}') ;")
    end
end

# B detach from A
db.execute("detach db2")

# A ... length
db.execute("select id from tbl_manga order by id desc limit 1 ;") do |data|
    last_id = data[0]
end

(1..last_id).each do |n|
    db.execute("select * from tbl_manga where id='#{n}' ;") do |data|
        if data.empty?
            next
	end

        puts n

        title = data[1].to_s.gsub(/\'/,"\'\'")
        url = data[2].to_s.gsub(/\'/,"\'\'")
        updated_datetime = data[3]
        author = data[4].to_s.gsub(/\'/,"\'\'")
        book_title = data[5].to_s.gsub(/\'/,"\'\'")
        tags = data[6].to_s.gsub(/\'/,"\'\'")
	
	counter += 1
        puts counter
        puts title
# C        
        new_db2.execute("insert into tbl_manga (id,title,url,updated_datetime,author,book_title,tags) values('#{counter}', '#{title}','#{url}','#{updated_datetime}','#{author}','#{book_title}','#{tags}')")
    end
end

# A
db.close
counter = 0

# D
new_db3 = SQLite3::Database.open "sorted_new_db2.db"

new_db3.execute(SQL)

# C to D 
new_db2.execute("select * from tbl_manga order by title ;") do |data|
    if data.empty?
        next
    end
    
    counter += 1
    id = counter
    title = data[1].to_s.gsub(/\'/,"\'\'")
    url = data[2]
    updated_datetime = data[3]
    author = data[4].to_s.gsub(/\'/,"\'\'")
    book_title = data[5].to_s.gsub(/\'/,"\'\'")
    tags = data[6].to_s.gsub(/\'/,"\'\'")
# D	
    new_db3.execute("insert into tbl_manga (id,title,url,updated_datetime,author,book_title,tags ) values('#{id}','#{title}','#{url}','#{updated_datetime}','#{author}','#{book_title}','#{tags}')")
    puts counter
    puts title
end

# C
new_db2.close

# D ... length
new_db3.execute("select id from tbl_manga order by id desc limit 1 ;") do |data|
    print "last id : "
    puts data
end

# D
new_db3.close

SQLite3 のデータの追加について

SQLite3 のデータの足し合わせ

関連記事

Discussion