Autoupdating index column sqlite3 python

Question

I would like to make a table where I would have a specific order of rows to make it easier to access them individually and as fast as possible. To achieve this I have created the index column and made it a PRIMARY KEY, NOT NULL and UNIQUE. I am not sure that the last two will meke it faster, but feel free to correct me. This is where the problems start to arise. Whenever I delete any record from this table, the indexing will be destroyed, and this is a big issue, becuse in my code I rely on the fact that the next row has an index that is larger by one. To battle this, I have attempted to use the AUTOINCREMENT keyword, but it did not work as planned. Here the code which shows what I mean:

import sqlite3

con = sqlite3.connect('test_db.db')

con.execute('''
    CREATE TABLE IF NOT EXISTS epic_table (
        'index'        INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
        city           TEXT NOT NULL,
        avg_salary     FLOAT
    );
''')

con.execute('INSERT INTO epic_table VALUES (0, "Chicago", 12.0)')
con.execute('INSERT INTO epic_table VALUES (1, "New York", 9.11)')
con.execute('INSERT INTO epic_table VALUES (2, "Detroit", 0.19)')

print(con.execute('SELECT * FROM epic_table').fetchall(), '\n')

con.execute('DELETE FROM epic_table WHERE `index` = 1')

print(con.execute('SELECT * FROM epic_table').fetchall())

As an output I get:

[(0, 'Chicago', 12.0), (1, 'New York', 9.11), (2, 'Detroit', 0.19)] 

[(0, 'Chicago', 12.0), (2, 'Detroit', 0.19)]

As you can see, Detroit should have had the index 1, but it hasn't updated.

I could not find any way of fixing this, so I am asking this here, but if you know how to approach this problem in a different way, I am open for suggestions, after all, the only thing that matters is the result, not the means.

Answer 1

You treat a relational table as an ARRAY of rows. This is very wrong. A relational table is a SET of rows. The primary means to retrieve individual rows from such a table is via the primary key. But you should never treat integer primary key as a relative position of the row within the table. In fact, even if your primary key is an integer and it covers a contiguous range, interpreting it as an array index is still completely meaningless. There exist special databases for analytical applications (OLAP), which store data column-wise and possibly in contiguous areas. For those databases, interpreting a "contiguous" integer PK as an array index may have at least some sense. But SQLite is not such a database. You need to learn more about RDBMSs and rethink how you access your data. Judging about your knowledge level based on your question, you should not be concerned about performance, you have to learn quite a bit more before thinking about it.

Answer 2

When a field is defined as a primary key, that implies that it is the unique identifier of the row, and this is definitely the expected behavior of a primary key. Primary keys are expected to not change, so they can identify one (and only one) row forever.

Using AUTOINCREMENT simply lets you create new rows without explicitly defining the "index" column (which, as a primary key, is required for new rows). The way AUTOINCREMENT works is by keeping track of a counter, which it uses to auto-ID the next row.

I would like to make a table where I would have a specific order of rows to make it easier to access them individually and as fast as possible.

The current system (using a primary key) can easily satisfy this purpose. Even with missing numbers throughout the table, you retain the order of rows as initially specified. And you can still access them individually using their primary key, as intended.

To access the "next" row in a table, you can use a limit + offset combo. For example,

SELECT * FROM epic_table 
LIMIT 1 OFFSET 2 
ORDER BY 'index' ASC;

This will allow you to increment a counter and access the n-th element in your table, instead of having to mess with primary keys.