1

I am trying to write extended properties to each column of a table from python. Here is my code:

for i in dat.columns:
    sql_query = """\
                EXEC sys.sp_addextendedproperty
                     @name = N'Description',
                     @value = N'{}',
                     @level0type = N'Schema', @level0name = '{}',
                     @level1type = N'Table',  @level1name = '{}',
                     @level2type = N'Column', @level2name = '{}'
                """.format(dat[i][0], db, tb, i)

Here dat[i][0] contains the description for each column. However, one of the description is like balabalabala...student's program...balabalabal, where there is a single ' in the description and it cause an error.

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 's'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Unclosed quotation mark after the character string '\n                    '. (105)")

How to handle this problem?

Thank you!

Eleanor
  • 2,647
  • 5
  • 18
  • 30
  • Don't the usual ways of handling this work with Python? – Tab Alleman Oct 16 '18 at 17:57
  • 1
    Whenever I insert a string that contains a single-quote character in SQL Server, I use two consecutive single quotes. In your case, the description would be `Balabalabalabala... student''s program, balabalabala.` Let me know if that helps. – Brian Oct 16 '18 at 18:28
  • @Brian works very well. Make it a solution here. I will vote and make it the answer. :) Thank you! – Eleanor Oct 16 '18 at 18:45

2 Answers2

3

Please use CHAR(39) instead of ' in your code.

Juozas
  • 916
  • 10
  • 17
  • Curiously, why do you recommend using `Char(39)` (39 being the ASCII code of the single-quote) instead of the single-quote character itself? – Brian Oct 16 '18 at 20:16
  • 1
    Your case is pretty simple. But, if you have way complex string, concatenations or dynamic sql used, I strongly recommend to use char(39) instead to make code readable, safer. And, sometimes it is impossible to solve problems using N level of single quotes – Juozas Oct 17 '18 at 07:55
2

Whenever inserting a string that contains a single-quote character in SQL Server, use two consecutive single quotes. In your case, the description would be:

'Balabalabalabala... student''s program, balabalabala.'
Brian
  • 1,238
  • 2
  • 11
  • 17