import sqlite3
import requests
from bs4 import BeautifulSoup

con=sqlite3.connect("DBLectures.db")
cursor=con.cursor()
sahibinden="https://www.sahibinden.com/mazda-rx-rx-8"
headers = {'User-Agent': 'My User Agent 1.0', 'From': 'youremail@domain.com'}
r=requests.get(sahibinden,headers=headers)
soup=BeautifulSoup(r.content,"html.parser")
rx8 = soup.find_all("table",{"id":"searchResultsTable"})
table=rx8[0]
tr=table.find_all("tr")

def create_chart():
    cursor.execute("CREATE TABLE IF NOT EXISTS Rx8 (CarAdTitle TEXT,CarPrice REAL,CarAdDate TEXT,CarLocation TEXT)")

def add_data(con,CarAdTitle,CarPrice,CarAdDate,CarLocation):
    cursor.execute("INSERT INTO Rx8 (CarAdTitle,CarPrice,CarAdDate,CarLocation) VALUES (?,?,?,?)",(CarAdTitle,CarPrice,CarAdDate,CarLocation))
    con.commit()

def get_data():
    for car in tr:
        car_ad_title=car.find_all("td",{"class":"searchResultsTitleValue"})
        car_price=car.find_all("td",{"class":"searchResultsPriceValue"})
        car_ad_date=car.find_all("td",{"class":"searchResultsDateValue"})
        car_location=car.find_all("td",{"class":"searchResultsLocationValue"})
        try:
            car_ad_title1=car_ad_title[0].text.replace("\n","").replace("   ",">>>")
            car_price1=car_price[0].text.replace("\n","")
            car_ad_date1 = car_ad_date[0].text.replace("\n","").replace(" ","/").replace("2020","/2020")
            car_location1=car_location[0].text.replace("                        ",">>> ")
            add_data(con,car_ad_title1,car_price1,car_ad_date1,car_location1)
            con.close()
        except:
            assert ("0")

create_chart()
get_data()