MySQL UPDATE with Python-CodePudding

I am trying to update records from my database named Template, Table clients. I get my updated information from a Tkinter Treeview. I am updating any field except user_id which is my primary key. I get a syntax error on cur.execute(sql_command). cur is define as my cursor.

# Function to Edit Record 
def edit_client():
# Update the Database
    print(user_id_box.get())
    sql_command = ("UPDATE clients SET \
        f_name = f_name_box.get(),\
        l_name = l_name_box.get(),\
        email = email_box.get(),\
        phone = phone_box.get(),\
        price = price_box.get(),\
        address = address_box.get(),\
        city = city_box.get(),\
        state = state_box.get(),\
        country = country_box.get(),\
        zipcode = zipcode_box.get() WHERE user_id = user_id_box.get()")

   # Execute the SQL command
   cur.execute(sql_command)
   # Commit the changes to the database
   mydb.commit()
   # Clear the old entry
   clear_record()
   # Refresh the Data Frame
   query_database()

CodePudding user response：

The simplest solution would be using f-string and braces to properly put values inside string. Here I also used docstrings, so you don't need backslashes.

def edit_client():
    print(user_id_box.get())
    sql_command = f"""UPDATE clients SET 
        f_name = {f_name_box.get()},
        l_name = {l_name_box.get()},
        email = {email_box.get()},
        phone = {phone_box.get()},
        price = {price_box.get()},
        address = {address_box.get()},
        city = {city_box.get()},
        state = {state_box.get()},
        country = {country_box.get()},
        zipcode = {zipcode_box.get()} WHERE user_id = {user_id_box.get()}"""

   cur.execute(sql_command)
   mydb.commit()
   clear_record()
   query_database()

HOWEVER I do strongly recommend you to sanitise your inputs and pass them as arguments to executor, as SQL injections are still a thing, even if you don't care about security, you can still simply break query, by having broken inputs.

I do not know which library you utilise in order to communicate with DB, but it's documentation should cover it, if it is not from early 1970, created by some student.

CodePudding user response：

Note that f_name.get() inside a string like "f_name = f_name.get()" will not work.

For your case, you can use placeholder (%s for MySQL) in SQL statement:

sql_command = f"""UPDATE clients
                  SET f_name = %s, l_name = %s,
                      email = %s, phone = %s,
                      price = %s, address = %s,
                      city = %s, state = %s,
                      country = %s, zipcode = %s
                  WHERE user_id = %s"""

cur.execute(sql_command, (f_name_box.get(), l_name_box.get(),
                          email_box.get(), phone_box.get(),
                          price_box.get(), address_box.get(),
                          city_box.get(), state_box.get(),
                          country_box.get(), zipcode_box.get(),
                          user_id_box.get()))