python - 将表单字段插入数据库(瓶子，sqlite)

必须有一种更好的方法来执行此操作。.我已经阅读了瓶子的文档，但实际上并没有太大帮助。可以，但是很脏。好像我缺少什么。

@route('/client/new', method="POST")
def add_new_client():
    c = db.cursor()
    query = """INSERT INTO Clients VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

    try:
        r = request.POST

        d = r['LastContact'].split('/') # mm/dd/yyyy
        try: lastcontact = datetime(int(d[2]), int(d[0]), int(d[1]))
        except Exception as e: lastcontact = datetime.now()

        try: taxExempt = r['TaxExempt']
        except Exception as e: taxExempt = 0

        c.execute(query, (r['FirstName'], r['LastName'], r['Address1'], r['Address2'], r['City'], r['State'], r['Zip'],
                 r['HomePhone'], r['CellPhone'], r['WorkPhone'], r['Email'], r['ContactMethod'], r['ContactTime'],
                 r['ContactFrequency'], lastcontact, taxExempt, r['TaxID'], r['Notes']))
        db.commit()
    except Exception as e:
        print "ERROR %s" % e
    redirect('/client/%d' % c.lastrowid)
    c.close()

最佳答案

如果您使用SQLAlchemy ORM（相关文章），事情看起来会更加干净：


Bottle – Adding SQLAlchemy to the Todo List Web App


如果您希望保留SQL和Python的混合使用，则可以使用命名/关键字查询占位符来简化代码，这将使您可以将r字典传递给查询：

d = r['LastContact'].split('/') # mm/dd/yyyy
try:
    r['LastContact'] = datetime(int(d[2]), int(d[0]), int(d[1]))
except Exception as e:
    r['LastContact'] = datetime.now()

if 'TaxExempt' not in r:
    r['TaxExempt'] = 0

query = """
    INSERT INTO
        Clients
    VALUES
        (NULL,
         :FirstName,
         :LastName,
         :Address1,
         :Address2,
         :City,
         :State,
         :Zip,
         :HomePhone,
         :CellPhone,
         :WorkPhone,
         :Email,
         :ContactMethod,
         :ContactFrequency,
         :ContactTime,
         :LastContact,
         :TaxExempt,
         :TaxID,
         :Notes)
"""
c.execute(query, r)