[FIXED] Cannot code auto increment id in SQLite Flask

Issue

I have just created a new column, Id in DB Browser sqlite. I am not sure how am I suppose to code this portion in App.py. Should I use a id = request.form['id]?

App.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__)

This is my table in DB Browswer 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")
)

Solution

Here's how you set up an integer primary key column in sqlite and then insert and select from it:

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', '[email protected]', 'A', 'Blah', 10, 'Checked', 'Description goes here')")
conn.commit()

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

for row in cursor:
  print(row)

And here's a colab notebook demonstration: https://colab.research.google.com/drive/1OhV9lWSBxLpOv45bNKmtRx9H0j6BZ-S3?usp=sharing

So your code sample above becomes:

@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__)

Answered By - ultra909