Keep getting error in trying to INSERT into MySQL

Question

I keep getting the same error everytime I try to INSERT data into a MySQL table in my Python script:

tools.cerabot@tools-login:~/wikitool-tasks2$ python didyouknow.py
didyouknow.py:62: Warning: Table 'did_you_know' already exists
  self.cursor.execute(self.create_query)
Traceback (most recent call last):
  File "didyouknow.py", line 121, in <module>
    test._parse_page()
  File "didyouknow.py", line 109, in _parse_page
    self.cursor.execute(record_exists.format(item["name"]))
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':Did you know nominations/Cirrus (song)' at line 1")

The code for this is openly viewable on GitHub, though you'd be particularly interested in lines 95 through 116. I've tried escaping and unicoding the string, modifying my query, nothing. (Admittedly, I'm a basic MySQL programmer.) Could anyone experienced in the area help me figure this out please?

@Joseph item["name"] is a wikipedia article title. They come in all shapes and sizes, funny characters abound. — ceradon
– ceradon, Commented Mar 27, 2014 at 12:07

Joseph · Accepted Answer · 2014-03-27 15:06:34Z

1

The problem is that the "funny characters" in your Wikipedia titles are messing with the SQL syntax. You need to handle that. This can be done by escaping, but the best practice is to use SQL parameterization, like so:

record_exists = u"SELECT COUNT(*) FROM did_you_know WHERE " \
                "name = %s"
# and later on
self.cursor.execute(record_exists, item["name"])

You're actually already doing this later on (line 112).

answered Mar 27, 2014 at 15:06

Joseph

13.3k21 gold badges79 silver badges117 bronze badges

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

Collectives™ on Stack Overflow

Keep getting error in trying to INSERT into MySQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related