Encoding issue with SQL Server VARCHAR column retrieved in Python
SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).

The problem here is that your code is mistranslating that 0x82 character, thinking that it's UTF-8, but it's not. There is no UTF-8 "character" having a value of 0x82, which is why you get the "unknown" / replacement symbol of "�". Please see the following UTF-8 table which shows that there is no character for a single-byte of 0x82:

UTF-8 encoding table

As stated by the O.P., the Collation of the column in question is SQL_Latin1_General_CP1_CI_AS, which means that the 8-bit encoding is using Code Page 1252, which is Windows Latin 1 (ANSI). And checking that chart (scroll down to the bottom chart as it has the character names) value 0x82 (look for "82" in the "Code Point" column) is in fact the Single Low-9 Quotation Mark that you see in SSMS. That character, in UTF-8, is a 3 byte sequence: E2 80 9A.

What all of this means is: your Python code needs to either set the client-encoding for the SQL Server connection to Code Page 1252, or you need to change / convert the encoding of the returned string from Code Page 1252 to UTF-8.

Of course, if this is being displayed on a web page, then you could change the declared charset of the page to be Windows-1252, but that might interfere with other characters on the page if there are UTF-8 characters already there.