1

I am trying to read in contents and write insert statements to create an sql script using Python.

I have come across an issue when the content I read contains an aprostrophe and I'm not sure how do I workaround this issue, because I can't just simply replace (') with any other characters as I'm dealing with asciimathml (Maths data) and I can't just change the contents I have read.

Many thanks!

XinYi Chua
  • 281
  • 1
  • 6
  • 17
  • 3
    If the apostrophe is a problem for you, you haven't *escaped* your data (which you must do before using it as part of a SQL query). See http://xkcd.com/327/ for an example of an input string which would be particularly... fun... for you to process unescaped. – Borealid Feb 19 '12 at 06:12
  • A quick search on "python sql parameters" found [this question](http://stackoverflow.com/questions/3410455/how-do-i-use-sql-parameters-with-python) - if that doesn't fit (using a different sql library?), then do a search including the name of your library and the word "parameter"/ – Damien_The_Unbeliever Feb 19 '12 at 06:31

1 Answers1

4

You can use one of the escape_string functions (every db package provides one) to double the single quote or precede it by a backslash. But you'll be far better off if you use parameter substitution. For example:

cursor.execute("INSERT INTO Test (column1, column2) VALUES (?, ?)", "It's all right", 45)

Do it that way and the server will ensure that your strings are escaped properly.

alexis
  • 48,685
  • 16
  • 101
  • 161