在数据库中添加值

Question

我对Python还比较陌生，我正在尝试将程序作为项目的一部分。我正试图让程序在我的代码中为新客户添加报价，但它不断给出错误消息：

Traceback (most recent call last):
  File "/Users/user/CompSci/CompSci Tradtional Exemplar/Python Traditional Program Actual.py", line 549, in <module>
    sys.exit()
SystemExit

这是错误之前显示给我的shell：

1. Search for a quote
2. Add quote
3. Monthly report
4. Exit
2
1. Add quote for existing client
2. Add quote for new client
2
Add new client name:
name
could not save new client to database
No client was selected 

这是代码的一部分：

import sys
import csv
import sqlite3
import datetime
import time

# constants used in the program
LABOURPERHOUR = 16.49
DB = 'gardening.db'
MATERIALFILE = 'materials.txt'

# structure of the database
def runFirstTime():
    # use try-except to catch any database errors
        try:
            conn = sqlite3.connect(DB)
            c = conn.cursor()
            # create the client table with its two attributes
            # client is created first because clientID is a foreign key in the quote table
            query='''CREATE TABLE IF NOT EXISTS client (clientID int, clientName text, primary key(clientIDc.execute(query)))'''
            conn.commit()
            # create the quote table with 24 attributes
            query = '''CREATE TABLE IF NOT EXISTS quote (
quoteID int,
clientID int,
dayOfQuote int,
monthOfQuote int,
yearOfQuote int,
dayOfJob int,
monthOfJob int,
yearOfJob int,
lawnLength real,
lawnWidth real,
lawnCostPerM real,
patioLength real,
patioWidth real,
patioCostPerM real, deckingLength real,
deckingWidth real, deckingCostPerM real,
pondLength real,
pondWidth real,
pondCostPerM real,
featureNumber int, featuresCostEa real, lightingNumber int, lightingCostEa real,
primary key (quoteID),
foreign key (clientID) references
client(clientID))'''
            c.execute(query)
            conn.commit()
            c.close()
        except:
            pass

# read in the costs from the external file
def getCostsFromFile(filename):
    # save the results in a dictionary
    costs = {}
    try:
        # use the built in CSV reader
        fileReader = csv.reader(open(MATERIALFILE, 'r'))
        # loop over the rows
        for row in fileReader:
            # the text becomes the key in the dictionary and the float is the value
            costs[row[0]] = float(row[1])
        return costs
    except:
        print ("make sure materials.txt exists and is in the correct format")
        sys.exit()

# procedure to display the four options
def displayMenu():
    print ("1. Search for a quote")
    print ("2. Add quote")
    print ("3. Monthly report")
    print ("4. Exit")

# procedure that calculates all of the costs for a quote and displays it
def displayQuote(quoteData):
    # runningTotals keeps the costs of the materials in a dictionary
    runningTotals = {}
    # keeps a running total
    totalMaterialCost = 0.0

# procedure if a new quote is added to the system
def newQuote():
    # choice is displayed
    print ("1. Add quote for existing client")
    print ("2. Add quote for new client")
    menuChoice = input()
    # input is validated (either 1 or 2)
    while menuChoice not in ['1', '2']:
        print ("Enter either 1 or 2")
        menuChoice = input()
    # the clientNumber is got by either method
    if menuChoice == '1':
        clientNumber = displayClients()
    else:
        clientNumber = addClient()
    # check to see if the user choose a client
    if not clientNumber:
        print ("No client was selected")
        return
    print ("Entering a quote for client " + str(clientNumber))
    # enter all the quote data into a dictionary
    quoteData = {}
    # each entry is validated either as float or an int
    # with the prompt to the user and the minimum value
    quoteData['lawnLength'] = getValidFloat("Enter lawn length", 0)
    quoteData['lawnWidth'] = getValidFloat("Enter lawn width", 0)
    quoteData['patioLength'] = getValidFloat("Enter patio length", 0)
    quoteData['patioWidth'] = getValidFloat("Enter patio width", 0)
    quoteData['deckingLength'] = getValidFloat("Enter decking length", 0)
    quoteData['deckingWidth'] = getValidFloat("Enter decking width", 0)
    quoteData['pondLength'] = getValidFloat("Enter pond length", 0)
    quoteData['pondWidth'] = getValidFloat("Enter pond width", 0)
    quoteData['featureNumber'] = getValidInt("Enter number of water features",0)
    quoteData['lightingNumber'] = getValidInt("Enter number of lighting features", 0)
    # this information is added to the database
    try:
        conn = sqlite3.connect(DB)
        c = conn.cursor()
        # todays date is got using this built in function
        today = datetime.date.today()
        # the day, month and year is found out from the date
        quoteDay = today.day
        quoteMonth = today.month
        quoteYear = today.year
        # the primary key is a time in milliseconds (will be unique)
        quoteID = int(time.time())
        # this is a tuple of all the data to be added
        entries = (quoteID, clientNumber, quoteDay, quoteMonth, quoteYear,
quoteData['lawnLength'], quoteData['lawnWidth'], costs['lawnCostPerMetre'], quoteData['patioLength'], quoteData['patioWidth'], costs['patioCostPerMetre'], quoteData['deckingLength'], quoteData['deckingWidth'], costs['deckingCostPerMetre'],
quoteData['pondLength'], quoteData['pondWidth'], costs['pondCostPerMetre'],
quoteData['featureNumber'], costs['featureCost'], quoteData['lightingNumber'], costs['lightingCost'])
        # this is the query to insert all of the data
        c.execute('''INSERT INTO quote(quoteID, clientID, dayOfQuote, monthOfQuote, yearOfQuote, lawnLength, lawnWidth,
lawnCostPerM, patioLength, patioWidth, patioCostPerM, deckingLength, deckingWidth, deckingCostPerM, pondLength,
pondWidth, pondCostPerM, featureNumber, featureCostEa, lightingNumber, lightingCostEa) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', entries)
        conn.commit()
        c.close()
    except:
        # an error message in case the data couldn't be saved
        print ("quote could not be saved to database")
        print("Quote reference number:", quoteID)
        print(displayQuote(quoteData))
#    print "Quote reference number:",
#    print quoteID
#    displayQuote(quoteData)

# validates user input based on type (float) and a minimum value
def getValidFloat(prompt, minValue):enter code here
    valid = False
    answer = input(prompt)
    while not valid:
        try:
            # this will cause an error if it is the wrong type and
            # will be False if it is greater than the min value
            if float(answer) >= minValue:
                valid = True
        except:
            answer = input("Enter a valid number")
    # returns the answer in the correct type
    return float(answer)

# same as getValidFloat only for integer
def getValidInt(prompt, minValue):
    valid = False
    answer = input(prompt)
    while not valid:
        try:
            if int(answer) >= minValue:
                valid = True
        except:
            answer = input("Enter a valid number")
    return int(answer)


# function to add a new client, returns the clientID
def addClient():
    print ("Add new client name:")
    # get the client name from user input
    clientName = input()
    # check they have entered something
    while not clientName:
        clientName = input("Add a name")
        print(int(time.time()))
    try:
        conn = sqlite3.connect(DB)
        c = conn.cursor()
        # get the current time in milliseconds to be the primary key (will be unique)
        clientNumber = int(time.time())
        # SQL to add a new client
        c.execute('''INSERT INTO client VALUES(?,?)''', (clientNumber, clientName))
        conn.commit()
        c.close()
        # return the primary key
        return clientNumber

    except:
        # error message if database error
        print ("could not save new client to database")
    return



## the 'main' part - where the program starts
if __name__ =='__main__':
    # check the database exists and if not create the tables
    runFirstTime()

    # get all the costs from the external file
    costs = getCostsFromFile(MATERIALFILE)

    # carry on going round (the user exits by choosing 4)
    while True:
        # display the main menu
        displayMenu()
        menuChoice = input()
        # validate the user input as being 1-4
        while menuChoice not in ['1','2','3','4']:
            displayMenu()
            menuChoice = input("Enter a number 1-4")
        # choose the correct procedure based on the user input
        if menuChoice == '1':
            searchQuote()
        elif menuChoice == '2':
            newQuote()
        elif menuChoice == '3':
            monthReport()
        else:
        # if they choose 4 then exit
            print ("system closing")
        sys.exit()

现在，我只是想让新的报价工作，然后我会看看其他部分。这是materials.txt文件中的内容：

lawnCostPerMetre,15.50
patioCostPerMetre,20.99
deckingCostPerMetre,15.75
pondCostPerMetre,25.00
featureCost,150.00
lightingCost,5.00
lawnMinutes,20.00
patioMinutes,20.00
deckingMinutes,30.00
pondMinutes,45.00
featureMinutes,60.00
lightingMinutes,10.00

Answer 1

根据addClient（）函数，这是当它无法连接到数据库时将收到的错误消息。 您键入了name，addClient试图连接到数据库name，该数据库name可能不存在，然后它给出了无法将新客户端保存到数据库的异常。 之后它返回并调用系统出口. 看起来它的行为应该是这样的。 输入有效的数据库并再次测试。你知道吗

Answer 2

与其确切地告诉你哪里可能出错，不如我的建议是，如果让你去掉try except；或者打印exception。你知道吗

不建议使用try-except方法，因为它太模糊了，当您遇到这样的问题时，您不知道捕获了什么异常。你知道吗

当你弄清楚（例如，如果你是一个初学者，可以通过试错法）哪些异常是常见的，然后你可以在try中添加一些特殊的异常（例如AttributeError、ValueError等等）。但是现在请去掉try，这样您就可以看到是什么导致了异常。你知道吗