0

This is the simple code I want to execute. I want the elements of tkinter combobox come from sqlite3 database it does the job but It appends the first row and first column values many times(no of rows in database) . I want it to append the first column of each rows of databse.

con_caster = sqlite3.connect('caster.db')
con_sec = sqlite3.connect('section.db')
cur_caster = con_caster.cursor()
con_sec = con_sec.cursor()
#cur_caster.execute("DROP TABLE IF EXISTS autos;")  # use this line only if you want to overwrite existing table
cur_caster.execute("CREATE TABLE IF NOT EXISTS caster ('casterid' TEXT, 'casterradius' NUMBER);")
cur_caster = con_caster.cursor()
query = cur_caster.execute('SELECT casterid FROM caster')
caster_data = []
for row in cur_caster.fetchall():
    caster_data.append(row[0])
combo['values'] = caster_data
con_caster.commit()

And this is how I have created the rows in database

def add_to_database():
    i=0
    # add details to database
    caster_data.append((add_cid.get()))
    cur_caster.execute("INSERT INTO caster VALUES (?, ?);", (caster_data[i],caster_data[i] ))
    con_caster.commit()
    combo['values'] = caster_data
    i+=1

When I delete the database file and run the app it shows picture showing no data When I add data, the combobox looks like after addition of data After closing and opening app again he combobox looks like the second combobox elemant is replaced by first And the database also changes same as tyhe combobox.

Looking for the help in this. Thank you so much in advance

Prashant Priyadarshi
  • 27
  • 5
  • There are utilities to look at the contents of an Sqlite database. You should use such a tool to check if the table contains the right data. – Michael Butscher Feb 16 '23 at 09:22
  • What is the output of `print(caster_data)` after the for loop? – acw1668 Feb 16 '23 at 09:27
  • @acw1668 it shows ```['e23e', 'e23e', 'e23e', 'e23e']``` which has repeated element from the database which has "e23e" as the first row and first column element . There must be some issue wih the for loop – Prashant Priyadarshi Feb 16 '23 at 10:31
  • It means that the table `caster` has 4 rows with same `casterid`. Since the column `casterid` is not a primary key, it can have duplicate value. – acw1668 Feb 16 '23 at 10:35
  • As the first comment said, you need to check the contents of the table using any sqlite tool. – acw1668 Feb 16 '23 at 10:55
  • @acw1668 I have checked And I came to know that there is problem with data. when I add the rows the dupluicate of first gets added to the database but the combobox works well. – Prashant Priyadarshi Feb 16 '23 at 11:04

1 Answers1

0

I finally removed the bug, There was problem with the data addition method. Every time I needed to add data ro database I used to add the first element instead of entry value which was caster_data[i]
The correct snippet is.

  def add_to_database():
      i=0
       
      caster_data.append((add_cid.get()))
      cur_caster.execute("INSERT INTO caster VALUES (?, ?);", (add_cid.get(),add_cr.get() ))
      con_caster.commit()
      combo['values'] = caster_data
      i+=1
Prashant Priyadarshi
  • 27
  • 5