时间戳

首页 > 解决方案 > 无法在 SQLite Flask 中编写自动增量 ID

python - 无法在 SQLite Flask 中编写自动增量 ID

问题描述

我刚刚在 DB Browser sqlite 中创建了一个新列 Id。我不确定我应该如何在 App.py 中编写这部分代码。我应该使用 id = request.form['id] 吗？

应用程序.py

@app.route('/addrec', methods=['POST', 'GET'])
def addrec():
    if request.method == 'POST':

        id = 
        use = session['user'].get("name")
        ema = session['user'].get("preferred_username")
        type = request.form['type']
        uploadre = request.form['uploadre']
        amt = request.form['amt']
        description = request.form['description']

        if request.form.get("price"):
            price_checked = "Yes"
        else:
            price_checked = "No"

    conn = sql.connect(db_path)
    c = conn.cursor()
    c.execute(
        "INSERT INTO SubmitClaim VALUES (?,?,?,?,?,?,?,?)", (id, use,ema, type, uploadre, amt,price_checked, description))
    conn.commit()
    c.execute("SELECT * FROM  SubmitClaim")
    print(c.fetchall())
    conn.close()

    return render_template('base.html', user=session["user"], version=msal.__version__)

这是我在 DB Browser Sqlite 中的表

CREATE TABLE "SubmitClaim" (
    "id"    INTEGER,
    "Name"  TEXT NOT NULL,
    "Email" TEXT NOT NULL,
    "ClaimType" TEXT NOT NULL,
    "UploadReceipt" TEXT NOT NULL,
    "ClaimAmount"   INTEGER NOT NULL,
    "checkbox"  TEXT NOT NULL,
    "ClaimDescription"  TEXT NOT NULL,
    PRIMARY KEY("id")
)

标签： pythonflask

解决方案

以下是如何在 sqlite 中设置整数主键列，然后插入并从中选择：

import sqlite3

conn = sqlite3.connect('test.db')
conn.execute('''
CREATE TABLE SubmitClaim (
    Id                INTEGER PRIMARY KEY NOT NULL,
    Name              NVARCHAR NOT NULL,
    Email             NVARCHAR NOT NULL,
    ClaimType         NVARCHAR NOT NULL,
    UploadReceipt     NVARCHAR NOT NULL,
    ClaimAmount       INTEGER NOT NULL,
    Checkbox          NVARCHAR NOT NULL,
    ClaimDescription  NVARCHAR NOT NULL
)
''')
conn.commit()

conn.execute("INSERT INTO SubmitClaim (Name, Email, ClaimType, UploadReceipt, ClaimAmount, Checkbox, ClaimDescription) VALUES ('Foo Bar', 'foo@bar.com', 'A', 'Blah', 10, 'Checked', 'Description goes here')")
conn.commit()

cursor = conn.execute('SELECT * FROM SubmitClaim')

for row in cursor:
  print(row)

这是一个 colab 笔记本演示：https ://colab.research.google.com/drive/1OhV9lWSBxLpOv45bNKmtRx9H0j6BZ-S3?usp=sharing

因此，您上面的代码示例变为：

@app.route('/addrec', methods=['POST', 'GET'])
def addrec():
    if request.method == 'POST':

        use = session['user'].get("name")
        ema = session['user'].get("preferred_username")
        type = request.form['type']
        uploadre = request.form['uploadre']
        amt = request.form['amt']
        description = request.form['description']

        if request.form.get("price"):
            price_checked = "Yes"
        else:
            price_checked = "No"

        conn = sql.connect(db_path)
        conn.execute('''
            INSERT INTO SubmitClaim 
                (Name, Email, ClaimType, UploadReceipt, ClaimAmount, Checkbox, ClaimDescription) 
                VALUES (?,?,?,?,?,?,?)''', 
            (use, ema, type, uploadre, amt, price_checked, description))
        conn.commit()

        c = conn.execute("SELECT * FROM  SubmitClaim")
        print(c.fetchall())
        conn.close()

    return render_template('base.html', user=session["user"], version=msal.__version__)

推荐阅读