Efficiently import csv with 2,000,000 rows in an empty SQLite table-CodePudding

I am trying to bring in a CSV of 2 million rows in a SQLite database but it is taking forever for the data to load.

I calculated it might take upto 3 days for the data to be uploaded. I might have to do this often. Is there a better way to read a CSV and add to an empty table

Below is the code I am trying -

import sqlite3
import csv
import pandas as pd
from csv import reader
cnx = sqlite3.connect('db/homepages.db')
cursor = cnx.cursor()
df = pd.read_sql_query("SELECT * FROM hostname_table", cnx)
with open('lb_csv.csv', 'r') as f:
    reader = csv.reader(f)
    for i, row in enumerate(reader):
        if i000 == 0:
            print(i)
        name_input = row[0]
        hostname_input = row[1]
        scheme_input = 'not_available'
        description_input = 'not_available'
        created_date_input = datetime.now()
       try:
           cursor.execute("INSERT INTO hostname_table (name, hostname, scheme, description, created_date) VALUES (?, ?, ?, ?, ?)", (name_input, hostname_input, scheme_input, description_input, created_date_input))
           cnx.commit()
       except Exception as e:
           print(e)
            cnx.rollback()
            pass

CodePudding user response：

If performance is an issue, and you can get rid of the constraint to use Python, you could just do:

sqlite> .mode csv
sqlite> .import /tmp/hostname_data.csv hostname_table

CodePudding user response：

If you don't need to do it in python, the sqlite command line has a .import command that will directly import csv files using the --csv flag. Here's an example from the documentation at https://www.sqlite.org/cli.html#csv_import

sqlite> .import --csv --skip 1 --schema temp C:/work/somedata.csv tab1

The --skip option tells it how many header rows to skip. The --schema option specifies an alternate schema from the current one to import into. The last argument is the new table name.