For loop with sqlite always inserting first value

Question

I am making a voting application and it allows the user to type all candidates with commas between each and hit enter to create a vote with those candidates.

def newVote(event):
                pos = position.get()
                candidates = addCandEntry.get()
                formatted = [x.strip() for x in candidates.split(',')]
                for x in formatted:
                    c.execute("INSERT INTO current VALUES(?, ?, ?)", (pos,x,0,))
                    c.execute('SELECT * FROM current WHERE position="Chief"')
                    print(c.fetchone())

If I were to pass the following values into the tkinter entry for position dad,mom,son,sister,grandma It would give this output in the sql

('Chief', 'dad', 0)
('Chief', 'dad', 0)
('Chief', 'dad', 0)
('Chief', 'dad', 0)
('Chief', 'dad', 0)

How can I make it add each value instead of just the first one?

Here is the full code if you need it

import tkinter
from tkinter import *
import sqlite3
import datetime


# testing purposes only
password = 'test'

# create database file
conn = sqlite3.connect('firefight')
c = conn.cursor()

# create tables
c.execute('''CREATE TABLE IF NOT EXISTS users(
          username text, password text, admin boolean)''')
c.execute('''CREATE TABLE IF NOT EXISTS positions(
          position text)''')
c.execute('''CREATE TABLE IF NOT EXISTS current(
          position text, candidate text, votes int)''')
c.execute('''CREATE TABLE IF NOT EXISTS past(
          date DATE, position text, candidate text, votes int, winner boolean)''')

c.execute("INSERT INTO users VALUES('admin', 'VoteProgram', 'yes')")


'''
tables:
users:
    username text
    password text
    admin boolean
positions:
    position text
current:
    position text
    candidate text
    votes int
past:
    position text
    candidate text
    votes int
    winner boolean
'''

# define root window
root = tkinter.Tk()
root.minsize(width=800, height = 600)
root.maxsize(width=800, height = 600)

# Admin sign in Label
areAdmin = Label(root, text="Administrator sign in", font=("Arial", 18))
areAdmin.pack()

# password label and password
passwordLabel = Label(root, text="Password: ", font=("Arial", 12))
passwordLabel.place(x=300, y=30)

# password entry
adminPasswordEntry = Entry(root)
adminPasswordEntry.place(x=385, y=32.5)

# function for button
def getEnteredPassword(event):
    enteredPassword = adminPasswordEntry.get()
    if enteredPassword == password:
        # define admin window
        admin = tkinter.Toplevel()
        admin.minsize(width=800, height = 600)
        admin.maxsize(width=800, height = 600)
        # label saying to change password
        changePasswordLabel = Label(admin, text="It is recommended to change your password the first time you log in.",
                                    font=("Arial", 16), wraplength=500)
        changePasswordLabel.pack()
        # old password
        changePassword_OldPasswordLabel = Label(admin, text="Old Password: ", font=("Arial", 12))
        changePassword_OldPasswordLabel.place(x=250, y=50)
        changePassword_OldPassword = Entry(admin)
        changePassword_OldPassword.place(x=365, y=52.5)
        # new password
        changePassword_NewPasswordLabel = Label(admin, text="New Password: ", font=("Arial", 12))
        changePassword_NewPasswordLabel.place(x=250, y=70)
        changePassword_NewPassword = Entry(admin)
        changePassword_NewPassword.place(x=365, y=72.5)

        # function to change password
        def passwordChangeCommand(event):
            global password
            oldPasswordValue = changePassword_OldPassword.get()
            newPasswordValue = changePassword_NewPassword.get()
            if oldPasswordValue == password:
                password = newPasswordValue
            else:
                wrong = tkinter.Toplevel()
                wrong.minsize(width=200, height = 100)
                wrong.maxsize(width=200, height = 100)
                Label(wrong, text="Sorry that password is incorrect!", font=("Arial", 24), anchor=W, wraplength=180,
                      fg="red").pack()

        # submit button
        newPasswordSubmit = Button(admin, text="Submit", width=10, command=passwordChangeCommand)
        newPasswordSubmit.place(x=350, y=100)

        newVoteLabel = Label(admin, text="Create New Vote", font=("Arial", 16))
        newVoteLabel.place(x=310, y=135)

        # positions drop down
        newVoteChoosePositionLabel = Label(admin, text="Choose the position you are voting for", font=("Arial", 12))
        newVoteChoosePositionLabel.place(x=265, y=170)
        position = StringVar(admin)
        position.set("Chief")
        newVoteOption = OptionMenu(admin, position, "Chief", "First Lieutenant", "Second Lieutenant", "Third Lieutenant",
                                   "Fourth Lieutenant")
        newVoteOption.place(x=355, y=195)

        #add candidates
        addCandLabel = Label(admin, text="Add candidates below, separate each candidate with a comma then press enter when all candidates have been entered"
                             , font=("Arial", 11), wraplength=300, anchor=W)
        addCandLabel.place(x=255, y=235)
        addCandEntry = Entry(admin, width=40)
        addCandEntry.place(x=272, y=295)

        #new vote
        newVoteButton = Button(admin, text="Create Vote", width=15)
        newVoteButton.place(x=335, y=325)

        def newVote(event):
            pos = position.get()
            candidates = addCandEntry.get()
            formatted = [x.strip() for x in candidates.split(',')]
            for x in formatted:
                c.execute("INSERT INTO current VALUES(?, ?, ?)", (pos,x,0,))
                c.execute('SELECT * FROM current WHERE position="Chief"')
                print(c.fetchone())


        addCandEntry.bind("<Return>", newVote)
        newVoteButton.bind("<Button-1>", newVote)

    else:
        wrong = tkinter.Toplevel()
        wrong.minsize(width=200, height=100)
        wrong.maxsize(width=200, height=100)
        Label(wrong, text="Sorry that password is incorrect!", font=("Arial", 24), anchor=W, wraplength=180,
              fg="red").pack()

# enter button for password
passwordEnterButton = Button(root, text="Enter", width=10)
passwordEnterButton.place(x=360, y=60)
passwordEnterButton.bind("<Button-1>", getEnteredPassword)
root.bind("<Return>", getEnteredPassword)

mainloop()

I don't know python very much, but what is ´addCandEntry.get()´ returning? — gian1200
– gian1200, Commented Dec 1, 2016 at 2:42
print out formatted before the insert loop. Is it a list of the same thing 5 times? — Paul Rooney
– Paul Rooney, Commented Dec 1, 2016 at 2:47
Instead of adding the full code, please take the time to create a Minimal, Complete and Verifiable Example. For example, is the password code really part of the problem? Is setting the minsize and maxsize of the window really part of the problem? — Bryan Oakley
– Bryan Oakley, Commented Dec 1, 2016 at 14:18

Parfait · Accepted Answer · 2016-12-01 05:15:43Z

Because you are using fetchone() you will always return the first row ('Chief', 'dad', 0). Regardless of the fetch though, the five separate records for dad, mom, son, sister, grandma should have been appended to table. Consider the adjustment where you commit each insert and then output each row of current in a separate loop:

for x in formatted:
    c.execute("INSERT INTO current VALUES(?, ?, ?)", (pos,x,0,))

for row in c.execute('SELECT * FROM current WHERE position="Chief"'):
    print(row)

However, it seems like you intended to retrieve last row after each insertion which is possible if you use an integer primary key, id that autoincrements with each record:

c.execute('''CREATE TABLE IF NOT EXISTS users(
          id integer primary key, username text, password text, admin boolean)''')
c.execute('''CREATE TABLE IF NOT EXISTS positions(
          id integer primary key, position text)''')
c.execute('''CREATE TABLE IF NOT EXISTS current(
          id integer primary key, position text, candidate text, votes int)''')
c.execute('''CREATE TABLE IF NOT EXISTS past(
          id integer primary key, date DATE, position text, candidate text, 
          votes int, winner boolean)''')

which forces you to list out the insert columns:

c.execute("INSERT INTO users (username, password, admin)" +
          " VALUES('admin', 'VoteProgram', 'yes')")

and then the fetchone() can be used:

for x in formatted:
    c.execute("INSERT INTO current (position, candidate, votes) VALUES(?, ?, ?)", (pos,x,0,))
    c.execute('SELECT * FROM current WHERE id = (SELECT Max(sub.id) FROM current sub)')
    print(c.fetchone())

Collectives™ on Stack Overflow

For loop with sqlite always inserting first value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related