CODEWITHSUNDEEP
pythonpython-3.xsqliteauto-increment
Siddhant
Siddhant

Reputation: 11

SQLite3 primary key not auto incrementing

I tried writing this code hoping that it will auto increment, but somehow it is not working and the output entries in id column are set to 'None'.I have also tried other answers but none of them are working.Please help if possible.

Here is the code:

import sqlite3

def connect():
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY,title text,author text,year int,isbn int)")
    conn.commit()
    conn.close()

def insert(title,author,year,isbn):
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("INSERT INTO book VALUES (?,?,?,?)",(title,author,year,isbn))
    conn.commit()
    conn.close()

def view():
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("SELECT * FROM book ")
    rows=cur.fetchall()
    conn.close()
    return rows

connect()
insert("sample","abc",2003,123456)
insert("sample2","def",2003,123457)
print(view())

This is the output:

[(None, 'sample', 'abc', 2003, 123456), (None, 'sample2', 'def', 2003, 123457)]

Upvotes: 1

Views: 4485

Answers (2)

Ethan
Ethan

Reputation: 1373

First SQLite recommends that you not use auto increment as your primary, you should select fields that will define a unique record whenever possible.

Second the data type you are passing in is “int” and requires the autoincrement keyword following primary key.

Third you should avoid using * in your select statement. If you simply need a row number back you can query the fields you need and add in the standard field “rowid”.

Upvotes: 1

Dave
Dave

Reputation: 622

Answer Edited: Thanks to Shawn's comment I went back to play with the code and hunt down the problem It is true that AUTOINCREMENT is not needed and as such is not the problem (I learned something new about sqlite).

The following code does work. Notice, since you're not supplying data to all columns in the table that you must specify which columns you are inserting data into in your insert statement. I have removed the unnecessary AUTOINCREMENT, and modified the insert statement to work correctly.

Also note: As others have stated, you should not use * wild card for selecting all columns in production code, but instead list all columns individual.

import sqlite3

def connect():
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY,title text,author text,year int,isbn int)")
    conn.commit()
    conn.close()

def insert(title,author,year,isbn):
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("INSERT INTO book (title, author, year, isbn) VALUES (?,?,?,?)",(title,author,year,isbn))
    conn.commit()
    conn.close()

def view():
    conn=sqlite3.connect("books.db")
    cur=conn.cursor()
    cur.execute("SELECT * FROM book ")
    rows=cur.fetchall()
    conn.close()
    return rows

connect()
insert("sample","abc",2003,123456)
insert("sample2","def",2003,123457)
print(view())

The produced output is:

[(1, 'sample', 'abc', 2003, 123456), (2, 'sample2', 'def', 2003, 123457)]

Upvotes: 3

Related Questions