Python MySQL data manipulation

Question

i have an assignment for uni, i've run into a wall, and i can't seem to get it working no amtter what i try.

So, this assignment is focused on accessing a MySQL database from Python, and viewing and arranging the data. The database is called inb104 and two tables exist, cars_for_sale and car_details.

Here is my code

def top_N_expensive(num_of_highest):
connection = MySQLdb.connect(host='localhost', user='root', passwd='root',\
                                                                 db='inb104')
cursor = connection.cursor()
sql = "SELECT cars_for_sale.make, cars_for_sale.model, +'$'+car_details.price \
            FROM cars_for_sale, car_details WHERE cars_for_sale.CarId = car_details.CarId \
            ORDER BY price DESC,price LIMIT " + str(num_of_highest)
cursor.execute(sql) 
rows = cursor.fetchall()
for row in rows:
    print row[0], row[1], row[2]
cursor.close()
connection.close()

Now when i run this code the test cases fail, this is just one of the test cases but the rest are the same

File "__main__", line 4, in __main__
Failed example:
top_N_expensive(10)
Expected:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000
Got:
VOLKSWAGEN GOLF 1300000.0
MERCEDES-BENZ SL 329990.0
BMW 3 299990.0
MERCEDES-BENZ SL 249990.0
PORSCHE 911 249990.0
MERCEDES-BENZ SL 224990.0
PORSCHE 911 219990.0
PORSCHE 911 190000.0
PORSCHE 911 184990.0
BMW M5 180000.0

As you can see, it's failing because there is a zero at the end and there is no $ at the beginning, anyone know the problem?

EDIT: After adding this new code

for row in rows:
    print "%s %s $%d" % (row[0], row[1], row[2])

I got 3/4 test case passes, i don't know why the last one isnt passing, but here is the test case dump

Trying:
top_N_expensive(10)
Expecting:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000

Warning (from warnings module):
File "Z:\Documents\top_N_expensive.py", line 82
cursor.execute(sql)
Warning: Truncated incorrect DOUBLE value: '$'
ok
Trying:
top_N_expensive(15)
Expecting:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000
MERCEDES-BENZ E55 $179990
MERCEDES-BENZ CLS $179990
PORSCHE 911 $165000
PORSCHE 911 $164900
PORSCHE 911 $161950
**********************************************************************
File "__main__", line 17, in __main__
Failed example:
top_N_expensive(15)
Expected:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000
MERCEDES-BENZ E55 $179990
MERCEDES-BENZ CLS $179990
PORSCHE 911 $165000
PORSCHE 911 $164900
PORSCHE 911 $161950
Got:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000
MERCEDES-BENZ CLS $179990
MERCEDES-BENZ E55 $179990
PORSCHE 911 $165000
PORSCHE 911 $164900
PORSCHE 911 $161950
Trying:
top_N_expensive(1)
Expecting:
VOLKSWAGEN GOLF $1300000
ok
Trying:
top_N_expensive(0)
Expecting nothing
ok
1 items had no tests:
__main__.top_N_expensive
**********************************************************************
1 items had failures:
1 of   4 in __main__
4 tests in 2 items.
3 passed and 1 failed.
***Test Failed*** 1 failures.

So, don't know why this is failing now, very strange.

Heres the updated Query

def top_N_expensive(num_of_highest):
connection = MySQLdb.connect(host='localhost', user='root', passwd='root',\
                                                                 db='inb104')
cursor = connection.cursor()
sql = "SELECT cars_for_sale.make, cars_for_sale.model, +'$'+car_details.price \
            FROM cars_for_sale, car_details WHERE cars_for_sale.CarId = car_details.CarId \
            ORDER BY price DESC, make, model DESC" + str(num_of_highest)
cursor.execute(sql) 
rows = cursor.fetchall()
for row in rows:
    print "%s %s $%d" % (row[0], row[1], row[2]) 
cursor.close()
connection.close()

Test cases

Trying:
top_N_expensive(10)
Expecting:
VOLKSWAGEN GOLF $1300000
MERCEDES-BENZ SL $329990
BMW 3 $299990
MERCEDES-BENZ SL $249990
PORSCHE 911 $249990
MERCEDES-BENZ SL $224990
PORSCHE 911 $219990
PORSCHE 911 $190000
PORSCHE 911 $184990
BMW M5 $180000
**********************************************************************
File "__main__", line 4, in __main__
Failed example:
top_N_expensive(10)
Exception raised:
Traceback (most recent call last):
  File "C:\Program Files\Python27\lib\doctest.py", line 1254, in __run
    compileflags, 1) in test.globs
  File "<doctest __main__[0]>", line 1, in <module>
    top_N_expensive(10)
  File "Z:\Documentstop_N_expensive.py", line 82, in top_N_expensive
    cursor.execute(sql)
  File "C:\Program Files\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Program Files\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC10' at line 1")

As to the zeroes, looks like you need to run the values through the Python float function. As to no $, you might need to print that. — octopusgrabbus, May 22 '12 at 14:05
Do you need to get the result using sql statement itself or you can format the output yourself. If it is later you prolly already have the solution. — specialscope, May 22 '12 at 14:07
I can format the output i guess, either way would work, theres no academic limitations on either, just not sure how i would go about it. Sorry i am new to the Python programming. — user183651, May 22 '12 at 14:10
The new failure is because "MERCEDES-BENZ E55" and "MERCEDES-BENZ CLS" are reversed. Your ORDER BY clause doesn't make sense. I think you want `ORDER BY price DESC, make, model`. — Tom, May 22 '12 at 14:46

Hugh Bothwell · Answer 1 · 2012-05-22T14:31:26.147

3

A slight variation on the previous:

for row in rows:
    print("{} {} ${:0.0f}".format(*row))

Edit: a follow-up to your edit - you're returning CLS before E55, it wants them the other way. Check your sort order; try

... ORDER BY price DESC, make, model DESC

edited May 22 '12 at 14:31

answered May 22 '12 at 14:26

Hugh Bothwell

55,315
8
84
99

I tried using your edit and it seemed to make all test cases fail – user183651 May 22 '12 at 14:38
! ok, unexpected... can you show one of the resulting failures' 'expected' vs 'got'? – Hugh Bothwell May 22 '12 at 14:42
Hi, i have edited the original post at the bottom – user183651 May 22 '12 at 14:47
it looks like you removed ' LIMIT ' from your query string, resulting in 'ORDER BY price DESC, make, model DESC 12' (which is bad syntax). Try sticking LIMIT back in. – Hugh Bothwell May 22 '12 at 14:50
Looks like that fixed it, i shouldnt have removed the limit part. Thanks for your help, all test cases passed, and i have a better understand now, wee! – user183651 May 22 '12 at 14:51

score 1 · Answer 2 · answered May 22 '12 at 14:27

1

If you want to do it in the SELECT statement you could try with CONCAT and ROUND:

SELECT CONCAT('$', ROUND(car_details.price)) AS price FROM car_details;

answered May 22 '12 at 14:27

Maehler

6,111
1
41
46

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

have a look at this question currency formatting in puython, it is most likely what you want. as a more hacky way you can convert to strings and the use string manipulation.

EDIT

copied from the above referenced question

>>> import locale
>>> locale.setlocale( locale.LC_ALL, '' )
'English_United States.1252'
>>> locale.currency( 188518982.18 )
'$188518982.18'
>>> locale.currency( 188518982.18, grouping=True )
'$188,518,982.18'

edited May 23 '17 at 11:49

Community

1
1

answered May 22 '12 at 14:05

olly_uk

11,559
3
39
45

How about putting an example here? – octopusgrabbus May 22 '12 at 14:06

score 0 · Answer 4 · answered May 22 '12 at 14:10

0

Doing some simple string formatting should get you there:

for row in rows:
    print "%s %s $%d" % (row[0], row[1], row[2])

The %s characters are for outputting strings. %d is for outputting integers (numbers with no decimal part), so it's a bit of a cheat. You could also use %.0f to show a float printed with 0 decimal places (whereas %.2f would show the number to two decimal places).

answered May 22 '12 at 14:10

Tom

22,301
5
63
96

Cheers for the assitance Tom, i have edited the original post with the further test case, just one which seems to be failing but for the life of me i could not figure out why. – user183651 May 22 '12 at 14:26

Python MySQL data manipulation

4 Answers4