Web Scraping & Bots · Aula

Integração com bancos de dados (SQL)

Entenda como conectar seus scripts de raspagem a bancos de dados SQL, como SQLite e PostgreSQL, para armazenar dados estruturados.

Integração com bancos de dados (SQL) é uma aula grátis de Web Scraping & Bots no CoddyKit. Esta é a aula 2 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de Web Scraping & Bots, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de Web Scraping & Bots inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em 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!

Perguntas Frequentes

A aula “Integração com bancos de dados (SQL)” é grátis?

Sim — o texto completo de “Integração com bancos de dados (SQL)” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de Web Scraping & Bots, atualize para CoddyKit PRO. O curso de Web Scraping & Bots inclui 4 aulas no total.

O que vou aprender em “Integração com bancos de dados (SQL)”?

Entenda como conectar seus scripts de raspagem a bancos de dados SQL, como SQLite e PostgreSQL, para armazenar dados estruturados. Você pratica Web Scraping & Bots com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar Web Scraping & Bots?

Nenhuma experiência prévia é necessária. Web Scraping & Bots no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 2 de 4.

Quanto tempo leva a aula “Integração com bancos de dados (SQL)”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de Web Scraping & Bots?

Sim. Cada aula de Web Scraping & Bots inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para Web Scraping & Bots