Using SQL and Python (CS50 PSET7)

Question

这个程序有两个部分。

1. 在import.py中，编写一个从CSV电子表格导入数据的程序。CSV电子表格包含全名、出生日期、年份，我想将全名分隔为"；名字"中间名"姓氏"；。我的计划如下：

导入.py

from csv import reader
from sys import argv, exit
from cs50 import SQL
import csv
import sys
# check command line arguments
if len(sys.argv) != 2:
print(f"Usage: import.py .csv")
exit(1)
if not argv[1].endswith(".csv"):
print(f"Usage: import.py .csv 3 arg")
exit(1)
# variables
db = SQL("sqlite:///students.db")
total = 0
first = []
middle = []
last = []
house = []
birth = []
with open(argv[1], 'r') as csvfile:
data = csv.reader(csvfile)
for row in data:
names = row[0]
house = row[1]
birth = row[2]
total = total + 1
# split names
if len(names) == 2:
first.append(names[0])
middle.append('None')
last.append(names[1])
if len(names) == 3:
first.append(names[0])
middle.append(middle[2])
last.append(names[2])
for i in range(total - 1):
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)", first[i], middle[i], last[i], house[i], birth[i])

2. 对于第二部分roster.py，任务是编写一个程序，按字母顺序打印给定房子的学生名单

例如

$ python roster.py Gryffindor

程序应返回，例如

Hermione Jean Granger, born 1979
Harry James Potter, born 1980
Ginevra Molly Weasley, born 1981
Ronald Bilius Weasley, born 1980

我的代码如下：

花名册.py

from csv import reader
from sys import argv, exit
from cs50 import SQL
import sys
db = SQL("sqlite:///students.db")
# check command line arguments
if len(sys.argv) != 2:
print(f"Usage: python roster.py house")
exit(1)
house = sys.argv[1]
# if sys.argv[1].lower() != "slytherin" or sys.argv[1].lower() != "gryffindor" or sys.argv[1].lower() != "ravenclaw" or sys.argv[1].lower() != "hufflepuff":
#  print("choose a house!")
# exit(1)
rows = db.execute('SELECT * FROM students WHERE house = (?) ORDER BY last, first', sys.argv[1])
for row in rows:
print(row['first'],end=" ")
if row["middle"] != 'None':
print(row['middle'],end=" ")
print(row['last'],end=", ")
print("born ", row['birth'])

我已经被卡住很长时间了，不明白为什么代码没有打印出名字！非常感谢您的帮助！

Answer 1

import.py有三个问题，首先CCD_ 1处于错误的缩进处，其次，您不是在列表中追加，而是覆盖它，最后，您的create语句不正确。

看看这是否有帮助-

total = 0
first = []
middle = []
last = []
house = []
birth = []
with open(argv[1], 'r') as csvfile:
data = csv.reader(csvfile)
for row in data:
names = row[0].split()
house = row[1]
birth = row[2]
total = total + 1
# split names
if len(names) == 2:
first.append(names[0])
middle.append('None')
last.append(names[1])

if len(names) == 3:
first.append(names[0])
middle .append(names[1])
last.append(names[2])
db.execute("CREATE TABLE students (first char(100), middle char(100), last char(100), house char(100), birth char(100))")
for i in range(total):
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)", first[i], middle[i], last[i], house[i], birth[i])

正如你所看到的，我已经把CREATE和INSERT分开了。由于我不知道出生的数据类型，我假设它是CHAR，请根据您的需要进行修改。