python MySQLdb got invalid syntax when trying to INSERT INTO table

Question

COMMENT OUT below just for reference

"" cursor.execute (""" CREATE TABLE yellowpages ( business_id BIGINT(20) NOT NULL AUTO_INCREMENT, categories_name VARCHAR(255), business_name VARCHAR(500) NOT NULL, business_address1 VARCHAR(500), business_city VARCHAR(255), business_state VARCHAR(255), business_zipcode VARCHAR(255), phone_number1 VARCHAR(255), website1 VARCHAR(1000), website2 VARCHAR(1000), created_date datetime, modified_date datetime, PRIMARY KEY(business_id) ) """) ""

TOP COMMENT OUT (just for reference)

code

website1g = "http://www.triman.com" business_nameg = "Triman Sales Inc" business_address1g = "510 E Airline Way" business_cityg = "Gardena" business_stateg = "CA" business_zipcodeg = "90248" phone_number1g = "(310) 323-5410" phone_number2g = "" website2g = ""

cursor.execute (""" INSERT INTO yellowpages(categories_name, business_name, business_address1, business_city, business_state, business_zipcode, phone_number1, website1, website2) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s') """, (''gas-stations'', business_nameg, business_address1g, business_cityg, business_stateg, business_zipcodeg, phone_number1g, website1g, website2g))

cursor.close() conn.close()

I keep getting this error

File "testdb.py", line 51 """, (''gas-stations'', business_nameg, business_address1g, business_cityg, business_stateg, business_zipcodeg, phon e_number1g, website1g, website2g)) ^ SyntaxError: invalid syntax

any idea why?

Thanks for the help in advance

---

Update #2, I have removed the double single quote on the "categories_name", but now even

import MySQLdb

conn = MySQLdb.connect(host="localhost",port=22008,user="cholestoff",passwd="whoami",db="mydatabase")
cursor = conn.cursor()

Find mysql version

cursor.execute ("SELECT VERSION()")
row = cursor.fetchone()
print "server version:", row[0]

website1g = "http://www.triman.com"
business_nameg = "Triman Sales Inc"
business_address1g = "510 E Airline Way"
business_cityg = "Gardena"
business_stateg = "CA"
business_zipcodeg = "90248"
phone_number1g = "(310) 323-5410"
phone_number2g = ""

cursor.execute (""" INSERT INTO yellowpages(categories_name, business_name) VALUES ('%s','%s') """, ('gas-stations', business_nameg))

cursor.close()
conn.close()

still gotten this error

server version: 5.1.33-community
Traceback (most recent call last):
File "testdb.py", line 23, in
""", ('gas-stations', business_nameg))
File "C:\Python26\lib\site-packages\MySQLdb\cursors.py", line 173, in execute
self.errorhandler(self, exc, value)
File "C:\Python26\lib\site-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 y our MySQL server version for the right syntax to use near 'gas-stations'',''Triman Sales Inc'')' at line 2")

Thanks again for the help

Answer 1

I think your problem is here:

''gas-stations''

This gives a syntax error. You probably want to use one set of quotes:

'gas-stations'

If you want to insert the value 'gas-stations' into the database including the quotes then you can either escape the quotes or surround the string with double-quotes instead of single quotes:

"'gas-stations'"

The reason why the "up arrow" is pointing at the wrong place is because your lines are so long that it is wrapping on your console. Make your lines shorter, or widen your console window to see where the error really is.

Answer 2

You can't use doubled single-quotes (i.e. ''gas-stations'') - use either just single single-quotes ('gas-stations'), or actual double quotes ("gas-stations").

Answer 3

For your second problem, you need to lose all those single-quote characters in your VALUES clause ... should look like VALUES (%s,%s) not like VALUES ('%s','%s').

The general rules are very simple: for each parameter, have one place-marker (in the case of mySQLdb this is %s) in your SQL statement, and supply one Python expression in your tuple of parameters. Then lean back and let the interface software do the right thing for you. This includes quoting strings properly. Don't try to do it your self. In particular, string expressions should be exactly what you expect to retrieve later.

Example: The business_name of a gas station is "O'Reilly's Pump'n'Go" as a Python string constant. This will end up in the constructed SQL as ...VALUES(...,'O''Reilly''s Pump''n''Go',... without you having to think about it.