kjubus kjubus - 1 year ago 69
SQL Question

Python and SQL - inserting and reading values

I have a problem with my code. I'm making a windowed application. Right now, I need to insert data to table in database and then show it to me (with SELECT). I have 2 fields to input the Name and Password for user (ID is calculated based on number of entries in the table) and a button to trigger the input. Then I have a field where I would like to have something like confirmation to be displayed after insert, and a button to trigger "SELECT" from the table. The application does start, but as far as I can tell, it doesn't add that to the table (and doesn't display anything as a confirmation). Here is my code:

from tkinter import *
import sqlite3 as lite
import sys
from Crypto.Cipher import AES

con = None
con = lite.connect('test.db')
cur = con.cursor()

class Application(Frame):
def __init__(self, master):
super(Application, self).__init__(master)

def create_widgets(self):
self.inst_lbl = Label(self, text = "Dodaj uzytkownika")
self.inst_lbl.grid(row = 0, column = 0, columnspan = 2, sticky = W)

self.pwa = Label(self, text = "Login")
self.pwa.grid(row = 1, column = 0, sticky = W)

self.pwb = Label(self, text = "Haslo")
self.pwb.grid(row = 2, column = 0, sticky = W)

self.a = Entry(self)
self.a.grid(row = 1, column = 1, sticky = W)
self.b = Entry(self)
self.b.grid(row = 2, column = 1, sticky = W)

self.submit_bttn = Button(self, text = "Dodaj uzytkownika", command = self.dodaj)
self.submit_bttn.grid(row = 3, column = 0, sticky = W)

self.secret_txt = Text(self, width = 35, height = 5, wrap = WORD)
self.secret_txt.grid(row = 4, column = 0, columnspan = 2, sticky = W)

self.view_users = Button(self, text = "Wyswietl uzytkownikow", command = self.wyswietl)
self.view_users.grid(row = 10, column = 0, sticky = W)

self.users_window = Text(self, width = 35, height = 5, wrap = WORD)
self.users_window.grid(row = 11, column = 0, columnspan = 2, sticky = W)

def dodaj(self):
a = self.a.get()
b = self.b.get()
index = cur.execute("SELECT COUNT(Id) FROM users")
cur.execute("INSERT INTO users (Id, Nazwa, Haslo) VALUES(?,?,?)", (int(index),str(a),str(b)))
#obj = AES.new('This is a key123', AES.MODE_CBC, 'This is an IV456')
#ciphertext = obj.encrypt(test)
self.secret_txt.delete(0.0, END)
self.secret_txt.insert(0.0, (index, a, b))
def wyswietl(self):
viewall = cur.execute("SELECT * FROM users")
self.users_window.delete(0.0, END)
self.users_window.insert(0.0, viewall)

root = Tk()

app = Application(root)


And when I try to view the contents of the table, I get the error in that field:

sqlite3.Cursor object at 0x02A6E220

Also, the code runs, but I get this error when running it:

*Exception in Tkinter callback

Traceback (most recent call last):

File "C:\Users\kjubus\AppData\Local\Programs\Python\Python35-32\lib\tkinter__init__.py", line 1549, in call
return self.func(args)

File "C:\Users\kjubus\Dysk Google!Szkoła\MGR\Projekt inzynierski\inzynierka v0.1.py", line 52, in dodaj

cur.execute("INSERT INTO users (Id, Nazwa,Haslo) VALUES(?,?,?)", (int(index),str(a),str(b)))

TypeError: int() argument must be a string, a bytes-like object or a number, not 'sqlite3.Cursor'

and just to clarify - in this part "line 1549" call before and after call there are 2 underscores.

Answer Source

__call__ is an internal method used to call objects and functions. You can probably ignore that.

A cursor's execute method normally returns some innocuous value like the number of rows affected, and the results are returned by a separate call to one of its fetch* methods. So you probably need something like

    cur.execute("SELECT COUNT(Id) FROM users")
    index = cur.fetchone()[0] # First (and only) element in the single row
    cur.execute("INSERT INTO users (Id, Nazwa, Haslo) VALUES(?,?,?)", (int(index),str(a),str(b)))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download