如何在 SQLite 数据库中正确创建 ProductID 列

在创建一个 SQLite 数据库时，用户想要在 Product 表中添加一个 ProductID 列作为主键，但发现插入数据时 ProductID 列始终为空。

import sqlite3

def create_table(db_name, table_name, sql):
    with sqlite3.connect(db_name) as db:
        cursor = db.cursor()
        cursor.execute("select name from sqlite_master where name=?", (table_name,))
        result = cursor.fetchall()
        keep_table = True

        if len(result) == 1:
            response = input("The table {0} already exists, do you want to recreate it (y/n)?:  ".format(table_name))
            if response == "y":
                keep_table = False
                print("The table {0} will be recreated - all existing data will be lost.".format(table_name))
                cursor.execute("drop table if exists {0}".format(table_name))
                db.commit()
            else:
                print("The existing table was kept")

    if not keep_table:
        cursor.execute(sql)
        db.commit()

if __name__ == "__main__":
    db_name = "coffee_shop.db"
    sql = """create table Product
             (ProductID intiger,
             Name text,
             Price real,
             primary key(ProductID))"""
    create_table(db_name, "Product", sql)

在此代码中，用户将 ProductID 列的类型指定为 intiger，这是错误的，应该使用 integer 类型。

2. 解决方案

为了解决这个问题，需要将 ProductID 列的类型从 intiger 更改为 integer，并将其设置为自增主键。

import sqlite3

def create_table(db_name, table_name, sql):
    with sqlite3.connect(db_name) as db:
        cursor = db.cursor()
        cursor.execute("select name from sqlite_master where name=?", (table_name,))
        result = cursor.fetchall()
        keep_table = True

        if len(result) == 1:
            response = input("The table {0} already exists, do you want to recreate it (y/n)?:  ".format(table_name))
            if response == "y":
                keep_table = False
                print("The table {0} will be recreated - all existing data will be lost.".format(table_name))
                cursor.execute("drop table if exists {0}".format(table_name))
                db.commit()
            else:
                print("The existing table was kept")

    if not keep_table:
        cursor.execute(sql)
        db.commit()

if __name__ == "__main__":
    db_name = "coffee_shop.db"
    sql = """create table Product
             (ProductID integer primary key autoincrement,
             Name text,
             Price real)"""
    create_table(db_name, "Product", sql)

在更改了 ProductID 列的类型后，就可以使用 INSERT 语句向 Product 表中插入数据了。

import sqlite3

def insert_data(values):
    with sqlite3.connect("coffee_shop.db") as db:
        cursor = db.cursor()
        sql = "insert into Product (Name, Price) values (?,?)"
        cursor.execute(sql, values)
        db.commit()


if __name__ == "__main__":
    name = input("What is the product called?:  ")
    value = float(input("How much does it cost?:  "))
    product = (name, value)
    insert_data(product)

现在，当用户向 Product 表中插入数据时，ProductID 列将自动生成并作为主键。