[SOLVED] Failed to insert user input into MySQL database

Failed to insert user input into MySQL database

I have created a database and inserted some value using python manual code but when i tried to taking input from user then inserting that input to my database table,i failed as i tried many ways. Here is my code

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="Adee11ruchi@",
  database="hdatabase"
)

mycursor = mydb.cursor()
name= str(input("What is your first name? "))
address=str(input("enter address:"))

#mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mycursor.execute = ("""INSERT INTO customers (name, address) VALUES (r{}, r{})""".format(name, address))
#val = ('Peter', 'Lowstreet 4')



mydb.commit()
print(mycursor.rowcount, "record inserted.")

It showing me as

What is your first name? diyu
enter address:hiouy
-1 record inserted.

What is the issue,i failed to find out.

Solution

You should be using a prepared statement here. Consider this version:

mycursor = mydb.cursor(prepared=True)
name = input("What is your first name? ")
address = input("enter address:")

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
mycursor.execute = (sql, (name, address,))
mydb.commit()

The main takeaways points here are that you leave the values to be bound as parameters %s, and then you bind the values as a tuple in the call to cursor#execute. Note that the prepared statement API will handle the proper formatting of the inputs for you.