Click here to Skip to main content
15,512,303 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
import sqlite3
import os
import csv

if os.path.isfile('phones.db'):
    os.remove ('phones.db')
   
conn= sqlite3.connect('phones.db')
db=conn.cursor()
db.execute("PRAGMA foreign_keys = ON")

db.execute ("""CREATE TABLE phones
            (PhoneID INTEGER PRIMARY KEY autoincrement,
             Phone_colour TEXT,
             Phone_model INTEGER,
             Storage_size INTEGER,
             Price INTEGER)""")

reader = csv.reader(open('phones.txt', 'r'), delimiter=',')
for row in reader:
    to_db = [row[0], row[1], row[2], row[3], row[4] ]
    db.execute('''INSERT INTO phones
(PhoneID, Phone_colour, Phone_model, Storage_size, Price)
                VALUES (?,?,?,?,?);''', to_db)

db.execute ("""CREATE TABLE customer
            (ClientID INTEGER PRIMARY KEY autoincrement,
            First_name TEXT,
            Surname TEXT,
            Phone_number INTEGER,
            Email STRING)""")
reader = csv.reader(open('clients.txt', 'r'), delimiter=',')
for row in reader:
    to_db = [row[0], row[1], row[2], row[3], row[4] ]
    db.execute('''INSERT INTO customer
  (ClientID, First_name, Surname, Phone_number, Email)
          VALUES (?,?,?,?,?);''', to_db)

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

conn.commit()
conn.close()


What I have tried:

I want all three tables to work but I keep getting an integrity error.
IntegrityError: datatype mismatch
phones.py, line 37

Line 37 is for table customer which is connected to the client csv file. This is the contents for the client csv file-
0300,Charles,Clerk,0401273640,Charlieb@hotmail.com,
0301,Hunter,Parker,0490876598,Hunterparker@gmail.com, ​
0302,Zack,Davis,0412567456,Zdavis@hotmail.com,
0303,Gina,Porter,0489768990,PorterGina@outlook.com, ​
0304,Jane,Grey,0445673897,Janegrey@yahoo.com,
0305,Elijah,Casswell,0456487650,EJcasswell@gmail.com, ​
0306,Joshua,Bass,0498734523,Joshuabass@gmail.com,
0307​,Rose​,Colton​,0410987603​,RoseColton@gmail.com,
0308​,Hazel​,Stan​,040913567543​,Stanhazel@hotmail.com, ​
0309,Valentina​,Brown​,0445637864​,ValBrown@yahoo.com,
Posted
Updated 11-Sep-22 0:36am
Comments
Richard MacCutchan 11-Sep-22 5:56am    
I already told you to check the last three lines of your csv data.
OriginalGriff 11-Sep-22 6:21am    
But ... but ... that means him thinking! :D

1 solution

When you asked the same question an hour ago: How do I get my code to work[^] I told you what you need to do.
Reposting the same thing doesn't change the answer: go back and read it again.
 
Share this answer
 

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