Sqlite 3 Database is not updating after using commit()

Sqlite 3 Database is not updating after using commit() - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Sqlite 3 Database is not updating after using commit() (/thread-30386.html)

Sqlite 3 Database is not updating after using commit() - JellyCreeper6 - Oct-19-2020

Any help is appreciated.

Table name - Encounters

name   |encounters|PreEnc|PostEnc|
'Name1'|0         |20    |0      |

the values for encounters, PreEnc, PostEnc are default to 0.

Now I want to update the PostEnc with a number.

  import sqlite3

  conn = sqlite3.connect('Encounters.db')
  c = conn.cursor()
   
  c.execute("UPDATE Encounters SET PostEnc=? WHERE name=?", (sumlst,user))
  conn.commit()
  for row in c.execute("SELECT PostEnc FROM Encounters WHERE name = ?", [user]):
    print(row)
  for row in c.execute("SELECT PreEnc FROM Encounters WHERE name = ?", [user]):
    print(row)

sumlst = 12345
user = 'Name'
But PostEnc does not get updated and I get nothing with the print(row)

RE: Sqlite 3 Database is not updating after using commit() - buran - Oct-19-2020

it looks like your table is empty. Do you have data in it?

RE: Sqlite 3 Database is not updating after using commit() - JellyCreeper6 - Oct-19-2020

(Oct-19-2020, 07:29 AM)buran Wrote: it looks like your table is empty. Do you have data in it?

Yes, I do. When doing this, name and PreEnc is filled. I want to fill PostEnc.

RE: Sqlite 3 Database is not updating after using commit() - Gribouillis - Oct-19-2020

It seems that the post contains only a small part of your code which makes it impossible to reproduce the bug. I guess the error is in the missing part. Can you write a small complete script that we can try with the samme behavior?

RE: Sqlite 3 Database is not updating after using commit() - buran - Oct-19-2020

There is something with your data. Otherwise your code works for me

import sqlite3

setup_sql = """BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Encounters" (
	"name"	TEXT,
	"encounters"	INTEGER,
	"PreEnc"	INTEGER,
	"PostEnc"	INTEGER
);
INSERT INTO "Encounters" ("name","encounters","PreEnc","PostEnc") VALUES ('John Doe',0,20,0);
COMMIT;"""

sumlst = 12345
user = 'John Doe'

conn = sqlite3.connect('Encounters_new.db')
c = conn.cursor()
c.executescript(setup_sql)
for row in c.execute("SELECT * FROM Encounters WHERE name = ?", [user]):
  print(row)   
c.execute("UPDATE Encounters SET PostEnc=? WHERE name=?", (sumlst, user))
conn.commit()
for row in c.execute("SELECT PostEnc FROM Encounters WHERE name = ?", [user]):
  print(row)
for row in c.execute("SELECT PreEnc FROM Encounters WHERE name = ?", [user]):
  print(row)
for row in c.execute("SELECT * FROM Encounters WHERE name = ?", [user]):
  print(row)

Output:('John Doe', 0, 20, 0)
(12345,)
(20,)
('John Doe', 0, 20, 12345)

RE: Sqlite 3 Database is not updating after using commit() - JellyCreeper6 - Oct-19-2020

I ran the same lines of code on a seperate file and the same thing happens for me. It must be the database... Do you have any idea what it could be. I don't see anything wrong with it[Database].