0Pricing
Web Scraping & Bots · Lección

Integración con bases de datos (SQL)

Comprenda cómo conectar sus scripts de scraping a bases de datos SQL, como SQLite y PostgreSQL, para almacenar datos estructurados.

Integración con bases de datos (SQL) es una lección gratuita de Web Scraping & Bots en CoddyKit. Esta es la lección 2 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de Web Scraping & Bots, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Web Scraping & Bots incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en inglés.

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!

Preguntas frecuentes

¿La lección «Integración con bases de datos (SQL)» es gratis?

Sí — el texto completo de «Integración con bases de datos (SQL)» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de Web Scraping & Bots, actualiza a CoddyKit PRO. El curso de Web Scraping & Bots incluye 4 lecciones en total.

¿Qué aprenderé en «Integración con bases de datos (SQL)»?

Comprenda cómo conectar sus scripts de scraping a bases de datos SQL, como SQLite y PostgreSQL, para almacenar datos estructurados. Practicas Web Scraping & Bots con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar Web Scraping & Bots?

No se requiere experiencia previa. Web Scraping & Bots en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 2 de 4.

¿Cuánto tiempo toma la lección «Integración con bases de datos (SQL)»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de Web Scraping & Bots?

Sí. Cada lección de Web Scraping & Bots incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Almacenamiento de datos en CSV/JSON
  2. Integración con bases de datos (SQL)
  3. Soluciones de almacenamiento en la nube
  4. Almacenamiento de datos en bases de datos NoSQL
← Volver a Web Scraping & Bots