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

• ima.jab: @akina Sorry I wrote out my code incorrectly, I have now edited it to be correct.

Answer 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!

• ima.jab: 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: What version of MySQL are you using?
• ima.jab: I am using MySQL 8.0