From Python to MySQL upload data failed

Question

The following table is my MySQL data of field

The table named infor

+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| id          | bigint(7)   | NO   | PRI | NULL              | auto_increment |
| temperature | varchar(20) | YES  |     | NULL              |                |
| wet         | varchar(20) | YES  |     | NULL              |                |
| created     | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+-------------+------+-----+-------------------+----------------+

When I want to upload data to my MySQL server

I use the following code:

temperature_item = '8.7`c'
wet_item = '0.87%'
cur.execute("INSERT INTO infor (temperature,wet) VALUES (temperature_item,wet_item)")

Then it shows the error following:

pymysql.err.InternalError: (1054, "Unknown column 'wet_item' in 'field list'")

But when I type:

cur.execute("INSERT INTO infor (temperature,wet) VALUES ('87`c','8.7%')")

It could work, and I have searched the previous question

Did I add the unprintable word? Or another problem?

did you try my answer?

e4c5
– e4c5

2016-12-17 11:56:27 +00:00
Commented Dec 17, 2016 at 11:56 — e4c5
– e4c5, Commented Dec 17, 2016 at 11:56
thx it worked !!!

Jonathan Cheng
– Jonathan Cheng

2016-12-17 12:59:47 +00:00
Commented Dec 17, 2016 at 12:59 — Jonathan Cheng
– Jonathan Cheng, Commented Dec 17, 2016 at 12:59

e4c5 · Accepted Answer · 2016-12-05 10:22:44Z

1

"INSERT INTO infor (temperature,wet) VALUES (temperature_item,wet_item)"

This is a python string literal. Your cur.execute statement is exactly equivalent to typing in the following at the mysql console.

INSERT INTO infor (temperature,wet) VALUES (temperature_item,wet_item)

Now do you have a wet_item defined in mysql? No so the above error is the result.

This is also a string literal

"INSERT INTO infor (temperature,wet) VALUES ('87c','8.7%')"

But here you are passing constants to mysql which it can deal with quite easily. If you wanted to pass your variable through to mysql the correct form is

cur.execute("INSERT INTO infor (temperature,wet) VALUES(%s, %s)", (temperature_item,wet_item))

answered Dec 5, 2016 at 10:22

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

4 Comments

Jonathan Cheng Over a year ago

thank you!!! this is very useful^^ but why after execute the sql statement ,the sql server didn't catch any data?

e4c5 Over a year ago

sorry I didn't understand that comment

Robin Koch Over a year ago

Execute cur.commit() after your last query.

e4c5 Over a year ago

@RobinKoch op hasn't said he is using transactions so I kept it simple

Daniel E. · Accepted Answer · 2016-12-05 10:23:10Z

0

I don't know python very well, but https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html, it seems you should pass your parameter when you execute it.

So if you try this :

query = ("INSERT INTO infor (temperature,wet) VALUES (%s,%s)")
temperature_item = '8.7`c'
wet_item = '0.87%'

cursor.execute(query, (temperature_item, wet_item))

answered Dec 5, 2016 at 10:23

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

2 Comments

Jonathan Cheng Over a year ago

thank you!!! this is very useful^^ but i don't know after execute the sql statement ,the sql server didn't catch any data?

Daniel E. Over a year ago

dev.mysql.com/doc/connector-python/en/… , did you commit ?

Robin Koch · Accepted Answer · 2016-12-05 10:37:28Z

Python has no variable expansion in strings. So in

cur.execute("INSERT INTO infor (temperature,wet) VALUES (temperature_item,wet_item)")

Python does not replace the variable names with it's values. (How is python even supposed to know when you want that?)

The general solution in python is the .format() method:

s = "World"
print("Hello {}".format(s))

which replaces the curly braces with the value of s.

However in this particular instance (working with databases) the better solution is

cur.execute("INSERT INTO infor (temperature,wet) VALUES(%s, %s)", (temperature_item,wet_item))

it's the way of the DB-interface to insert variables values. It's the better way, because it also makes sure this does not happen:

temperature_item = "87c"
wet_item = "8.7%'); DROP TABLE infor; --"
cur.execute("INSERT INTO infor (temperature,wet) VALUES('{}', '{}')".format(temperature_item, wet_item))

which would be bad.

Collectives™ on Stack Overflow

From Python to MySQL upload data failed

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related