Re: Problems Writing £ (pound sterling) To MS SQL Server using pymssql

[MRAB]

On Nov 17, 2:52 pm, Darren Mansell <darren.mans... at opengi.co.uk>
wrote:
> Hi.
>
> I'm relatively new to python so please be gentle :)
>
> I'm trying to write a £ symbol to an MS SQL server using pymsssql . This
> works but when selecting the data back (e.g. using SQL management
> studio) the £ symbol is replaced with £ (latin capital letter A with
> circumflex).
>
> I can reproduce it like so:
>
> >>> con = pymssql.connect(host='testdb',user='testuser',password='password',database='test')
> >>> sql = "insert into table_1 values ('£')"
> >>> cur.execute(sql)
> >>> con.commit()
> >>> sql = "insert into table_1 values ('1')"
> >>> cur.execute(sql)
> >>> con.commit()
> >>> sql = "select * from table_1"
> >>> cur.execute(sql)
> >>> cur.fetchall()
>
> [('\xc2\xa3',), ('1',)]
>
> If I insert a £ symbol in using SQL management studio then select it back from Python I get this:
>
> [('\xc2\xa3',), ('1',), ('\xa3',)]
>
> If I look in SQL management studio it says this:
>
> £
>
> for the inserts I do through Python/pymssql.
>
> Does anyone have any ideas whats happening and how to fix it?
>
> Thanks
>
I recommend that you work with Unicode wherever possible. If pymssql
can't handle Unicode then use UTF-8 when talking to it. The result
should look something like this:

>>> con = pymssql.connect(host='testdb',user='testuser',password='password',database='test')
>>> sql = u"insert into table_1 values ('£')".encode("utf-8")
>>> cur.execute(sql)
>>> con.commit()
>>> sql = u"insert into table_1 values ('1')".encode("utf-8")
>>> cur.execute(sql)
>>> con.commit()
>>> sql = u"select * from table_1".encode("utf-8")
>>> cur.execute(sql)
>>> result = cur.fetchall()
>>> result
[('\xc2\xa3',), ('1',)]

You'll then need to decode from UTF-8:

>>> [tuple(field.decode("utf-8") for field in row) for row in result]
[(u'\xa3',), (u'1',)]
>>> print u'\xa3'
£