Python user input inside infinite loop too slow, easily confused
<p>I have a Python script running on a Raspberry Pi that sits waiting for user input and records the input in a SQLite database:</p>

<pre><code>#!/usr/bin/env python

import logging
import db

while True:
    barcode = raw_input("Scan ISBN: ")
    if ( len(barcode) &gt; 1 ):
        logging.info("Recording scanned ISBN: " + barcode)
        print "Recording scanned ISBN: " + barcode
        db.recordScan(barcode, 1)
</code></pre>

<p>That <code>db.recordScan()</code> method looks like this:</p>

<pre><code># Adds an item to queue
def recordScan(isbn, shop_id):
    insert = "INSERT INTO scans ( isbn, shop_id ) VALUES ( ?, ? )"
    conn = connect()
    conn.cursor().execute(insert, [isbn, shop_id])
    conn.commit()
    conn.close()
</code></pre>

<p><em>(Note: The whole code repo is available at <a href="https://github.com/martinjoiner/bookfetch-scanner-python/" rel="nofollow">https://github.com/martinjoiner/bookfetch-scanner-python/</a> if you wanna see how I'm connecting to the db and such)</em> </p>

<p>My problem is that using a USB barcode scanner (which is effectively just a keyboard input that sends a series of keystrokes followed by the <code>Enter</code> key) it is really easy to input at such a fast rate that the command line seems to get <em>"confused"</em>. </p>

<p><strong>For example compare the following results...</strong> </p>

<p>When you go slow the script works well and the command looks neat like this:</p>

<pre><code>Scan ISBN: 9780465031467
Recording scanned ISBN: 9780465031467
Scan ISBN: 9780141014593
Recording scanned ISBN: 9780141014593
Scan ISBN: 
</code></pre>

<p>But when you hammer it hard and go really fast the input prompt kind of gets ahead of itself and the messages printed by the script get written on top of the input prompt:</p>

<pre><code>Recording scanned ISBN: 9780141014593
9780141014593
9780141014593
9780465031467
Recording scanned ISBN: 9780141014593
Scan ISBN: Recording scanned ISBN: 9780141014593
Scan ISBN: Recording scanned ISBN: 9780141014593
Scan ISBN: Recording scanned ISBN: 9780465031467
Scan ISBN: 9780571273188
9780141014593
</code></pre>

<p>It sometimes hangs in that position indefinitely, I don't know what it's doing but you can wake it back up again with another input and it carries on as normal although the input before the one it hung on doesn't get recorded which is bad because it makes the whole system unreliable. </p>

<p>My question is: Is this an inevitability that I just have to live with? Will I always be able to out-pace the low-powered Raspberry Pi by hitting it with too many inputs in close succession or is there some faster way of doing this? Can I push the database write operation to another thread or something along those lines? Forgive my ignorance, I am learning. </p>

<p>You appear to be opening and closing the database each and every time. That will clearly add a huge overhead, especially as you are "hammering" away at it.<br>
Connect to the database once at the beginning and close it upon exit.<br>
In between, simply perform your <code>insert</code>, <code>update</code> and <code>delete</code> statements. </p>

<p>Edit:<br>
For the purposes of this I renamed <code>db.py</code> to be called <code>barcode1.py</code> so edit appropriately.
Alter <code>listen.py</code> to be as follows:    </p>

<pre><code>#!/usr/bin/env python

import logging
import barcode1
DB_FILE_NAME = "scan-queue.db"
my_db = barcode1.sqlite3.connect(DB_FILE_NAME)
my_cursor = my_db.cursor()

def InsertScan(isbn, shop_id):
    insert = "INSERT INTO scans ( isbn, shop_id ) VALUES ( ?, ? )"
    my_cursor.execute(insert, [isbn, shop_id])
    my_db.commit()

while True:
    barcode = raw_input("Scan ISBN: ")
    if ( len(barcode) &gt; 1 ):
        logging.info("Recording scanned ISBN: " + barcode)
        print "Recording scanned ISBN: " + barcode
        InsertScan(barcode, 1)
my_db.close()
</code></pre>

<p>For your purposes replace references to "barcode1" with "db"<br>
As you can see all that happens here is that a separate function has been added to do the writing and only the writing.<br>
Clearly this is a quick mock up and could be improved immeasurably, in fact I'd rewrite it as a single script. This is one of those classic examples where in an attempt to write object oriented code, you end up shooting yourself in the foot.<br>
In fact you could do without the function and just include the <code>insert</code> code within the <code>while</code> statement.</p>

<p>Locking:
from the sqlite3 documents:</p>

<pre><code> sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])
</code></pre>

<p>Opens a connection to the SQLite database file database. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk.</p>

<p>When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).</p>

python python-2.7 sqlite raspberry-pi