Path: csiph.com!usenet.pasdenom.info!dedibox.gegeweb.org!gegeweb.eu!nntpfeed.proxad.net!proxad.net!feeder1-2.proxad.net!usenet-fr.net!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed4.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Date: Tue, 01 Jul 2014 13:13:56 +0100
From: MRAB <python@mrabarnett.plus.com>
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: python-list@python.org
Subject: Re: Searching for lots of similar strings (filenames) in sqlite3 database
References: <tgia8bx2sf.ln2@news.ducksburg.com>
In-Reply-To: <tgia8bx2sf.ln2@news.ducksburg.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.11363.1404216846.18130.python-list@python.org>
Lines: 57
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:73784

On 2014-07-01 12:26, Adam Funk wrote:
> I have some code that reads files in a leafnode2 news spool & needs to
> check for new files periodically.  The full paths are all like
> '/var/spool/news/message.id/345/<123456@example.com>' with a 3-digit
> subdirectory & a Message-ID for the filename itself.  I'm using Python
> 3 & sqlite3 in the standard library.
>
> I have a table of filenames created with the following command:
>
>     cursor.execute('CREATE TABLE files (filename TEXT PRIMARY KEY, used INTEGER)')
>
> To check for new files in one of the subdirectories, I run A then
> either B or C below (I've tried both).
>
> A.
>      listing1 = os.listdir(directory)
>      listing [os.path.join(directory, x) for x in listing1]
>
> B.
>      cursor = db_conn.cursor()
>      for filename in listing:
>          cursor.execute('SELECT filename FROM files WHERE filename IS ?', (filename,))
>          row = cursor.fetchone()
>          if not row:
>              cursor.execute('INSERT INTO files VALUES (?, ?)', (filename, 0) )
>              files_new += 1
>      db_conn.commit()
>
> C.
>      cursor = db_conn.cursor()
>      subdir_like = directory + '/%'
>      cursor.execute('SELECT filename FROM files WHERE filename LIKE ?', (subdir_like,))
>      rows = cursor.fetchall()
>      known_files =  [row[0] for row in rows]
>      for filename in listing:
>          if filename not in known_files:
>              cursor.execute('INSERT INTO files VALUES (?, ?)', (filename, 0) )
>              files_new += 1
>      db_conn.commit()
>
> A+B was the first method I came up with, because it looks like the
> "keep it simple & let the database do its job" approach, but it was
> very time-consuming, so I tested A+C out.  A is quick (a second); B
> can drag on for over an hour to check 2000 filenames (for example) in
> a subdirectory; C always takes less than a minute.  So C is much
> better than B, but it looks (to me) like one of those attempts to
> bypass & ignore the database's built-in optimizations.
>
> Comments?
>
In C, 'known_files' is a list, so it performs a linear search for each
of the filenames. If you make 'known_files' a set, it'll probably be
even faster!

Anyway, I'm sure there's something in SQL for "insert or update" or "on
duplicate", but that's an SQL question, not a Python question.