Searching for lots of similar strings (filenames) in sqlite3 database

[Adam Funk]

On 2014-07-01, Chris Angelico wrote:

> On Wed, Jul 2, 2014 at 1:15 AM, Adam Funk <a24061 at ducksburg.com> wrote:
>> On 2014-07-01, Chris Angelico wrote:
>>
>>> On Tue, Jul 1, 2014 at 9:26 PM, Adam Funk <a24061 at ducksburg.com> wrote:
>>>>         cursor.execute('SELECT filename FROM files WHERE filename IS ?', (filename,))
>>>
>>> Shouldn't this be an equality check rather than IS, which normally I'd
>>> expect to be "IS NULL" or "IS NOT NULL"?
>>
>> Oh, it probably should be in "heavy" SQL.  In SQLite, '==', '=', &
>> 'IS' are interchangeable.
>>
>> http://www.tutorialspoint.com/sqlite/sqlite_operators.htm
>
> Ah, okay. In that case, I'd advise going with either == for
> consistency with the rest of Python, or (preferably) = for consistency
> with other SQL engines. You wouldn't use "is" to test if two Python
> strings are equal, so there's no particular reason to use it here :)

I agree.

>> Oh, even better:
>>
>>     add_files = listing - known_files
>>     delete_files = known_files - listing
>>
>> and then I can remove files that have disappeared off the spool from
>> the table.  Thanks very much!
>
> Ah! Didn't know that was a valuable feature for you, but getting that
> "for free" is an extra little bonus, so that's awesome!

I didn't know it was a valuable feature until I saw that easy way to
do it!  This will keep the database from growing indefinitely, though.



-- 
'...and Tom [Snyder] turns to him and says, "so Alice [Cooper], is it
true you kill chickens on stage?"  That was the opening question, and
Alice looks at him real serious and goes, "Oh no, no no.  That's
Colonel Sanders.  Colonel Sanders kills chickens."'