我将如何使用python -在sqlite中使用AUTOINCREMENT

Question

我创建了一个名为"问题"的表，Issue_serial_number为自动增量-

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") 

现在，当我运行以下内容时-

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_no：无 roll_no：3 Book_ID：1 Issue_Date：2012-05-05

如何实现自动增量？

Answer 1

正确的语法是Issue_serial_no INTEGER PRIMARY KEY或Issue_serial_no INTEGER PRIMARY KEY AUTOINCREMENT。在许多情况下，不需要AUTOINCREMENT。请参阅 https://www.sqlite.org/autoinc.html

Answer 2

试试这个方式：

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))