Sqlite3 Operation Failed to execute fast many transactions

I get

sqlite3.OperationalError: SQL logic error or missing database

      

        
        
        
      

    

when i run the app i am working on. The following is a narrowed down but complete sample that shows the problem for me. This example uses two tables; one for storing users and one for recording whether the user's information is current in the external directory system. (As you can imagine, the tables in my real application are longer). The sample creates a group of random users and then goes through the list of (random) users and adds them to the second table.

#!/usr/bin/env python

import sqlite3
import random

def random_username():
    # Returns one of 10 000 four-letter placeholders for a username
    seq = 'abcdefghij'
    return random.choice(seq) + random.choice(seq) + \
           random.choice(seq) + random.choice(seq)

connection = sqlite3.connect("test.sqlite")

connection.execute('''CREATE TABLE IF NOT EXISTS "users" (
    "entry_id"              INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,
    "user_id"               INTEGER NOT NULL ,
    "obfuscated_name"       TEXT NOT NULL)''')
connection.execute('''CREATE TABLE IF NOT EXISTS "dir_x_user" (
    "user_id"               INTEGER PRIMARY KEY NOT NULL)''')        

# Create a bunch of random users
random.seed(0)  # get the same results every time
for i in xrange(1500):
    connection.execute('''INSERT INTO users
        (user_id, obfuscated_name) VALUES (?, ?)''',
        (i, random_username()))
connection.commit()

#random.seed()
for i in xrange(4000):
    username = random_username()
    result = connection.execute(
        'SELECT user_id FROM users WHERE obfuscated_name = ?',
        (username, ))
    row = result.fetchone()
    if row is not None:
        user_id = row[0]
        print "  %4d %s" % (user_id, username)
        connection.execute(
            'INSERT OR IGNORE INTO dir_x_user (user_id) VALUES(?)',
            (user_id, ))
    else:
        print "     ? %s" % username
    if i % 10 == 0:
        print "i = %s; committing" % i
        connection.commit()
connection.commit()

      

        
        
        
      

    

Of particular note is the line near the end, which reads:

if i % 10 == 0:

      

        
        
        
      

    

In a real application, I am requesting data from a network resource and I want to capture users from time to time. Changing this line is changed when an error occurs; it seems that when I commit, there is a non-zero chance of OperationalError. It looks like it has something to do with the data I'm putting into the database, but I can't figure out what the problem is.

Most of the time, if I read all the data and then commit only once, no error occurs. [Yes, there is an obvious work in there, but a hidden problem remains.]

Here is the end of a trial run on my computer:

     ? cgha
i = 530; committing
     ? gegh
     ? aabd
     ? efhe
     ? jhji
     ? hejd
     ? biei
     ? eiaa
     ? eiib
     ? bgbf
   759 bedd
i = 540; committing
Traceback (most recent call last):
  File "sqlitetest.py", line 46, in <module>
    connection.commit()
sqlite3.OperationalError: SQL logic error or missing database

      

        
        
        
      

    

I am using Mac OS X 10.5.8 with Python 2.5.1 and Sqlite3 3.4.0 embedded.