Mysql syntax error when inserting string using Python connector

Question

very new to both MySQL and Python so any help would be appreciated. This syntax error is driving me crazy because I feel like I'm following the tutorials exactly...

Here's my very simple DB:

>>> cursor.execute('DESCRIBE Sports_Master')
>>> cursor.fetchall()
[(u'ID', u'int(11)', u'NO', u'PRI', None, u'auto_increment'), (u'Name', u'varchar(255)', u'NO', u'', None, u'')]

What I'm attempting to do:

import mysql.connector
conn = mysql.connector.connect(**mysql_config)
cursor = conn.cursor()
test_name = 'Test'
insert_statement = ("INSERT INTO Sports_Master (Name) "
                    "VALUES (%s)"
                   )
cursor.execute(insert_statement, test_name)

And this is the resulting error:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 507, in execute
self._handle_result(self._connection.cmd_query(stmt))
  File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 722, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 640, in _handle_result
raise errors.get_exception(packet)
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

Is there something obvious that I'm doing wrong?

mgilson · Accepted Answer · 2015-01-27 00:18:42Z

1

IIRC, The values that you pass in are supposed to be a non-string sequences (or mappings if you want to use the %(name) syntax). tuples work nicely for what you're doing:

test_name = ('Test',)

edited Jan 27, 2015 at 0:18

answered Jan 27, 2015 at 0:01

mgilson

312k70 gold badges656 silver badges722 bronze badges

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

4 Comments

Sam WB Over a year ago

You're a star thank you. 0 out of the 5 tutorials I looked at specified that values had to be non-string sequences... would think that would be important to note! As for (Name) it just specifies that the value is being inserted into the Name column

Sam WB Over a year ago

Based on your edit, does this mean that VALUES (%(name)s) and test_name = {'name': 'Test'} would also work?

mgilson Over a year ago

@SamWB -- I doubt it (But you're welcome to try and let me know ;-). the DB has pretty stringent rules about where you can substitute what. I don't think you can do table names. I mean that: cursor.execute('INSERT INTO foo (bar) VALUES (%(value)s)', {'value': 'Test'}) should also work.

Sam WB Over a year ago

Apologies, my use of 'name' as the dict key was coincidental and I omitted the rest of the INSERT line out of laziness. Thanks again for the help

Collectives™ on Stack Overflow

Mysql syntax error when inserting string using Python connector

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related