SQLite Forum

Query Contraint with an if clause?

Query Contraint with an if clause?

(1) By saru4 (samrusso4) on 2021-03-28 10:44:46 [link] [source]

I have a column (FP) that has a filename's FullPath(eg /home/sam/doc.txt)
I have another column (FMT) that has the Filename's Modifed Time (eg 2021-03-28 19:56:05)

+++++ I have a unique index on FP, but here is my problem: ++++++
I'd like to do an update if the FP is equal but the FMT is different. I would otherwise do an ignore
So I'm using python with an executemany with a "insert or ignore" (there are thousands of records)
cursor2.executemany("""INSERT OR ignore INTO FilesFolders ('FileName','FP','FolderOfFile','FileSizeOf','FMT' ) VALUES (?,?,?,?,?);""",FilesArray)

I cannot update a FP when the FMT is different - it just ignores it.
1. If I remove the unique of FP I run the risk of having 2 files with the same FP
2. If I do an "insert or replace" then I'm literally re-inserting thousands of records everytime it see the same FP.
I'd like to minimize inserts.

This sort of query would work But I don't know how to code it in sqlite:
On conflict of FP, if FMT is different then replace

Here is an example of a file entry:
id|FP               |FileName|FolderofFile|FileSizeOf|FMT
 3|/home/sam/doc.txt|doc.txt |/home/sam   |204       |2021-03-28 19:37:12

Any ideas on how to run such a query or maybe re-jig the table's constraints?

Here is my table:
CREATE TABLE FilesFolders (
    id              INTEGER,
    FP        STRING   UNIQUE,
    FileName        STRING,
    FolderOfFile    STRING,
    FileSizeOf      INTEGER,
    FMT		    DATETIME,
    PRIMARY KEY (id ASC    )
ATM there are approx 350,000 entries in the table.

(2.2) By Simon Slavin (slavin) on 2021-03-28 12:20:22 edited from 2.1 in reply to 1 [source]

SQLite supports the UPDATE command. You don't need INSERT OR IGNORE. You want something like

UPDATE FilesFolders SET [whatever] WHERE FP = [whatever] AND FMT != [whatever]

(4) By saru4 (samrusso4) on 2021-03-28 20:38:39 in reply to 2.2 [link] [source]

Hi Simon, The problem is that because of the size of the DB doing updates takes too long. I need an executemany triggered with a constraint and 1 transaction (I'm inserting via a python collection object e.g FilesArray)

(8) By TripeHound on 2021-03-29 09:20:32 in reply to 4 [link] [source]

Have you tried wrapping those UPDATEs in a transaction? I don't know Python, but I suspect executemany cannot be doing much different than:


so you should be aiming for:


How you engineer a wrapping-transaction in Python I don't know, but I'd be very surprised if it isn't possible.

(9) By Keith Medcalf (kmedcalf) on 2021-03-29 09:55:14 in reply to 8 [link] [source]

For your information, that depends on the wrapper being used. The included Python wrapper, pysqlite2 -- which is renamed to sqlite3, will, unless you tell it otherwise, automatically start a transaction for you whenever you issue a DML command which updates the database (INSERT/UPDATE) and you have to commit that transaction yourself, with the connection .commit() or .rollback() method.

You control the "type" of transaction magically started with the isolation_level parameter to the sqlite3.connect method -- the value of the parameter is appended to the BEGIN command used, the default being '' (an empty string), so the transactions are automatically started with "BEGIN". Valid isolation_level values are '', 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and None which result in the automatic transaction start being "BEGIN", "BEGIN DEFERRED", "BEGIN IMMEDIATE", "BEGIN EXCLUSIVE" or nothing at all, respectively.

If you set the isolation_level=None then you have manual control of transactions and must start them by issuing the appropriate .execute('BEGIN') commands.

Whenever the isolation_level is not None, a DML update is detected, and autocommit is True (that is, no transaction is in progress) then the BEGIN is issued automatically.

The BEGIN command is not a DML statement, so even if magic mode is in effect you can still .execute begin commands manually to start a transaction when one is not in progress without messing up the magic.

The .commit() and .rollback() methods checks that autocommit is False (a transaction is in process) before issuing a COMMIT or ROLLBACK command. If no transaction is in progress the .commit() and .rollback() methods are silently ignored.

Different versions of pysqlite2 (sqlite3) use various methods of determining when to incant their magic with varying amounts of success.

(3) By Keith Medcalf (kmedcalf) on 2021-03-28 19:08:06 in reply to 1 [link] [source]

You probably want to use the silly-named UPSERT. See here:


cursor2.executemany("""INSERT INTO FilesFolders ('FileName','FP','FolderOfFile','FileSizeOf','FMT' ) VALUES (?,?,?,?,?) ON CONFLICT (FP) DO UPDATE SET FMT=excluded.FMT;""",FilesArray)

Assuming you are using a version of SQLite3 that understands that syntax.

(5) By saru4 (samrusso4) on 2021-03-28 20:54:22 in reply to 3 [link] [source]

Hi Keith, The problem I see is that 99% of the time the FP will trigger a conflict (since most files are still the same) and will therefore trigger an update. I only want the update if the FMT is different. (BTW I cannot do a unique on the FMT as many files might have the same FMT as well)

Maybe I can do a on conflict (FP,FMT)? I have tried a unique on multiple fields but I can't get it working since FP is already unique.

Also (not a sqlite Qn): Since I'm updating via a executemany and an a Array, how do I get access to the updated FMT? You've listed excluded.FMT, but I don't understand that.

thanks for you help Sam

(6.1) By Keith Medcalf (kmedcalf) on 2021-03-28 22:58:58 edited from 6.0 in reply to 5 [link] [source]

Did you read the documentation linked?

Like everything in SQL when you want to do something only WHERE a condition is true, then one one uses a WHERE clause.

That is

INSERT INTO FilesFolders (FileName, FP, FolderOfFile, FileSizeOf, FMT) 
     VALUES (?, ?, ?, ?, ?)
                       SET FMT = excluded.FMT
                     WHERE FMT IS NOT excluded.FMT

Again, I would suggest reading the fine documentation by clicking on the following link:


Edited to pretty format

(7) By saru4 (samrusso4) on 2021-03-29 07:27:04 in reply to 6.1 [link] [source]

SOLVED! Thank you Keith. It worked as per your last post

Working Query: INSERT INTO FilesFolders (FileName, FP, FolderOfFile, FileSizeOf, FMT) VALUES (?, ?, ?, ?, ?) ON CONFLICT (FP) DO UPDATE SET FMT = excluded.FMT WHERE FMT IS NOT excluded.FMT;

Also, sorry about not reading the entire link. I did read the top bit, but I needed to read the entire lot to understand the use of "excluded" - now understood.

Also It took me some time to upgrade sqlite to a version >=3.24 for the upsert to work (in pycharm).

SQLITE continues to amaze me. regards Sam