0

I ave been trying to insert data to MYSQL table from python. The fields in my sql table are id, token, start_time, end_time, and no_of_trans. I want to store a token generated using uuid4 in the token column. But since the value contains (-), I'm getting this error: mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column '3900af75' in 'field list'' where the token generated is: 3900af75-4563-4c05-81ba-b0f1630abecc

And also I've tried inserting date in string format that also doesn't work. So I explicitly passed the date value as a string: mycursor.execute("""INSERT INTO generate (token, start_time, end_time, no_of_trans) VALUES (%s, %s, %s, %s)""" %(rand_token, '2020-04-20', '2020-04-20', 3))

Which inserts data into table but date is treated as a integer and the value that gets stored is 1996 which is nothing but 2020-04 from the date I've passed.

Here's the screenshot of my table: enter image description here

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| token       | varchar(255) | YES  |     | NULL    |                |
| start_time  | varchar(255) | YES  |     | NULL    |                |
| end_time    | varchar(255) | YES  |     | NULL    |                |
| no_of_trans | int(20)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
Karl Knechtel
  • 62,466
  • 11
  • 102
  • 153
Vishal A.
  • 1,373
  • 8
  • 19
  • String literals must be enclosed with single quote chars `'`. – Akina Apr 20 '20 at 11:59
  • And what is *INSERT INTO generate (**`12`**, start_time, end_time ...* ? – Akina Apr 20 '20 at 12:00
  • @Akina I think the problem here is the Python UUID. I'm not sure the statement API is smart enough to figure out how to bind it. – Tim Biegeleisen Apr 20 '20 at 12:07
  • @Akina, can you please be more specific? – Vishal A. Apr 20 '20 at 12:12
  • @TimBiegeleisen UUID transfer is specified as string `%s` - I doubt it is a problem to convert UUID to string for python... – Akina Apr 20 '20 at 12:13
  • If the problem is with UUID at least other fields should work when I'm explicitly passing the value in place of UUID. But, instead the dates with '```-'``` are still not working. – Vishal A. Apr 20 '20 at 12:18
  • 3
    I think the syntax you're looking for is `mycursor.execute("""INSERT INTO generate (token, start_time, end_time, no_of_trans) VALUES (%s, %s, %s, %s)""", (rand_token, '2020-04-20', '2020-04-20', 3))` note `,`, not `%` – Nick Apr 20 '20 at 12:54
  • 1
    See comment posted by @Nick. If you are going to do string formatting using the `%` operator as you are currently doing, then you need to enclose each `%s` that corresponds to a string value with quotes, i.e. `VALUES ('%s', '%s', '%s', %s)`. But the prepared statement, which prevents SQL Injection attacks when the input is "from the outside", is the way to go. – Booboo Apr 20 '20 at 14:02
  • See https://stackoverflow.com/questions/7929364/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries That is a question about SQL injection, but the answers are the clearest explanation of the solution you are looking for that I have seen. – Bill Karwin Apr 20 '20 at 15:17
  • Thanks @Nick, your solution worked for me!! – Vishal A. Apr 21 '20 at 05:41
  • @VishalA. no worries - I'm glad it helped. – Nick Apr 21 '20 at 06:06

1 Answers1

1

All but the last %s in your VALUES part need to be enclosed in single quotes as you are passing character strings. e.g. Without quotes 2020-04-20 becomes 1996.

But the actual problem seems to be to be the first unquoted %s, so that your rand_token variable value is interpreted as

3900af75 - 4563 - 4c05 - 81ba - b0f1630abecc

So "value of column 3900af75 minus literal number 4563 minus column with name 4c05 ..."

Hartmut Holzgraefe
  • 2,585
  • 12
  • 14