Why is my SQLite Autoincrement query not working?-CodePudding

I'm new to SQLite and SQL in general, recently I've been trying to mess around and use the autoincrement function, I've done something wrong in my code because the autoincrement is not filling in the column at all (Just returns "None").

import sqlite3

make_table = "CREATE TABLE IF NOT EXISTS products (product_ID INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(50), price int);"
c.execute(make_table)

insert_apple = "INSERT INTO products (name, price) VALUES ('apple', 2);"
c.execute(insert_apple)
insert_orange = "INSERT INTO products (name, price) VALUES ('orange', 2);"
c.execute(insert_orange)
insert_banana = "INSERT INTO products (name, price) VALUES ('banana', 3);"
c.execute(insert_banana)

select_products = "SELECT * FROM products;"
c.execute(select_products)
print(c.fetchall())

I already looked through the way to generally use AUTOINCREMENT but the error keeps repeating and I can't find my mistake, and didn't find any questions with the same problem. I saw people mentioning ROWID but none of the explanations felt thorough enough and explained how to use it.

Any help would be much appreciated.

EDIT: I Just tried making a dummy using the same technique of this and it worked, so it must be a typo somewhere in the code above.

Here is the full code of the dummy that did work:

import sqlite3

try:
    connection = sqlite3.connect('SQLite_Python.db')
    c = connection.cursor()
    print("Database created and Successfully Connected to SQLite")

    create_table = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(50), age INTEGER);"
    insert_data1 = "INSERT INTO users (name, age) VALUES ('John', 25);"
    insert_data2 = "INSERT INTO users (name, age) VALUES ('Jessica', 27);"
    c.execute(create_table)
    c.execute(insert_data2)
    c.execute(insert_data1)

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)

select_data = "SELECT * FROM users;"
c.execute(select_data)
print(c.fetchall())

So my guess is that it is a typo, I will be closing this post down if it turns out to be so

CodePudding user response：

This works for me:

import sqlite3

con = sqlite3.connect('test')

with con:

    c = con.cursor()

    make_table = "CREATE TABLE IF NOT EXISTS products (product_ID INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(50), price int);"
    c.execute(make_table)

    insert_apple = "INSERT INTO products (name, price) VALUES ('apple', 2);"
    c.execute(insert_apple)
    insert_orange = "INSERT INTO products (name, price) VALUES ('orange', 2);"
    c.execute(insert_orange)
    insert_banana = "INSERT INTO products (name, price) VALUES ('banana', 3);"
    c.execute(insert_banana)

    select_products = "SELECT * FROM products;"
    c.execute(select_products)
    print(c.fetchall())

Result:

[(1, 'apple', 2), (2, 'orange', 2), (3, 'banana', 3)]