Inserting values to sqlite table using input

[Niloofar24]

Hello.

When we want to insert a vlue into a table in sqlite, we use this line, right?

cursor.execute('INSERT INTO colors Values ("red", "blue")')

As we can see above, we want to insert 2 words and we type them as when we are typing whole the codes.
But i want to get 2 words from input and insert them into the table in sqlite.
I'm using Entry box in Tkinter window and i want something like this:

ent = Entry()
ent.grid()

################

cursor.execute('INSERT INTO colors VALUES (ent.get())')

But it doesn't work and give me a syntaxt error.
What can i do?

[Gribouillis]

Open the documentation page of the sqlite3 module, then search all occurrences of INSERT INTO in this page. Your answer is there!

[Niloofar24]

I searched that page and read all 'insert into" parts, so this code must be correct but why it doesn't work?

word = 'happy'

####################

cursor.execute("INSERT INTO colors VALUES (?)", (word))

I also tried this one but i doesn't work:

cursor.execute("INSERT INTO colors VALUES (%s)" % 'happy')

[Niloofar24]

Here is the error i get:

    cursor.execute("INSERT INTO colors VALUES (?)", (word))
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.

[HiHe]

What does your colors table look like?
What does your traceback error message look like?

[Niloofar24]

The color table has only (colorname TEXT).

And i have sent the error in my last post.

note:
I used the value "happy", i wanted to type a color but i mixed it up with another file so used the word "happy" instead of a color like "red" for example. But it's not important, anyway with any word, i don't know how to insert it into a table.

[Gribouillis]

I think execute() expects a tuple such as (word,) instead of (word) which is the same as word without parentheses.

[HiHe]

Important, qmark style needs tuple or list:
cursor.execute("INSERT INTO colors (colorname) VALUES (?)", [word])

[Niloofar24]

Thank you @Gribouillis, it works.

And thank you @HiHe for your note.

Now here i have a def:

def show():
    import sqlite3 as db

    conn = db.connect('sarah.db')
    conn.row_factory = db.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM colors;')
    rows = cursor.fetchall()
    for row in rows:
        print ('%s' % (row['colorname']))
    conn.commit()
    cursor.close()
    conn.close()

I want now to get data from the table for printing.
With the def above, the word i have inserted into the table will be printed in the terminal, but i want to print it in a Label widget in Tkinter.
What shuld i do to print in Label widget?

[Gribouillis]

Perhaps label.config(text='value')

[Niloofar24]

No, again with the def show(), the color will be printed in the terminal not in the label widget.

[HiHe]

This might help:

''' tk_sqlite3_label.py
create a sqlite3 database and load it
read the database and put the resulting string into a Tkinter label
'''

import tkinter as tk
import sqlite3

root = tk.Tk()

label = tk.Label(width=40, height=10, relief='ridge', bg='yellow')
label.pack(padx=5, pady=3)

# list of [name, age, occupation] lists for testing
mylists = [
['John Jupiter', 15, 'Student'   ],
['Paul Park', 42, 'Casino Dealer'],
['Nick Noble', 30, 'Dentist'     ],
['Mark Moses', 75, 'WM Greeter'  ]
]

# create/connect to a permanent database file
conn = sqlite3.connect('data3.db')

# with takes care of conn.commit() and conn.close()
with conn:
    # establish the cursor, needed to execute the connected db
    cur = conn.cursor()
    # do this or it will keep adding data to existing data file
    cur.execute("DROP TABLE IF EXISTS data")
    # query language in upper case is optional
    # create the table
    cur.execute('''
    CREATE TABLE IF NOT EXISTS data
    (name TEXT, age INT, occupation TEXT)
    ''')

    # now load/insert the list of lists
    cur.executemany('INSERT INTO data VALUES (?,?,?)', mylists)

    # extract data into a label
    cur.execute("SELECT * FROM data")
    rows = cur.fetchall()
    data_str = ""
    sf = "{} age {} is a {}"
    for row in rows:
        data_str += sf.format(row[0], row[1], row[2]) + '\n'    
    label['text'] = data_str


root.mainloop()

[Niloofar24]

Thank you @HiHe, it helped me.

Now there is another problem now.
I have a Tkinter window with an Entry box and add button and show button. I want to enter a color everytime and add it into table in sqlite3 and i want to print color when i click the show button.

I have created this:

cursor.execute('CREATE TABLE IF NOT EXISTS colors (colorname TEXT)')

Now i only can enter one word and insert into the table and call it again to print in the label widget, only ONE word, but i want the database to keep all words, every colorname i enter and add everytime. And i want the show button to print all colornames everytime i click it.

The problem is that it seems the table keeps only one word. I want a database to keep one kind of value, only "colorname TEXT".
How should i change the code?

[Niloofar24]

It seems the table has only one row, how can i add more rows each time?

[Niloofar24]

No no!! wait!
Look at this:

def show():
    import sqlite3 as db

    conn = db.connect('sarah.db')
    conn.row_factory = db.Row
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM colors;')
    rows = cursor.fetchall()
    for row in rows:
        print ('%s' % (row['colorname']))
        data_str = (row['colorname']) + '\n'
        lbl['text'] = data_str
    conn.commit()
    cursor.close()
    conn.close()

I think the problem is related into the "for row in rows:" part.
With the print line as we see, all colornames i have inserted to the table will be printed in the terminal!

But with the data_str = (row['colorname']) + '\n' line i have typed, i get only the last word i have added to the table.
How should i change this line?

[Gribouillis]

There is a += in HiHe's code. Do you understand it ?

[Niloofar24]

Yes i understand and i tried it but i got this error:

Traceback (most recent call last):
  File "/usr/lib/python2.7/lib-tk/Tkinter.py", line 1535, in __call__
    return self.func(*args)
  File "./mydb", line 45, in show
    data_str += (row['colorname']) + '\n'
UnboundLocalError: local variable 'data_str' referenced before assignment

So i asked again.

Now it works because i have typed += and also data_str = "" wich i forgot to type before and was the cause of the problem.

[vegaseat]

Well, I am glad we solved this beginner problem.