Click here to Skip to main content
15,790,267 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
import sqlite3
loop = True

def purchase_info():
    conn = sqlite3.connect('car.db')
    cursor = conn.cursor()
    sql = '''SELECT clients.First_name, clients.Surname, cars.Make, cars.Model, purchase.Date_of_Purchase FROM
((purchase INNER JOIN cars ON purchase.numbID = cars.numbID) INNER JOIN clients ON purchase.ClientID = clients.ClientID)
WHERE purchaseID = ?''';
    cursor.execute(sql)
    result = cursor.fetchall()
    for row in result:
        print ("First_name: " + str(row[0]) +", Surname: " +
              str(row[1]) + ", Make: " + str(row[2]) + ",Model: " +
               str(row[3]) + ",Date_of_Purchase: " +str(row[4]))
    

def displaymenu():
    print("1 to display records")
    print("2 to enter records")
    print("3 to update records")
    print("4 to delete records")
    print("5 to exit the menu\n")
    #print("")
def select():
    conn = sqlite3.connect('car.db')
    cursor = conn.cursor()
    sql = 'SELECT * FROM cars';
    cursor.execute(sql)
    result = cursor.fetchall()
    for row in result:
        print ("numbID: " + str(row[0]) +", Make: " +
              str(row[1]) + ", Model: " + str(row[2]) + ",Colour: " +
               str(row[3]) + ",Year: " +str(row[4]) + ",Transmission:" +
               str(row[5]) + ",Price: "+str(row[6]) + ",Number In Stock:" +str(row[7]))
               
def insert():
    var_choice = int(input("Select the number of cars to input "))
    var_count = 1
    while var_choice >=var_count:
        var_Make = str(input("Input car make. "))
        var_Model = str(input("Input car model. "))
        var_Colour = str(input("Input car's colour. "))
        var_Year = str(input("Input car release date."))
        var_Transmission= str(input("Input car's transmission type."))
        var_Price=str(input("Input car's price."))
        var_Stock= str(input("Input car stock number."))
        var_insert = []
        var_insert.append(var_Make)
        var_insert.append(var_Model)
        var_insert.append(var_Colour)
        var_insert.append(var_Year)
        var_insert.append(var_Transmission)
        var_insert.append(var_Price)
        var_insert.append(var_Stock)
        conn = sqlite3.connect('car.db')
        cursor = conn.cursor()
        print(var_insert)
        cursor.execute('insert INTO cars(Make, Model, Colour, Year,Transmission,Price,Stock)VALUES (?,?,?,?,?,?,?);', var_insert)
        conn.commit()
        var_count += 1
def update():
    var_updt = str(input("Give the ID number of the car to update. "))
    var_updt_Make = str(input("Give the new make of car. "))
    var_updt_Model= str(input("Give the new model of car."))
    var_updt_Colour= str(input("Give the new colour of the car."))
    var_updt_Year= str(input("Give the new year of the car."))
    var_updt_Transmission= str(input("Give the new transmission of the car."))
    var_updt_Price= str(input("Give the new pricing of the car."))
    var_updt_Stock = str(input("How many cars in stock?"))
    var_update = []
    var_update.append(var_updt_Make)
    var_update.append(var_updt_Model)
    var_update.append(var_updt_Colour)
    var_update.append(var_updt_Year)
    var_update.append(var_updt_Transmission)
    var_update.append(var_updt_Price)
    var_update.append(var_updt_Stock)
    var_update.append(var_updt)
    conn = sqlite3.connect('car.db')
    cursor = conn.cursor()
    print(var_update)
    cursor.execute('UPDATE cars SET Make = ?, Model= ?, Colour=?, Year=?, Transmission=?, Price=?, Stock=? WHERE numbID =?', var_update)
    
    conn.commit()
def delete():
    var_del =[]
    var_del_choice = str(input("Give the ID number of the car record to delete."))
    var_del.append(var_del_choice)
    #print (var_del)
    conn = sqlite3.connect ('car.db')
    cursor = conn.cursor()
    cursor.execute ('DELETE FROM car WHERE numbID =?', var_del)
    conn.commit()

while loop:
    displaymenu()
    var_choice = input("Please make your choice using numbers 1-5.")
    print("")
    if var_choice =='1':
        select()
    elif var_choice =='2':
         insert()
    elif var_choice =='3':
          update()
    elif var_choice =='4':
          delete()
    elif var_choice =='5':
            #this next statment turns the loop off
          loop= False
    elif var_choice =="":
            # the double quotes together are a null value
         print("Please enter a number.")
    else:
         print("Invalid input.")


What I have tried:

This code has crud system but I want to create an inner join table with tables clients, cars and purchase table. There are foreign keys included but I'm not sure how it works. Please help me.
Posted
Updated 23-Aug-22 3:19am
v2

1 solution

Start here: SQL Joins[^]
Then think about your data design, and exactly what you want to retrieve with the JOIN.

You'll work it out!
 
Share this answer
 
Comments
YunusShuaib 24-Aug-22 1:07am    
I tried that website but still stuck. I am a beginner in this field so please help me
OriginalGriff 24-Aug-22 1:44am    
How? We can't give you "the solution", we have no access to your DB to check what the tables are and how they interrelate, or test any possibilities we might come up with. Heck, we don't even know exactly what you expect to get from JOINing the data we can't see! :laugh:

And even if we did, just giving you a solution doesn't help you at all - it doesn't teach you how to write them next time!
So seriously: "think about your data design, and exactly what you want to retrieve with the JOIN." Then think about what the link shows you about what JOINs do and what data you need to combine to get what you actually want.

Sorry, but we can't do that for yoU!
YunusShuaib 24-Aug-22 5:04am    
Is there anything wrong with my select statement though?
OriginalGriff 24-Aug-22 5:44am    
How would we know?

"... we have no access to your DB to check what the tables are and how they interrelate, or test any possibilities we might come up with. Heck, we don't even know exactly what you expect to get from JOINing the data we can't see!"

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with: we get no other context for your project.

Imagine this: you go for a drive in the country, but you have a problem with the car. You call the garage, say "it broke" and turn off your phone. How long will you be waiting before the garage arrives with the right bits and tools to fix the car given they don't know what make or model it is, who you are, what happened when it all went wrong, or even where you are?

I can assume that your SELECT statement is wrong, because if it worked you wouldn't be asking a question about it, but that's all we can tell.
YunusShuaib 24-Aug-22 5:07am    
Is there any place to find three tables inner join python mySQL help?

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