Web Scraping & Bots · Lektion

Integration in Datenbanken (SQL)

Verstehen Sie, wie Sie Ihre Scraping-Skripte mit SQL-Datenbanken (z. B. SQLite, PostgreSQL) verbinden, um strukturierte Daten zu speichern.

Integration in Datenbanken (SQL) ist eine kostenlose Web Scraping & Bots-Lektion auf CoddyKit. Dies ist Lektion 2 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Web Scraping & Bots-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Web Scraping & Bots-Kurs umfasst insgesamt 4 Lektionen.

Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.

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!

Häufig gestellte Fragen

Ist die Lektion „Integration in Datenbanken (SQL)“ kostenlos?

Ja — der vollständige Text von „Integration in Datenbanken (SQL)“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Web Scraping & Bots-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Web Scraping & Bots-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Integration in Datenbanken (SQL)“?

Verstehen Sie, wie Sie Ihre Scraping-Skripte mit SQL-Datenbanken (z. B. SQLite, PostgreSQL) verbinden, um strukturierte Daten zu speichern. Du übst Web Scraping & Bots mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um Web Scraping & Bots zu starten?

Keine Vorkenntnisse erforderlich. Web Scraping & Bots auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 2 von 4.

Wie lange dauert die Lektion „Integration in Datenbanken (SQL)“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser Web Scraping & Bots-Lektion Code schreiben und ausführen?

Ja. Jede Web Scraping & Bots-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

← Zurück zu Web Scraping & Bots