CODEWITHSUNDEEP
pythonpython-3.xsqlite
Nazeem Najmu
Nazeem Najmu

Reputation: 45

ID tag is not auto incrementing the numbers in python

I am learning python and trying to replicate what online tutorials do. I am trying to create a python desktop app where data is store in Postgresql. code is added below, 

 `cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)")`

problem is with (id INTEGER PRIMARY KEY), when i execute the code its showing none in place of 1st index. i want to show numbers. please help

this is for Python 3.7.3, psycopg2==2.8.3,

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 integer, isbn integer)")
conn.commit()
conn.close()

the result I am expecting is auto increment of numbers in 1st index where as presently it shows NONE. below is the present and expected result again. none title auther year isbn 01 title auther year isbn

Upvotes: 0

Views: 175

Answers (2)

user11664287
user11664287

Reputation:

because you say I am inserting the data manually then instead of 

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 integer, isbn integer)")
conn.commit()
conn.close()

Try to use

def connect():
conn=sqlite3.connect("books.db")
cur=conn.cursor()
cur.execute("SELECT * FROM books")
for row in cur:
    print(row[0],row[1],row[2],row[3],row[4])
conn.commit()
conn.close()

Upvotes: 1

MikeT
MikeT

Reputation: 56943

Trying to use a Cursor execute will not work for a CREATE statement and hence the NONE. See below for an example.

Re Indexes :-

There will be no specific index as column_name INTEGER PRIMARY KEY is special in that it defines the column as an alias of the rowid column which is a special intrinsic index using the underlying B-tree storage engine.

When a row is inserted then if no value is specified for the column (e.g. INSERT INTO book (title,author, year, isbn) VALUES ('book1','The Author','1999','1234567890') then id will be 1 and typically (but not certainly) the next row inserted will have an id of 2 and so on.

If after adding some rows you use SELECT * FROM book, then the rows will be ordered according to the id as no other index is specified/used.

Perhaps have a look at Rowid Tables.

Example

Perhaps consider the following example :-

DROP TABLE IF EXISTS book;
CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer);
INSERT INTO book (title,author, year, isbn) VALUES 
    ('book1','The Author','1999','1234567890'),
    ('book2','Author 2','1899','2234567890'),
    ('book3','Author 3','1799','3234567890')
;
INSERT INTO book VALUES (100,'book10','Author 10','1999','4234567890'); --<<<<<<<<<< specific ID
INSERT INTO book (title,author, year, isbn) VALUES 
    ('book11','Author 11','1999','1234567890'),
    ('book12','Author 12','1899','2234567890'),
    ('book13','Author 13','1799','3234567890')
;
INSERT INTO book VALUES (10,'book10','Author 10','1999','4234567890'); --<<<<<<<<<< specific ID
SELECT * FROM book;

This :-

  1. DROPs the book table (to make it easily re-runable)
  2. CREATEs the book table.
  3. INSERTs 3 books with the id not specified (typpical)
  4. INSERTs a fourth book but with a specific id of 100
  5. INSERTs another 3 books (not that these will be 101-103 as 100 is the highest id before the inserts)
  6. INSERTs a last row BUT with a specific id of 10.
  7. SELECTs all rows with all columns from the book table ordered, as no ORDER BY has been specified, according to the hidden index based upon the id. NOTE although id 10 was the last inserted it is the 4th row.

Result

enter image description here

In Python :-

conn = sqlite3.connect("books.db")
conn.execute("DROP TABLE IF EXISTS book")
conn.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY,title text, author text, year integer, isbn integer)")
conn.execute("INSERT INTO book (title,author, year, isbn) "
             "VALUES('book1','The Author','1999','1234567890'), "
             "('book2','Author 2','1899','2234567890'), "
             "('book3','Author 3','1799','3234567890');")
conn.execute("INSERT INTO book VALUES (100,'book10','Author 10','1999','4234567890'); --<<<<<<<<<< specific ID")
conn.execute("INSERT INTO book (title,author, year, isbn) VALUES ('book11','Author 11','1999','1234567890'),('book12','Author 12','1899','2234567890'),('book13','Author 13','1799','3234567890');")
conn.execute("INSERT INTO book VALUES (10,'book10','Author 10','1999','4234567890'); --<<<<<<<<<< specific ID")
cur = conn.cursor()
cur.execute("SELECT * FROM book") 
for each in cur:
    print("{0:<20} {1:<20} {2:<20} {3:<20} {4:<20}".format(each[0],each[1],each[2],each[3],each[4]))
conn.commit()
conn.close()

The results in :-

1                    book1                The Author           1999                 1234567890          
2                    book2                Author 2             1899                 2234567890          
3                    book3                Author 3             1799                 3234567890          
10                   book10               Author 10            1999                 4234567890          
100                  book10               Author 10            1999                 4234567890          
101                  book11               Author 11            1999                 1234567890          
102                  book12               Author 12            1899                 2234567890          
103                  book13               Author 13            1799                 3234567890

Upvotes: 1

Related Questions