R ve SQL ile Web Scraping (Kazıma)

2019 yılında Verikar Yazılım olarak Kaça isminde bir uygulama geliştirmiştik. Konu ile ilgili görsellere Kaça Tanıtım – YouTube ve Market Market Gezmeye Son Dedirtecek Uygulama – YouTube adreslerinden ulaşabilirsiniz. Uygulama bu yazının yayımlanmasından daha önce kullanımdan kaldırılmıştır.

Web’ten ürün fiyatı toplarken çok farklı yaklaşımlar kullanabilirsiniz. Birçok kullanıcı Python tabanlı çözümleri tercih edebilir. Ancak unutulmaması gereken bir konu varki toplanan veriler bir veritabanına konulmadığı sürece çöpten başka bir şey değildir. Bu açıdan veritabanı ile entegre olmayan bir web kazıma işlemi çöp yığını oluşturmaktan başka bir fayda sağlamayabilir. Kaça uygulaması çalıştığı zamanlarda .Net platformunda geliştirdiğimiz özel kodlar yardımıyla web üzerinden veri toplamıştık. Bu tür programlar geliştirildiğinde en önemli açmaz ilgili web sayfasında değişiklik olduğunda programınızı tekrar güncellemeniz ve yeniden derlemeniz gerekmesidir.

Bu yazımız Rvest paketi yardımıyla web kazıma, elde edilen verilerin SQL veritabanında tutulması ve işlenmesi konularındadır. Öncelikle mevcut sistemimizde R 4.2.1. versiyonu RStudio üzerinde çalışırken veritabanı olarak MSSQL Server 2019 Development versiyonu kurulu olduğunu hatırlatmak isterim. R paketleri yardımıyla web’ten toplanan verilerin VeriAktar veritabanında RvestImports isminde bir tabloya kaydedildiğini varsayalım. Bu tablonun SQL’de şu şekilde oluşturulduğunu varsayalım.

USE [VeriAktar]
GO

/****** Object:  Table [dbo].[RvestImports]    Script Date: 1/12/2023 10:57:35 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RvestImports](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[img] [nvarchar](300) NULL,
	[title] [nvarchar](200) NULL,
	[fiyat] [nvarchar](50) NULL,
	[timestamp] [datetime] NULL,
	[barcode] [nvarchar](50) NULL,
	[price] [float] NULL,
 CONSTRAINT [PK_RvestImports] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RvestImports] ADD  CONSTRAINT [DF_RvestImports_timestamp]  DEFAULT (getdate()) FOR [timestamp]
GO

Yukarıdaki şekilde SQL tablosu oluşturduktan sonra artık Rvest paketiyle web üzerinden veri çekebiliriz. Burada veri çektiğiniz web sitesinin yapısı da dikkate alınmalıdır. Öncelikle gereken R paketlerini şu şekilde yükleyelim.

library(dplyr)
library(tidyverse)  

# Parsing of HTML/XML files  
library(rvest)    

# String manipulation
library(stringr)   

# Verbose regular expressions
library(rebus)     

# Eases DateTime manipulation
library(lubridate) 
library(purrr)
library(magrittr)
library(RODBC)

Daha sonraki aşamada veri çekilecek sitenin ürün sayfaları dikkate alınarak sayfalara ulaşılmalı, ilgili alanlardaki veriler toplanmalı ve veritabanına yazdırılmalıdır.

dbhandle <- odbcDriverConnect('driver={SQL Server};Server=*****;Database=VeriAktar;UID=sa;PWD=*****;')

urlData <- read.table("egesarkuteriUrls.txt",header=FALSE,as.is = TRUE)
#print(urlData[2,1])

Yukarıdaki R kod blokunda SQL Server ve Password alanları “*****” ile karartılmıştır. Sisteminizin gerektirdiği şekilde belirtebilirsiniz. Bu noktada SQL Server için standard port dışında bir port kullanmanızı tavsiye ederim. Standard port 1433’tür. Bu şekilde daha güvenli bir SQL konfigürasyonu sağlanabilir ve SQL Server’da buna göre bir erişim ayarı yapmanız gerekir. Erişilecek sayfa listesinin bir txt dosyasında kaydedildiğini varsayalım. Unutmayın bu liste veritabanında da tutulabilir. Sadece farklı erişim yöntemlerinin de kullanılabileceğini göstermek istiyoruz. İlgili dosyayı yukarıdaki linkten çalışma dizininize indirebilirsiniz.

Artık link listemizdeki tüm HTML sayfaları taranarak bu sayfalardaki ürünlerin isimleri, fiyatları ve görsellerinin url adresleri toplanılabilir. Toplanan bu veriler veritabanına yüklenmelidir. Aşağıdaki kod blokunda her HTML sayfasında tüm ürün bilgileri fiyat, imaj ve baslik alanlarına kaydedilir. Bu kaydedilen veriler makro halinde hazırlanan SQL komutları ile veritabanına yazdırılır. for döngüsünün her url için yapıldığına dikkat edin. Unutmayın ki her HTML sayfasında bir ürün kategorisine ait tüm ürünler listelenmiştir. Örnek bir link için tıklayınız.

for(i in 1:length(urlData$V1))
{
egeUrl<-urlData[i,1]

#egeUrl<-"https://www.egesarkuteri.com/kategori/11/1/1"
ege<- read_html(egeUrl)

fiyat <- ege %>% 
  html_nodes('.f2') %>% 
  html_text() %>%
  unlist()

imaj <- ege %>% 
  html_nodes(".im a img")  %>% 
  html_attr('src') %>% 
  unlist()

baslik <- ege %>% 
  html_nodes(".ad a")  %>% 
  html_attr('title') %>%  
  unlist()

df<-data.frame(imaj,baslik,fiyat)
values <- paste( " df[  , c(", 
                 paste( names(df),collapse=",") ,
                 ")] ", collapse="" ) 
values <- paste("('",df$imaj,"','", df$baslik,"','",df$fiyat,"')", sep="", collapse=",")
sqlcmd <- paste("insert into [RvestImports] ([img],[title],[fiyat]) values ", values)

result <- sqlQuery(dbhandle, sqlcmd, as.is=TRUE)
}
close(dbhandle)

Yukarıda R içinden çalıştırılan SQL komutlarında bir nevi makro yazılarak bir sayfada bulunan tüm ürünlere ait fiyat, görsel adresi ve ürün isimleri aynı anda SQL komutuyla veritabanına yazılır. Burada insert into SQL komutunun yazım kuralları dikkate alınarak işlem yapıldığına dikkat edelim. Veriler tabloya yazıldıktan sonra SQL’de kısa bir işlem yapılması gerekmektedir. Unutmayalım ki elde ettiğimiz veriler metin (string) haldedir. En azından fiyatın sayısal hale dönüştürülmesi gerekmektedir. Diğer veri temizleme işlemleri SQL ile kolayca yapılabilir.

update [VeriAktar].[dbo].[RvestImports]
 set barcode= ltrim(rtrim(replace(replace(img,'https://app1.beesistem.com/Mcf6Cmrx_EgeSarkuteri/foto/urun/listeleme/',''),'.jpg',''))),
 price = cast(ltrim(rtrim(replace([fiyat],'TL',''))) as float)
 where barcode is null

Umarım bu yazı faydalı olmuştur. Sizlerde yeni fikirler oluşmasına yardımcı olur. Tüm kod bloku aşağıda yeniden verilmiştir.

library(dplyr)
library(tidyverse)  

# Parsing of HTML/XML files  
library(rvest)    

# String manipulation
library(stringr)   

# Verbose regular expressions
library(rebus)     

# Eases DateTime manipulation
library(lubridate) 
library( purrr)
library(magrittr)
library(RODBC)

dbhandle <- odbcDriverConnect('driver={SQL Server};Server=*****;Database=VeriAktar;UID=sa;PWD=*****;')

urlData <- read.table("egesarkuteriUrls.txt",header=FALSE,as.is = TRUE)

for(i in 1:length(urlData$V1))
{
egeUrl<-urlData[i,1]

#egeUrl<-"https://www.egesarkuteri.com/kategori/11/1/1"
ege<- read_html(egeUrl)

fiyat <- ege %>% 
  html_nodes('.f2') %>% 
  # The status information is this time a tag attribute
  html_text() %>%
  unlist()

imaj <- ege %>% 
  html_nodes(".im a img")  %>% 
  html_attr('src') %>% 
  unlist()

baslik <- ege %>% 
  html_nodes(".ad a")  %>% 
  html_attr('title') %>% 
  unlist()

df<-data.frame(imaj,baslik,fiyat)
values <- paste( " df[  , c(", 
                 paste( names(df),collapse=",") ,
                 ")] ", collapse="" ) 
values <- paste("('",df$imaj,"','", df$baslik,"','",df$fiyat,"')", sep="", collapse=",")
sqlcmd <- paste("insert into [RvestImports] ([img],[title],[fiyat]) values ", values)

result <- sqlQuery(dbhandle, sqlcmd, as.is=TRUE)
}
close(dbhandle)