SQL Update Command in Python cannot find column and database gets locked

Question

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!

my database is locked ... what is your actual SQL database? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 15, 2021 at 2:12
sqlite3. I think it is getting locked because the process takes too long based on stackoverflow.com/questions/3172929/…. I'm more unsure why the code above is problematic — Tucker
– Tucker, Commented Feb 15, 2021 at 2:42
Yes, name is a key column. My table has two columns (name and score) with about 70 rows — Tucker
– Tucker, Commented Feb 15, 2021 at 3:08
This is similar to stackoverflow.com/questions/2740806/…. Please check. — Teja Goud Kandula
– Teja Goud Kandula, Commented Feb 15, 2021 at 4:05

Teja Goud Kandula · Accepted Answer · 2021-02-15 02:27:46Z

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.

answered Feb 15, 2021 at 2:27

Teja Goud Kandula

1,5841 gold badge18 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tucker Over a year ago

I don't think that this is the issue based on stackoverflow.com/questions/3172929/…. 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

Teja Goud Kandula Over a year ago

Run the same update command on the database directly and see if that is working fine or that is also throwing an error.

Tucker Over a year ago

Successfully works when I run UPDATE scores SET score = 505 WHERE name = "Swag"

Teja Goud Kandula Over a year ago

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);

Tucker Over a year ago

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 · Accepted Answer · 2021-02-15 04:25:32Z

0

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

answered Feb 15, 2021 at 4:25

Tucker

577 bronze badges

Collectives™ on Stack Overflow

SQL Update Command in Python cannot find column and database gets locked

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related