Sql ile birçok şey yapabilirsiniz fakat bir noktaya kadar geldiniz ve tıkandınız. Bir yardımcıya ihtiyacınız var ama ortalığı çok dağıtmakda istemiyorsunuz. 🙂 Sql server bunuda düşünmüş ve sizin için Python, r gibi dilleri kullanma imkanı vermiş. Fazla uzatmadan yazımıza geçelim.
Senaryomuzda bir sql datamız var malum önümüzde yeni yıl patron katalog çıkarmınızı yeni senede resimli bir fiyat listesi ayarlamanızı istiyor. Bu listeyi hazırlayan sadece sizde değilsiniz bu sebeple rakip firmaların fiyat karşılaştırmasını da yapmanızı istiyor. Eğer 3-5 ürün için bunu yapacaksanız çok sorun değil. 1 saat internet araştırması ile sonuca gidebilirsiniz fakat sql tablonuzda binlerce ürün olduğunu düşünürseniz muhtemelen 1 sonraki seneye bir rapor sunmak zorunda kalacaksınız.
Peki bu durumda ne yapabiliriz ben örnek resimli bir katalog veya fiyat listesi için arama motorunda yararlanacağınızı düşündüm. Bunun içinde bing’den arama yaptırıp resimleri download etmek istedim.https://www.linkedin.com/embeds/publishingEmbed.html?articleId=9156970697500563122
Evet bu kadar kolay kullanacağınız dil ve dil’e ait kodunuzu sql’e bildirmeniz yeterli oluyor. Sql 2016’dan itibaren R, 2017’den itibaren ise Python’u destekliyor.
EXECUTE sp_execute_external_script @language = N'Python'
,@script = @kod
Örnekde script içerisinden download ettiğim resimleri c:\resimler’e kayıt ettim. Bunun için kayıt yapacağınız dizinde güvenlik ayarlarından NT Service\MSSQLLaunchpad, ALL APPLICATION PACKAGES (TÜM UYGULAMA PAKETLERİ) kullanıcılarına yazma izini vermeniz gerekmektedir.
Sql server ile direkt alakalı olmasada örnek’de uygulamadığımız için Python scriptinide aşağıda paylaşıyorum.
declare @arama nvarchar(255)='cerrahi maske'
declare @kod nvarchar(max)=N'
import requests
import shutil
import os
import sys
import urllib.request
import urllib
import imghdr
import posixpath
import re
indirilecekResim=6
resimNo = 0
sayfaUst = {"User-Agent": "Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0"}
webAdres = ("https://www.bing.com/images/async?q='+replace(@arama,' ','%20')+'&first=1&count=1&adlt=1&qft=1").encode("ascii", "ignore").decode("ascii")
request = urllib.request.Request(webAdres, None, headers=sayfaUst)
response = urllib.request.urlopen(request)
html = response.read().decode("utf8")
resimler = re.findall("murl":"(.*?)"", html)
for resim in resimler:
resimNo += 1
if resimNo <= indirilecekResim:
dosya="resim"+str(resimNo)+".jpg"
r = requests.get(resim)
with open("c:/resimler/"+dosya, "wb") as outfile:
outfile.write(r.content)
print(resimNo,resim,dosya)
'
EXECUTE sp_execute_external_script @language = N'Python'
,@script = @kod
Şimdi bing’den arama yaptık resimleri indirdik eğer resim isimlerinizi tablonuzdaki ID, Ürün kodu gibi bir alanla isimlendirirseniz bu sayede resimli katalog, fiyat listeniz için datanız hazır olacaktır. (Daha sonra mevcut datanız ile sql’de katalog, resimli fiyat listesi hazırlama ve bunu web adresinize otomatik yükleme içinde bir makale yazacağım. Evet hepsini sql ile yapacağız. 🙂 )
Fakat alışveriş sitelerinden ürün bilgilerinide almanız gerekiyor gerek karşılaştırma yapmak için gerekse rakip fiyat analizi vb.
Bu örnek içinde hepsiburada’yı seçtim. Daha önceki makalemdeki xmlHttp ile yapmak istedim fakat hepsiburada buna önlem aldığı için (403 error-xmlHttp referer yolluyor siz header tanımlasanızda) yine Sql Server içerisinde Python kullanmak zorunda kaldım.https://www.linkedin.com/embeds/publishingEmbed.html?articleId=7939080209816031358
Yazı çok karışık olmasın diye hepsiburada kodlarını ve açıklamalarını aşağıda yazacağım burada tek fark verilerin bir çıktısını almak için @output_data_1_name kullandım. df değişkenine atadığım bilgileri output_data ile alıp grid’e yazdırdım.
EXECUTE sp_execute_external_script @language = N'Python'
,@script = @script
,@output_data_1_name = N'df'
Peki bu ne işimize yarayacak eğer siz gelen verileri kalıcı olarak bir tabloya alıp daha sonra kullanacaksanız bu şekilde bir çıktı üretmeniz gerekmektedir.
Örnek olarak hepsiburadaVeriler tablosuna sonuçlarımızı yazdırıyoruz.
drop table if exists hepsiburadaVeriler
create table hepsiburadaVeriler (urunAdi nvarchar(255), urunResim nvarchar(255),urunFiyat nvarchar(255))
insert into hepsiburadaVeriler
EXECUTE sp_execute_external_script @language = N'Python',@script = @script,@output_data_1_name = N'df'
select * from hepsiburadaVeriler
sp_execute_external_script burada bitmiyor. Verileri dışarıya verebildiğiniz gibi verileri içeriye alıp Python ve R ile analiz yapabilirsiniz bunu bir başka makalede daha anlamlı bir örnekle yapmak istiyorum.O yüzden şimdilik sp_execute_external_script makalesini bitiriyorum.
Bundan sonrası sql içinde yazdığımız Python kodları ile ilgilidir. Eğer sadece Sql server kısmı ile ilgileniyorsanız burayı es geçebilirsiniz.
Hepsiburada veri çekme kodları.
declare @arama nvarchar(255)='cerrahi maske'
declare @script nvarchar(max)=N'
import urllib.request
import re
headers = {
"authority":" www.hepsiburada.com",
"cache-control":" max-age=0",
"sec-ch-ua":" ""Google Chrome"";v=""87"", "" Not;A Brand"";v=""99"", ""Chromium"";v=""87""",
"sec-ch-ua-mobile":" ?0",
"upgrade-insecure-requests":" 1",
"user-agent":" Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36",
"accept":" text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
"service-worker-navigation-preload":" true",
"sec-fetch-site":" none",
"sec-fetch-mode":" navigate",
"sec-fetch-user":" ?1",
"sec-fetch-dest":" document",
"accept-language":" tr-TR,tr;q=0.9,en-US;q=0.8,en;q=0.7",
"cookie":" isGlobalIp=0; HB_S_MR=1; "
}
adres = ("https://www.hepsiburada.com/ara?q='+replace(@arama,' ','%20')+'").encode("ascii", "ignore").decode("ascii")
request = urllib.request.Request(adres, None, headers=headers)
response = urllib.request.urlopen(request)
html = response.read().decode("utf-8","replace")
isimlerRegex="""<h3 class=\"product-title title\" title=\"(.+)\">""" #<h3 class="product-title title" title="{veri}"> olanları getiri.
isimlerPattern=re.compile(isimlerRegex)
isimler=re.findall(isimlerPattern,html)
resimlerRegex="""img data-src=[\"''](.+)[\"'']""" #img data-src={veri} gelir.
resimlerPattern=re.compile(resimlerRegex)
resimler=re.findall(resimlerPattern,html)
re_list = [
"<span class=\"price product-price\">(.+)</span>",
"<span class=\"price old product-old-price\" style=\"text-decoration: none;\">(.+)</span>",
]
fiyatlar = []
for r in re_list:
fiyatlar += re.findall( r, html)
df = pandas.DataFrame({"Isimler":isimler,"Resimler":resimler,"Fiyatlar":fiyatlar})
'
drop table if exists hepsiburadaVeriler
create table hepsiburadaVeriler (urunAdi nvarchar(255), urunResim nvarchar(255),urunFiyat nvarchar(255))
insert into hepsiburadaVeriler
EXECUTE sp_execute_external_script @language = N'Python',@script = @script,@output_data_1_name = N'df'
select * from hepsiburadaVeriler
Bu kodları biraz açıklamam gerekiyor. Hepsiburada header ve referer bilgisi kontrolü yaptığı için (403) bir tarayıcı ile hepsiburadayı ziyaret ettim ve header bilgilerini kod içerisine yapıştırdım (detaylı kontrol etmedim neye baktığını)
arama değerinde Türkçe karakter ve boşluk olursa problem oluyordu Python’da bu sebeple replace ve encode daha sonra decode yapmak zorunda kaldım arama cümlesini.
Bu scripti çalıştırdığımızda aslında html değişkenine hepsiburadanın tüm sayfa içeriğini getiriyor. Gelen html kodundan regex kullanarak isimler, resimler ve fiyatları ayıklamam gerekti. Regex’de zorlandığım için şu siteden yararlandığımı söyleyim www.regex101.com
Html cümlelerinden veri ayıklamak için çok sık kullanılan beautifulsoup4 diye bir modül aslında var ama yazıyı karmaşık yapmamak için regex ile çözmeye çalıştım.
pandas modülü veri ile uğraşıyorsanız Python ile en çok kullanılan modüllerden normalde import ile scriptinize dahil etmeniz gerekli ama sql server’ı yazan sevgili arkadaşlar bunuda düşünmüş ve varsayılan olarak pandas’ı dahil etmişler sağolsunlar.
regex ile aldığımız veriler aslında bize bir class:list döndürüyor. pandas.DataFrame bize class:list’leri satırlara dönüştürmemizi ve diğer değerleri ile birleştirerek 3 kolonlu bir veri listesi oluşturmamızı sağlıyor.
Bundan 2 gün öncesine kadar Python hakkında herhangi bir kod bilgim yoktu. Aslında resimli katalog ve fiyat listesini sql’de hazırlamak ve web’de yayınlamak için bir makale yazacaktım ama resimli uygun bir data bulamadığım için bununla uğraşmak zorunda kaldım. Bunuda makale haline getirmek istedim. Sql Server’ın Python seçimi yaparken çok doğru bir karar verdiğini söylemeliyim öğrenmesi kolay, oldukça az kod ile performanslı çalışan güçlü bir dil.
