Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0613· OpenAIHugging Face Arena Human Preference 55K

Code a Python script to ingest a million lines CSV into a SQL Database

  1. User

    Code a Python script to ingest a million lines CSV into a SQL Database

  2. Assistant

    Sure, first of all, you'll need to install necessary libraries such as pandas, sqlalchemy and psycopg2 for PostgreSQL (or pymssql for MS SQL Server).

    You can install them via pip:

    pip install pandas sqlalchemy psycopg2-binary
    

    Here is a simple script to ingest a large CSV file into a SQL database using chunksize parameter:

    import pandas as pd
    from sqlalchemy import create_engine
    
    # create sqlalchemy engine
    engine = create_engine("postgresql://username:password@localhost:5432/mydatabase")
    
    chunksize = 50000  # batch size
    i = 0
    j = 0
    
    for df in pd.read_csv("my_large_file.csv", chunksize=chunksize, iterator=True):
        df = df.rename(columns={c: c.replace(' ', '') for c in df.columns}) 
        df.index += j
    
        df.to_sql("my_table", engine, if_exists="append")
        j = df.index[-1] + 1
    
        print(f"{j} rows ingested")
    
    print("Ingestion is complete!")
    

    Please replace "my_large_file.csv" with your actual CSV file path, "my_table" with your actual table name, and the engine creation line with your actual database connection string.

    This script reads the CSV file in chunks and appends them to the specified SQL table. This is more memory-friendly than trying to load the whole file into memory at once.

    You may need to tune the chunksize parameter to find the optimal balance between memory usage and performance. This will depend on the capacity of your machine and the complexity of your data.

    Also, note that this script assumes that the SQL table already exists and matches the structure of the CSV file. If this is not the case, you may need to create the table first or adjust the script to create it automatically (you can replace if_exists="append" with if_exists="replace" to create a new table or replace an existing one, but be careful as this will delete any existing data in the table).