python-csv.md

2024-12-04T03:42:12 conversation: 01je7w65hqx9vkbwaxejxfsw1p

Model: us.amazon.nova-pro-v1:0

Prompt:

python function to domnload a CSV file from a URL, create a corresponding table in SQLite and import the data

Response:

Certainly! Below is a Python function that downloads a CSV file from a given URL, creates a corresponding table in an SQLite database, and imports the data into that table.

import requests
import csv
import sqlite3
import os
from io import StringIO

def download_csv_and_import_to_sqlite(url, db_name, table_name):
    # Download the CSV file from the URL
    response = requests.get(url)
    response.raise_for_status()  # Ensure we notice bad responses...
    
    # Read the CSV data
    csv_data = response.content.decode('utf-8')
    csv_reader = csv.reader(StringIO(csv_data))
    
    # Get the header (column names) from the CSV
    header = next(csv_reader)
    
    # Create the SQLite database and connect to it
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    
    # Create the table
    create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ("
    create_table_query += ",".join([f"{col} TEXT" for col in header])
    create_table_query += ")"
    cursor.execute(create_table_query)
    
    # Insert the data into the table
    insert_query = f"INSERT INTO {table_name} VALUES ({','.join(['?' for _ in header])})"
    for row in csv_reader:
        cursor.execute(insert_query, row)
    
    # Commit the changes and close the connection
    conn.commit()
    conn.close()
    
    print(f"Data successfully imported into {db_name} in table {table_name}.")

# Example usage
url = "https://example.com/data.csv"
db_name = "example.db"
table_name = "data_table"

download_csv_and_import_to_sqlite(url, db_name, table_name)

Explanation:

1. Download the CSV File:

   • Uses requests.get(url) to download the CSV file.
   • response.raise_for_status() ensures that any HTTP errors are raised.

2. Read the CSV Data:

   • Decodes the response content and reads it using csv.reader.

3. Create the SQLite Database and Table:

   • Connects to an SQLite database using sqlite3.connect(db_name).
   • Creates a table with columns corresponding to the CSV headers.

4. Insert Data into the Table:

   • Constructs an INSERT query dynamically based on the number of columns.
   • Inserts each row from the CSV into the table.

5. Commit and Close:

   • Commits the transaction and closes the database connection.

Notes:

• This function assumes that all CSV columns are of type TEXT in SQLite. You may need to adjust the column types based on your specific data.
• Error handling is minimal for brevity. You might want to add more robust error handling in a production scenario.