Having trouble autoincrementing in an API

Question

I built a to-do list API with Flask and SQlite, and now I'm trying to use AUTOINCREMENT for incrementing the id's for the tasks. However, I am getting an error ("Error: NOT NULL constraint failed: incomplete.id") when I try to add something to the list. I'm not sure why, I looked at the sqlite documentation, and I seem to be following. I even tried reformatting the create table statements. I'm not sure what else to do, i'd really appreciate some guidance/advice/help. Thanks!

Here is my helper.py

import helper
from flask import Flask, request, jsonify, Response

import json

app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello World!'

@app.route('/tasks/new', methods=['PUT'])
def add_task():
    # global idCount
    # idCount = idCount + 1 
    # get item from the POST body, request module used to parse request and get HTTP body data. response is used to return response to the client, of type JSON
    req_data = request.get_json()
    task = req_data['task']
    # add task to the list
    res_data = helper.add_to_incomplete(task)

    # return error if task cant be added
    if res_data is None:
        response = Response("{'error': 'Task not added - " + task + "'}", mimetype='application/json')
        return response;
    response = Response(json.dumps(res_data), mimetype='application/json')
    return response 

@app.route('/tasks/all', methods = ["GET"])
def get_all_items():
    res_data = helper.get_all_completes(), helper.get_all_incompletes()
    response = Response(json.dumps(res_data), mimetype='application/json')
    return response

@app.route('/tasks/complete', methods = ["POST"])
def complete_task():
    req_data = request.get_json()
    inputId = req_data['id']
    res_data = helper.add_to_complete(inputId)
    # find matching task to input id
    return "completed task" + inputId

@app.route('/tasks/incomplete', methods = ["PATCH"])
def uncomplete_task():
    req_data = request.get_json()
    inputId = req_data['id']
    res_data = helper.uncomplete(inputId)
    # find matching task to input id
    return "un-completed task" + inputId

@app.route('/tasks/remove', methods = ["DELETE"])
def delete():
    req_data = request.get_json()
    inputId = req_data['id']
    res_data = helper.delete_task(inputId)
    if res_data is None:
        response = Response("{'error': 'Error deleting task - '" + task +  "}", status=400 , mimetype='application/json')
    return "deleted task id" + " " + inputId 

@app.route('/tasks/empty', methods = ["EMPTY"])
def delete_all():
    helper.empty()
    return "you deleted everything"

Here is my helper.py:

import sqlite3
import random 

#for id's because users dont set them

DB_PATH = './todo.db'

# connect to database
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()

c.execute("CREATE TABLE IF NOT EXISTS complete (id INTEGER PRIMARY KEY, task TEXT NOT NULL);")
# save the change
c.execute("CREATE TABLE IF NOT EXISTS incomplete (id INTEGER PRIMARY KEY, task TEXT NOT NULL);")
conn.commit()


def add_to_incomplete(task): 
    try:
        # id = str(random.randrange(100,999))
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('insert into incomplete(task) values(?)', (task,))
        conn.commit()
        return {"id": id}

    except Exception as e:
        print('Error: ', e)
        return None

def add_to_complete(inputId):
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('select task from incomplete where id=?', (inputId,))
        tasks = c.fetchone()[0]
        c.execute('insert into complete values(?,?)', (inputId,tasks))
        delete_task(inputId)
        conn.commit()
        return {"id": id}

    except Exception as e:
        print('Error: ', e)
        return None

def get_all_completes():
    try: 
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('select * from complete')
        rows = c.fetchall()
        conn.commit()
        return { "complete": rows }
    except Exception as e:
        print('Error: ', e)
        return None

def get_all_incompletes():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('select * from incomplete')
        rows = c.fetchall()
        conn.commit()
        return { "incomplete": rows } 
    except Exception as e:
        print('Error: ', e)
        return None 

def uncomplete(inputId):
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('select task from complete where id=?', (inputId,))
        tasks = c.fetchone()[0]
        c.execute('insert into incomplete values(?,?)', (inputId,tasks))
        delete_task(inputId)
        conn.commit()
        return {"id": id}

    except Exception as e:
        print('Error: ', e)
        return None

def delete_task(inputId):
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('delete from complete where id=?', (inputId,))
        c.execute('delete from incomplete where id=?', (inputId,))
        conn.commit()
        return {"id":id}
    except Exception as e:
        print('Error: ', e)
        return None

def empty():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('delete from complete')
        c.execute('delete from incomplete')
        conn.commit()
        return "you deleted everything mwahaha"
    except Exception as e:
        print('Error: ', e)
        return None

Answer 1

I would suggest changing your sql table creation code to:

create table if not exists complete
(
    id int auto_increment,
    constraint complete_pk
        primary key (id)
);

However a better option is to use SQLAlchemy