sqlite3.OperationalError: database is locked

[wanghaisheng]

    db = itemdb.ItemDB(dbname)
    db.ensure_table("all","!videoid","status")

s_thread_fastlane
    db.ensure_table("all","!videoid","status")  
  File "D:\Program Files\anaconda3\lib\site-packages\itemdb.py", line 211, in ensure_table      
    self._ensure_table_helper1(table_name, indices, missing_indices)
  File "D:\Program Files\anaconda3\lib\site-packages\itemdb.py", line 217, in _ensure_table_helper1
    self._ensure_table_helper2(table_name, indices)
  File "D:\Program Files\anaconda3\lib\site-packages\itemdb.py", line 251, in _ensure_table_helper2
    cur.execute(text)
sqlite3.OperationalError: near "all": syntax error

all seem to be keyword change to others fixed that

[wanghaisheng]

@almarklein

    dbname = os.path.join(os.getcwd()+os.sep+'data'+os.sep, "fastlaneall.db")

    # Open the database and make sure there is a table with appopriate indices
    db = itemdb.ItemDB(dbname)
    db.ensure_table("fastlaneall","!videoid","status")    
    print('\n==============', f)
    # logfile,len_list =process()
    if not  db.select_one("fastlaneall", "videoid ==?",
            videoid).get("scenepath"):
        logfile,posttext,title = convert_fastlanepost2script(f)

        with db:
            db.put_one("fastlaneall", videoid=videoid, status=0,scenepath=logfile,post_text=posttext,title=title
            ) 
        db.close()

[wanghaisheng]

is there any wrong with this code @almarklein

    with db:

        if  db.count(dbname, "videoid ==?",
                            videoid)==1:
            logfile = db.select_one(dbname, "videoid ==?",
                                    videoid).get("scenepath")
            post_text = db.select_one(dbname, "videoid ==?",
                                    videoid).get("post_text")
            title = db.select_one(dbname, "videoid ==?",
                                videoid).get("title")
            if  logfile and logfile.endswith('.json') and  post_text and  title:
                pass
            else:            
                print('startign convert json to scene text',f)
                logfile, post_text, title = converr(f)
                nowtime = time.time()
                update_time = int(nowtime)
                olddata = db.select_one(dbname, "videoid ==?", videoid)
                olddata["scenepath"] = logfile
                olddata["post_text"] = post_text
                olddata["title"] = title
                olddata["update_time"] = update_time

                db.put(dbname, olddata)
                print('convert json to scene,video info  update', videoid, filename)

            raise RuntimeError()

[almarklein]

Hi @wanghaisheng, that looks good at first glance, but I don't fully understand the problem, do you get an error? If it is possible to create a small example that I can run as well, that will be great, because then I can reproduce the error, and better see what's going on.

[wanghaisheng]

sqlite3.OperationalError: database is locked continue say this i notice when this happens, there is journal file

[almarklein]

Ok, I found out that db.ensure_table("all","!videoid","status") raises a syntaxerror because sqlite gets confused about the table name "all". Similarly, "table" produces an error. I created #6 for this.

I also found that running that example twice in an interactive session locks the db as well, because you create a new db object, and the old db object may be holding on to the lock.

That said, I'm not sure if this is what you are experiencing. If you can provide a complete piece of code that I can run myself, then its easier to try it out.

[wanghaisheng]

exactly what you say what do you mean by a new db object? should i initialize db through path globally?

[almarklein]

I meant in an interactive session (like in an IDE or in a Jupyer notebook). But nevermind, what's more valuable if we have some code that we can both run, and examine to see what the issue is.

[wanghaisheng]

I kindof fix that only i need is to define them at the beginning so all method share this variable for my case i define two path for 2 database ,and call them through name matching