Problems Writing =?ISO-8859-1?Q?=A3?= (pound sterling) To MS SQLServer using pymssql

[Darren Mansell]

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='pass word',database= 'test')
>>sql = "insert into table_1 values ('£')"
>>cur.execute(s ql)
>>con.commit( )
>>sql = "insert into table_1 values ('1')"
>>cur.execute(s ql)
>>con.commit( )
>>sql = "select * from table_1"
>>cur.execute(s ql)
>>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

--
Darren Mansell <darren.mansell @opengi.co.uk>

[MRAB]

=?ISO-8859-1?Q?Re=3A_Probl ems_Writing_=A3 _=28pound_sterl ing=29_To_MS_SQ L_S?==?ISO-8859-1?Q?erver_using _pymssql?=

On Nov 17, 2:52 pm, Darren Mansell <darren.mans... @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='pass word',database= 'test')
>sql = "insert into table_1 values ('£')"
>cur.execute(sq l)
>con.commit()
>sql = "insert into table_1 values ('1')"
>cur.execute(sq l)
>con.commit()
>sql = "select * from table_1"
>cur.execute(sq l)
>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='pass word',database= 'test')
>>sql = u"insert into table_1 values ('£')".encode(" utf-8")
>>cur.execute(s ql)
>>con.commit( )
>>sql = u"insert into table_1 values ('1')".encode(" utf-8")
>>cur.execute(s ql)
>>con.commit( )
>>sql = u"select * from table_1".encode ("utf-8")
>>cur.execute(s ql)
>>result = cur.fetchall()
>>result

[('\xc2\xa3',), ('1',)]

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

>>[tuple(field.dec ode("utf-8") for field in row) for row in result]

[(u'\xa3',), (u'1',)]

>>print u'\xa3'

£