Error inserting an email string in sqlite3 and python Tkinter

Question

I am writing a simple program to update a basic db based on data entered on a simple GUI. I'm using string formatting but keep getting an error trying to enter an email address , which I know should be surrounded with double-quotes. I'm sure the solution is simple- I just don't know what it is!

def update_rec():
# Connect to the db
conn = sqlite3.connect("address_book.db")
# create a cursor
c = conn.cursor()
fields = ["f_name", "s_name", "mob", "email"]

# Check which textboxes have data
update_txt = ""
update_field = ""
rec_no = str(id_no.get())

if len(f_name.get()) > 0:
    update_txt = f_name.get()
    update_field = fields[0]
elif len(s_name.get()) > 0:
    update_txt = s_name.get()
    update_field = fields[1]
elif len(mob.get()) > 0:
    update_txt = mob.get()
    update_field = fields[2]
elif len(email.get()) > 0:
    update_txt = email.get()
    update_field = fields[3]
else:
    update_txt = ""
    update_field = ""

c.execute("""UPDATE address_book SET  {0} = ?  WHERE {1} = ?""".format(update_field, update_txt), rec_no)

conn.commit()
conn.close()

I keep getting this error:

c.execute("""UPDATE address_book SET {0} = ? WHERE {1} = ?""".format(update_field, update_txt), rec_no) sqlite3.OperationalError: near "@gmail": syntax error

mechanical_meat · Accepted Answer · 2020-04-22 13:59:31Z

2

What needs to be supplied to .format() is getting confused with what needs to be passed to c.execute().

Do it in two steps so it's easier to understand.

You need to tell us what rec_field should be. It's probably something like id or address_book_id or ?

rec_field = 'id' # you know what this should be...
qry = """UPDATE address_book 
         SET {0} = ? 
         WHERE {1} = ?;""".format(update_field, rec_field)
c.execute(qry, (update_txt,rec_no))

answered Apr 22, 2020 at 13:59

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

3 Comments

user2127168 Over a year ago

Thanks for your reply- I can see that this is clearer but how does it solve the entry of an email address string which is what I think the problem is? I want to set the email field(if data has been entered) to the value entered where the record number equals the one entered on the GUI.

mechanical_meat Over a year ago

You're welcome. The problem is two-fold. You're currently putting the email string in the wrong place in the query string, and you're not putting it in the place of the first placeholder where it should be. The sqlite3 library handles the quoting of the email string just fine so make sure you let it do its job.

user2127168 Over a year ago

Thanks again- I understand where I was going wrong now with the .format and query fields. I ended up doing it like this as I understand it better:c.execute("""UPDATE address_book SET {0} = ? WHERE oid = ?;""".format(update_field), (update_txt, rec_no))

Collectives™ on Stack Overflow

Error inserting an email string in sqlite3 and python Tkinter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related