Python MySQL数据操作

Question

我有一个大学的作业，我碰到了一堵墙，我似乎不管怎么努力都无法使它发挥作用。在

因此，这项任务的重点是从Python访问MySQL数据库，并查看和排列数据。数据库名为inb104，存在两个表，cars_sale和car_details。在

这是我的密码

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()

其他的测试用例都是一样的，但是现在测试失败了

^{pr2}$

正如你所看到的，它失败了，因为结尾是零，开始时没有$，有人知道这个问题吗？在

编辑：添加新代码后

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

我有3/4个测试用例通过了，我不知道为什么最后一个没有通过，但是这里是测试用例转储

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.

所以，不知道为什么现在失败了，很奇怪。在

以下是更新后的查询

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()

测试用例

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")

Answer 1

与前一个稍有不同：

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

编辑：对您编辑的后续操作-您返回的是E55之前的CLS，它希望它们以另一种方式返回。检查你的分类顺序；试试看

^{pr2}$

Answer 2

如果要在SELECT语句中执行此操作，可以尝试使用^{}和{a2}：

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

Answer 3

做一些简单的string formatting应该能让你做到：

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

%s字符用于输出字符串。%d用于输出整数（没有小数部分的数字），因此有点作弊。您还可以使用%.0f来显示一个带有0个小数位的浮点（而%.2f将显示小数点后两位的数字）。在