import sqlite3
import os
import csv
# Remove database if exist
if os.path.isfile('car.db'):
os.remove('car.db')
#Create database and open database
conn=sqlite3.connect('car.db')
db=conn.cursor()
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)
conn.commit()
conn.close()
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