使用SQL和Python (CS50 PSET7)

Question

这一方案分为两部分。

1. In import.py，编写从CSV电子表格导入数据的程序。CSV电子表格包含全名、出生、年份，我想将全名分为“名字”、“中间名”、“姓氏”。我的节目如下：

import.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])

在第二部分，

1. ，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

我的代码如下：

roster.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有三个问题，第一，total在错误的缩进，第二，您没有附加到列表中，而是覆盖它，最后，您的创建语句不正确。

看看这是否有帮助-

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

如您所见，我已经将创建和插入分开了。由于我不知道出生的数据类型，我已经假设它是CHAR，请根据您的需要修改。