CODEWITHSUNDEEP
pythonsqliteoptimizationquery-optimization
vy32
vy32

Reputation: 29677

get the ID of a PRIMARY KEY after an INSERT OR IGNORE

I'm using sqlite and have a table that looks like this:

CREATE TABLE IF NOT EXISTS dirnames (dirnameid INTEGER PRIMARY KEY,
                                     dirname TEXT NOT NULL UNIQUE);
CREATE INDEX IF NOT EXISTS dirnames_idx1 ON dirnames(dirnameid);
CREATE INDEX IF NOT EXISTS dirnames_idx2 ON dirnames(dirname);

I would like to get the dirnameid of an arbitrary directory, so right now I'm doing something that looks like this:

INSERT or IGNORE INTO dirnames (dirname) VALUE (?)
SELECT dirnameid from dirnames where dirname=?

Where ? is substituted with my directory name.

Is there a more efficient way to do this

Upvotes: 1

Views: 82

Answers (1)

Adriano Martins
Adriano Martins

Reputation: 1808

There is no way to optimize this query any further, since it would require a command on sqlite that does this action - which doesn't exist.

But, there is a way to 'optimize' this: You could try to use the LBYL tatics, so you would 'save' a roundtrip if the directory already exists.

Pseudocode (since I have no idea how your code looks like):

import sqlite3

#(...)

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

dirname = 'foo'
dirnameid = None

cursor.execute("SELECT dirnameid from dirnames where dirname=?;", (dirname,))
dirnameid = cursor.fetchone()[0]

if not dirnameid:
    cursor.execute("INSERT or IGNORE INTO dirnames (dirname) VALUES (?);", (dirname,))

    # WARNING: The following line should be used if, and ONLY if the access to the sqlite is not concurrent (AKA: Only one connection inserting to the table)
    dirnameid = cursor.lastrowid

    # Otherwise, use this:
    cursor.execute("SELECT dirnameid from dirnames where dirname=?;", (dirname,))
    dirnameid = cursor.fetchone()[0]

connection.close()

Sadly, this code will add another roundtrip if this is not the only program / thread working on the table.

But I must warn you, this seems to be a premature optimization, which should be avoided at all costs! (https://softwareengineering.stackexchange.com/questions/80084/is-premature-optimization-really-the-root-of-all-evil)

Upvotes: 1

Related Questions