Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

So I know this problem is not new in flask, and people have already asked it before. However I am still facing a problem while executing my database commands in bash as I am new to python. This is what i did

import sqlite3
conn = sqlite.connect('/home/pjbardolia/mysite/tweet_count.db')
c = conn.cursor()
c.execute("create table count_twitter (count_id integer primary key autoincrement ,count_present integer not null,last_tweet not null)")
c.execute(insert into count_twitter values('',10,10))
however after executing insert statement I am getting operational error: database is locked.
Can someone tellme in simple terms what does this error means? and how to solve it.
Thanks in advance
                Just to check but is the last line in your code actually: c.execute("insert into count_twitter values('',10,10)")?  The  SQLite doc page for that error gives plenty of guidance. One thing to watch in Flask is going from dev mode (using "app.run()") to production mode (in a WSGI server) you are moving from single to multiple threads which might cause problems with SQLite and require extra concurrency handling.
– allen-smithee
                Nov 11, 2014 at 11:31
  
SQLite is meant to be a lightweight database, and thus can't support a
  high level of concurrency. OperationalError: database is locked errors
  indicate that your application is experiencing more concurrency than
  sqlite can handle in default configuration. This error means that one
  thread or process has an exclusive lock on the database connection and
  another thread timed out waiting for the lock the be released.
  
Python's SQLite wrapper has a default timeout value that determines
  how long the second thread is allowed to wait on the lock before it
  times out and raises the OperationalError: database is locked error.
  
If you're getting this error, you can solve it by:
  
Switching to another database backend. At a certain point SQLite
  becomes too "lite" for real-world applications, and these sorts of
  concurrency errors indicate you've reached that point.
  
Rewriting your code to reduce concurrency and ensure that database
  transactions are short-lived.
  
Increase the default timeout value by setting the timeout database
  option.
Probably you have another connection in your code that is not closed or not committed and this cause this error. Basically trying to do second execute when it is already locked by the another one. If you really want to have your concurrent transactions you need to have a RDBMS.
                I would like to add that there are performance optimizations that you can look into in these scenarios and they may satisfy your needs. As @PirateApp mentioned there is journal mode=WAL. Please consult here:  sqlite.org/wal.html  And here:  phiresky.github.io/blog/2020/sqlite-performance-tuning
– wgwz
                Jul 28, 2021 at 21:54
Here's what I used to manage concurrency.  I was hitting one DB from 270 processes.  Just increasing SQLite's timeout didn't help, but this approach where you just wait without attempting to connect for a while seemed to work.  The number of attempts (50) and wait period (10-30 seconds) could be adjusted.  I was collecting results from long running analyses, so 10-30 seconds was fine, but maybe 1-3 would have worked.
import random
import sqlite3
def do_query(path, q, args=None, commit=False):
    do_query - Run a SQLite query, waiting for DB in necessary
    Args:
        path (str): path to DB file
        q (str): SQL query
        args (list): values for `?` placeholders in q
        commit (bool): whether or not to commit after running query
    Returns:
        list of lists: fetchall() for the query
    if args is None:
        args = []
    for attempt in range(50):
            con = sqlite3.connect(path)
            cur = con.cursor()
            cur.execute(q, args)
            ans = cur.fetchall()
            if commit:
                con.commit()
            cur.close()
            con.close()
            del cur
            del con
            return ans
        except sqlite3.OperationalError:
            time.sleep(random.randint(10, 30))
I deleted a row from the DBBrowser for sqlite GUI tool then I posted the form data and I got the same error.
I closely look at the GUI and there is "Write Changes" tab which was active, means after i delete, i had to click on this. I click on that and problem was solved. sqllite3 is light but not that light
I had the same issue when I was using threads in my flask app, I tried almost everything (Checking if the Syntax of ORM command is right etc.), but all of these tries didn't solve my issue.
The last thing I did was to change the database from SQLite3 to PostgreSQL, now everything works fine.
                This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
– IgorZ
                Jan 16, 2022 at 16:10
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.