Linking python GUI to MySQL database

Question

I'm a nube at the Python language and I'm having a hard time linking my Python GUI (in Tkinter) to a database. The layout is such that I want the end user to add Names to a database through an Entry widget. I tried to get the text to save to the database by using a variable, something like:

entr= Entry(frame1)
entr.grid(row=0, column=1)

var1=entr.get()

def save():
    """Save content in the entry box"""
    con = mdb.connect(host="localhost", user="root", passwd="xxxxxx", db="xxxxxxx")
    with con:
        cur=con.cursor()
        sql_update= "UPDATE Tests SET NAME='%s' WHERE id='%s'", (var1, id)
        cur.execute(sql_update)
        cur.close()
        con.commit()
        con.close()

this gives back the error message:

TypeError: query() argument 1 must be string or read-only buffer, not tuple

is there any way I can save data from the entry widget to the database without having to use var1 = raw_input("Name: ") somewhere else instead?

Thank you for your time! :)

ThiefMaster · Accepted Answer · 2012-03-28 09:12:46Z

4

Replace

    sql_update= "UPDATE Tests SET NAME='%s' WHERE id='%s'", (var1, id)
    cur.execute(sql_update)

with (preferred)

    sql_update= "UPDATE Tests SET NAME=%s WHERE id=%s"
    cur.execute(sql_update, (var1, id))

or

    sql_update= "UPDATE Tests SET NAME=%s WHERE id=%s", (var1, id)
    cur.execute(*sql_update)

If you wonder why your code did not work: Passing a tuple as a function argument will pass it as a single argument; while , creates tuples, it only does so if it's not inside a function declaration/call - in there it's the argument separator.

Using *sql_update unpacks the tuple into positional arguments so it works again. However, since you probably just use the variable to keep your code lines shorter, only put the SQL string in there and create the tuple inline when calling cur.execute().

edited Mar 28, 2012 at 9:12

answered Mar 27, 2012 at 23:01

ThiefMaster

320k85 gold badges608 silver badges648 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

butteredtoast Over a year ago

Thank you for your reply. I tried your methods and got no error this time, so I decided to test it:

ThiefMaster Over a year ago

I just noticed the quotes around %s - you need to remove them. The dbapi will take care of properly quoting your attributes (or pass them separately if supported by the database), so quoting them on your own will cause problems.

butteredtoast Over a year ago

I tried your methods and got no error this time, so I decided to test it using cur.execute("SELECT * FROM Tests") rows=cur.fetchall() for row in rows: print row The items added to the database look like this (1L, "'") (2L, "'") (3L, "'") (4L, "'") (5L, "'") which means it's the variable that has the problem. I know it should be painfully obvious but is it the way that I'm referencing var1=ent.get() that gives this output? Any other way to get the text from the entry widget? Your help is greatly appreciated.

ThiefMaster Over a year ago

See my comment from a minute ago; that probably broke your queries.

sigdelsanjog Over a year ago

Any reference sites to the learn linking python GUI with MySQL query?

Collectives™ on Stack Overflow

Linking python GUI to MySQL database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related