Failed to insert user input into MySQL database

Question

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.

Does this answer your question? Python: best practice and securest way to connect to MySQL and execute queries — Karl Knechtel
– Karl Knechtel, Commented Feb 5, 2023 at 12:02

Tim Biegeleisen · Accepted Answer · 2020-02-05 06:25:40Z

1

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.

edited Feb 5, 2020 at 6:25

answered Feb 5, 2020 at 6:10

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

Adee Over a year ago

,i edited as per your suggestion but stillWhat is your first name? hitu enter address:jopal 0 record inserted.showing.

Tim Biegeleisen Over a year ago

Try hard coding the values in the tuple; maybe there is a problem in some other part of your script.

Adee Over a year ago

,when i try with

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"                   val = ('Peter', 'Lowstreet 4')                                      mycursor.execute = (sql, val)

it is working

Tim Biegeleisen Over a year ago

Please don't wrap input with str(), and follow this question to see how to use input() in Python 3.x.

Adee Over a year ago

,as per suggestion,i changed name = input("What is your first name? ") address = input("enter address:") still same prob arisesWhat is your first name? hilop enter address:tilta 0 record inserted.

|

Collectives™ on Stack Overflow

Failed to insert user input into MySQL database

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related