Step by Step Guide to Scraping PakWheels Cars Data and Saving into SQL Server


Introduction:

In this blog, we will explore how to scrape car data from PakWheels using Selenium, process the information with Pandas, and store it in a SQL Server database. This guide is designed for anyone interested in automating data extraction and database integration using Python.

Technologies Used in the Scraping Process:

In this project, we leverage several key technologies and tools to efficiently scrape and manage car data from PakWheels:
  • Python: The core programming language used for scripting the entire scraping process.
  • Selenium: A powerful web automation tool that helps us control a web browser to interact with and scrape dynamic content from PakWheels.
  • WebDriver Manager: Automatically manages and installs the correct version of ChromeDriver needed to run Selenium smoothly.
  • Pandas: A versatile data analysis library used to structure and process the scraped car data into a DataFrame for further manipulation and storage.
  • PyODBC: A library that enables seamless communication between Python and SQL Server, allowing us to store the scraped data into a relational database.
  • SQL Server: The database system where all the extracted data is stored for future access and analysis.
These technologies work together to automate the process of scraping web data and efficiently store it in a structured database.

Let’s Start Scraping: Pre-requisites Installation


pip install selenium webdriver-manager pyodbc pandas
This command will install:

Selenium for web scraping.
WebDriver Manager for managing the browser driver.
PyODBC for connecting Python to SQL Server.
Pandas for data processing and manipulation.

Importing Required Libraries

After installing the necessary libraries, the next step is to import them into our Python script. The following imports are essential for our scraping project:
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from webdriver_manager.chrome import ChromeDriverManager
import pyodbc
import time
import pandas as pd
With these libraries imported, we are now ready to begin the scraping process!

Including a User-Agent Header

To prevent being blocked during the scraping process, we include a User-Agent header that simulates a request from a real web browser. This header string helps us bypass any restrictions that websites may impose on automated requests. By using a User-Agent, we make our scraping activity appear more like regular user traffic, reducing the likelihood of getting blocked.
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36'
}

Creating the Scrape PakWheels Data Function

First, we'll create a function called scrape_pakwheels that will handle the entire scraping process. This function will initialize an empty list to store the car data and set up the Chrome WebDriver for navigating the web pages.
def scrape_pakwheels():
    BetterDF = []
This function serves as the foundation for our scraping process, and we will build upon it to gather data from multiple pages of the PakWheels website.

Setting Up Chrome WebDriver Options

After creating the scrape_pakwheels function, the next step is to configure the Chrome WebDriver options:
options = webdriver.ChromeOptions()
options.add_argument(f"user-agent={headers['User-Agent']}")
options.add_argument('--ignore-certificate-errors')
service = Service(ChromeDriverManager().install())
driver = webdriver.Chrome(service=service, options=options)
We configure the Chrome WebDriver, set the User-Agent string, and handle SSL certificate errors using Chrome options. The WebDriver Manager automatically downloads and installs the ChromeDriver. With these configurations in place, we’re now ready to begin scraping data from the PakWheels website!

Looping Through Multiple Pages

for i in range(1, 391):
    print(f'Page number is {i}')
    url = f'https://www.pakwheels.com/used-cars/lahore/24858?registration_city=punjab&page={i}'
    driver.get(url)
    time.sleep(3)
This loop goes through each page of PakWheels by dynamically generating URLs for up to 390 pages. It uses time.sleep(3) to allow the page to load completely before scraping data.

Extracting Car Data from Each Listing


containers = driver.find_elements(By.CLASS_NAME, 'col-md-9.grid-style')
This finds all the car containers on the page by identifying their class name. Each container holds details like the car's title, price, and more.

Extracting Specific Details from Listings

for container in containers:
    title = container.find_element(By.TAG_NAME, 'h3').text.strip()
    price = container.find_element(By.CLASS_NAME, 'price-details').text.strip().replace('\n', '')
We loop through each container to extract specific details, such as the car's title and price. The find_element method locates elements within each container, while .strip() cleans up any unnecessary whitespace from the extracted text. Additionally, .replace('\n', '') ensures that any newlines in the price are removed for cleaner data.

Extracting Vehicle Information and Link

ul = container.find_element(By.CLASS_NAME, 'list-unstyled.search-vehicle-info-2.fs13')
li = ul.find_elements(By.TAG_NAME, 'li')
Year = li[0].text.strip()
Mileage = li[1].text.strip()
Fuel = li[2].text.strip()
HP = li[3].text.strip()
Transmission = li[4].text.strip()
link = container.find_element(By.CLASS_NAME, 'car-name.ad-detail-path').get_attribute('href')
This block extracts additional details from each listing, such as the car's year, mileage, fuel type, horsepower, and transmission. Additionally, the direct link to the car listing is retrieved for easy access.

Storing Data in a List


BetterDF.append([title, price, Year, Mileage, Fuel, HP, Transmission, page, link])
All the extracted details—title, price, year, mileage, fuel type, horsepower, transmission, page, and link—are stored in a list format and appended to BetterDF. This data will later be converted into a Pandas DataFrame for further manipulation and storage.

Closing the Web Driver


driver.quit()
After the scraping process is complete, we ensure that the Chrome browser is closed properly to free up system resources.

Converting the Data to a Pandas DataFrame

df = pd.DataFrame(BetterDF, columns=['TITLE', 'PRICE',  'Year', 
'Mileage', 'Fuel Type', 'Horsepower', 'Transmission', 'Page', 'Link']) 
return df
The collected data is converted into a Pandas DataFrame, making it easier to manipulate and prepare for insertion into the database. The columns in the DataFrame correspond to the data extracted from each car listing, allowing for organized and structured data handling.

Inserting Data into SQL Server


def insert_into_database(cars_df):
This function takes the scraped data stored in the Pandas DataFrame and inserts it into the SQL Server database.

SQL Server Connection Setup

connection = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=Your_Server_Name;'
    'DATABASE=Your_Database_Name;'
    'Trusted_Connection=yes;'
)
We establish a connection to SQL Server using PyODBC. Here, we specify the driver, server, and database name. We use Windows authentication (Trusted_Connection=yes) to access the database securely.

Creating the Table If It Doesn’t Exist

cursor.execute('''
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Table_Name' AND xtype='U')
BEGIN
    CREATE TABLE Table_Name (
        id INT IDENTITY(1,1) PRIMARY KEY,
        Title NVARCHAR(255) NOT NULL,
        Price NVARCHAR(50),
        Year NVARCHAR(10),
        Mileage NVARCHAR(50),
        Fuel_Type NVARCHAR(50),
        Horsepower NVARCHAR(50),
        Transmission NVARCHAR(50),
        Page NVARCHAR(10),
        Link NVARCHAR(MAX)
    )
END
''')
Before inserting the data, we ensure that the table PakWheels_Cars_Lhr exists. If it doesn't, we create it with appropriate column types and constraints, ensuring that it can hold the data we plan to insert.

Inserting Data into the Table

for index, row in cars_df.iterrows():
    cursor.execute('''
        INSERT INTO Table_Name (Title, Price, Year, Mileage, Fuel_Type, Horsepower, Transmission, Page, Link)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', 
    row['TITLE'], 
    row['PRICE'],
    row['Year'], 
    row['Mileage'], 
    row['Fuel Type'], 
    row['Horsepower'], 
    row['Transmission'], 
    row['Page'], 
    row['Link']
)
We loop through each row of the Pandas DataFrame and insert the data into the SQL Server table. The ? placeholders are replaced by actual values from the DataFrame, ensuring that the data is correctly mapped to the corresponding columns in the database.

Commit and Close Database Connection

connection.commit()
cursor.close()
connection.close()
After inserting the data, we commit the transaction to the database and close the cursor and connection to free up resources. This step is essential to ensure that all changes are saved and that the database connection is properly terminated.

Running the Scraping and Database Insertion Process

for index, row in cars_df.iterrows():
    if __name__ == '__main__':
    cars_df = scrape_pakwheels()
    insert_into_database(cars_df)
    print("Scraping and database insertion completed successfully.")
The script starts by calling the scrape_pakwheels() function to scrape the data. The scraped data is then inserted into the SQL Server database by calling insert_into_database(cars_df). Finally, a success message is printed, indicating that the entire process has been completed.

Conclusion

By following this guide, you will have a complete end-to-end solution for scraping car data from PakWheels and storing it in a SQL Server database using Python, Selenium, and Pandas. This approach not only automates data collection but also facilitates efficient data management for further analysis. Whether you're interested in building your own datasets or enhancing your data science projects, this method provides a solid foundation for web scraping and data storage.


Comments

Post a Comment