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](https://stackoverflow.com/questions/7929364/python-best-practice-and-securest-way-to-connect-to-mysql-and-execute-queries) — Karl Knechtel, Feb 05 '23 at 12:02

Tim Biegeleisen · Accepted Answer · 2020-02-05T06:25:40.267

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 05 '20 at 06:25

answered Feb 05 '20 at 06:10

Tim Biegeleisen

502,043
27
286
360

,i edited as per your suggestion but still`What is your first name? hitu enter address:jopal 0 record inserted.`showing. – Adee Feb 05 '20 at 06:19
Try hard coding the values in the tuple; maybe there is a problem in some other part of your script. – Tim Biegeleisen Feb 05 '20 at 06:20
,when i try with `sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ('Peter', 'Lowstreet 4') mycursor.execute = (sql, val) `it is working – Adee Feb 05 '20 at 06:22
Please don't wrap `input` with `str()`, and follow [this question](https://stackoverflow.com/questions/70797/user-input-and-command-line-arguments) to see how to use `input()` in Python 3.x. – Tim Biegeleisen Feb 05 '20 at 06:26
,as per suggestion,i changed `name = input("What is your first name? ") address = input("enter address:") `still same prob arises`What is your first name? hilop enter address:tilta 0 record inserted.` – Adee Feb 05 '20 at 06:36
@Adee Would you believe it if I told you that you know more Python 3.x than I do? I don't know how to fix this problem, but your original question, which centered around the SQL, has been answered at least. – Tim Biegeleisen Feb 05 '20 at 06:38

Failed to insert user input into MySQL database

1 Answers1