0

So, I have a very simple application that receives a request via a rest API, writes a record to a database using SQLite and then sends a rest API call to another server on the internet. When I send the rest API call to the server on the internet, that server does some network processing and then sends a response back to me which I then update the record.

The problem I'm having is that the process of sending the API call to the server and getting the response back is completed before the initial write occurs to SQLite. Therefore, when I try to update the database it doesn't exist. On the insert of the data, I do a commit, but does anyone know why the write isn't as instantaneous as I would assume?

My code is below to do the insert: Yes the insert works

def insert_into_database(dbname,table,**kwargs):
    conn = sqlite3.connect(dbname)

    #generate a unique ID
    id=get_a_uuid()
    #generate the current date for timestamps
    d=datetime.datetime.now()

    fieldnames,values = create_command_line(kwargs)

    insert="INSERT INTO "+table+" (ID,DATE,"+fieldnames+") VALUES ('"+id+"','"+str(d)+"',"+values+")"
    print (insert)
    try:
        conn.execute(insert)
        conn.commit()
    except (sqlite3.Error) as e:
        print(e)
        return(False,str(e))
    conn.close()
    return(True,id)
ChrisBogDog
  • 231
  • 1
  • 2
  • 6
  • 2
    Post the code which inserts the initial record and confirm that it does not have any errors and works correctly on its own without calling the 2nd API. – takendarkk Jan 07 '19 at 17:12
  • For the record, SQLite isn't a full RDBMS, it's a flat-file supported by a library that *behaves* like a RDBMS. You're largely at the mercy of your own physical disk to determine when data gets flushed...but in all honesty it also depends on how quickly you're handling this transaction (this *is* in a transactional block, right?). Show us some code which exhibits the same issue for your use case. It *might* be the case that because your REST calls don't happen in the same transaction which would indicate that there's no guarantee of if your data is in the database. – Makoto Jan 07 '19 at 17:23
  • This is the code and yes, the insert works correctly: – ChrisBogDog Jan 07 '19 at 18:35
  • did you see the [FAQ](https://sqlite.org/faq.html#q5) about this and maybe this [related exchange](https://stackoverflow.com/q/1063438/1358308)? – Sam Mason Jan 07 '19 at 18:42
  • (Well that SQL statement has 'SQLite injection vulnerability' concatenated all through it...) Okay, so now in context with your API calls, when is the data committed to the database, and when it is retrieved? – Makoto Jan 07 '19 at 18:43

1 Answers1

0

Get another db. If you want to use a database for persistence never use sqlite. If you need an in memory database then go for it if you want to save data for later use pick another database.

NoWord
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Blue Robin Mar 16 '23 at 04:44