Click here to Skip to main content
15,791,846 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
import sqlite3
import os
import csv
# Remove database if exist
if os.path.isfile('car.db'):
#Create database and open database
db.execute("PRAGMA foreign_keys = ON")

db.execute("""CREATE TABLE clients
            (ClientID INTEGER PRIMARY KEY autoincrement,
            First_name TEXT,
            Surname TEXT,
            Age INTEGER,
            Address STRING,
            City TEXT,
            Postcode INTEGER,
            Email STRING,
            Phone_number INTEGER)""") 
reader = csv.reader(open('clients.txt', 'r'), delimiter=',')
for row in reader:
    to_db = [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8] ]
    db.execute('''INSERT INTO clients
(ClientID, First_name, Surname, Age, Address, City,Postcode,Email,Phone_number)
      VALUES (?,?,?,?,?,?,?,?,?);''', to_db)
db.execute("""CREATE TABLE cars
            (numbID INTEGER PRIMARY KEY autoincrement,
            Make TEXT,
            Model TEXT,
            Colour TEXT,
            Year INTEGER,
            Transmission TEXT,
            Price INTEGER,
            Stock INTEGER)""")
reader = csv.reader(open('cars.txt', 'r'), delimiter=',')
for row in reader:
    to_db = [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7] ]
    db.execute('''INSERT INTO cars
(numbID, Make, Model, Colour, Year, Transmission, Price, Stock)
      VALUES (?,?,?,?,?,?,?,?);''', to_db)

db.execute("""CREATE TABLE purchase
            (PurchaseID INTEGER PRIMARY KEY autoincrement,
            numbID INTEGER,
            ClientID INTEGER,
            Date_of_Purchase TEXT,
            ETA_of_Arrival STRING,
            FOREIGN KEY(clientID)REFERENCES clients(clientID),
            FOREIGN KEY(numbID)REFERENCES cars(numbID))""")
reader = csv.reader(open('purchase.txt', 'r'), delimiter=',')
for row in reader:
    to_db = [row[0], row[1], row[2], row[3], row[4] ]
    db.execute('''INSERT INTO purchase
(PurchaseID, ClientID, numbID, Date_of_Purchase, ETA_of_Arrival)
      VALUES (?,?,?,?,?);''', to_db)

What I have tried:

In sqlite3, all three tables are supposed to show. But when I run .schema, only one table is shown. This is the error message I keep getting. On line 55, where the purchase table is I keep getting an index error.
    to_db = [row[0], row[1], row[2], row[3], row[4] ]
IndexError: list index out of range
Updated 9-Sep-22 1:06am
Richard MacCutchan 9-Sep-22 6:15am    
Do not hard code the index values in your csv reading code. You should check the actual number of fields read from the file and report any with missing data.

1 solution

"Index out of range" means that you are trying to access an array element that doesn't exist. In Python, arrays indexes start as zero, and run to N - 1 where N is the number of elements in the array.
So an array with three elements will have valid indexes of 0, 1, and 2 only - all other values will give you an "out of range" error.

So the most likely reason is that rows does not hold 9 columns of data.

Sorry, but we can't fix that for you: you need to use the debugger to find out which line from the CSV file is throwing the error, and what exactly it does contain.
Only then can you start working out why there aren't enough columns and start fixing the problem.
Share this answer
YunusShuaib 9-Sep-22 6:08am    
How do I use the debugger to find which line of the csv?
OriginalGriff 9-Sep-22 6:35am

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900