How to add a record to a table in MySQL using python?

Question

In my code, I am trying to insert data into the 'user_attempts' table of my DB which has two fields: attemptID (auto-incremented) and username. Therefore when I'm passing data in I only have to pass the username as the attemptID is generated by MySQL. This my code:

username='test1'
add_userattempt=mycursor.execute('INSERT INTO user_attempt (username) VALUES %(currentuser)s', {'currentuser' :username})
mydb.commit()

However it returns this error:

Traceback (most recent call last):
  File "C:\Users\User\Desktop\project\AA game things\Iteration 1\review.py", line 266, in <module>
    reviewPage(screen) # the home screen function is called which essentially starts the whole program.
  File "C:\Users\User\Desktop\project\AA game things\Iteration 1\review.py", line 132, in reviewPage
    add_userattempt=mycursor.execute('INSERT INTO user_attempt (username) VALUES %(currentuser)s', {'currentuser' :username})
  File "C:\Users\User\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\User\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\connection.py", line 590, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\User\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\connection.py", line 478, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test1'' at line 1

@akina Sorry I wrote out my code incorrectly, I have now edited it to be correct. — ima.jab
– ima.jab, Commented Jan 21, 2020 at 11:39

sequence_hard · Accepted Answer · 2020-01-21 10:12:43Z

1

As shown by the error you get, you have a syntax error in your SQL code. Instead of

mycursor.execute('INSERT INTO user_attempt (username) VALUES %(currentuser)s', {'currentuser' :username})

it should be

mycursor.execute('INSERT INTO user_attempt(username) VALUES(%s)', username)

This webpage here (https://www.mysqltutorial.org/python-mysql-insert/) explains the whole procedure of inserting data into MySQL tables using python pretty well. Why do you declare your execute statement as a variable?

Hope this helps!

answered Jan 21, 2020 at 10:12

sequence_hard

5,41511 gold badges37 silver badges51 bronze badges

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

3 Comments

ima.jab Over a year ago

Hi, I have just tried editing that into my code and it still shows an error

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1

sequence_hard Over a year ago

What version of MySQL are you using?

ima.jab Over a year ago

I am using MySQL 8.0

Collectives™ on Stack Overflow

How to add a record to a table in MySQL using python?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related