اتصال به دیتابیس با پایتون

امروزه، زبان برنامه نویسی پایتون و قدرت اون تقریبا برای هیچ برنامه نویسی پوشیده نیست! در این نوشته قراره باهم یاد بگیریم که چطوری با پایتون، دیتا رو از یه سایت بخونیم و اون رو توی دیتابیس ذخیره کنیم تا بتونیم بعدا ازش استفاده کنیم. (این آموزش متناسب با سیستم عامل ویندوز طراحی شده)

مواد لازم:

• پایتون (+3.5)
• آشنایی با Web Scraping
• آشنایی با MySQL
• وب سرور مجازی (MySQL یا XAMPP)
• یخورده برنامه نویسی :)

یه پروژه ی ساده

  توی این پروژه ی آموزشی، یه جدول داخل دیتابیس میسازیم و بعدش دیتا رو از این سایت (منو میگه) میخونیم و داخل دیتابیسی که ساختیم ذخیره میکنیم. برنامه قراره از ما اسم منطقه یا محله ای از تهران رو دریافت کنه و خونه هایی که در اون محله برای خرید گذاشته شدن رو بهمون برگردونه (هیجان انگیز نیست؟😆). همونطور که در بالا هم اشاره کردم، دو روش برای ایجاد وب سرور مجازی داریم. من برای این کار از XAMPP استفاده می کنم چون نصب آسون تری داره. بنابراین XAMPP رو از این لینک دانلود و نصب کنید. حالا میریم سراغ:

نصب ماژول های مورد نیاز 

داخل ترمینال این دستورات رو اجرا کنید

برای web scraping نیاز داریم به:

pip install requests
pip install bs4

و برای دیتابیس:

python -m pip install mysql-connector
python -m pip install wheel
python -m pip install mysql-connector-python

آفرین 😊 (چه پسری/دختری)!

حال، چه کنم ؟!

حالا که همه چیو نصب کردیم، برای راه اندازی دیتابیس باید XAMPP رو اجرا کنیم و دو تا گزینه ی Apache و MySQL رو start کنیم. (اگه MySQL ارور قرمز رنگ داشت و start نشد، ممکنه Port اون اشغال شده باشه. برای رفع این ارور، از منوی استارت، Services رو سرچ کنید. توی لیست، MySQL رو پیدا کنید و اون سرویس رو stop کنید)

حالا یه فایل پایتونی به نام init_db.py بسازید و اجرا کنید تا یک دیتابیس و یک جدول داخل اون ایجاد بشه:

# In init_db.py

import mysql.connector

# Database connection
connection = mysql.connector.connect(
    user='root',
    password='',
    host='127.0.0.1',  # localhost
)
cursor = connection.cursor()


def init_db():
    new_db = """
    CREATE DATABASE homes;
    """
    new_table = """
    CREATE TABLE data(
        code VARCHAR(10),
        type VARCHAR(20),
        address VARCHAR(256),
        price VARCHAR(32));
    """
    cursor.execute(new_db)
    connection.commit()
    cursor.execute(new_table)
    connection.commit()
    cursor.close()


init_db()
connection.close()  # Close the connection

چی شد ؟!

ابتدای برنامه، ما یک کانکشن به دیتابیس زدیم. با متد connect به سرور مون وصل شدیم و با استفاده از اون یک آبجکت cursor ساختیم. حالا با این آبجکت میتونیم کوئری های SQL مون رو اجرا کنیم. داخل تابع دو تا کوئری نوشتم؛ new_db برای ساخت یک دیتابیس جدید و new_table برای ساخت یک جدول جدید (یکم به دانش SQL نیاز دارید 🙃). بعد از اینکه کد رو اجرا کردین خروجی خاصی نمیبینید. میتونید به عنوان تمرین کوئری های زیر رو داخل برنامه بذارید تا مطمئن بشید که دیتابیس و جدولش ایجاد شدن (البته حواستون باشه دوباره تابع init_db رو اجرا نکنیدا !):

cursor.execute("SHOW DATABASES;")
for db in cursor:
    print(db)
	
cursor.execute("SHOW TABLES;")
for tb in cursor:
    print(tb)

اینجا چون داریم دیتا رو میخونیم (عمل READ) دیگه نیازی به connection.commit نیست!

حالا یه فایل پایتون دیگه به نام دلخواه بسازید و داخلش بنویسید:

import time

import requests
from bs4 import BeautifulSoup
import mysql.connector

# Database connection
connection = mysql.connector.connect(
    user='root',
    password='',
    host='127.0.0.1',
    database='homes',  # Recently added
)
cursor = connection.cursor()


def insert_to_db(data: dict):
    add_home = """
    INSERT INTO data(code, type, address, price) 
    VALUES (%s, %s, %s, %s); 
    """
    cursor.execute(add_home, (data['code'], data['type'], data['address'], data['price']))
    connection.commit()
    cursor.close()

بعد از اینکه ماژول های مورد نیازمون رو import کردیم، دوباره نیاز داریم به دیتابیس متصل بشیم. دقت کنید که اینبار پارامتر database به متد connect اضافه شده (برای استفاده کردن از دیتابیسی که ایجاد کردیم). ورودی تابع یک دیکشنری هست که پارامتر هایی که میخوایم توی دیتابیس ذخیره کنیم رو داخل اون قرار دادیم. دقت کنید که اولین پارامترِ متدِ execute، کوئری SQL و دومین پارامتر، یک تاپل از مقادیری هست که میخوایم داخل کوئری قرار بدیم.

حالا یک تابع به نام crawl نوشتم که دیتا رو از روی سایت میخونه:

def crawl(pages: int):
    """Crawler"""
    print('The crawler is running...')

    for page in range(1, pages + 1):
        url = f'https://iranfile.ir/Search/A4BDC39B-{page}/NOX-%D8%AE%D8%B1%DB%8C%D8%AF_%D8%AA%D9%87%D8%B1%D8%A7%D9%86'
        response = requests.get(url)
        soup = BeautifulSoup(response.text, 'html.parser')

        table = soup.find_all('table', attrs={'class': 'table table-hover'})
        table_soup = BeautifulSoup(str(table), 'html.parser')
        t_body = table_soup.find('tbody')
        trs = [row for row in t_body]  # All 'tr' html tags on each page
        data = {}
        for tr in trs:
            tr_soup = BeautifulSoup(str(tr), 'html.parser')
            td = tr_soup.find_all('td')
            if td:
                data['code'] = td[1].text.strip()
                data['type'] = td[3].text.strip()
                data['address'] = td[4].text.strip()
                data['price'] = td[7].text.strip()

                insert_to_db(data)
        time.sleep(1)

ورودی این تابع تعداد صفحاتی از سایت هست که میخوایم دیتا رو از اون صفحات برداریم (مثلا میخوایم 12 تا صفحه اول رو بخونه). دیتا هایی که از سایت برداشتم شامل: کد ملک، نوع ملک، آدرس و قیمت هست.

یدونه sleep هم میذاریم با مکث 1 ثانیه ای واسه اینکه بلاک نشیم 🤭

یک تابع دیگه هم میخوایم برای اینکه دیتایی که از سایت برداشتیم و داخل دیتابیس قرار دادیم رو از دیتابیس بخونه:

def read_form_db(location):
    query = """
    SELECT * FROM data;
    """
    cursor.execute(query)
    result = []
    for code, type_, address, price in cursor:
        if location in address:
            data = {code: {'type': type_, 'address': address, 'price': price}}
            result.append(data)
    return result

دیگه چیزی نمونده!

الان ما توابع اصلی رو داریم، ولی مهم تر از اینا، کنترل جریان برنامه تونه! (این یک تکنیک پایتونی می باشد😎: توی برنامه هاتون سعی کنید همیشه یه تابع به نام main داشته باشین که جریان اصلی برنامه تون رو کنترل می کنه):

def main():
    while True:
        mode = input('Select mode by press [1], [2] or [0]:\n\t1. Crawl 🐍\n\t2. Read 📖\n\t0. Cancel ❌\n>>> ')
        if mode == '1':
            pages = int(input('Enter the number of pages you want to crawl; example: 5\n>>> '))
            crawl(pages)
            print(f"{pages} page(s) are saved in database ✔️")
        elif mode == '2':
            location = input('Enter the location you want; example: نارمک\n>>> ')
            result = read_form_db(location)
            print(f"\n{len(result)} results:")
            if result:
                for item in result:
                    for code, value in item.items():
                        output = "-- -- -- --\n"
                        output += f"code: {code}\n"
                        output += f"\taddress: {value['address']}\n"
                        output += f"\ttype: {value['type']}\n"
                        output += f"\tprice: {value['price']}\n"
                        print(output)
            else:
                print('Sorry, Not found!')
        elif mode == '0':
            break
        else:
            print('Just press [1], [2] or [0]')


if __name__ == '__main__':
    main()
    connection.close()

دیگه اینا مربوط به فرایند خوشگلاسیون سازی ورودی گرفتن و خروجی دادن برنامه س که اگه یه مقدار پایتون بلد باشید، می فهمید چی به چیه 😅

در آخر، از اینکه کانکشن را باز نگه دارید بپرهیزید که به عذابی سخت دچار خواهید شد.

چرا؟ 👀

چون (بعضی) پایگاه‌ داده ها اتصال رو باز نگه می‌دارن تا زمانی که برنامه به اون بگه که اون اتصال رو ببند. اگر صدها اتصال با یک پایگاه داده برقرار می‌کنید، برای اون پایگاه داده 100 اتصال باز وجود داره! داشتن هزاران یا صدها هزار اتصال با یک پایگاه داده در یک برنامه شلوغ اصلا غیرمعمول نیست، و دیر یا زود عملکرد DB میتونه عملکرد برنامه رو از بین ببره.

اگر دلیل موجهی برای باز نگه داشتن اون دارید، این کار رو انجام بدید! اگر نه، به محض اینکه کارتون تموم شد، اونو ببندید. اما بهتره عادت کنید که اتصالات رو ببندید و تا زمانی که بهش نیاز ندارید اون ها رو باز نگذارید. این یه عادت خوبه (😊) مثل بستن کمربند ایمنی یا بستن در یخچال وقتی نمیخواید ازش غذا بردارید!

کد یکپارچه

import time

import requests
from bs4 import BeautifulSoup
import mysql.connector

# Database connection
connection = mysql.connector.connect(
    user='root',
    password='amirHosein@1379',
    host='127.0.0.1',
    database='homes',
)
cursor = connection.cursor()


def insert_to_db(data: dict):
    add_home = """
    INSERT INTO data(code, type, address, price) 
    VALUES (%s, %s, %s, %s); 
    """
    cursor.execute(add_home, (data['code'], data['type'], data['address'], data['price']))
    connection.commit()
    cursor.close()


def read_form_db(location):
    query = """
    SELECT * FROM data;
    """
    cursor.execute(query)
    result = []
    for code, type_, address, price in cursor:
        if location in address:
            data = {code: {'type': type_, 'address': address, 'price': price}}
            result.append(data)
    return result


def crawl(pages: int):
    """Crawler"""
    print('The crawler is running...')

    for page in range(1, pages + 1):
        url = f'https://iranfile.ir/Search/A4BDC39B-{page}/NOX-%D8%AE%D8%B1%DB%8C%D8%AF_%D8%AA%D9%87%D8%B1%D8%A7%D9%86'
        response = requests.get(url)
        soup = BeautifulSoup(response.text, 'html.parser')

        table = soup.find_all('table', attrs={'class': 'table table-hover'})
        table_soup = BeautifulSoup(str(table), 'html.parser')
        t_body = table_soup.find('tbody')
        trs = [row for row in t_body]  # All 'tr' html tags on each page
        data = {}
        for tr in trs:
            tr_soup = BeautifulSoup(str(tr), 'html.parser')
            td = tr_soup.find_all('td')
            if td:
                data['code'] = td[1].text.strip()
                data['type'] = td[3].text.strip()
                data['address'] = td[4].text.strip()
                data['price'] = td[7].text.strip()

                insert_to_db(data)
        time.sleep(1)


def main():
    while True:
        mode = input('Select mode by press [1], [2] or [0]:\n\t1. Crawl 🐍\n\t2. Read 📖\n\t0. Cancel ❌\n>>> ')
        if mode == '1':
            pages = int(input('Enter the number of pages you want to crawl; example: 5\n>>> '))
            crawl(pages)
            print(f"{pages} page(s) are saved in database ✔️")
        elif mode == '2':
            location = input('Enter the location you want; example: نارمک\n>>> ')
            result = read_form_db(location)
            print(f"\n{len(result)} results:")
            if result:
                for item in result:
                    for code, value in item.items():
                        output = "-- -- -- --\n"
                        output += f"code: {code}\n"
                        output += f"\taddress: {value['address']}\n"
                        output += f"\ttype: {value['type']}\n"
                        output += f"\tprice: {value['price']}\n"
                        print(output)
            else:
                print('Sorry, Not found!')
        elif mode == '0':
            break
        else:
            print('Just press [1], [2] or [0]')


if __name__ == '__main__':
    main()
    connection.close()