I am running a Python based system on an Android phone. The database is in Sqlite3.  It has been running well for approximately five years now, with various upgraded phones, changes to the system, etc.

However, in the last week or so, there has been a problem in the system that is very peculiar.  One of the tables on the system is called "Invoices" and it currently has approximately 21,500 records in it.  However, reading the database from Python on the phone, using the sqlite3 module, will suddenly return only around 2,400 records.  If I copy the database over to a PC and open it in Python via Linux, the same thing will happen.  If I then open it with the command line `sqlite3` tool, the table will read correctly.  After that, it will start working correctly in Python as well.  If I copy the same database back to the phone, it will work correctly there as well - for approximately three or four hours (i.e., given the usual frequency of my program, about 90 - 120 reads / writes).  Then the problem will return.

I have changed phones in case this was a problem in the phone's memory, but that didn't help.  I have run `vacuum` on the sqlite3 database in question as well, to no avail.  There do not appear to be any other obvious errors in the database. 

What could be the reason for this behaviour?  

Below I've posted some of the code that I use to read the database.  Have cut out some extraneous stuff so you may see variables that are not defined etc. But I'm fairly sure it's not the code, as this same code has been running for years with no trouble.


    def sqlite_exec(sqlcommand, dbname, inserttable = "", insertstuff = None, returndict = 0, override_stop = False, returncheck = False, nojournal = False, onlyjournal = False):
        #...
        if sqlcommand == "insert":
            # Substitute single quotes with double quotes in input text to avoid sqlite syntax errors
            actual_command = "INSERT INTO {0} ({1}) VALUES ({2});".format(inserttable, ", ".join(insertstuff.keys()), ", ".join(["'" + re.sub("'",'"',valuetext) + "'" for valuetext in insertstuff.values()]))
        else:
            actual_command = sqlcommand
        conn = sqlite3.connect(dbname,timeout = 40.0,isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES)
        if returndict:
            # Using the sqlite module documentation example; this happens to be better suited for our purposes than the sqlite.Row object
            def dict_factory(cursor, row):
                d = dict((col[0],row[idx]) for idx,col in enumerate(cursor.description))
                return d
            conn.row_factory = dict_factory
        sqliteobj = conn.cursor()
        # ...
        if not onlyjournal:
            try:
                sqliteobj.execute(actual_command)
            # except...
        return sqliteobj