How do I get my code to work

Question

0.00/5 (No votes)

See more:

import csv
import sqlite3
import os
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,
             phone_colour TEXT,
             phone_model INTEGER,
             storage_size INTEGER,
             price TEXT)""")

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,
            firstname TEXT,
            surname TEXT,
            age INTEGER,
            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], row[5] ]
    db.execute('''INSERT INTO customer
(clientID, firstname, surname, age, number,email)
      VALUES (?,?,?,?,?,?);''', to_db)

db.execute ("""CREATE TABLE purchase
            (purchaseID INTEGER PRIMARY KEY,
            clientID INTEGER,
            datepurchased TEXT,
            timepurchased INTEGER,
            phoneID INTEGER,
            purchasedetails TEXT,
            price INTEGER,
            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], row[4], row[5], row[6] ]
    db.execute('''INSERT INTO purchase
(purchaseID,clientID,datepurchased,timepurchased,phoneID,purchasedetails,price)
      VALUES (?,?,?,?,?,?,?);''', to_db)

conn.commit
conn.close

What I have tried:

I'm trying to get my three tables to work but they aren't. This is the error I keep getting. Please help me.

IntegrityError: datatype mismatch
phones.py, line 37
No specific suggestions for this error (yet).
Let Thonny developers know
Click on the feedback link at the bottom of this panel to let Thonny developers know about your problem. They may add support for such cases in future Thonny versions.
Search the web

This is the csv file for table customer

0300,Charles,Clerk,21,0401273640,Charlieb@hotmail.com,
0301,Hunter,Parker,23,0490876598,Hunterparker@gmail.com, 
0302,Zack,Davis,34,0412567456,Zdavis@hotmail.com,
0303,Gina,Porter,21,0489768990,PorterGina@outlook.com, 
0304,Jane,Grey,42,0445673897,Janegrey@yahoo.com,
0305,Elijah,Casswell,34,0456487650,EJcasswell@gmail.com, 
0306,Joshua,Bass,24,0498734523,Joshuabass@gmail.com,
0307,Rose,Colton,54,0410987603,RoseColton@gmail.com,
0308,Hazel,Stan,43,040913567543,Stanhazel@hotmail.com, 
0309,Valentina,Brown,28,0445637864,ValBrown@yahoo.com,

Posted 10-Sep-22 21:41pm

YunusShuaib

Updated 10-Sep-22 22:40pm

v2

Add a Solution

Comments

Richard MacCutchan 11-Sep-22 4:39am

You need to print the actual values that are being read from the csv file at that point to see which one is incorrect.

Richard MacCutchan 11-Sep-22 4:45am

I copied your csv text and tried to process it. There appear to be some strange characters on the last three lines. That may well be the problem as sqlite will not be able to convert the text to integers where required.

['0300', 'Charles', 'Clerk', '21', '0401273640', 'Charlieb@hotmail.com']
['0301', 'Hunter', 'Parker', '23', '0490876598', 'Hunterparker@gmail.com']
['0302', 'Zack', 'Davis', '34', '0412567456', 'Zdavis@hotmail.com']
['0303', 'Gina', 'Porter', '21', '0489768990', 'PorterGina@outlook.com']
['0304', 'Jane', 'Grey', '42', '0445673897', 'Janegrey@yahoo.com']
['0305', 'Elijah', 'Casswell', '34', '0456487650', 'EJcasswell@gmail.com']
['0306', 'Joshua', 'Bass', '24', '0498734523', 'Joshuabass@gmail.com']
['0307â€‹', 'Roseâ€‹', 'Coltonâ€‹', '54â€‹', '0410987603â€‹', 'RoseColton@gmail.com']
['0308â€‹', 'Hazelâ€‹', 'Stanâ€‹', '43â€‹', '040913567543â€‹', 'Stanhazel@hotmail.com']
['0309', 'Valentinaâ€‹', 'Brownâ€‹', '28', '0445637864â€‹', 'ValBrown@yahoo.com']

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2022-09-10T22:40:00

As I said yesterday, you need to look at error messages, and at your code:

IntegrityError: datatype mismatch
phones.py, line 37

Lines 36 and 37:

Python

to_db = [row[0], row[1], row[2], row[3], row[4], row[5] ]
db.execute('''INSERT INTO customer
(clientID, firstname, surname, age, number,email)
  VALUES (?,?,?,?,?,?);''', to_db)

So, somehow you are sending data to SQLite that doesn't match the column datatype.
Looking at the table definition, and knowing it's one of the non-text columns it has to be teh value you send to clientID, age, or number.
So it's data in row 0, 3, or 4 of your row data.
And we can't see that, because it comes from your CSV file.

So ... use the debugger to look at exactly what you are sending to the DB and work out which CSV row it is, and what exactly it contains. Then you can start working out how to fix it!

We can't do any of that for you - we have no access to your data!

You need to start looking at these things logically - you are going to meet them very often and it's really a lot quicker and easier to fix them yourself than to wait for others to point you in the right direction. And learning how to work it out for yourself is a lot easier on trivial code like this that it will be when you start writing larger, complex applications. So give it a try ... you might even start to enjoy it!