How to quote a value in INSERT?

[Seung Won Jun]

I hope this posting is relevant here...

I'd like to do something like this:

txt = """a long, complex string"""
cursor.execute("""INSERT INTO tbl (a_long_text) VALUES ("%s")""" % txt)

The problem is the variable txt may contain quotation marks (") as well as escape characters (e.g., \n). I'd like to insert txt as is.

Maybe I could write a function that converts txt into an escaped form, but I'd like to avoid the conversion cost (txt is long). Any way to insert a value as is?

[Andy Dustman]

Quoting is automatic. Do not place quotes around placeholders.

txt = """a long, complex string"""
cursor.execute("""INSERT INTO tbl (a_long_text) VALUES (%s)""" % txt)

[Seung Won Jun]

Thanks, but it doesn't work. Would you see below? With quotes, it works.

>>> txt = "abc"
>>> cursor.execute("""INSERT INTO document (content) VALUES (%s)""" % txt)
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/net/hp48/jun/local/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "/net/hp48/jun/local/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'abc' in 'field list'")
>>> cursor.execute("""INSERT INTO document (content) VALUES ("%s")""" % txt)
1L

[Andy Dustman]

Sorry, I forgot to fix your second mistake:

cursor.execute("""INSERT INTO document (content) VALUES (%s)""" , (txt,))

[Seung Won Jun]

Aha. Thank you so much!