Using flask framework I created table in SQLite with 3 rows (id, author, title). While i run program id is equal to null (id=null) in JSON format. Please help out to this. I created 2 files in python app.py to run my code in flask framework and db.py to create a database in SQLite

app.py flie

from flask import Flask, request, jsonify
import sqlite3
import json

app = Flask(__name__)

def db_connection():
    conn = None
    try:
        conn = sqlite3.connect('books.sqlite')
    except sqlite3.error as e:
        print(e)
    return conn

@app.route('/books', methods=['GET','POST'])
def books():
    conn = db_connection()
    cursor = conn.cursor()

    if request.method == 'GET':
        cursor = conn.execute("SELECT * FROM books")
        books = [
            dict(id=row[0],author=row[1], title=row[2])
            for row in cursor.fetchall()
        ]
        if books is not None:
            return jsonify(books)
    
    if request.method == 'POST':
        new_author = request.form['author']
        new_title = request.form['title']
        sql = """
                INSERT INTO books (author,title) VALUES (?,?)
        """
        cursor = cursor.execute(sql, (new_author,new_title))
        conn.commit()

        return f'Book with the id: {cursor.lastrowid} created successfully',201

db.py flie

import sqlite3

conn = sqlite3.connect("books.sqlite")

cursor = conn.cursor()

sql_query = """ 
    CREATE TABLE books (
        id int AUTO_INCREMENT PRIMERY KEY,
        author varchar(255) NOT NULL,
        title varchar(255) NOt NULL
    )
    """

cursor.execute(sql_query)

CodePudding user response：

This is the correct syntax for SQLite:

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author TEXT NOT NULL,
    title TEXT NOT NULL
);

Notice that you should define the column id as INTEGER and not int, if you want the column to be autoincremented.
The keyword AUTOINCREMENT (and not AUTO_INCREMENT) is optional and you should use it only if you don't want deleted id values to be reused in new rows.
Also, there is no VARCHAR data type in SQLite. Use TEXT.