Python CSV to Database: Import 100,000 Rows in 10 Seconds

Importing CSV data manually through a UI is slow and error-prone. Python can do it in seconds with proper batching.

import sqlite3 import csv import time from pathlib import Path

def import_csv_to_db ( csv_path , db_path , table_name , batch_size = 1000 ): conn = sqlite3 . connect ( db_path )

with open ( csv_path , ' r ' , encoding = ' utf-8-sig ' ) as f : reader = csv . DictReader ( f ) headers = reader . fieldnames

# Create table from headers col_defs = ' , ' . join ( f ' ` { h } ` TEXT ' for h in headers ) conn . execute ( f ' CREATE TABLE IF NOT EXISTS { table_name } ( { col_defs } ) ' )

placeholders = ' , ' . join ( ' ? ' * len ( headers )) insert_sql = f ' INSERT INTO { table_name } VALUES ( { placeholders } ) '

batch = [] total = 0 start = time . time ()

for row in reader : batch . append ([ row [ h ] for h in headers ])

if len ( batch ) >= batch_size : conn . executemany ( insert_sql , batch ) conn .…