Is there an auto in...
 
Share:
Notifications
Clear all

Is there an auto increment in SQLite using Python?


myTechMint
(@mytechmint)
Member Moderator
Joined: 4 years ago
Posts: 52
Topic starter  

I am trying to create a table with an auto-incrementing primary key in Sqlite3. I am not sure if this is really possible, but I am hoping to only have to designate the other fields.

For example:

CREATE TABLE people (id integer primary key auto increment, first_name varchar(20), last_name varchar(20));
Then, when I add a value, I was hoping to only have to do:
INSERT INTO people
VALUES ("John", "Smith");

Is this even possible?


Quote
Topic Tags
myTechMint
(@mytechmint)
Member Moderator
Joined: 4 years ago
Posts: 52
Topic starter  

Use ROWID. This is in every SQLite table whether you ask for it or not.

Syntax:

ROWID INTEGER PRIMARY KEY

If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.

ROWID (by whatever name you call it) is assigned a value whenever you INSERT a row, as you would expect. If you explicitly assign a non-NULL value on INSERT, it will get that specified value instead of the auto-increment. If you explicitly assign a value of NULL on INSERT, it will get the next auto-increment value.

Also, you should try to avoid:

INSERT INTO people VALUES ("John", "Smith");

and use

INSERT INTO people (first_name, last_name) VALUES ("John", "Smith");

instead. The first version is very fragile — if you ever add, move, or delete columns in your table definition the INSERT will either fail or produce incorrect data (with the values in the wrong columns).

Full Code Example of Same:

import sqlite3
from datetime import datetime
LOAD_TIMESTAMP = datetime.today().strftime('%Y-%m-%d %H%S')

def write_db (source_location, destination_location):
    try:
        connection = sqlite3.connect('database.db')
        cursor = connection.cursor()
        cursor.execute("""CREATE TABLE IF NOT EXISTS database_TABLE (ROWID INTEGER PRIMARY KEY, source_location TEXT, destination_location TEXT, load_time TIMESTAMP)""")
        db_insert_query = """INSERT INTO 'database_TABLE' ('source_location', 'destination_location', 'load_time') VALUES (?, ?, ?);"""
        db_data = (source_location, destination_location, LOAD_TIMESTAMP)
        cursor.execute(db_insert_query, db_data)
        connection.commit()
        connection.close()
    except Exception as connect_db_error:
        logging.error('ERROR !!! Not able to connect to Database: ', connect_db_error)

Output Snapshot:

ROWID source_location destination_location load_time
1 C:\Users\myTechMint\Documents\test-folder\ s3://myTechMint/test-folder/ 2021-12-06-1943
    1. Summary:

 

  1. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

  2. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

  3. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

  4. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

This post was modified 3 years ago 2 times by myTechMint

ReplyQuote