Add as newest ID to SQLite Database

Question

At our company we use an SQLite Database to manage our Drawing IDs (CAD Drawings). The code is written in Python and I need to update it. I think the code I have has some problems, but I am quite unsure so I hope to get feedback and criticism from you all!

1. What my code does:

It saves the ID, Title and Project Number of the Drawing in a SQLite Database
Therefore it has a function GetID to get the latest ID and create a new one from it

Our Drawing IDs look like this "DR0000001". So I need to split the string, convert it to an Integer and add +1. Then rebuild the ID and return it to the OnSave function.

Then if false is not returned I start the save process.

2. Problems I think I have with this code:

I think the time between getting the recent ID and saving the new one to the DB is susceptible to errors like double usage of IDs.
I really think this isn't a professional way to do this and I want to learn the correct way.

3. Here is the Code we used up to this point:

def GetID(self):
    try:
        conn = sqlite3.connect(SQL_PATH)
        c = conn.cursor()   
        c.execute("SELECT DrawingID FROM Drawing LIMIT 1 OFFSET (SELECT COUNT(*) FROM Drawing)-1") 
        recent_drawing_id = c.fetchall()

        new_drawing_id = "DR"+str(int(recent_drawing_id[0][0][2:])+1).zfill(len(recent_drawing_id[0][0][2:]))

        return new_drawing_id
    except:
        wx.MessageBox("Couldn't get new Drawing ID!", "Info", wx.OK | wx.ICON_INFORMATION)
        return False


def OnSave(self, event, mode):
    drawingID = self.GetID()

    if drawingID != False:
        conn = sqlite3.connect(SQL_PATH)
        c = conn.cursor()

        titel = self.tc2.GetValue()
        project_number = self.tc3.GetValue()

        try:
            c.execute("INSERT into Drawing (DrawingID,Titel,ProjectNumber) values (?,?,?)", (userID,titel,project_number))
            conn.commit() 
            c.close()
            return
        except:
            wx.MessageBox("Couldn't create new Drawing entry!", "Info", wx.OK | wx.ICON_INFORMATION)
            return
    else:
        return

MikeT · Accepted Answer · 2019-12-18 20:59:46Z

Really you don't need DR to be stored as it's a user convenience. That is you can add DR when extracting the value. You also don't need to check calculate the next number as SQlite can do this on your behalf.

To further elaborate if DrawingID is defined using INTEGER PRIMARY KEY the column is then an alias of the normally hidden rowid (if not a WITHOUT ROWID table (rarely used)).

When inserting a row and you don't sepcify a value for the column then a unique value is supplied. This is typically 1 greater than the highest with the first value as 1.

However, there is no guarantee that the unique value will be 1 greater or that if when (if) you reached the maximum number of 9223372036854775807 (basically impossible due to storage device capacity) that a lower (but still unique) number will be assigned.

Using an alias of the rowid will be more efficient not only as the value stored is shorter (maximum of 8 bytes), it is stored in the most efficient (up to twice as fast as other indexes) index. Of course you then don't have the additional inefficiency of trying to replicate this inbuilt behaviour by So I need to split the string, convert it to an Integer and add +1. Then rebuild the ID and return it to the OnSave function.

As an example consider the following :-

/* Cleanup in case existing Environment exists */
DROP TABLE IF EXISTS Drawing;
/* The suggested Schema */
CREATE TABLE IF NOT EXISTS Drawing (DrawingID INTEGER PRIMARY KEY, Title TEXT, ProjectNumber INTEGER);

/* Add various drawings */
INSERT INTO Drawing (Title,ProjectNumber) VALUES
    ('Room1',1),('Room2',1),('Room1',3),('Room3',3),('Room3',1),('Room2',3),('Room1',2);
/* Extracting Project, Drawing Title and Drawing ID */
SELECT 'Project - '||ProjectNumber AS ProjectNumber, Title, 'DR'||printf('%07d',DrawingID) AS DrawingID FROM Drawing ORDER BY ProjectNumber,DrawingId; 
/* Cleanup testing Environment */
DROP TABLE IF EXISTS Drawing;

The above results in :-

The actual data in the table being

sorted as above i.e. extracted using SELECT * FROM Drawing ORDER BY ProjectNumber,DrawingId;) :-

You may wish to have a read of

SQL As Understood By SQLite - CREATE TABLE - ROWIDs and the INTEGER PRIMARY KEY
SQLite Autoincrement
- (Noting that althogh this is a link to AUTOINCREMENT it explains why AUTOINCREMENT is best not used in addition to adding information)

RootTwo · Accepted Answer · 2019-12-10 05:59:06Z

cursor.lastrowid

I'm not a SQLite expert, but I think you could INSERT the row then UPDATE the row to add a DrawingID based on the automatically inserted/incremented rowid.

# choose a value for OFFSET to make sure that DrawingIDs generated using this
# method don't collide with those made by the old method
OFFSET = 0

def OnSave(self, event, mode):
    conn = sqlite3.connect(SQL_PATH)
    c = conn.cursor()

    titel = self.tc2.GetValue()
    project_number = self.tc3.GetValue()

    try:
        # insert the row without a DrawingID
        c.execute("INSERT into Drawing (Titel,ProjectNumber) values (?,?)",
                  (titel,project_number))

        conn.commit() 

        # calculate the drawingid based on the rowid of the just added row
        drawingid = f'DR{cursor.lastrowid + OFFSET:07d}'

        # update that row with the drawingid
        c.execute('UPDATE foo SET DrawingID=? WHERE rowid=?', 
                  (drawingid, cursor.lastrowid))

        conn.close()

Stack Exchange Network

Add as newest ID to SQLite Database

1. What my code does:

2. Problems I think I have with this code:

3. Here is the Code we used up to this point:

2 Answers 2

cursor.lastrowid

You must log in to answer this question.

Hot Network Questions

Add as newest ID to SQLite Database

1. What my code does:

2. Problems I think I have with this code:

3. Here is the Code we used up to this point:

2 Answers 2

cursor.lastrowid

You must log in to answer this question.

Related

Hot Network Questions