[FIXED] Cannot figure out why value in database is not updating (python, aiogram, SQLite)

Issue

I have an integer column hasInteracted in the Userdata table. When I try

 cursor.execute("UPDATE Userdata SET hasInteracted = 1 WHERE chatid = ?", (chatid,))

The value is not updating. I am new to all this, is it something obvious I missed?

Entire code:

import time
import logging
import sqlite3
from aiogram import Bot, Dispatcher, executor, types
from aiogram.types import InlineKeyboardMarkup, InlineKeyboardButton 

TOKEN = "" #deleted for the question


connection = sqlite3.connect("database.db")
cursor = connection.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS Userdata (
chatid INTEGER PRIMARY KEY UNIQUE ON CONFLICT IGNORE,
hasInteracted INTEGER
)
''')
connection.commit()
#cursor.execute('CREATE INDEX idxChatid ON Users (chatid)')
#connection.commit()
#connection.close()

bot = Bot(token = TOKEN)
dp = Dispatcher(bot = bot)
chatid = 0
startbtn = InlineKeyboardButton(text = "start", callback_data = "startbtn")
btn2 = InlineKeyboardButton(text = "example decision", callback_data = "btn2")
startMessageKb = InlineKeyboardMarkup().add(startbtn)

async def startConsoleMessage(dp: Dispatcher):
     print("start")
async def secondMessage(message: types.Message):
     await message.answer(text = "make a decision", reply_markup = InlineKeyboardMarkup().add(btn2))

@dp.message_handler(commands = ['start'])
async def startMessageInline(message: types.Message):
     await message.answer(text = "do you wish to start?", reply_markup = startMessageKb)
     chatid = message.from_user.id
     cursor.execute("INSERT OR REPLACE INTO Userdata (chatid) VALUES (?)", (chatid,))
     connection.commit()

@dp.callback_query_handler(text = ["startbtn", "btn2"])
async def keyboardHandler(call: types.CallbackQuery):
    if call.data == "startbtn":
         await secondMessage(call.message)
    if call.data == "btn2":
         global chatid
         await call.message.answer("decision made")
         cursor.execute("UPDATE Userdata SET hasInteracted = 1 WHERE chatid = ?", (chatid,))
         connection.commit()
         connection.close()

if __name__ == '__main__':
    executor.start_polling(dp, skip_updates = True, on_startup = startConsoleMessage)

I tried updating it manually through the SQLite DB browser. When I ran the code, it updated to Null again. Tried using the

cursor.execute("UPDATE Userdata SET hasInteracted = ? WHERE chatid = ?", (1, chatid))

at first but the result was the same. What I need is simply for hasInteracted to change to 1

Solution

Seems I just missed global chatid before assigning the message.from_user.id so it didn't update properly

Answered By - Ven