Web Scraping & Bots · Lezione

Integrazione con database (SQL)

Comprenda come collegare gli script di scraping a database SQL, come SQLite e PostgreSQL, per archiviare dati strutturati.

Integrazione con database (SQL) è una lezione Web Scraping & Bots gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Web Scraping & Bots, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Web Scraping & Bots include 4 lezioni in totale.

Parti di questa lezione non sono ancora state tradotte e vengono mostrate in inglese.

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!

Domande Frequenti

La lezione «Integrazione con database (SQL)» è gratuita?

Sì — il testo completo di «Integrazione con database (SQL)» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Web Scraping & Bots, passa a CoddyKit PRO. Il corso Web Scraping & Bots include 4 lezioni in totale.

Cosa imparerò in «Integrazione con database (SQL)»?

Comprenda come collegare gli script di scraping a database SQL, come SQLite e PostgreSQL, per archiviare dati strutturati. Eserciti Web Scraping & Bots con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare Web Scraping & Bots?

Non è richiesta alcuna esperienza precedente. Web Scraping & Bots su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Integrazione con database (SQL)»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione Web Scraping & Bots?

Sì. Ogni lezione Web Scraping & Bots include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a Web Scraping & Bots