Web Scraping & Bots · 강의

데이터베이스(SQL) 통합

구조화된 데이터 저장을 위해 스크래핑 스크립트를 SQL 데이터베이스(예: SQLite, PostgreSQL)에 연결하는 방법을 이해합니다.

데이터베이스(SQL) 통합은(는) CoddyKit의 무료 Web Scraping & Bots 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 Web Scraping & Bots 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. Web Scraping & Bots 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

SQL for Scraped Data Storage

Welcome! After collecting data from the web, you need a reliable way to store it. While CSV or JSON files work for small tasks, databases offer powerful advantages for larger, more complex scraping projects.

This lesson explores how to integrate your Python scraping scripts with SQL databases like SQLite, a lightweight, file-based database perfect for local development.

Why Use SQL Databases?

Storing scraped data in a SQL database provides significant benefits over simple file storage:

Structured Storage: Data is organized into tables with defined columns, ensuring consistency.
Queryability: Easily search, filter, and analyze your data using SQL queries.
Scalability: Handle large volumes of data more efficiently than flat files.
Data Integrity: Enforce rules to prevent invalid or duplicate data entries.

SQL Basics: Tables, Rows, Columns

Think of a SQL database like a collection of spreadsheets. Each 'spreadsheet' is called a table. Each row in a spreadsheet is a row (or record) in a table, and each column is a column (or field).

For example, a 'products' table might have columns for name, price, and url.

Connecting to SQLite in Python

Python has a built-in module, sqlite3, that allows you to interact with SQLite databases. First, you need to establish a connection.

Run this code to see how to connect to and then close a database file named scraped_data.db.

import sqlite3

def connect_to_db(db_name="scraped_data.db"):
    conn = None
    try:
        # Connects to the database file. If it doesn't exist, it creates it.
        conn = sqlite3.connect(db_name)
        print(f"Successfully connected to {db_name}")
    except sqlite3.Error as e:
        print(f"Database connection error: {e}")
    finally:
        if conn:
            # Always close the connection when done
            conn.close()
            print("Connection closed.")

if __name__ == "__main__":
    connect_to_db()

Creating Your First Table

Once connected, you need to define the structure for your data. This is done by creating a table using a SQL CREATE TABLE statement. We'll use a cursor object to execute SQL commands.

This example creates a products table with an ID, name, price, and URL column.

import sqlite3

def create_products_table(db_name="scraped_data.db"):
    conn = None
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()
        # SQL command to create a table if it doesn't already exist
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                price REAL,
                url TEXT UNIQUE
            )
        """)
        conn.commit() # Save the changes to the database
        print("Table 'products' created or already exists.")
    except sqlite3.Error as e:
        print(f"Database error: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    create_products_table()

Inserting Scraped Data

After creating your table, you can start adding the data you've scraped. The SQL INSERT INTO statement is used for this. It's crucial to use parameterized queries (with ? placeholders) to prevent SQL injection vulnerabilities.

Let's add a single product to our table.

import sqlite3

def insert_product(db_name="scraped_data.db", name="Sample Product", price=99.99, url="http://example.com/sample"):
    conn = None
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()
        # SQL to insert a single row
        cursor.execute("INSERT INTO products (name, price, url) VALUES (?, ?, ?)",
                       (name, price, url))
        conn.commit()
        print(f"Inserted product: {name}")
    except sqlite3.Error as e:
        print(f"Database error: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # Ensure table exists before inserting
    conn_temp = sqlite3.connect("scraped_data.db")
    cursor_temp = conn_temp.cursor()
    cursor_temp.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL,
            url TEXT UNIQUE
        )
    """)
    conn_temp.commit()
    conn_temp.close()

    insert_product(name="Python Book", price=35.50, url="http://bookstore.com/python")

Inserting Multiple Records Efficiently

When you have many scraped items to save, inserting them one by one can be slow. The cursor.executemany() method allows you to insert multiple rows with a single command, making the process much faster.

Provide a list of tuples, where each tuple represents a row to be inserted.

import sqlite3

def insert_multiple_products(db_name="scraped_data.db", products_data=None):
    if products_data is None:
        products_data = [
            ("Mechanical Keyboard", 120.00, "http://shop.com/mech-kb"),
            ("Gaming Mouse", 65.99, "http://shop.com/gaming-mouse"),
            ("Webcam HD", 49.99, "http://shop.com/webcam")
        ]

    conn = None
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()
        # Use executemany for bulk inserts
        cursor.executemany("INSERT INTO products (name, price, url) VALUES (?, ?, ?)",
                           products_data)
        conn.commit()
        print(f"Inserted {len(products_data)} products.")
    except sqlite3.Error as e:
        print(f"Database error: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # Ensure table exists before inserting
    conn_temp = sqlite3.connect("scraped_data.db")
    cursor_temp = conn_temp.cursor()
    cursor_temp.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL,
            url TEXT UNIQUE
        )
    """)
    conn_temp.commit()
    conn_temp.close()

    insert_multiple_products()

Handling Existing Data (Updates/Ignores)

What if you scrape data that might already be in your database? You generally have two options:

Ignore Duplicates: Use INSERT OR IGNORE INTO SQL syntax if a unique constraint (like our url TEXT UNIQUE) would be violated.
Update Existing: Use the UPDATE SQL statement to modify an existing record instead of inserting a new one if a match is found.

Choosing the right strategy depends on whether new data should overwrite old, or if old data should simply be kept.

Robust Interactions: Commit & Close

Always remember to:

conn.commit(): This saves your changes (like inserts or updates) to the database file. Without it, your changes might not persist!
conn.close(): Close the database connection when you're done. This frees up resources and ensures data integrity.
Error Handling: Wrap your database operations in try...except...finally blocks to catch errors and ensure the connection is always closed, even if an error occurs.

Quick Check: SQL Data Insert

You've learned how to connect to a database and insert data. Which Python method is best suited for inserting many rows of data into a SQL table in one go?

Recap: SQL for Persistence

Great job! You now understand the fundamentals of integrating your web scraping scripts with SQL databases.

We covered:

The benefits of databases for scraped data.
Connecting to SQLite using Python's sqlite3.
Creating tables with CREATE TABLE.
Inserting single and multiple records using INSERT INTO and executemany().
Best practices like commit() and close().

This knowledge is vital for building robust and scalable scraping solutions!

자주 묻는 질문

“데이터베이스(SQL) 통합” 강의는 무료인가요?

네 — “데이터베이스(SQL) 통합” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 Web Scraping & Bots 강의 전체를 잠금 해제할 수 있습니다. Web Scraping & Bots 강의에는 총 4개의 강의가 포함되어 있습니다.

“데이터베이스(SQL) 통합”에서 뭘 배우나요?

구조화된 데이터 저장을 위해 스크래핑 스크립트를 SQL 데이터베이스(예: SQLite, PostgreSQL)에 연결하는 방법을 이해합니다. 브라우저에서 직접 실행하는 실습 코드로 Web Scraping & Bots을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

Web Scraping & Bots을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 Web Scraping & Bots은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“데이터베이스(SQL) 통합” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 Web Scraping & Bots 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 Web Scraping & Bots 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← Web Scraping & Bots(으)로 돌아가기