时间戳

首页 > 解决方案 > 在创建表后插入数据时，python3 sqlite 表的自动递增 id 的占位符是什么？

python - 在创建表后插入数据时，python3 sqlite 表的自动递增 id 的占位符是什么？

问题描述

如果 createTable() 方法不存在，我已经创建了一个 python3 sqlite 表。之后说我想使用以下方法插入数据（insertVaribleIntoTable()）。

现在对于这种方法，我不知道新行的 id 之后我将插入数据，因为它是自动递增的。

所以我没有将 id 传递给 insertVaribleIntoTable() 方法。我应该为 id 变量做什么？谁能告诉我我应该将什么作为 id 占位符传递给 insertVaribleIntoTable() 方法，因为它会在我插入数据时自动递增？谁能告诉我该怎么做？你可以看到少了一个“？” 在 insertVaribleIntoTable() 方法中的“VALUES”关键字之后。谢谢你。

    def createTable(id, name, company, address, postal, country, home, business, mobile, fax, notes):
            
        try:
            sqliteConnection = sqlite3.connect('SQLite_Python.db');
            cursor = sqliteConnection.cursor();
            print("Connected to SQLite");
    
            sqlite_insert_with_param = """CREATE TABLE IF NOT EXISTS 'SqliteDb_Addresser' (id INTEGER PRIMARY KEY AUTOINCREMENT, name, company, address, postal, country, home, business, mobile, fax, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);""";
    
            data_tuple = (id, name, company, address, postal, country, home, business, mobile, fax, notes);
            cursor.execute(sqlite_insert_with_param, data_tuple);
            sqliteConnection.commit();
            print("Python Variables inserted successfully into SqliteDb_developers table");
    
            cursor.close();
    
        except sqlite3.Error as error:
            print("Failed to insert Python variable into sqlite table", error);
        finally:
            if (sqliteConnection):
                sqliteConnection.close();
                print("The SQLite connection is closed");  
    
    def insertVaribleIntoTable(name, company, address, postal, country, home, business, mobile, fax, notes):
            
        try:
            sqliteConnection = sqlite3.connect('SQLite_Python.db');
            cursor = sqliteConnection.cursor();
            print("Connected to SQLite");
    
            sqlite_insert_with_param = """INSERT INTO 'SqliteDb_Addresser'
                            (name, company, address, postal, country, home, business, mobile, fax, notes) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);""";
    
            data_tuple = (name, company, address, postal, country, home, business, mobile, fax, notes);
            cursor.execute(sqlite_insert_with_param, data_tuple);
            sqliteConnection.commit();
            print("Python Variables inserted successfully into SqliteDb_developers table");
    
            cursor.close();
    
        except sqlite3.Error as error:
            print("Failed to insert Python variable into sqlite table", error);
        finally:
            if (sqliteConnection):
                sqliteConnection.close();
                print("The SQLite connection is closed");

标签： pythonpython-3.xdatabasesqliteprimary-key

解决方案

如果该列是自动递增的，则不要插入该列。它将由数据库创建。

如果要查看最后插入的 ID，可以使用 last_insert_rowid()

CREATE TABLE Person (
     ID integer primary key AUTOINCREMENT, 
     Name varchar(20), 
     Age Integer 
)

INSERT INTO Person (Name, Age) VALUES ('Tim', 12);  -- Don't include ID

SELECT last_insert_rowid()    -- 1

通过代码插入时，同样的想法也适用。

推荐阅读