时间戳

首页 > 解决方案 > 用格式打印出sqlite数据库查询的结果

python - 用格式打印出sqlite数据库查询的结果

问题描述

我有这个有四列的学生数据库：first_name, middle_name, last_name, school, birth_year. 我已经使用 SELECT 语句成功读取了 db 的内容，但这就是我陷入困境的地方：

如何以如下格式打印查询结果： first_name middle_name(if available) last_name was born in birth_year？因此，对于查询中的每一行，预期的结果都应该这样做：Aida Blue was born in 1985.

现在，如果我运行print(all_rows)代码，将打印如下查询结果：[{'first_name':'Aida', 'middle_name': None, 'last_name': 'Blue', 'school': 'Washington', 'birth_year': 1981}, {...}, ....]

以下是我尝试解决此类问题的代码：

db = cs.SQL("sqlite:///students.db")

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print("Usage: python student_school.py school")
        sys.exit()
    else:
        school = str(sys.argv[1])
        all_rows = db.execute("SELECT * FROM students WHERE school=?", school)
        first = all_rows[:][0] #I want to pick up the first col
        middle = all_rows[:][1] #if value <> None?
        last= all_rows[:][2]
        birth_year = all_rows[:][4]
        print(first_name, " " , middle_name, " ", last_name, "was born in ", birth_year \n)

有人可以请教吗？谢谢！

标签： pythonsqlite

解决方案

  • 您的all_rows变量字典列表。每个 dict 代表一个学生记录。
  • 您可以编写一个 for 循环来迭代它并打印每个循环。
  • 同样在打印语句中，当您写“，”时，它会自动将分隔符放入（单个空格）。无需显式编写" ". 与\n不需要显式写入'\n'相同。在此处阅读更多信息https://docs.python.org/3/library/functions.html#print。
db = cs.SQL("sqlite:///students.db")

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print("Usage: python student_school.py school")
        sys.exit()
    else:
        school = str(sys.argv[1])
        all_rows = db.execute("SELECT * FROM students WHERE school=?", school)
        for student in all_rows:
            print(student['first_name'], student['middle_name'] if student['middle_name'] else '', student['last_name'], "was born in", student['birth_year'])

推荐阅读