Simple SQL Query for in Memory Database
Introduction to Computational and Algorithmic Thinking
Preliminaries
For this lab you will be working with the following classes that represent cars for sale at various car dealerships:
class Car:
def __init__(self, id, brand, price, attributes):
self.id = id
self.brand = brand
self.price = price
self.attributes = attributes
class CarAttributes:
def __init__(self, paint, tires, trim):
CSE 101 – Fall 2017 Lab #8 Page 1
self.paint = paint
self.tires = tires
self.trim = trim
class Dealership:
def __init__(self, car_list, name):
self.car_list = car_list
self.name = name
defadd_cars(self, cars):
return None # you will write this method in Part I
defupdate_car(self, id, new_value):
return None # you will write this method in Part II
You will be asked to write two methods inside the Dealership class and one function.
For the examples below we will be using the following objects, but CodeLoadwill contain more cars.
car1 = Car(1, ’Ford’, 23000, CarAttributes(’Red’, ’Rain’, ’Level-1’))
car2 = Car(2, ’BMW’, 46000, CarAttributes(’Blue’, ’Regular’, ’Regular’))
car3 = Car(3, ’Ferrari’, 150000, CarAttributes(’Violet’, ’Regular’, ’Level-2’))
car4 = Car(4, ’Toyota’, 26000, CarAttributes(’Black’, ’Snow’, ’Regular’))
car5 = Car(5, ’BMW’, 50000, CarAttributes(’Red’, ’Sport’, ’Level-3’))
car6 = Car(6, ’Lotus’, 50000, CarAttributes(’Grey’, ’Sport’, ’Regular’))
car7 = Car(7, ’Audi’, 40000, CarAttributes(’Blue’, ’Regular’, ’Level-2’))
car8 = Car(8, ’Audi’, 45000, CarAttributes(’Blue’, ’Rain’, ’Regular’))
car9 = Car(9, ’Ford’, 30000, CarAttributes(’Violet’, ’Sport’, ’Level-1’))
dealership1 = Dealership([car1, car2, car3], ’KMac’)
dealership2 = Dealership([car4, car5, car6, car7], ’JRM’)
dealership3 = Dealership([car8, car9], ’YPeng’)
Part I: Add a Car to a Dealership
Implement the method add cars() for the Dealership class. The method takes one argument, cars, which is a list of lists of car details. Each list within the cars list represents the details (properties) for a single car.
You may assume the entire list is always valid. A details list for a particular car will always be presented in thisorder: [id, brand, price, color, tires, trim-level]. The id, brand and price will be
stored inside a Car object and the other three properties will be stored inside a CarAttributesobject insidethe Car object.
Your method should create new Car objects and add them to the car list given to you in the Dealershipclass.
Examples:
Consider the following lists of lists of car details:
p1List = [
[11, ’Mercedes’, 40000, ’Grey’, ’Snow’, ’Regular’],
[12, ’Ford’, 20000, ’Red’, ’Rain’, ’Level-1’],
]
p2List = [ ]
p3List = [
[13, ’Mercedes’, 40000, ’Grey’, ’Snow’, ’Regular’],
[14, ’Mercedes’, 40000, ’Blue’, ’Snow’, ’Regular’],
[15, ’Mercedes’, 40000, ’Orange’, ’Snow’, ’Regular’],
]
Function Call:
dealership1.add_cars(p1List)
Updated dealership1.car list:
KMac:
Car: [ <1> Ford – 23000 – Attributes: [Red – Rain – Level-1] ]
Car: [ <2> BMW – 46000 – Attributes: [Blue – Regular – Regular] ]
Car: [ <3> Ferrari – 150000 – Attributes: [Violet – Regular – Level-2] ]
Car: [ <11> Mercedes – 40000 – Attributes: [Grey – Snow – Regular ] ]
Car: [ <12> Ford – 20000 – Attributes: [Red – Rain – Level-1] ]
Function Call:
dealership2.add_cars(p2List)
Updated dealership2.car list:
JRM:
Car: [ <4> Toyota – 26000 – Attributes: [Black – Snow – Regular] ]
Car: [ <5> BMW – 50000 – Attributes: [Red – Sport – Level-3] ]
Car: [ <6> Lotus – 50000 – Attributes: [Grey – Sport – Regular] ]
Car: [ <7> Audi – 40000 – Attributes: [Blue – Regular – Level-2] ]
Function Call:
dealership3.add_cars(p3List)
Updated dealership3.car list:
YPeng:
Car: [ <8> Audi – 45000 – Attributes: [Blue – Rain – Regular] ]
CSE 101 – Fall 2017 Lab #8 Page 3
Car: [ <9> Ford – 30000 – Attributes: [Violet – Sport – Level-1] ]
Car: [ <13> Mercedes – 40000 – Attributes: [Grey – Snow – Regular] ]
Car: [ <14> Mercedes – 40000 – Attributes: [Blue – Snow – Regular] ]
Car: [ <15> Mercedes – 40000 – Attributes: [Orange – Snow – Regular] ]
Note: to access the contents of the attributes property of a Car object you need to use the dot operator.
For example, suppose car1 refers to a Car object. To change that car’s paint color to red we would type this:
car1.attributes.paint = ’Red’.
Part II: Update a Car
Implement the method update car() in the Dealership class. The method takes the following arguments, in this order:
1. id: The ID # of the car to be updated.
2. new value: A tuple containing the detail to be updated and the corresponding value. The tuple will look similar to this: (’brand’, ’Dodge’). Any one of the five details can be modified, as identified by one of these strings: ’brand’, ’price’, ’paint’, ’tires’ or ’trim’.
Your function should update the property of the car that matches the id in the given dealership and return ’Updated’. If the id doesn’t match and car offered for sale by the dealership, return ’Car not found’, capitalized exactly as written.
Note: no two cars will ever have the same id.
Examples:
Function Call:
dealership1.update_car(1, (’brand’, ’Hyundai’))
Return Value: “Updated”
Updated Dealership:
KMac:
Car: [ <1> Hyundai – 23000 – Attributes: [Red – Rain – Level-1] ]
Car: [ <2> BMW – 46000 – Attributes: [Blue – Regular – Regular] ]
Car: [ <3> Ferrari – 150000 – Attributes: [Violet – Regular – Level-2] ]
Function Call:
dealership2.update_car(100, (’paint’, ’Red’))
Return Value: “Car not found”
Updated Dealership:
CSE 101 – Fall 2017 Lab #8 Page 4
JRM:
Car: [ <4> Toyota – 26000 – Attributes: [Black – Snow – Regular] ]
Car: [ <5> BMW – 50000 – Attributes: [Red – Sport – Level-3] ]
Car: [ <6> Lotus – 50000 – Attributes: [Grey – Sport – Regular] ]
Car: [ <7> Audi – 40000 – Attributes: [Blue – Regular – Level-2] ]
Function Call:
dealership3.update_car(8, (’trim’, ’Level-1’))
Return Value: “Updated”
Updated Dealership
YPeng:
Car: [ <8> Audi – 45000 – Attributes: [Blue – Rain – Level-1] ]
Car: [ <9> Ford – 30000 – Attributes: [Violet – Sport – Level-1] ]
Part III: Search for a Car Among the Dealerships
Write a function search car() that takes the following arguments, in this order:
- query: A string containing information to search for. You may assume the format of the string will always be valid. The format of the string will always be: “SELECT * FROM name of dealership WHERE attribute of car == value of the attribute we are looking for”
This string may look strange to you, but it closely mimics a search query written in a language used in database applications called Structured Query Language, or SQL, for short.
You may assume that a dealership’s name never contains spaces. - dealership: A list of Dealership objects.
Your function should return a list of all Car objects that match the given criteria. If no cars match the criteria, then return the empty list, []. If dealership is empty, return None.
Examples:
Function Call:
dealers1 = [dealership1]
query1 = “SELECT * FROM KMac WHERE trim == Level-1”
search_car(query1, dealers1)
query1 roughly translates to: Find cars with trim “Level-1”, from the dealership “KMac”.
Return Value:
[
Car: [ <1> Ford – 23000 – Attribute: [Red – Rain – Level-1] ]
]
Function Call:
dealers2 = [dealership1, dealership2]
query2 = “SELECT * FROM JRM WHERE brand == Toyota”
search_car(query2, dealers2)
query2 roughly translates to: Find cars with brand “Toyota”, from the dealership “JRM”.
Return Value:
[
Car: [ <4> Toyota – 26000 – Attributes: [Black – Snow – Regular] ]
]
Function Call:
dealers3 = [dealership1, dealership2, dealership3]
query3 = “SELECT * FROM YPeng WHERE brand == McLaren”
search_car(query3, dealers3)
query3 roughly translates to: Find cars with brand “McLaren”, from the dealership “YPeng”.
Return Value:
[]
Solution
class Car:
def __init__(self, id, brand, price, attributes):
self.id = id
self.brand = brand
self.price = price
self.attributes = attributes
def __repr__(self):
attr = self.attributes
return ‘\tCar: [ <‘ + str(self.id) + ‘> ‘ + str(self.brand) + ‘ – ‘ + str(self.price) + str(attr) + ‘ ]’
def __eq__(self, other):
return self.id == other.id and self.brand == other.brand and \
self.price == other.price and \
self.attributes.paint == other.attributes.paint and \
self.attributes.tires == other.attributes.tires and \
self.attributes.trim == other.attributes.trim
def __lt__(self, other):
return self.id < other.id
classCarAttributes:
def __init__(self, paint, tires, trim):
self.paint = paint
self.tires = tires
self.trim = trim
def __repr__(self):
return ‘ – Attributes: [‘ + str(self.paint) + ‘ – ‘ + str(self.tires) + ‘ – ‘ + str(self.trim) + ‘]’
class Dealership:
def __init__(self, car_list, name):
self.car_list = car_list
self.name = name
def __repr__(self):
string = self.name + ‘: \n’
for i in self.car_list:
string += str(i) + ‘\n’
return string
defadd_cars(self, cars):
for car in cars:
attr = CarAttributes(car[3], car[4], car[5])
c = Car(car[0], car[1], car[2], attr)
self.car_list.append(c)
defupdate_car(self, id, new_value):
updated = False
for car in self.car_list:
if car.id == id:
ifnew_value[0] == ‘brand’:
car.brand = new_value[1]
ifnew_value[0] == ‘price’:
car.price = new_value[1]
ifnew_value[0] == ‘paint’:
car.attributes.paint = new_value[1]
ifnew_value[0] == ‘tires’:
car.attributes.tires = new_value[1]
ifnew_value[0] == ‘trim’:
car.attributes.trim = new_value[1]
updated = True
if updated:
return “Updated”
return “Car not found”
defsearch_car(query, dealerships): # SELECT * FROM dealership1 WHERE brand == Ford AND paint == RED
# get the dealer and list of filters for searching
query = query.split(maxsplit=3)[3]
dealer, filters = query.split(“WHERE”, maxsplit=1)
dealer = dealer.strip()
filters = filters.split(“AND”)
values = []
forflt in filters:
key, value = flt.split(“==”)
values.append((key.strip(), value.strip()))
# search the result
cars = []
for dealership in dealerships:
if dealership.name == dealer:
for c in dealership.car_list: # filter the cars
matched = True
for key, value in values:
if key == ‘id’ and c.id != int(value):
matched = False
if key == ‘brand’ and c.brand != value:
matched = False
if key == ‘price’ and c.price != int(value):
matched = False
if key == ‘paint’ and c.attributes.paint != value:
matched = False
if key == ‘tires’ and c.attributes.tires != value:
matched = False
if key == ‘trim’ and c.attributes.trim != value:
matched = False
if matched:
cars.append(c)
return cars
# DO NOT MODIFY ANYTHING BEYOND THIS POINT
defreset_cars():
global car1, car2, car3, car4, car5, car6, car7, car8, car9, dealership1, dealership2, dealership3
car1 = Car(1, ‘Ford’, 23000, CarAttributes(‘Red’, ‘Rain’, ‘Level-1’))
car2 = Car(2, ‘BMW’, 46000, CarAttributes(‘Blue’, ‘Regular’, ‘Regular’))
car3 = Car(3, ‘Ferrari’, 150000, CarAttributes(‘Violet’, ‘Regular’, ‘Level-2’))
car4 = Car(4, ‘Toyota’, 26000, CarAttributes(‘Black’, ‘Snow’, ‘Regular’))
car5 = Car(5, ‘BMW’, 50000, CarAttributes(‘Red’, ‘Sport’, ‘Level-3’))
car6 = Car(6, ‘Lotus’, 50000, CarAttributes(‘Grey’, ‘Sport’, ‘Regular’))
car7 = Car(7, ‘Audi’, 40000, CarAttributes(‘Blue’, ‘Regular’, ‘Level-2’))
car8 = Car(8, ‘Audi’, 45000, CarAttributes(‘Blue’, ‘Rain’, ‘Regular’))
car9 = Car(9, ‘Ford’, 30000, CarAttributes(‘Violet’, ‘Sport’, ‘Level-1’))
dealership1 = Dealership([car1, car2, car3], ‘KMac’)
dealership2 = Dealership([car4, car5, car6, car7], ‘JRM’)
dealership3 = Dealership([car8, car9], ‘YPeng’)
# global variables
car1 = car2 = car3 = car4 = car5 = car6 = car7 = car8 = car9 = dealership1 = dealership2 = dealership3 = None
reset_cars()
if __name__ == ‘__main__’:
# Testing Part I
p1List = [[11, ‘Mercedes’, 40000, ‘Grey’, ‘Snow’, ‘Regular’],
[12, ‘Ford’, 20000, ‘Red’, ‘Rain’, ‘Level-1’],
]
p2List = []
p3List = [
[13, ‘Mercedes’, 40000, ‘Grey’, ‘Snow’, ‘Regular’],
[14, ‘Mercedes’, 40000, ‘Blue’, ‘Snow’, ‘Regular’],
[15, ‘Mercedes’, 40000, ‘Orange’, ‘Snow’, ‘Regular’],
]
dealership1.add_cars(p1List)
print(‘Testing addCars() for cars = p1List: \n’ + str(dealership1))
reset_cars()
dealership2.add_cars(p2List)
print(‘Testing addCars() for cars = p2List: \n’ + str(dealership2))
reset_cars()
dealership3.add_cars(p3List)
print(‘Testing addCars() for cars = p3List: \n’ + str(dealership3))
reset_cars()
print()
# Testing Part II
print(‘Testing updateCars() for id = 1, newValue = (“brand”, “Hyundai”) : ‘ + str(
dealership1.update_car(1, (‘brand’, ‘Hyundai’))))
print(dealership1)
print()
reset_cars()
print(‘Testing updateCars() for id = 100, newValue = (“paint”,”Red”): ‘ + str(
dealership2.update_car(100, (‘paint’, ‘Red’))))
print(dealership2)
print()
reset_cars()
print(‘Testing updateCars() for id = 8, newValue = (“trim”, “Level-1”): ‘ + str(
dealership3.update_car(8, (“trim”, “Level-1”))))
print(dealership3)
print()
reset_cars()
print()
# # Testing Part III
dealers1 = [dealership1]
query1 = “SELECT * FROM KMac WHERE trim == Level-1”
print(
‘Testing searchCar() for query = “SELECT * FROM KMac WHERE trim == Level-1”, dealership = [dealership1] \n’ + str(
search_car(query1, dealers1)))
print()
reset_cars()
dealers2 = [dealership1, dealership2]
query2 = “SELECT * FROM JRM WHERE brand == Toyota”
print(
‘Testing searchCar() for query = “SELECT * FROM JRM WHERE brand == Toyota”, dealership = [dealership1,dealership2] \n’ + str(
search_car(query2, dealers2)))
print()
reset_cars()
dealers3 = [dealership1, dealership2, dealership3]
query3 = “SELECT * FROM YPeng WHERE brand == McLaren”
print(
‘Testing searchCar() for query = “SELECT * FROM YPeng WHERE brand == McLaren”, dealership = [dealership1,dealership2,dealership3] \n’ + str(
search_car(query3, dealers3)))
reset_cars()
print()