Adding url to mysql string in python

Question

I am trying to add a url to a text string in mysql using python and the MySQLdb library, but when I run my code it says there is an error in my sql syntax. Can you tell me what I'm doing wrong?

Here is my code:

import MySQLdb as mdb connection = mdb.connect("Localhost", "root", "", "db") cursor = connection.cursor() url = mdb.escape_string("http://www.google.com") cursor.execute("""INSERT INTO index(url) VALUES(%s)""", (url,)) 

Here is the error:

 Traceback (most recent call last): File "C:\Python27\lib\threading.py", line 551, in __bootstrap_inner self.run() File "E:\prospector\webworker.py", line 77, in run cursor.execute("INSERT INTO index(url) VALUES('%s')", (url_t,)) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue ProgrammingError: (1064, "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 'index(url) VALUES('http://www.google.com/')' at line 1") 

Answer 1

I was able to reproduce your problem as follows:

 mysql> create table `index` (url varchar(50)); Query OK, 0 rows affected (0.05 sec) mysql> insert into index(url) values ('http://www.google.com'); ERROR 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 'index(url) values ('http://www.google.com')' at line 1 mysql> insert into `index`(url) values ('http://www.google.com'); Query OK, 1 row affected (0.00 sec) 

---

index is a keyword in MySQL. Your life will be easier if you do not use it as a table name. However, if you really want it, you can use it, but then you should quote it:

 cursor.execute("""INSERT INTO `index`(url) VALUES(%s)""", (url,)) 

---

PS: No need to call

 url = mdb.escape_string("http://www.google.com") 

MySQLdb will do this automatically for you when you call

 cursor.execute("""INSERT INTO index(url) VALUES(%s)""", (url,)) 

In fact, since cursor.execute calls mdb.escape_string for you, this can cause unwanted values ​​to be inserted into the database depending on the url value:

 In [105]: MySQLdb.escape_string("That all folks") Out[105]: "That\\ all folks" In [106]: MySQLdb.escape_string(MySQLdb.escape_string("That all folks")) Out[106]: "That\\\\\\ all folks"