How would I use AUTOINCREMENT in sqlite using python -

Question

I have created a table named 'Issue' with Issue_serial_number as AUTOINCREMENT-

sql= '''
CREATE TABLE Issue(
   Issue_serial_no INTEGER AUTO INCREMENT,
   roll_no INTEGER,
   Book_ID INTEGER,
   Issue_Date DATE,
   FOREIGN KEY (roll_no) REFERENCES Student(roll_no),
   FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID)
);
'''

try:
    connection.execute(sql)
    print("Issue table is being created")
except:
    print("issue table not created")

Now when I am running the following-

import datetime

c=connection.cursor()
now=datetime.date(2012,5,5)
c.execute("INSERT INTO Issue (roll_no, Book_ID, Issue_Date) VALUES(?,?,?)",(n,bi,now))
p=connection.cursor()
p.execute("select * from Issue")

for row in p.fetchall():
    print('Issue_serial_no: '+str(row[0]) + ' roll_no: '+ str(row[1])+ ' Book_ID: '+ str(row[2])+ ' Issue_Date: '+ str(row[3]))

Issue serial number is coming as None- Issue_serial_no: None roll_no: 3 Book_ID: 1 Issue_Date: 2012-05-05

How AUTOINCREMENT will be implemented?

Possible duplicate of [Is there an auto increment in sqlite?](https://stackoverflow.com/questions/7905859/is-there-an-auto-increment-in-sqlite) — Amit Tripathi, Jan 07 '19 at 06:44

score 0 · Accepted Answer · answered Jan 07 '19 at 07:47

0

Correct syntax is Issue_serial_no INTEGER PRIMARY KEY or Issue_serial_no INTEGER PRIMARY KEY AUTOINCREMENT. In many cases, AUTOINCREMENT is not required. See https://www.sqlite.org/autoinc.html

answered Jan 07 '19 at 07:47

klim

1,179
8
11

score 0 · Answer 2 · answered Jan 07 '19 at 08:01

0

Try This Way:

CREATE TABLE Issue(
   Issue_serial_no INTEGER PRIMARY KEY,
   roll_no INTEGER,
   Book_ID INTEGER,
   Issue_Date DATE,
   FOREIGN KEY (roll_no) REFERENCES Student(roll_no),
   FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID)
);

cur = conn.cursor()
sql = "INSERT INTO Issue VALUES (Null, ?, ?, ?)"
cur.execute(sql, (roll_no, Book_ID, Issue_Date))

answered Jan 07 '19 at 08:01

Partho63

3,117
2
21
39

It got resolved after creating the table like this- sql= ''' CREATE TABLE Issue( Issue_serial_no INTEGER PRIMARY KEY AUTOINCREMENT , roll_no INTEGER, Book_ID INTEGER, Issue_Date DATE, FOREIGN KEY (roll_no) REFERENCES Student(roll_no), FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID) ); ''' – SB_tech Jan 07 '19 at 09:18

How would I use AUTOINCREMENT in sqlite using python -

2 Answers2