0

I'm trying to increase an integer in a SQL database by one, but I get an error that my database is locked when running the code below:

cursor.execute("""SELECT score FROM scores WHERE name=?""", (user,)) # works
new_score = cursor.fetchone()[0] + 1 # works

params = (new_score, user) 
cursor.execute("""UPDATE scores SET score=? WHERE name=?""", params) # error here
  • new_score is a variable type int which is increased by one
  • user is a string that is under the "name" column in the database
  • scores is the table name
  • score is a column name in the data base

Thanks for any help!

Tucker
  • 57
  • 7

2 Answers2

0

If your database is showing it as locked check the running process at that instance and if your process is being blocked by some other process or your process is blocking some other process and unable to find the resources to execute the query.

Generally the solution for dead lock would be to kill any one of those queries. The query that needs to be killed is chosen based on the priority. Based on the database that you are using you can find the respective queries for finding the processes that running at any given point in time.

If you are running the query on MS SQL Server then use the command EXEC sp_who this will show the processes list running. Then use the command EXEC KILL spid_of_the_query to kill the query. Find the queries that are running at that the instance during which you are running this update query.

Teja Goud Kandula
  • 1,462
  • 13
  • 26
  • I don't think that this is the issue based on https://stackoverflow.com/questions/3172929/operationalerror-database-is-locked/39449185. I'm mostly unsure why the code labeled # error here is problematic. Thanks for your answer though, and let me know if I'm wrong still – Tucker Feb 15 '21 at 02:43
  • Run the same update command on the database directly and see if that is working fine or that is also throwing an error. – Teja Goud Kandula Feb 15 '21 at 03:41
  • Successfully works when I run UPDATE scores SET score = 505 WHERE name = "Swag" – Tucker Feb 15 '21 at 03:44
  • Try rebuild using command that you are using inside the execute. ```cursor.execute("""UPDATE scores SET score=? WHERE name=?""", params) ``` change this as follows. ```update_str = 'UPDATE scores SET score=' + str(new_score) + 'WHERE name=' + user``` Then run the command as follows. ```cursor.execute(update_str);``` – Teja Goud Kandula Feb 15 '21 at 03:47
  • Database is locked when changing to ``update_str = 'UPDATE scores SET score=' + str(new_score) + ' WHERE name=' + "'" + user + "'"``. When it is just ``update_str = 'UPDATE scores SET score=' + str(new_score) + ' WHERE name=' + user``, I get error "sqlite3.OperationalError: no such column: Swag" (Swag being the string for *user*) – Tucker Feb 15 '21 at 03:56
  • ```update_str = 'UPDATE scores SET score=' + str(new_score) + ' WHERE name="' + user +'"' ``` Use this. – Teja Goud Kandula Feb 15 '21 at 04:00
  • Still database is locked – Tucker Feb 15 '21 at 04:02
0

Turns out that I never closed my connection in the SQLite Browser that I was using. Thanks for all the responses!

Tucker
  • 57
  • 7