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?

Recommended Answers

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

Jump to Post

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

Jump to Post

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

Jump to Post

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

Jump to Post

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 …
Jump to Post

All 17 Replies

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

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')

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.

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

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.

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

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

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?

Perhaps label.config(text='value')

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

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()
commented: good MWE +14

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?

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

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?

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

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.

Well, I am glad we solved this beginner problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.